Soul ask: database connection pool exactly how to match?

From the public account: New World Grocery store

Dude, I’m straight dude! GitHub is the world’s largest gay dating site, with rich resources and high quality!

What principles should be used to configure connection pooling? That’s a question I wondered About for a long time, until I found About Pool Sizing on GitHub. After reading a sweep of haze in the heart of the author, refreshed. Mom no longer need to worry about my blind connection pool in the project!

The following contents are summarized according to the translation of the original text.

As mentioned in the article, developers often misconfigure connection pooling, and correctly configuring connection pooling requires an understanding of some principles, even if they may be counterintuitive.

10,000 concurrent users

Suppose you have a website that needs to handle 20,000 transactions per second. How big should your connection pool be configured? Surprisingly, the question should not be how big the connection pool configuration should be, but how small it should be.

Here’s a video from the Oracle Real World Performance group (I encourage you to take a ladder and see it for yourself) :

www.youtube.com/watch?v=xND…

The following is a brief summary of the content in the video.

Note: The video is a test of Oracle database. The author uses Mysql database in development/online environment, but by analogy, the test results are still of high reference value.

The initial configuration is as follows:

The author randomly captured the results of the first pressure measurement in the video as follows:

Sessions = 2048, queue-ms = 30ms, run-ms = 71ms, and a lot of buffer busy waits.

Set the initial thread pool size to 1024 and get the following result:

When Sessions reach 1024, queue-MS (38ms) is almost unchanged from the first session, run-MS (30ms) is significantly reduced from the first session, and buffer busy waits is significantly reduced from the first session.

Finally, set the thread pool size to 96, other things being equal, and you get the following result:

At this point, queue-MS and run-MS take extremely short time and see no buffer busy waits. Boy, the original author typed an ✅ on the result at this point.

Finally, compare the throughput of the three pressure tests:

The upper part of the figure is divided into time factor and the lower part is throughput. Red, blue, and yellow boxes represent throughput with link pool sizes of 2048, 1024, and 96, respectively. According to the line chart, the throughput increases significantly when the connection pool size is 96.

Without making any other adjustments, just reducing the connection pool size can increase the performance of your application by nearly 50 times!

But why?

How is it that nginx with only 4 threads is so much better than Apache Web server with 100 processes? Think back to the basics of computer science, and the answer is pretty obvious.

Even single-core computers can support dozens or hundreds of threads “simultaneously.” But we should all know that this is just a trick that the operating system executes alternately through time sharding. In fact, a single kernel can only execute one thread at a time, and then the operating system switches context to execute code for another thread, and so on. It is A basic law of computer science that given A CPU core, it is always faster to execute A and B sequentially than to execute A and B “simultaneously” through time sharding. Once the number of threads exceeds the number of CPU cores, increasing the number of threads makes the system slower, not faster.

The author believes that the sequential execution of A and B is faster than the simultaneous execution when there is no I/O blocking.

Limited resources

When we look at database performance bottlenecks, they can be generalized into three categories: CPU, disk, and network. The bandwidth of memory is several orders of magnitude higher than that of disk or network.

It’s even easier if we ignore disks and networks. On an 8-core server, setting the number of connections to 8 will provide the best performance, and increasing the number of connections will degrade performance due to context switch losses. But we can’t ignore disks and networks.

Databases typically store data on disk, and for older mechanical hard disks there are addressing time costs and rotation time costs. During this time (I/O wait), the connection/query/thread is blocked waiting for disk, at which point the operating system controls the CPU to execute other threaded code to make better use of CPU resources. So, since threads are blocking on I/O, we can have more threads/connections than the CPU core and get more done in the same amount of time.

How many connections should you set? It depends on the disk. Because the new SSDS do not require addressing and rotation overhead. Don’t assume at this point that “SSDS are faster, so we should have more threads”; on the contrary, faster means less blocking, so threads closer to the core will perform optimally. Only when blocking creates more opportunities for execution can more threads produce better performance.

A network is similar to a disk. Writing data to a line through an Ethernet interface can also cause blocking when the send/receive buffer fills up and stops. The latency of 10GB broadband is less than 1 GB, and the latency of 1 GB broadband is less than 100 MB. Networking is usually a secondary concern in terms of blocking, but it can still be overlooked in performance calculations.

To be honest, the following picture I studied for a long time did not understand its significance, but since the original post, I had to copy.

As you can see in the PostgreSQL benchmark above, TPS rates begin to level off at about 50 connections. In the Oracle video above, reduce the number of connections from 2048 to 96. But in reality, 96 is pretty high, too, unless the server is running a 16 – or 32-core processor.

A formula to calculate

Although the following formula is provided by PostgreSQL, we believe it can be applied to a wide variety of databases. You can use this formula to calculate an initial value from which to simulate the load and adjust the number of connections to find an appropriate number of connections.

Number of connections = ((Number of cores x 2) + Number of valid disks)

Over many years of benchmarking, the number of active connections that maintained optimal throughput came close to the formula. The core number should not contain hyperthreading, even if hyperthreading is enabled. If all the active data is cached, then the number of effective disks is 0, and as the cache hit ratio decreases, the number of effective disks gradually approaches the actual number of disks.

Note: There is no analysis of the effect of this formula on SSDS.

What does this formula mean? If you have a server with a disk and a quad-core I7 processor, the server’s connection pool size should be: 9 = ((4 * 2) +1), or an integer of 10. Does it look small? But give it a try, we bet with this setup it can easily handle 3000 front-end users performing simple queries at 6000TPS. If you increase the size of the connection pool and run load tests, you will find that the TPS rate starts to decrease as the front-end response time increases.

Axiom: You need a small queue full of threads waiting to connect

If you have 10,000 users, setting a connection pool of 10,000 is almost crazy, 1000 is still scary, and even 100 is too much. You need a small pool of a dozen connections at most, and the rest of the business threads are blocked until connections are available. The number of connections in the connection pool should be equal to the number of query tasks your database can efficiently perform simultaneously (usually no more than 2 CPU cores).

It is common to see a small web application with about a dozen concurrent users using a connection pool of 100 connections. Do not overconfigure the database.

A case in point: A reader who set the maximum number of free connections in the HTTP connection pool to 300 found frequent problems, and I persuaded him to use the default configuration and never called me again.

The connection pool size to avoid deadlocks

A single thread requiring multiple connections at the same time can cause deadlocks. This is largely a business problem that can be addressed by increasing the connection pool size. But before increasing the connection pool size, we strongly recommend that you first examine what you can do on the business side.

To avoid deadlocks, there is a simple formula for calculating connection pools:

Connections = Maximum number of threads * (maximum number of connections required by a single thread – 1) + 1

Suppose you have three threads and each thread needs four connections to perform some task. The pool size required to ensure that deadlocks never occur is:

3 x (4-1) + 1 = 10

👉 is not necessarily the optimal connection pool size, but rather the minimum connection pool size needed to avoid deadlocks.

Based on my own development experience, I hereby specially remind you:

1. In Go, a single coroutine should not operate with multiple connections at the same time.
2. Do not retrieve data during a transaction through a non-current transaction connection, instead, use the current transaction connection to retrieve data directly (using a non-current transaction connection to retrieve data during a transaction is equivalent to using two connections simultaneously).

advice

The size of the connection pool ultimately depends on the system characteristics.

For example, a system with a mix of long-duration and short-duration transactions is very difficult to tune using connection pooling. It is common practice to use two connection pools, one for long-term transactions and one for real-time queries.

In systems that run mainly long-duration transactions, there are often external constraints on connection pool size. For example, a task execution queue allows only a fixed number of tasks to run simultaneously. At this point, the size of the task queue should match the size of the connection pool, not the other way around.

Finally, I sincerely hope that the author’s translation can be of some help to all readers.