方法不是越高级越好。而应该善于做减法。至简是一种智慧，首先要做的是收集MySQL的各状态数据。收集到了，不管各个时间段出现的问题，至少你手上有第一时间的状态数据，而不是凭空想象的去做剖析和分析问题。
数据模拟参考
仿真多线程插入和查询语句，可以参考Python模拟数据(MySQL数据库)文档
备注:
当MySQL出现瞬时负载升高，IO负载增大的时候。SHOW FULL PROCESSLIST和pager more联用，可以立马查到当前MySQL线程中的一些问题。现在可以把SHOW FULL PROCESSLIST封装成一个脚本来执行，效果更佳。也说明MySQL的状态值的收集的重要性！
碰到问题，快速定位问题，把上述的状态命令进行封装。定位查找，脚本如下:
备注:
这里将来会写一个MySQL线上环境的一个实例《xxxxx》，来解释MySQL服务间歇性问题，如:MySQL服务卡壳或者慢查询。
#!/bin/sh
file=$(date +%F_%H_%M_%S)
PREFIX=ZMySQL-edition
loadavg="$(uptime)"
ts="$(date +"TS %s.%N %F %T")"
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW FULL PROCESSLIST\G‘ >> $PREFIX-${file}-processlist#!/bin/sh
file=$(date +%F_%H_%M_%S)
PREFIX=ZMySQL-edition
loadavg="$(uptime)"
ts="$(date +"TS %s.%N %F %T")"
echo "$ts $loadavg" >> $PREFIX-${file}-status
/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW GLOBAL STATUS‘ >> $PREFIX-${file}-status#!/bin/sh
file=$(date +%F_%H_%M_%S)
PREFIX=ZMySQL-edition
loadavg="$(uptime)"
ts="$(date +"TS %s.%N %F %T")"
echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW ENGINE INNODB STATUS\G‘ >> $PREFIX-${file}-innodbstatus#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/data/mysqldata/running
/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW GLOBAL VARIABLES‘ >> mysql-variables
while test -e $RUNFILE; do
file=$(date +%F_%H)
sleep=$(date +%s.%N | awk "{print $INTERVAL -(\$1 % $INTERVAL)}")
sleep 5
ts="$(date +"TS %s.%N %F %T")"
loadavg="$(uptime)"
echo "$ts $loadavg" >> $PREFIX-${file}-status
/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW GLOBAL STATUS‘ >> $PREFIX-${file}-status &
echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW ENGINE INNODB STATUS\G‘ >> $PREFIX-${file}-innodbstatus &
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
/usr/local/mysql/bin/mysql -uroot -p‘zsd@7101‘ -S /data/mysqldata/3306/mysql.sock -e ‘SHOW FULL PROCESSLIST\G‘ >> $PREFIX-${file}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exist.[root@racnode1 scripts]# ls -l
total 63192
-rw-rw-r-- 1 mysql mysql 5504846 Jun 15 18:00 5-sec-status-2018-06-15_17-innodbstatus
-rw-rw-r-- 1 mysql mysql 436374 Jun 15 18:00 5-sec-status-2018-06-15_17-processlist
-rw-rw-r-- 1 mysql mysql 7169459 Jun 15 18:00 5-sec-status-2018-06-15_17-status
-rw-rw-r-- 1 mysql mysql 2695460 Jun 15 18:26 5-sec-status-2018-06-15_18-innodbstatus
-rw-rw-r-- 1 mysql mysql 212897 Jun 15 18:26 5-sec-status-2018-06-15_18-processlist
-rw-rw-r-- 1 mysql mysql 3512080 Jun 15 18:26 5-sec-status-2018-06-15_18-status其中三个不同的文件分别为MySQL status，processlist和innodb status的具体信息。
#!/bin/sh
awk ‘
BEGIN{
printf "#ts date time load QPS";
fmt = " %.2f";
}
/^TS/{
ts = substr($2,1,index($2,".")-1);
load = NF - 2;
diff = ts - prev_ts;
prev_ts = ts;
printf "\n%s %s %s %s",ts,$3,$4,substr($load,1,length($load)-1);
}
/Queries/{
printf fmt,($2-Queries)/diff;
Queries=$2
}
‘ "$@"[root@racnode1 scripts]# ./analyze 5-sec-status-2018-06-15_17-status >>QPS-5-sec-0615日志信息如下:
#ts date time load QPS
1529053574 2018-06-15 17:06:14 0.49 0.00
1529053579 2018-06-15 17:06:19 0.53 1.00
1529053584 2018-06-15 17:06:24 0.49 1.20
1529053622 2018-06-15 17:07:02 0.52 0.39
1529053627 2018-06-15 17:07:07 0.48 1.20
1529053632 2018-06-15 17:07:12 0.44 1.20gnuplot为一个制图开源工具。可以通过yum install gnuplot安装。
gnuplot_mysql_status.sh 画图，并生成一个为MySQL_STATUS.png的图片。
#!/bin/bash
TITLE="MySQL_GLOBAL_STATUS"
OUTPUT="MySQL_STATUS.png"
PLOT="plot ‘QPS-5-sec-06161735‘ using 4 title‘LOAD‘ w lines,\
‘QPS-5-sec-06161735‘ using 5 title ‘QPS‘ w lines"
YRANGE="[0:]"
gnuplot << EOF
set terminal png small size 480,360
set title "$TITLE"
set xtics 60
# set mxtics 720
set yrange $YRANGE
set grid
set output "$OUTPUT"
$PLOT
EOF图片如下:
原文：https://www.cnblogs.com/zhangshengdong/p/9194691.html