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
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中复用,提高性能,降低资源消耗。
当然,如果已经有了中间件或者其他的连接池管理,那么这个参数就没有那么重要了,但是如果没有其他的连接池管理,那么优化这个参数还是可以得到不错的回报的。
最新文章
- 复习sql server
- C#软件设计——小话设计模式原则之:单一职责原则SRP
- 20169212《Linux内核原理及分析》第十二周作业
- SQL Server SQL分页查询
- List.Sort用法
- XMl入门介绍及php操作XML
- 遍历寻找json中的重复数据
- date &; dirname
- 【转】color颜色十六进制编码大全
- Python之路第十一天,高级(3)-线程池
- java字符串比较
- 一日一练-JS toString 和valueOf 方法的联系与区别
- 高通 android平台LCD驱动分析
- spring boot slf4j日记记录配置详解
- thingsboard填坑之路
- 挑选队友 (生成函数 + FFT + 分治)
- thinkphp验证码不显示
- [Converge] Gradient Descent - Several solvers
- TensorRT 进行推理
- zend framwork项目基本操作
热门文章
- Linux下文件目录权限和对应命令的总结
- static作用(修饰函数、局部变量、全局变量)转自http://www.cnblogs.com/stoneJin/archive/2011/09/21/2183313.html
- IT人员必备linux安全运维之Ssh用途、安全性、身份认证以及配置……【转】
- python使用unittest模块selenium访问斗鱼获取直播信息
- BZOJ 3958 Mummy Madness
- 铁器 · Burp Suite
- scala学习7--class、object、trait
- javscript练习(三)
- Mysql聚合函数count(*) 的性能分析
- html5弹性布局两则,有交互。