一、基准测试
基准测试(benchmark)是针对系统设计的一种压力测试。
基准测试是简化了的压力测试。
1.1 常见指标
- TPS
- QPS
- 响应时间
- 并发量
1.2 收集与分析数据脚本
收集数据的shell脚本gather.sh
#!/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;' >> $PREFIX-${file}-innodbstatus &
echo "$ts $loadavg" >> $PREFIX-${file}-processlist
mysql -e 'show full processlist;' >> $PREFIX-${file}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exist.
分析数据脚本analyze.sh
#!/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 "
%s %s %s %s", ts, $3, $4, substr($load, 1, length($load) - 1);
}
/Queries/ {
printf fmt, ($2-Queries)/diff;
Queries=$2;
}
' "$@"
二、基准测试工具
2.1 mysqlslap
常用参数
# mysqlslap --help
--auto-generate-sql 由系统自动生成SQL脚本进行测试
--auto-generate-sql-add-autoincrement 在生成的表中增加自增ID
--auto-generate-sql-load-type=name 指定测试中使用的查询类型
--auto-generate-sql-execute-number= 指定初始化数据时生成的数据量
--concurrency= 指定并发线程的数量
--engine 指定要测试表的存储引擎，可以用逗号分割多个存储引擎
--no-drop 指定不清理测试数据
--iterations 指定测试运行的次数
--number-of-queries 指定每一个线程执行的查询数量
--debug-info 指定输出额外的内存及CPU统计信息
--number-int-cols 指定测试表中包含的INT类型列的数量
--number-char-cols 指定测试表中包含的varchar类型的数量
--create-schema 指定了用于执行测试的数据库的名字
--query 用于指定自定义SQL的脚本
--only-print 并不运行测试脚本，而是把生成的脚本打印出来
示例
mysqlslap
--concurrency=1,50,100,200
--iterations=3
--number-int-cols=5
--number-char-cols=5
--auto-generate-sql
--auto-generate-sql-add-autoincrement
--engine=myisam,innodb
--number-of-queries=10
--create-schema=test
> 1.txt
测试结果
[root@mysql211 ~]# cat 1.txt
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.001 seconds
Minimum number of seconds to run all queries: 0.001 seconds
Maximum number of seconds to run all queries: 0.001 seconds
Number of clients running queries: 1
Average number of queries per client: 10
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.043 seconds
Minimum number of seconds to run all queries: 0.037 seconds
Maximum number of seconds to run all queries: 0.052 seconds
Number of clients running queries: 50
Average number of queries per client: 0
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.082 seconds
Minimum number of seconds to run all queries: 0.082 seconds
Maximum number of seconds to run all queries: 0.084 seconds
Number of clients running queries: 100
Average number of queries per client: 0
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.245 seconds
Minimum number of seconds to run all queries: 0.245 seconds
Maximum number of seconds to run all queries: 0.246 seconds
Number of clients running queries: 200
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.007 seconds
Minimum number of seconds to run all queries: 0.002 seconds
Maximum number of seconds to run all queries: 0.010 seconds
Number of clients running queries: 1
Average number of queries per client: 10
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.229 seconds
Minimum number of seconds to run all queries: 0.212 seconds
Maximum number of seconds to run all queries: 0.238 seconds
Number of clients running queries: 50
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.519 seconds
Minimum number of seconds to run all queries: 0.471 seconds
Maximum number of seconds to run all queries: 0.555 seconds
Number of clients running queries: 100
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.849 seconds
Minimum number of seconds to run all queries: 0.767 seconds
Maximum number of seconds to run all queries: 0.909 seconds
Number of clients running queries: 200
Average number of queries per client: 0
[root@mysql211 ~]#
2.2 sysbench 0.5
下载
# MySQL上找到的版本太低
# https://dev.mysql.com/downloads/benchmarks.html
wget https://downloads.mysql.com/source/sysbench-0.4.12.14.tar.gz
# https://github.com/akopytov/sysbench/releases
wget https://github.com/akopytov/sysbench/archive/1.0.13.tar.gz
wget https://github.com/akopytov/sysbench/archive/0.5.zip
安装，参照解压后的README.md
# 解压 解压后目录为 ~/sysbench-0.5
unzip sysbench-0.5.zip
cd sysbench-0.5
# 运行后没报错就可以继续
./autogen.sh
# 配置
./configure
--with-mysql-includes=/usr/local/mysql/include
--with-mysql-libs=/usr/local/mysql/lib
# 编译安装
make && make install
常用参数
sysbench --help
--test 用于指定所要执行的测试类型，支持以下参数
fileio - File I/O test 文件系统I/O性能测试
cpu - CPU performance test CPU性能测试
memory - Memory functions speed test 内存性能测试
threads - Threads subsystem performance test
mutex - Mutex performance test
Oltp测试要指定具体的lua脚本
Lua脚本位于sysbench-0.5/sysbench/tests/db
--mysql-db 用于指定执行基准测试的数据库名
--mysql-table-engine 用于指定所使用的存储引擎
--oltp-tables-count 执行测试的表的数量
--oltp-table-size 指定每个表中的数据行数
--num-threads 指定测试的并发线程数量
--max-time 指定最大的测试时间
--report-interval 指定间隔多长时间输出一次统计信息
--mysql-user 指定执行测试的MySQL用户
--mysql-password 指定执行测试的MySQL用户的密码
prepare 用于准备测试数据
run 用于实际进行测试
cleanup 用于清理测试数据
测试CPU
sysbench --test=cpu --cpu-max-prime=1000 run
测试磁盘I/O
# free -m看内存
sysbench --test=fileio --file-total-size=1G prepare
# 查看相关参数
sysbench --test=fileio --help
# 测试
sysbench --test=fileio
--num-threads=8
--init-rng=on
--file-total-size=1G
--file-test-mode=rndrw
--report-interval=1
run
2.3 sysbench 测试数据库
创建库和表
create database dbtest;
grant all privileges on *.* to dbtest@'localhost' identified by '123456';
准备生成表和数据
# 进入脚本目录
cd ~/root/sysbench-0.5/sysbench/tests/db
# 查看脚本
ls -l *.lua
# 准备
sysbench --test=./oltp.lua
--mysql-table-engine=innodb
--oltp-table-size=10000
--mysql-db=dbtest
--mysql-user=dbtest
--mysql-password=123456
--oltp-tables-count=10
--mysql-socket=/tmp/mysql.sock
prepare
运行收集测试数据的脚本 gather.sh
# 创建脚本需要的目录
mkdir /home/benchmarks && cd /home/benchmarks
# 将gather.sh脚本放在上述目标，并创建running
touch running
# 后台运行
sh gather.sh &
进行基准测试
# 运行
sysbench --test=./oltp.lua
--mysql-table-engine=innodb
--oltp-table-size=10000
--mysql-db=dbtest
--mysql-user=dbtest
--mysql-password=123456
--oltp-tables-count=10
--mysql-socket=/tmp/mysql.sock
run
测试结束后，运行分析数据的脚本 analyze.sh
# 查看获取的数据
[root@mysql211 benchmarks]# ll -h 5*
-rw-r--r--. 1 root root 124K Mar 12 16:41 5-sec-status-2018-03-12_04-innodbstatus
-rw-r--r--. 1 root root 11K Mar 12 16:41 5-sec-status-2018-03-12_04-processlist
-rw-r--r--. 1 root root 236K Mar 12 16:41 5-sec-status-2018-03-12_04-status
# 分析这些数据 analyze.sh 文件名
sh analyze.sh /home/benchmarks/5-sec-status-2018-03-12_04-innodbstatus
三、参考
- 《高性能MySQL(第三版)》第二章