在运维MySQL时,经常遇到的一个问题就是活跃连接数飙升。一旦遇到这样的问题,都根据后台保存的processlist信息,或者连上MySQL环境,分析MySQL的连接情况。处理类似的故障多了,就萌生了一种想法,做个小工具,每次接到这种报警的时候,能够快速地从各个维度去分析和统计当前MySQL中的连接状态。比如当前连接的分布情况,活跃情况等等。
另外,真实故障处理时,光知道连接分布情况往往还不够,我们需要知道当前MySQL的正在忙于做什么,也就是正在执行一些什么样的SQL,而且,有时候即使我们知道了当前执行SQL的情况,也很难找到根因,因为如果活跃连接一旦飙升,这是CPU的基本上是处于被打满的状态,IO的负载也非常高,即使平时很快的SQL也变成了慢SQL,更不用说本身就很慢的SQL了。那我们怎么去甄别这些SQL里,哪些是导致问题的罪魁祸首,哪些仅仅是受害者呢?
带着这些需求和问题,本文逐渐展开并一一做分析和解答,展示我们这个小工具的功能。
想知道当前MySQL的连接信息,最直观的方法是看MySQL的processlist,如果希望看到完整的SQL,可以执行显示全部processlist,或者直接查information_schema中的processlist这个表。当MySQL中连接数比较少的时候,还能够人肉分析出来,可是如果连接数比较多,那就很难考肉眼看processlist去分析问题了。
最开始,我们的做法是写个脚本,用MySQL客户端在命令行登录MySQL,并执行显示全部processlist,然后将输出作为一个文本分析。本来这种实现方式在MySQL5.5和MariaDB上运行得很好,可是,当在MySQL5.6环境上运行时,出现了问题,在控制台输出中会多出一行警告:使用一个密码alt="玩转processlist,高效追溯MySQL活跃连接数飙升根因">
ID:线程ID,这个信息对统计来说没有太大作用
<李>用户:连接使用的账号,这个是一个统计维度,用于统计来自每个账号的连接数
<李>主持人:连接客户端的IP/主机名+网络端口号,这也是一个统计维度,用于确定发起连接的客户端
<李>DB:连接使用的默认数据库,DB通常对应具体服务,可以用于判断服务的连接分布,这算一个统计维度
<李>命令:连接的动作,实际上是说连接处于哪个阶段,常见的有睡眠,查询、连接、统计等,这也是一个统计维度,主要用于判断连接是否处于空闲状态
<李>时间:连接处于当前状态的时间,单位是年代,这个在后面进行分析,暂不算在连接状态的统计维度中
<李>状态:连接的状态,表示当前MySQl连接正在做什么操作,这算一个统计维度,可能的值也比较多,详细可以查阅官方文档
<李>信息:连接正在执行的SQL,这个在下一节分析,暂不算在连接状态的统计维度中
通过上面的分析,总结出了5个连接的统计维度:用户、主机、数据库、命令和状态。有了这5个统计维度,我们就可以开始着手写小工具了。
最基本的功能需求就是,查询information_schema.processlist这个表,然后按刚才总结的5个统计维度,对MySQL中的连接进行分组统计,按照统计个数排序.processlist这个表的主人字段需要做一些细节上的处理,因为它的值实际上是IP/主机名+网络端口号的组合,我们需要把端口号裁剪掉,这样才能按照客户端进行统计,否则每个客户端连接的端口号都是不一样的,没法进行分组统计。
最后的输出如下:
有了最基本的功能,能满足最基本的统计需求。可是在实际排查和处理线上问题时,可能并不关心所有的统计维度,只需要按照上述5个维度中的部分进行统计;另外,可能希望主机出现在用户的前面,优先按照客户端的IP或者是主机名进行统计,所以,这就要求这个工具具有增加灵活地添加或者删除统计维度的功能,而且能够对统计维度的出现顺序进行动态调整。
最后的示例输出如下:
