MySQL 高性能之工具篇

整理《MySQL 高性能》- 第三版 提及的工具

信息收集

  • gnuplot R绘图 (可以对脚本收集的数据作为数据源用于图标分析) (page 47 pdf 85)
  • ab http_load JMeter (集成测试工具)
  • mysqlslap sql-bench(MySQL Benchmark Suite)
  • Super Smack
  • Database Test Suite
  • Percona's TPCC-MySQL Tool
  • sysbench

系统监控

  • 诊断间歇性问题(利用mysql命令 page 88 pdf 125)
  • pt-query-digest
  • Percona Toolkit pt-query-digest (著名工具 Maatkit mk-query-digest)
  • New Relic (software-as-service)
  • xhprof xdebug valgrind cachegrind (php性能剖析) (page 74 pdf 112)
  • IfP(instrumentation-for-php) 托管在code.google.com
  • MySQL 企业监控器(Enterprise Monitor)
  • pt-collect (percona Toolkit 中的一员) pt-stalk (最好安装gdb和oprofile)
  • pt-mysql-summary
  • pt-summary
  • pt-sift

Linux命令

  • oprofile
  • strace
  • tcpdump
  • gdb
  • pt-pmp

MySQL

  • 系统函数 BENCHMARK() (page 52 pdf 89)
  • show status
  • show profile
  • explain
  • Performance Schema (page 86 pdf 124)

收集MySQL测试脚本

#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
  file=$(date +%F_%I)
  sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
  sleep $sleep
  ts="$(date +"TS %s.%N %F %T")"
  loadavg="$(uptime)"
  echo "$ts $loadavg" >> $PREFIX-${file}-status
  mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
  echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
  mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
  echo "$ts $loadavg" >> $PREFIX-${file}-processlist
  mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
  echo $ts
done
echo Exiting because $RUNFILE does not exist.

github-收集MySQL测试脚本

对测试数据分析脚本

#!/bin/sh
# This script converts SHOW GLOBAL STATUS into a tabulated format, one line
# per sample in the input, with the metrics divided by the time elapsed
# between samples.
awk '
  BEGIN {
    printf "#ts date time load QPS";
    fmt = " %.2f";
  }
  /^TS/ { # The timestamp lines begin with 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
  }
  ' "$@"

github-对测试数据分析脚本

相关资料

配套代码网站

发表评论