【Mysql】Mysql负载过大，app访问延迟
收到线上某业务后端的MySQL实例负载比较高的告警信息，于是登入服务器检查确认
1. 首先我们进行OS层面的检查确认 此处)折叠或打开 top命令 [yejr@imysql.com:~ ]# toptop - 11:53:04 up 702 days, 56 min, 1 user, load average: 7.18, 6.70, 6.47Tasks: 576 total, 1 running, 575 sleeping, 0 stopped, 0 zombieCpu(s): 7.7%us, 3.4%sy, 0.0%ni, 77.6%id, 11.0%wa, 0.0%hi, 0.3%si, 0.0%st ----%us和%wa的值较高，表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。 Mem: 49374024k total, 32018844k used, 17355180k free, 115416k buffersSwap: 16777208k total, 117612k used, 16659596k free, 5689020k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 14165 mysql 20 0 8822m 3.1g 4672 S 162.3 6.6 89839:59 mysqld40610 mysql 20 0 25.6g 14g 8336 S 121.7 31.5 282809:08 mysqld 49023 mysql 20 0 16.9g 5.1g 4772 S 4.6 10.8 34940:09 mysqld
如果%us过高
1：有大量的排序工作
2：sql索引不合理查看慢日志，分析优化SQL
如果%sy过高
[root@HaoDai_App_DB01 ~]# iostat -x -m 2
Linux 2.6.32-504.16.2.el6.x86_64 (HaoDai_App_DB01) 03/18/2016 _x86_64_ (40 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
4.29 0.00 0.20 0.05 0.00 95.46
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 2.97 0.04 0.48 0.00 0.01 72.81 0.00 0.49 0.28 0.01
sdb 0.00 143.43 0.00 131.67 0.00 1.04 16.10 0.01 0.10 0.07 0.91
avg-cpu: %user %nice %system %iowait %steal %idle
12.54 0.00 0.38 0.03 0.00 87.06
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 174.00 0.00 137.50 0.00 1.17 17.40 0.03 0.19 0.14 1.95
如果，吞吐量（rMB/s+wMB/s)过低，但是util过高表示：随机io特别的严重（可用pt-ioprofile去定位导致问题出现的表） IOPS=R/s+W/s
再利用iotop确认到底哪些进程消耗的磁盘I/O资源最多：
一次请求读写的数据量太大，导致磁盘I/O读写值较大，例如一个SQL里要读取或更新几万行数据甚至更多，这种最好是想办法减少一次读写的数据量；SQL查询中没有适当的索引可以用来完成条件过滤、排序（ORDER BY）、分组（GROUP BY）、数据聚合（MIN/MAX/COUNT/AVG等），添加索引或者进行SQL改写吧；瞬间突发有大量请求，这种一般只要能扛过峰值就好，保险起见还是要适当提高服务器的配置，万一峰值抗不过去就可能发生雪崩效应； 因为某些定时任务引起的负载升高，比如做数据统计分析和备份，这种对CPU、内存、磁盘I/O消耗都很大，最好放在独立的slave服务器上执行；服务器自身的节能策略发现负载较低时会让CPU降频，当发现负载升高时再自动升频，但通常不是那么及时，结果导致CPU性能不足，抗不过突发的请求；使用raid卡的时候，通常配备BBU（cache模块的备用电池），早期一般采用锂电池技术，需要定期充放电（DELL服务器90天一次，IBM是30天），我们可以通过监控在下一次充放电的时间前在业务低谷时提前对其进行放电，不过新一代服务器大多采用电容式电池，也就不存在这个问题了。文件系统采用ext4甚至ext3，而不是xfs，在高I/O压力时，很可能导致%util已经跑到100%了，但iops却无法再提升，换成xfs一般可获得大幅提升；内核的io scheduler策略采用cfq而非deadline或noop，可以在线直接调整，也可获得大幅提升。基本如果%us使用过高 或者 %us和%iowait都高，一般都是并发时的sql写的不好导致的
用这个思路来分析一下我们生产上157负载高的原因（19:00持续到19:05）
SELECT COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, EVENT_NAME FROM performance_schema.events_waits_summary_global_by_event_name where COUNT_STAR > 0 and EVENT_NAME like 'wait/synch/%' order by SUM_TIMER_WAIT desc limit 10; +------------+------------------+----------------+--------------------------------------------+ | COUNT_STAR | SUM_TIMER_WAIT | AVG_TIMER_WAIT | EVENT_NAME |+------------+------------------+----------------+--------------------------------------------+ | 36847781 | 1052968694795446 | 28575867 | wait/synch/mutex/innodb/lock_mutex || 8096 | 81663413514785 | 10086883818 | wait/synch/cond/threadpool/timer_cond || 19 | 3219754571347 | 169460766775 | wait/synch/cond/threadpool/worker_cond || 12318491 | 1928008466219 | 156446 | wait/synch/mutex/innodb/trx_sys_mutex || 36481800 | 1294486175099 | 35397 | wait/synch/mutex/innodb/trx_mutex || 14792965 | 459532479943 | 31027 | wait/synch/mutex/innodb/os_mutex || 2457971 | 62564589052 | 25346 | wait/synch/mutex/innodb/mutex_list_mutex || 2457939 | 62188866940 | 24909 | wait/synch/mutex/innodb/rw_lock_list_mutex || 201370 | 32882813144 | 163001 | wait/synch/rwlock/innodb/hash_table_locks || 1555 | 15321632528 | 9853039 | wait/synch/mutex/innodb/dict_sys_mutex |+------------+------------------+----------------+--------------------------------------------+ 10 rows in set (0.01 sec)
1. 首先我们进行OS层面的检查确认 此处)折叠或打开 top命令 [yejr@imysql.com:~ ]# toptop - 11:53:04 up 702 days, 56 min, 1 user, load average: 7.18, 6.70, 6.47Tasks: 576 total, 1 running, 575 sleeping, 0 stopped, 0 zombieCpu(s): 7.7%us, 3.4%sy, 0.0%ni, 77.6%id, 11.0%wa, 0.0%hi, 0.3%si, 0.0%st ----%us和%wa的值较高，表示当前比较大的瓶颈可能是在用户进程消耗的CPU以及磁盘I/O等待上。 Mem: 49374024k total, 32018844k used, 17355180k free, 115416k buffersSwap: 16777208k total, 117612k used, 16659596k free, 5689020k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 14165 mysql 20 0 8822m 3.1g 4672 S 162.3 6.6 89839:59 mysqld40610 mysql 20 0 25.6g 14g 8336 S 121.7 31.5 282809:08 mysqld 49023 mysql 20 0 16.9g 5.1g 4772 S 4.6 10.8 34940:09 mysqld
如果%us过高
1：有大量的排序工作
2：sql索引不合理查看慢日志，分析优化SQL
如果%sy过高
[root@HaoDai_App_DB01 ~]# iostat -x -m 2
Linux 2.6.32-504.16.2.el6.x86_64 (HaoDai_App_DB01) 03/18/2016 _x86_64_ (40 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
4.29 0.00 0.20 0.05 0.00 95.46
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 2.97 0.04 0.48 0.00 0.01 72.81 0.00 0.49 0.28 0.01
sdb 0.00 143.43 0.00 131.67 0.00 1.04 16.10 0.01 0.10 0.07 0.91
avg-cpu: %user %nice %system %iowait %steal %idle
12.54 0.00 0.38 0.03 0.00 87.06
Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz avgqu-sz await svctm %util
sda 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sdb 0.00 174.00 0.00 137.50 0.00 1.17 17.40 0.03 0.19 0.14 1.95
如果，吞吐量（rMB/s+wMB/s)过低，但是util过高表示：随机io特别的严重（可用pt-ioprofile去定位导致问题出现的表） IOPS=R/s+W/s
再利用iotop确认到底哪些进程消耗的磁盘I/O资源最多：
一次请求读写的数据量太大，导致磁盘I/O读写值较大，例如一个SQL里要读取或更新几万行数据甚至更多，这种最好是想办法减少一次读写的数据量；SQL查询中没有适当的索引可以用来完成条件过滤、排序（ORDER BY）、分组（GROUP BY）、数据聚合（MIN/MAX/COUNT/AVG等），添加索引或者进行SQL改写吧；瞬间突发有大量请求，这种一般只要能扛过峰值就好，保险起见还是要适当提高服务器的配置，万一峰值抗不过去就可能发生雪崩效应； 因为某些定时任务引起的负载升高，比如做数据统计分析和备份，这种对CPU、内存、磁盘I/O消耗都很大，最好放在独立的slave服务器上执行；服务器自身的节能策略发现负载较低时会让CPU降频，当发现负载升高时再自动升频，但通常不是那么及时，结果导致CPU性能不足，抗不过突发的请求；使用raid卡的时候，通常配备BBU（cache模块的备用电池），早期一般采用锂电池技术，需要定期充放电（DELL服务器90天一次，IBM是30天），我们可以通过监控在下一次充放电的时间前在业务低谷时提前对其进行放电，不过新一代服务器大多采用电容式电池，也就不存在这个问题了。文件系统采用ext4甚至ext3，而不是xfs，在高I/O压力时，很可能导致%util已经跑到100%了，但iops却无法再提升，换成xfs一般可获得大幅提升；内核的io scheduler策略采用cfq而非deadline或noop，可以在线直接调整，也可获得大幅提升。基本如果%us使用过高 或者 %us和%iowait都高，一般都是并发时的sql写的不好导致的
用这个思路来分析一下我们生产上157负载高的原因（19:00持续到19:05）
SELECT COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT, EVENT_NAME FROM performance_schema.events_waits_summary_global_by_event_name where COUNT_STAR > 0 and EVENT_NAME like 'wait/synch/%' order by SUM_TIMER_WAIT desc limit 10; +------------+------------------+----------------+--------------------------------------------+ | COUNT_STAR | SUM_TIMER_WAIT | AVG_TIMER_WAIT | EVENT_NAME |+------------+------------------+----------------+--------------------------------------------+ | 36847781 | 1052968694795446 | 28575867 | wait/synch/mutex/innodb/lock_mutex || 8096 | 81663413514785 | 10086883818 | wait/synch/cond/threadpool/timer_cond || 19 | 3219754571347 | 169460766775 | wait/synch/cond/threadpool/worker_cond || 12318491 | 1928008466219 | 156446 | wait/synch/mutex/innodb/trx_sys_mutex || 36481800 | 1294486175099 | 35397 | wait/synch/mutex/innodb/trx_mutex || 14792965 | 459532479943 | 31027 | wait/synch/mutex/innodb/os_mutex || 2457971 | 62564589052 | 25346 | wait/synch/mutex/innodb/mutex_list_mutex || 2457939 | 62188866940 | 24909 | wait/synch/mutex/innodb/rw_lock_list_mutex || 201370 | 32882813144 | 163001 | wait/synch/rwlock/innodb/hash_table_locks || 1555 | 15321632528 | 9853039 | wait/synch/mutex/innodb/dict_sys_mutex |+------------+------------------+----------------+--------------------------------------------+ 10 rows in set (0.01 sec)
从上面的表可以确认，lock_mutex（在MySQL源码里对应的是lock_sys->mutex）的锁等待累积时间最长（SUM_TIMER_WAIT）。lock_sys表示全局的InnoDB锁系统，在源码里看到InnoDB加/解某个记录锁的时候（这个case里是X锁），同时需要维护lock_sys，这时会请求lock_sys->mutex。
在这个case里，因为在Searching rows for update的阶段频繁地加/解X锁，就会频繁请求lock_sys->mutex，导致lock_sys->mutex锁总等待时间过长，同时在等待的时候消耗了大量CPU。
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
