github.com/brettwooldr…
Welcome to pay attention to personal wechat public number: Ha learning Java, the end of the article to share “Java core knowledge collation & interview. PDF” resource link!!
Personal website: www.exception.site/essay/how-t…
directory
- I. Preface of the author
- Two, the beginning of the dinner
- Suppose your service has 10,000 concurrent accesses
- Four, why have this kind of effect?
- Other factors that should be taken into consideration
- 6. Calculation formula of connection number
- Conclusion: What you need is a small pool of connections and a queue of threads waiting for connections
- 8. Additional points that need to be noted
I. Preface of the author
Basically, most projects need to interact with the database, so what is the appropriate size for the database connection pool?
Some veteran developers will tell you: It doesn’t matter, try to set it to a large size, such as 200, so that the database performance will be higher, and the throughput will be higher!
You might nod yes, but is it true? After reading this article, maybe it will overturn your cognition!
Two, the beginning of the dinner
It’s pretty straightforward to say that every developer will fall into a hole at one point in the database connection pool. In fact, most programmers will probably rely on their gut instinct to set the database connection pool size to 100. After thinking for a long time, think for yourself, should be about the same?
Suppose your service has 10,000 concurrent accesses
Imagine that you have a website and you’re not as stressed as Facebook, but what? There is also a concurrency of about 10,000! That’s about 20,000 TPS.
So here’s the problem! How appropriate is the database connection pool for this site?
The correct way to ask this question is:
“How small should this site’s database connection pool be?”
PS: here is an Oracle performance group issued a brief video, links to www.dailymotion.com/video/x2s8u…
Sleep 550ms between each database operation. Note that the thread pool size was set to 2048 at the beginning of the video.
Let’s look at the ghostly performance test results for a database connection pool size of 2048:
Each request needs to wait for 33ms in the connection pool queue. After obtaining the connection, it takes 77ms to execute SQL, and the CPU consumption is maintained at about 95%.
Next, let’s change the size of the connection pool to 1024 and keep the other test parameters unchanged. What is the result?
“Here, the wait time to get a connection is basically the same, but the SQL execution time is reduced!”
Oh, you’ve made progress!
Let’s set the size of the connection pool to 96 and keep the number of concurrent connections unchanged.
The average waiting time for each request in the connection pool queue is 1ms and the SQL execution time is 2ms.
I go to! What the hell?
We didn’t adjust anything, just reduced the size of the database connection pool, so that the average response time from 100ms was reduced to 3ms. Throughput went up exponentially!
This is too easy for you!
Four, why have this kind of effect?
Why does Nginx’s internal use of 4 threads significantly outperform Apache HTTPD with 100 processes? If you think back to the basics of computer science, the answer is pretty obvious.
Remember, even a computer with a single CPU can run hundreds of threads “at the same time.” But as we all know, this is just a trick the operating system plays on us by switching time slices quickly.
A CPU core can only execute one thread at a time, and then the operating system switches context, and the CPU core quickly dispatches and executes code from another thread, over and over again, giving us the illusion that all processes are running at the same time.
In fact, on A single-cpu machine, it is always faster to execute A and B sequentially than to execute A and B “simultaneously” with A time slice switch, for reasons that should be obvious to anyone who has taken an operating system course. Once the number of threads exceeds the number of CPU cores, increasing the number of threads makes the system slower, not faster, because of the additional performance cost of context switching.
At this point, you should have taken a tumble…
Other factors that should be taken into consideration
The main reason was mentioned in the previous section, but it is not so simple. There are other factors that need to be considered.
When we look for database performance bottlenecks, we can generally fall into three categories:
- CPU
- Disk I/o
- Network IO
And memory, you might say, right? Memory is certainly something to consider, but it’s a bit more trivial compared to disk IO and network IO, so I won’t mention it here.
Assuming that we ignore disk IO and network IO, it is safe to say that on an 8-core server, setting the number of database connections/threads to 8 provides optimal performance, but increasing the number of connections will cause performance degradation due to context switching.
As you know, databases usually store data on disks, which are usually composed of rotating metal disks and a read/write head mounted on a stepper motor. The read/write header can only be in one location at a time, and when it needs to perform a read/write operation again, it must “address” to another location to complete the task. So? There’s the addressing time, and then there’s the rotation time, where the read/write head waits for the target data on the disk to “rotate into place” before it can read or write. Using caching can certainly improve performance, but the same principles still apply.
During this (“I/O wait “) time, the thread is in the “blocked” wait state, which means it is not doing anything! The operating system can then use this free CPU core to service other threads.
To sum up, when your threads are doing I/O intensive business, you can set the thread/connection count to be larger than the CPU core, so that more work can be done in the same amount of time, increasing throughput.
So the question is again?
What is the appropriate size?
It depends on the disk, if you’re using an SSD, it doesn’t need to address or rotate the disk. Hold on, hold on! Don’t take it for granted: “Since SSDS are faster, let’s make the thread count larger!!”
Just the opposite! No addressing and no loop time does mean less blocking, so fewer threads (closer to the number of CPU cores) will deliver better performance. Only when blocking is dense can more threads provide better performance.
Now that we’ve talked about disk IO, let’s talk about network IO!
Network IO is also very similar. Data reading and writing through an Ethernet interface is also blocked. The 10 GBIT/s bandwidth takes less time than the 1 gbit/s bandwidth, and the 1 GBIT/s bandwidth consumes less time than the 100 MBIT/s bandwidth. Typically, network IO is considered a third priority, but some people ignore the impact of network IO in performance calculations.
The graph above shows PostgreSQL’s benchmark performance test data. As you can see, TPS slows down at 50 connections. Thinking back, in the Oracle performance test video above, the testers reduced the number of connections from 2048 to 96, which is actually too high unless you have 16 or 32 CPU cores on your server.
6. Calculation formula of connection number
The following formula is provided by PostgreSQL, but the underlying principle is the same and applies to most database products on the market. Also, you should simulate the expected number of visits and use the following formula to set a reasonable value, and then fine-tune it to find the most appropriate number of connections for the actual test.
Number of connections = ((Number of cores x 2) + Number of valid disks)
The number of cores should not contain hyper threads, even if hyperthreads are enabled. If all hot data is cached, the number of effective disks is actually zero. As the cache hit ratio decreases, the number of effective disks gradually approaches the actual number of disks. It is also important to note that it is not clear how well this formula works on SSDS.
Well, according to this formula, if your server CPU is 4-core I7, the connection pool size should be ((4 * 2) + 1) = 9.
Let’s make it a whole, let’s make it 10. Can you do this? Ten is too small!
If you don’t think it’s too good, run a performance test and we can guarantee that it can easily support a scenario where 3000 users execute simple queries concurrently at 6000 TPS. You can also increase the connection pool size beyond 10, at which point you will see response times start to increase and TPS start to decrease.
Conclusion: What you need is a small pool of connections and a queue of threads waiting for connections
Let’s say you have 10000 concurrent accesses, and you set the connection pool size to 10000.
Let’s make it 1,000. Too high? Changed to 100? It’s still too much.
You just need a pool of 10 database connections and let the rest of the business threads wait in the queue.
The number of connections in the connection pool should be set to the number of query tasks that the database can effectively perform simultaneously (typically no more than 2 CPU cores).
You should often see web applications with small numbers of users set the database connection pool to 100 or 200 to accommodate a dozen or so concurrent requests. Do not overconfigure the size of your database connection pool.
8. Additional points that need to be noted
In fact, the size of the connection pool depends on the actual business scenario.
For example, if your system has a mixture of long and short transactions, it becomes difficult to calculate according to the above formula. The correct approach would be to create two connection pools, one for long transactions and one for “live” queries, that is, short transactions.
In another case, for example, if a system is running a task queue and the business requires that only a certain number of tasks be allowed to be executed at any one time, we should let the number of concurrent tasks match the number of connections in the pool, rather than the number of connections match the number of concurrent tasks.
Ref
Github.com/brettwooldr…
Free to share | interview & learning welfare resources
Recently found a good PDF resource on the Internet “Java core knowledge & interview. PDF” to share with you, not only interview, learning, you are worth it!!
Access: pay attention to the public number: xiahaxuejava, background reply resources, both free access to resources without routine links, the following is the directory and some screenshots:
Important things to say twice, pay attention to the public number: small ha learning Java, background reply resources, both free no routine access to resources links!!
Welcome to follow the wechat public number: Xiaoxue Java
