MySQL thread cache

Recently, I was suddenly very interested in MySQL connections. According to the thread keyword, I can find the following status:

show global status   Variable_name      Value  Threads_cached          Threads_connected     Threads_created      Threads_running         

Thread_cached: The number of threads in the thread cache

Thread_connected: The number of currently open connections.

Thread_created: The number of threads created to handle connections.

Thread_running: The number of threads that are not sleeping.

 

The above is the meaning of the four States. thread_connected is equal to show processlist, and thread_running indicates that the actual running state (equal to 1 is generally the show status Command itself ), thread_cached indicates the number of resources that can be reused in the thread pool managed by mysql. thread_created indicates the newly created thread. (According to the official documentation, if thread_created increases rapidly, increase thread_cache_size appropriately ).

Let's take a look at the intuitive relationship between the four States.

From the figure above, we can sum up a formula: running has little to do with the other three States, but it will certainly not exceed thread_connected.

From the formula above, we can see that if create is equal to 0, the thread_connected reduction is equal to the thread_cached increase, and the thread_connected increase is equal to the thread_cached reduction. (In fact, this is the meaning of thread_cached. resources can be reused)

 

Let's take a look at the parameter thread_cache_size that affects thread_cached.

How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads there. Requests for threads are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. This variable can be increased to improve performance if you have a lot of new connections. Normally, this does not provide a notable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. For details, see Section 5.1.6, “Server Status Variables”.

As we all know, establishing a connection to mysql consumes a lot of resources, so it has thread_cache. When a connection is no longer in use, the mysql server does not directly disconnect the connection, but transfers the existing connection to thread_cache, in this way, the cache can be reused to improve performance and reduce resource consumption when you need to create thread.

Of course, if you already have middleware or other connection pool management, this parameter is not that important, but if there is no other connection pool management, so optimizing this parameter can still get a good return.