The relationship between MySQL Thread_cache and Thread_pool

The thread pool is a core feature of Mysql5.6, where high concurrent requests are always a topic for server applications, whether it is a Web application service or a DB service. When there are a large number of requests for concurrent access, it must accompany the constant creation and release of resources, resulting in low resource utilization and reduced service quality. The thread pool is a generic technique that, by pre-creating a certain number of threads, allocates a thread to serve the service when a request is reached, and then goes to service other requests when the request is finished. In this way, it avoids the frequent creation and release of Threads and memory objects, reduces the concurrency of the server, reduces the competition between the context and resources, and improves the efficiency of resource utilization. The thread pool of all services is essentially a bit more efficient in resource utilization and is implemented in roughly the same way. This article mainly explains the implementation principle of MySQL thread pool.

Before the advent of Mysql5.6, Mysql handled the connection in One-connection-per-thread, that is, for each database connection, Mysql-server creates a separate thread service that, after the request is finished, destroys the thread. One more connection request, then create a connection, and then destroy the end. This approach leads to frequent creation and release of threads in high concurrency situations. Of course, with Thread-cache, we can cache threads for next use, avoid frequently created and released problems, but cannot solve the problem of high number of connections. One-connection-per-thread mode as the number of connections increases, resulting in the need to create the same number of service threads, high concurrent threads mean high memory consumption, more context switching ( decreased CPU cache hit rate), and more resource contention, resulting in service jitter. In relation to one-thread-per-connection mode, a thread corresponds to a connection, in Thread-pool implementation, the minimum unit of thread processing is statement (statement), and one thread can handle multiple connection requests. In this way, the server jitter caused by the sudden increase in the number of transient connections can be avoided by ensuring that the hardware resources are fully utilized (setting the thread pool size reasonably).

The relationship between MySQL Thread_cache and Thread_pool