2015/11/09

mysql常用 show processlist 解决故障(转)


本文转自:
作者:黑·白
博客:[http://blog.xiayf.cn/2015/10/02/note-of-a-system-fault/)

前段时间,工作中遭遇一次故障,虽然不算什么“疑难杂症”,倒也花了不少时间才真正找到故障的原因,故也值得记录一下。

为方便读者快速理解故障,先给出系统大致的架构图:

mysql_process_01.png

每台 Web 服务器上开启12个 PHP-FPM 实例,并配置到 Nginx 的 upstream ,每个实例最多可以开启 10 个子进程。

Database Proxy 的代理规则为:写操作及事务中的所有SQL操作都交给主MySQL处理,其余的读操作都交给任意一台从MySQL处理


故障所表现现象

1. 大量请求响应为502,但每次故障发生时,错误响应一般集中在一台Web服务器。

mysql_process_02.png mysql_process_03.png

2. (一台或多台)MySQL数据库服务器CPU使用率飙升(但并非总是一起表现故障)。

mysql_process_04.png


问题分析解决

故障刚开始出现时,重启/关闭出现故障现象的MySQL服务,或将出现故障的Web服务器上所有PHP-FPM重启,也能解一时的问题,但治不了本,故障还是频繁出现。

在故障发生时,从相关服务器上收集到的信息如下所示:

1. 出现故障现象的Web服务器 - CPU使用率、内存使用率等系统指标均正常,但PHP-FPM子进程数达到上限(12 x 10 = 120),并且PHP-FPM进程与数据库代理服务器之间的网络连接数量较多(与PHP-FPM子进程数大致相当)

2. 出现故障现象的MySQL服务器 - CPU使用率飙升,主要为MySQL进程占用;MySQL进程与数据库代理服务器之间的网络连接较多

3. 继而,对出现故障现象的MySQL服务器上的数据库执行命令 SHOW PROCESSLIST(查看当前MySQL实例运行着哪些线程),结果如下所示(截图只是一部分结果):

mysql_process_05.png


先来分析一下 SHOW PROCESSLIST 的执行结果:

Command字段,表示当前线程正在执行的任务类型

db字段,表示当前线程所执行任务涉及的数据库是哪个

State字段,表示当前线程所处的状态

Time字段,表示当前线程处于State字段持续的时间,单位为秒

Info字段,表示如果当前线程是在执行查询操作(Query),那么查询的语句是什么样的,如非查询操作,则该字段为NULL

结果中,有两种任务线程:Binlog DumpQuery,其中 Query 数量占绝大多数(和MySQL进程与数据库代理服务器之间的网络连接数大致相当):


Binlog Dump: 该任务线程表明当前MySQL实例为主MySQL,并且其状态表明主从同步已顺利完成

Query: 表明当前线程正在执行一次SQL查询操作。该SQL为 SELECT h.host, p.result, p.update_time FROM PIXIU p join Host h using(host_id) WHERE ...,线程所处状 态为 Sorting result(正在创建排序索引),持续时间为 86-99 秒左右。很明显,这句SQL语句花费的时间过长,存在问题。

综合上面所述,可以引出一个猜测:由于这条SQL查询需耗费较长时间,并且被频繁执行,涉及该SQL的请求需要较长时间完成,大量SQL线程排队无响应,阻塞了大量PHP-FPM进程,在某些时候会 达到PHP-FPM并发子进程数上限(更何况某个会被频繁访问的页面请求涉及该SQL,导致情况更糟),PHP-FPM无法处理新的请求,对于已有的请求也会因为超时导致Nginx响应502。


那么针对该猜测,可以做两个优化来解决故障:

优化这条SQL

使用缓存

这条SQL的完整语句为: SELECT h.host,p.result,p.update_time FROM Pixiu p join Host h using(host_id) WHERE result!='[]' order by update_time desc

由于业务逻辑并不要求该SQL的结果是排序的,所以我们将该SQL中的排序条件 order by update_time desc 删除,经测试发现查询时间大幅度降低到 9ms 左右(原来的平均查询时间为600多-700ms左右), 另外。

由于业务逻辑对于该条SQL涉及的数据的实时性要求不高,我们使用 Memcached 缓存了该SQL的查询结果。

重新部署,压测,并线上运行观察,之后故障再未发生。事后回想,故障也确实是在涉及该SQL的功能模块上线之后才发生的。.