MySQL之thread cache

最近突然对MySQL的连接非常感兴趣，从status根据thread关键字可以查出如下是个状态

show global status like 'thread%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 57    |
| Threads_connected | 1268  |
| Threads_created   | 31715 |
| Threads_running   | 1     |
+-------------------+-------+

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.

 

　　以上是这4个状态的含义，thread_connected等于show processlist，thread_running代表真正在运行的（等于1一般就是这个show status命令本身），thread_cached代表mysql管理的线程池中还有多少可以被复用的资源，thread_created代表新创建的thread（根据官方文档，如果thread_created增大迅速，需要适当调高thread_cache_size）。

　　我们先来实际看下这4个状态之间的直观关系。

　　从上面这个图，我们可以总结出来一个公式：running和其他三个状态关系不大，但肯定不会超过thread_connected

　　(new_con-old_con)=create+(old_cache-new_cache)

　　从上面公式可以看出，如果create等于0，那么thread_connected减少的和thread_cached增加的相等，thread_connected增加的和thread_cached减少的相等。（其实这也就是thread_cached存在的意义，资源可以复用）

 

　　我们来看眼影响thread_cached的参数thread_cache_size

1

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”.

　　众所周知，mysql建立连接非常消耗资源，所以就有了thread_cache，当已有连接不再使用之后，mysql server不是直接断开连接，而是将已有连接转入到thread_cache中，以便下次在有create thread的需求时，可以在cache中复用，提高性能，降低资源消耗。　　

　　当然，如果已经有了中间件或者其他的连接池管理，那么这个参数就没有那么重要了，但是如果没有其他的连接池管理，那么优化这个参数还是可以得到不错的回报的。