MySQL基准测试(二)--方法

目的：

方法不是越高级越好。而应该善于做减法。至简是一种智慧，首先要做的是收集MySQL的各状态数据。收集到了，不管各个时间段出现的问题，至少你手上有第一时间的状态数据，而不是凭空想象的去做剖析和分析问题。

数据模拟参考

仿真多线程插入和查询语句，可以参考Python模拟数据(MySQL数据库)文档

MySQL状态命令的解释

• SHOW GLOBAL VARIABLES 查看目前的MySQL的各个参数变量值。
• SHOW GLOBAL STATUS 查看全局的MySSQL的状态信息。
• SHOW FULL PROCESSLIST 查看当前MySQL当前进程运行的具体信息。
• SHOW ENGINE INNODB STATUS 查看InnoDB当前的状态信息

备注:

当MySQL出现瞬时负载升高，IO负载增大的时候。SHOW FULL PROCESSLIST 和pager more联用，可以立马查到当前MySQL线程中的一些问题。现在可以把SHOW FULL PROCESSLIST 封装成一个脚本来执行，效果更佳。也说明MySQL的状态值的收集的重要性！

脚本封装

碰到问题，快速定位问题，把上述的状态命令进行封装。定位查找，脚本如下:

备注:

这里将来会写一个MySQL线上环境的一个实例《xxxxx》，来解释MySQL服务间歇性问题，如:MySQL服务卡壳或者慢查询。

• exec_mysql_processlist.sh 打出当前进程运行的具体信息

#!/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

• exec_mysql_status.sh 打出当前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}-status

/usr/local/mysql/bin/mysql -uroot -p'zsd@7101' -S /data/mysqldata/3306/mysql.sock -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status

• exec_mysql_innodbstatus.sh 打出当前MySQL Innodb存储引擎的状态信息

#!/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

脚本收集

• collect_mysql.sh 每5秒收集上述各个状态命令的相关信息，并输出到文件中，后续可以用awk等其他格式化命令来分析日志文件，并画图变现出来。

#!/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的具体信息。

脚本分析

• analyze 分析上述生成的文件如:5-sec-status-2018-06-15_17-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.20

画图

gnuplot为一个制图开源工具。可以通过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

• 图片如下: