一、导致MySQL慢可能的因素有
1、计算资源不足
2、系统层面未进行基本的优化，或不同进程间资源抢占
3、MySQL配置不科学(附神器：http://imysql.com/my-cnf-wizard.html)
4、垃圾SQL满天飞
二、查看系统层面负载手段
1、top查看整体负载情况，快速确认哪个进程系负载高
2、free查看内存情况，是否有内存泄露和用了swap等风险
3、vmstat/sar查看当前系统瓶颈到底在哪，如CPU、IO、网络等
# sudo vmstat 1 procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r b swpd free buff cache si so bi bo in cs us sy id wa st 2 0 0 95740 136752 729052 0 0 0 4 8 4 0 0 100 0 0 0 0 0 95740 136752 729088 0 0 0 0 353 1038 1 0 99 0 0 0 0 0 95740 136752 729088 0 0 0 0 332 1016 1 1 98 0 0 0 0 0 95740 136752 729088 0 0 0 0 329 993 0 0 100 0 0 0 0 0 95616 136752 729088 0 0 0 0 345 1036 1 0 99 0 0 3 0 0 95616 136752 729088 0 0 0 68 340 1012 0 1 99 0 0
sar使用具体参考:https://www.cnblogs.com/howhy/p/6396437.html
sar -d 输出每一块磁盘的使用信息
# sar -d 1 10 Linux 3.10.0-514.26.2.el7.x86_64 (izuf60bp6kd88idp0no1urz) 04/17/2019 _x86_64_ (1 CPU) 08:44:37 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 08:44:38 PM dev253-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 08:44:38 PM DEV tps rd_sec/s wr_sec/s avgrq-sz avgqu-sz await svctm %util 08:44:39 PM dev253-0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sar -u 统计CPU的使用情况
# sar -u 1 10 Linux 3.10.0-514.26.2.el7.x86_64 (izuf60bp6kd88idp0no1urz) 04/17/2019 _x86_64_ (1 CPU) 08:49:16 PM CPU %user %nice %system %iowait %steal %idle 08:49:17 PM all 1.00 0.00 0.00 0.00 0.00 99.00 08:49:18 PM all 0.00 0.00 1.01 0.00 0.00 98.99
sar -n 查看网卡信息
DEV显示网络接口信息
EDEV显示关于网络错误的统计数据
NFS统计活动的NFS客户端的信息
NFSD统计NFS服务器的信息
SOCK显示套接字信息
ALL显示所有5个开关。它们可以单独或者一起使用。
# sar -n DEV 1 10 Linux 3.10.0-514.26.2.el7.x86_64 (izuf60bp6kd88idp0no1urz) 04/17/2019 _x86_64_ (1 CPU) 09:11:55 PM IFACE rxpck/s txpck/s rxkB/s txkB/s rxcmp/s txcmp/s rxmcst/s 09:11:56 PM eth0 1.00 1.00 0.06 0.15 0.00 0.00 0.00 09:11:56 PM lo 18.00 18.00 4.43 4.43 0.00 0.00 0.00
IFACE 本地网卡接口的名称
rxpck/s 每秒钟接受的数据包
txpck/s 每秒钟发送的数据库
rxKB/S 每秒钟接受的数据包大小，单位为KB
txKB/S 每秒钟发送的数据包大小，单位为KB
rxcmp/s 每秒钟接受的压缩数据包
txcmp/s 每秒钟发送的压缩包
rxmcst/s 每秒钟接收的多播数据包
其他略,具体参考
4、终极神器perf top查看cpu消耗在哪些系统调用函数
Linux 性能优化工具 perf top
参考: https://www.cnblogs.com/digdeep/p/4896235.html
案例: http://blog.chinaunix.net/uid-20785090-id-4282589.html
perf top -e xxx
1) perf top -e cpu-clock: 查看CPU的使用
2）perf top -e faults : 查看 page faults
3）perf top -e block:block_rq_issue : 查看系统IO的请求，比如可以在发现系统IO异常时，可以使用该命令进行调查，就能指定到底是什么原因导致的IO异常。 block_rq_issue 表示 block_request_issue 就是IO请求数。其实从这些可以看出，分析和调查Linux上的各种性能问题，需要我们对Linux内核有比较多的了解，不然恐怕是无从下手的。
三、查看MySQL的整体情况
1、观察show processlist输出中是否有临时表、排序、大量逻辑读、锁等待等状态
检查事务锁
-- mysql 5.6 information_schema 简称I_S innodb_trx innodb_locks innodb_lock_waits SELECT lw.requesting_trx_id AS request_XID, trx.trx_mysql_thread_id as request_mysql_PID , trx.trx_query AS request_query, lw.blocking_trx_id AS blocking_XID , trx1.trx_mysql_thread_id as blocking_mysql_PID, trx1.trx_query AS blocking_query , lo.lock_index AS lock_index FROM information_schema.innodb_lock_waits lw INNER JOIN information_schema.innodb_locks lo ON lw.requesting_trx_id = lo.lock_trx_id INNER JOIN information_schema.innodb_locks lo1 ON lw.blocking_trx_id = lo1.lock_trx_id INNER JOIN information_schema.innodb_trx trx ON lo.lock_trx_id = trx.trx_id INNER JOIN information_schema.innodb_trx trx1 ON lo1.lock_trx_id = trx1.trx_id limit 100 ;
2、观察show engine innodb status输出中是否有大事务、长事务、锁等待等状态
四、干掉垃圾SQL，常用手段
1、用explain、desc观察执行计划
2、用profiling定位sql执行的瓶颈
set profiling=1; //打开分析 run your sql1; run your sql2; show profiles; //查看sql1,sql2的语句分析 show profile for query 1; //查看sql1的具体分析 show profile ALL for query 1; //查看sql1相关的所有分析【主要看i/o与cpu,下边分析中有各项意义介绍】 set profiling=0; SHOW profile CPU,BLOCK IO io FOR query 1;
3、用pt-query-digest分析慢sql
pt-query-digest --user=root --password=admin /home/data/slowlog/slow.log
样例:
slow log 分析：
报告最近半个小时的慢查询：
./pt-query-digest --report --since 1800s /var/lib/mysql/slow.log
报告一个时间段的慢查询：
./pt-query-digest --report --since '2014-12-30 14:50:00' --until '2014-12-30 14:59:59' /var/lib/mysql/slow.log
报告只含select语句的慢查询:
./pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/slow.log
报告针对某个用户的慢查询:
./pt-query-digest --filter '($event->{user} || "") =~ m/^dbapp/i' /var/lib/mysql/slow.log
报告所有的全表扫描或full join的慢查询:
./pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' slow.log
把查询保存到query_review表
./pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log
把查询保存到query_history表
./pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history--create-review-table slow.log_20140401
./pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query_history--create-review-table slow.log_20140402
通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
./pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
./pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log
分析general log
./pt-query-digest --type=genlog localhost.log > slow_report11.log
五、几个窍门
1、mysqld进程消耗CPU长时间超过90%的话，99.9%是因为没用好索引
2、cpu的%sys高的话，大概率是swap或中断不均衡导致，也可能是有多个索引且超高并发写入(更新)，或者有很严重的锁等待事件
3、最大的瓶颈通常是在磁盘I/O上，因此尽量用高速磁盘设备
4、如果物理磁盘无法再升级，则通过增加内存提升性能容量
5、遇到无法诊断的问题时，试试perf top来观测跟踪
6、SQL执行慢，有时未必是效率低，也可能是因为锁等待，甚至是磁盘满了
详情戳：https://ke.qq.com/course/392646