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|php-fpm: poo 372M|nginx: work 38k 154k|php-fpm: pool1.2

11. 0 12.0 |3400M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 178k 364k|php-fpm: pool1.5

12. 0 0 |3400M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 758k 639k|php-fpm: pool1.5

13. 0 12.0 |3400M 178M 3892M 399M|php-fpm: poo 372M|nginx: work 773k 616k|php-fpm: pool2.0

14. 6.00 0 |3401M 178M 3892M 398M|php-fpm: poo 372M|nginx: work 994k 688k|nginx: worker1.5

15. 0 272 |3401M 178M 3892M 398M|php-fpm: poo 372M|nginx: work 388k 422k|php-fpm: pool1.5

16. 0 0 |3400M 178M 3893M 398M|php-fpm: poo 372M|nginx: work 483k 548k|php-fpm: pool1.8

17. 0 4.00 |3400M 178M 3893M 398M|php-fpm: poo 372M|php-fpm: po 787k 650B|php-fpm: pool1.5

18. 0 12.0 |3400M 178M 3893M 398M|php-fpm: poo 372M|nginx: work 223k 323k|php-fpm: pool1.5

19. 0 0 |3400M 178M 3893M 398M|php-fpm: poo 372M|nginx: work 371k 474k|php-fpm: pool7.8

Htop  ( htop是top的增强版，更直观) 

1. # htop

2. 1 [||||||||||| 12.4%]

3. 2 [||||||||| 9.5%]

4. 3 [| 1.0%]

5. 4 [|| 1.9%]

6. Mem[|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||3394/7869MB]

7. Swp[|||||||||||||| 75/478MB]

8. Tasks: 71, 12 thr; 2 running

9. Load average: 0.39 0.39 0.31

10. Uptime: 319 days(!), 19:01:17

11. PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command

12. 1 root 20 0 19232 396 248 S 0.0 0.0 0:01.86 /sbin/init

13. 30752 root 20 0 52532 72 56 S 0.0 0.0 0:00.16 ├─ /usr/sbin/vsftpd /etc/vsftpd/vsftpd.conf

14. 24301 root 20 0 193M 3268 1600 S 0.0 0.0 1:41.43 ├─ /usr/sbin/snmpd -LS0-6d -Lf /dev/null -p /var/run/snmpd.pid

15. 21361 root 20 0 902M 6500 1308 S 0.0 0.1 0:07.16 ├─ php-fpm: master process (/etc/php-fpm.conf)

16. 28627 www 20 0 962M 202M 138M S 0.0 2.6 0:34.46 │ ├─ php-fpm: pool www-c

17. 27537 www 20 0 965M 236M 171M R 1.4 3.0 1:19.64 │ ├─ php-fpm: pool www-c

18. 27449 www 20 0 961M 251M 189M S 0.0 3.2 1:35.54 │ ├─ php-fpm: pool www-a

19. 26442 www 20 0 962M 280M 217M S 0.0 3.6 2:29.71 │ ├─ php-fpm: pool www-a

20. 26310 www 20 0 917M 251M 234M S 1.9 3.2 2:46.45 │ ├─ php-fpm: pool www-a

21. 26162 www 20 0 962M 297M 233M S 0.0 3.8 2:37.50 │ ├─ php-fpm: pool www-b

22. 26147 www 20 0 924M 258M 233M S 0.0 3.3 2:38.37 │ ├─ php-fpm: pool www-c

23. 25717 www 20 0 965M 302M 238M S 0.0 3.8 2:54.50 │ ├─ php-fpm: pool www-c

24. 24585 www 20 0 964M 324M 260M S 0.0 4.1 4:15.20 │ ├─ php-fpm: pool www-b

tcpdump

抓取mysql包分析，一般抓3306端口的数据

[root@cc ~]# tcpdump -i eth0 -A -s 3000 port 3306 > ~/sql.log

然后使用awk,sort,wc 等命令进行分析

或 用 # tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e 'while(<>) { chomp; next if /^[^ ]+[ ]*$/;

if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {if (defined $q) { print "$qn"; }$q=$_;} else {$_ =~ s/^[ t]+//; $q.=" $_";}}'

就可以看出最繁忙的sql语句

strace 或 pstack 查看系统调用是否有问题，进程是否堵塞，是否有Broken pipe

[root@cc ~]# strace -p 26578

[root@cc ~]# pstack 26356

分析mysql慢日志，查看哪些sql语句最耗时

 # pt-query-digest slow.logs

1. # 390ms USER TIME, 10ms system TIME, 15.67M rss, 105.84M vsz

2. # CURRENT DATE: Thu DEC 29 13:22:42 2014

3. # Hostname: test

4. # Files: slow.log

5. # Overall: 776 total, 11 UNIQUE, 0.00 QPS, 0.00x concurrency _____________

6. # TIME range: 2021-07-30 04:03:19 TO 2021-07-3005:02:51

7. # Attribute total MIN MAX avg 95% stddev median

8. # ============ ======= ======= ======= ======= ======= 

9. # EXEC TIME 5657s 2s 33s 7s 23s 6s 5s

10. # LOCK TIME 33s 0 19s 43ms 98us 715ms 38us

11. # ROWS sent 323.38k 0 107.36k 426.73 0.99 6.35k 0

12. # ROWS examine 323.39k 0 107.36k 426.74 0 6.35k 0

13. # Query SIZE 217.95k 38 562 287.61 420.77 81.78 284.79

查看系统到底在干什么

 mysql> show full processlist;

1. +-----------+---------------+---------------------+

2. | Id | User | Host | db | Command | Time | State | Info |

3. +-----------+---------------+---------------------+

4. | 184498848 | testdb_rr1356 | 10.11.211.120:61343 | testdb_rr1356_db121 | Sleep | 1384 | | NULL |

5. | 184508740 | testdb_rr1356 | 10.11.211.120:11809 | testdb_rr1356_db121 | Sleep | 87 | | NULL |

6. | 184509415 | testdb_rr1356 | 10.11.211.120:12760 | testdb_rr1356_db121 | Query | 0 | NULL | show full processlist |

7. | 184509451 | testdb_rr1356 | 10.11.211.120:12804 | testdb_rr1356_db121 | Sleep | 10 | | NULL |

8. | 184509528 | testdb_rr1356 | 10.11.211.120:12919 | testdb_rr1356_db121 | Query | 0 | freeing items | DESCRIBE test_channel |

检查mysql配置参数是否有问题，引起大量的IO或者高CPU操作

innodb_flush_log_at_trx_commit、innodb_buffer_pool_size 、key_buffer_size 等重要参数

1. mysql> show variables like '%innodb%';

2. +-----------------------------+----------------------------+

3. | Variable_name | Value |

4. +-----------------------------+----------------------------+

5. | have_innodb | YES |

6. | ignore_builtin_innodb | ON |

7. | innodb_adaptive_flushing | ON |

8. | innodb_adaptive_hash_index | ON |

9. | innodb_additional_mem_pool_size | 2097152 |

10. | innodb_autoextend_increment | 8 |

11. | innodb_autoinc_lock_mode | 1 |

12. | innodb_buffer_pool_size | 2013265920 |

13. | innodb_change_buffering | inserts |

14. | innodb_checksums | ON |

查看当前事务，内存使用情况

1. mysql> show engine innodb status \G

2. LATEST DETECTED DEADLOCK

3. ------------------------

4. 150731 10:36:50

5. *** (1) TRANSACTION:

6. TRANSACTION EBFBBEC, ACTIVE 0 sec, process no 20691, OS thread id 47345217033984 inserting

7. mysql tables in use 1, locked 1

8. LOCK WAIT 5 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 2

9. MySQL thread id 143249904, query id 1286731854 10.135.21.120 tybuser2014 update

10. BUFFER POOL AND MEMORY

11. ----------------------

12. Total memory allocated 2058485760; in additional pool allocated 0

13. Dictionary memory allocated 819282

14. Buffer pool size 122879

15. Free buffers 97599

16. Database pages 24313

17. Old database pages 8954

18. Modified db pages 7

19. Pending reads 0

20. Pending writes: LRU 0, flush list 0, single page 0

21. Pages made young 6, not young 0

22. 0.00 youngs/s, 0.00 non-youngs/s

23. Pages read 1049, created 41853, written 30401604

24. 0.00 reads/s, 0.00 creates/s, 1.75 writes/s

25. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

26. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

27. LRU len: 24313, unzip_LRU len: 0

28. I/O sum[45]:cur[0], unzip sum[0]:cur[0]

最后通过zabbix或者cacti等监控来查看IO、CPU、MEMORY、磁盘等是否有异常

通过以上基本就可以把问题找出来了