MySQL中负载很高的排查思路分享

思路：

1、确定高负载的类型 htop，dstat命令看负载高是CPU还是IO
2、监控具体的sql语句，是insert update 还是 delete导致高负载
3、检查mysql日志
4、检查硬件问题

dstat

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

htop

htop是top的增强版，更直观

tcpdump


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

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

复制代码

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

或者

1. [root@cc ~]# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
   
2. while(<>) { chomp; next if /^[^ ]+[ ]*$/;
   
3. if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
   
4.     if (defined $q) { print "$qn"; }
   
5.     $q=$_;
   
6.     } else {
   
7.     $_ =~ s/^[ t]+//; $q.=" $_";
   
8.     }
   
9. }'
   

复制代码

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

strace

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

1. [root@cc ~]# strace -p 26578

复制代码

pt-query-digest

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

1. [root@cc ~]#  pt-query-digest slow.logs
   
2. # 390ms USER TIME, 10ms system TIME, 15.67M rss, 105.84M vsz  
   
3. # CURRENT DATE: Thu DEC 29 13:22:42 2014  
   
4. # Hostname: test   
   
5. # Files: slow.log  
   
6. # Overall: 776 total, 11 UNIQUE, 0.00 QPS, 0.00x concurrency _____________  
   
7. # TIME range: 2011-09-10 04:03:19 TO 2011-12-29 05:02:51  
   
8. # Attribute          total     MIN     MAX     avg     95%  stddev  median  
   
9. # ============     ======= ======= ======= ======= ======= ======= =======  
   
10. # EXEC TIME          5657s      2s     33s      7s     23s      6s      5s  
    
11. # LOCK TIME            33s       0     19s    43ms    98us   715ms    38us  
    
12. # ROWS sent        323.38k       0 107.36k  426.73    0.99   6.35k       0  
    
13. # ROWS examine     323.39k       0 107.36k  426.74       0   6.35k       0  
    
14. # Query SIZE       217.95k      38     562  287.61  420.77   81.78  284.79
    

复制代码

show processlist

查看系统到底在干什么

1. mysql> show full processlist;
   
2. +-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+
   
3. | Id        | User          | Host                | db                  | Command | Time | State         | Info                      |
   
4. +-----------+---------------+---------------------+---------------------+---------+------+---------------+---------------------------+
   
5. | 184498848 | testdb_rr1356 | 10.11.211.120:61343 | testdb_rr1356_db121 | Sleep   | 1384 |               | NULL                      |
   
6. | 184508740 | testdb_rr1356 | 10.11.211.120:11809 | testdb_rr1356_db121 | Sleep   |   87 |               | NULL                      |
   
7. | 184509415 | testdb_rr1356 | 10.11.211.120:12760 | testdb_rr1356_db121 | Query   |    0 | NULL          | show full processlist     |
   
8. | 184509451 | testdb_rr1356 | 10.11.211.120:12804 | testdb_rr1356_db121 | Sleep   |   10 |               | NULL                      |
   
9. | 184509528 | testdb_rr1356 | 10.11.211.120:12919 | testdb_rr1356_db121 | Query   |    0 | freeing items | DESCRIBE test_channel     |

复制代码

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

1. [code]innodb_flush_log_at_trx_commit 、innodb_buffer_pool_size 、key_buffer_size 等重要参数
   
2. mysql> show variables like '%innodb%';
   
3. +---------------------------------+----------------------------+
   
4. | Variable_name                   | Value                      |
   
5. +---------------------------------+----------------------------+
   
6. | have_innodb                     | YES                        |
   
7. | ignore_builtin_innodb           | ON                         |
   
8. | innodb_adaptive_flushing        | ON                         |
   
9. | innodb_adaptive_hash_index      | ON                         |
   
10. | innodb_additional_mem_pool_size | 2097152                    |
    
11. | innodb_autoextend_increment     | 8                          |
    
12. | innodb_autoinc_lock_mode        | 1                          |
    
13. | innodb_buffer_pool_size         | 2013265920                 |
    
14. | innodb_change_buffering         | inserts                    |
    
15. | innodb_checksums                | ON                         |
    

复制代码

通过show engine innodb status查看当前事务，内存使用

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. 
    
11. #此处具体sql省略
    
12. 
    
13. ----------------------
    
14. BUFFER POOL AND MEMORY
    
15. ----------------------
    
16. Total memory allocated 2058485760; in additional pool allocated 0
    
17. Dictionary memory allocated 819282
    
18. Buffer pool size   122879
    
19. Free buffers       97599
    
20. Database pages     24313
    
21. Old database pages 8954
    
22. Modified db pages  7
    
23. Pending reads 0
    
24. Pending writes: LRU 0, flush list 0, single page 0
    
25. Pages made young 6, not young 0
    
26. 0.00 youngs/s, 0.00 non-youngs/s
    
27. Pages read 1049, created 41853, written 30401604
    
28. 0.00 reads/s, 0.00 creates/s, 1.75 writes/s
    
29. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
    
30. Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    
31. LRU len: 24313, unzip_LRU len: 0
    
32. I/O sum[45]:cur[0], unzip sum[0]:cur[0]
    

复制代码

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

这样基本上就可以把问题找出来了