Mysql的qps高DB随时可能挂掉时的处理方法

导读

作者：杨漆
16年关系型数据库管理，从oracle 9i 、10g、11g、12c到Mysql5.5、5.6、5.7、8.0 到TiDB获得3个OCP、2个OCM；运维路上不平坦，跌过不少坑、熬过许多夜。把工作笔记整理出来分享给大伙儿，希望帮到大家少走弯路、少熬夜。

使用Mysql中如果CPU在95%及以上，Qps突然增到2万以上，这时Mysql随时有死去风险。

这时该怎么办？

应急方法：

第一: 先限制Innodb的并发处理.如果innodb_thread_concurrency = 0 可以先改成 16或64 看机器压力,如果 非常大,先改成16让机器的压力下来,然后慢慢增大,适应自已的业务.

set global innodb_thread_concurrency=16;

第二: 对于连接数已经超过600的情况,可以适当的限制一下连接数,宁可让前端报一下错,也别让DB挂. 只要DB活着总是可以用来加载一下数据,慢慢的DB压力也会降下来的. 限制单用户连接数在300以下

set global max_user_connections=300;

关闭 innodb_stats_on_metadata防止对读取information_schema时造成大量读取磁盘进行信息统计 （有些监控程序从这里抓取数据，会终止）

set global innodb_stats_on_metadata=0;

================================

应急处置完毕后进行RootCause分析：

思路：

1、确定高负载的类型 htop，dstat命令看负载高是CPU还是IO

2、监控具体的sql语句，是insert update 还是 delete导致高负载

3、检查mysql慢日志

打开慢查询方法：vi my.cnf,在[mysqld]如下几行:

log_slow_queries = /data/slow.log #慢查询日志路径

long_query_time = 1 #记录SQL查询超过1s的语句

log-queries-not-using-indexes = 1 #记录没有使用索引的sql

4、检查硬件问题

dstat

看具体哪个用户哪个进程占用了相关系统资源，当前CPU、内存谁在使用

1. dstat -l -m -r -c --top-io --top-mem --top-cpu

2. –io/total- ------memory-usage----- --most-expensive- ----most-expensive---- -most-expensive-

3. read writ| used buff cach free| memory process | i/o process | cpu process

4. 1.90 267 |3399M 178M 3892M 400M|php-fpm: poo 372M|init 1682k 647k|flush-202:0 0.1

5. 0 72.0 |3399M 178M 3892M 400M|php-fpm: poo 372M|php-fpm: po 10k 143k|php-fpm: pool2.0

6. 0 8.00 |3399M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 228k 229k|php-fpm: pool0.5

7. 0 88.0 |3399M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 102k 166k|php-fpm: pool 11

8. 0 38.0 |3399M 178M 3892M 399M|php-fpm: poo 372M|php-fpm: po 787k 650B|php-fpm: pool4.8

9. 0 0 |3399M 178M 3892M 399M|php-fpm: poo 372M|php-fpm: po 788k 723B|php-fpm: pool1.8

10. 0 140 |3400M 178M 3892M 399M|ph