MySQL基准测试
1、什么是基准测试？
基准测试是一种测量和评估软件性能指标的活动。用于建立某个时刻的性能基准，以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响。
基准测试是针对系统设置的一种压力测试。
. 压力测试需要针对不同的主题，所使用的的数据和查询也是真实用到的。
. 基准测试可能不关心业务逻辑，所使用的查询和业务的真实性可以和业务环境没关系。
2、基准测试的目的
(1) 建立MySQL服务器的性能基准线，确定当前MySQL服务器运行情况。
(2) 模拟比当前系统更高的负载，以找出系统的扩展瓶颈。增加数据库并发，观察QPS,TPS变化，确定并发量与性能最优的关系。
(3) 测试不同的硬件、软件和操作系统配置。
(4) 证明新的硬件系统设备是否配置正确。
3、如何进行基准测试
(1) 对整个系统进行基准测试。
从系统入口进行测试(如网站Web前端，手机APP前端)。
优点：
. 能够测试整个系统的性能，包括web服务器、缓存、数据库等。
. 能反映出系统中各个组件接口间的性能问题，体现真实性能状况。
缺点：
. 测试设计复杂，消耗时间长。
(2) 单独对MySQL进行基准测试
优点：
. 测试设计简单，所需耗费时间短。
缺点：
. 无法全面了解整个系统性能的基准线。
4、MySQL基准测试的常见指标
(1) 单位时间内所处理的事务数（TPS）。
(2) 单位时间内锁处理的查询数（QPS）。
(3) 响应时间。
(4) 并发量：同时处理的查询请求的数量。
正在工作中的并发的操作数或同时工作的数量。
5、基准测试的步骤
(1) 计划和设计基准测试
. 对整个系统还是某一组件
. 使用什么样的数据
. 准备基准测试及数据收集脚本
CPU使用率、IO、网络流量、状态与计数器信息等。
Get_Test_info.sh
|
. 运行基准测试
. 保存及分析基准测试结果
analyze.sh
|
6、基准测试中容易忽略的问题
(1) 使用生产环境数据时只使用了部分数据。
推荐：使用数据库完全备份来测试。
(2) 在多用户场景中，只做单用户测试。
推荐：使用多线程并发测试。
(3) 在单服务器上测试分布式应用。
推荐：使用相同架构进行测试。
(4) 反复执行同一查询。
容易缓存命中，无法反应真实查询性能。
7、MySQL基准测试工具之mysqlslap
(1) 常用参数说明
--auto-generate-sql 由系统自动生成SQL脚本进行测试
--auto-generate-sql-add-autoincrement 在生成的表中增加自增ID
--auto-generate-sql-load-type 指定测试中使用的查询类型
--auto-generate-sql-write-number 指定初始化数据时生成的数据量
--concurrency 指定并发线程的数量
--engine 指定要测试表的存储引擎，可以用逗号分割多个存储引擎
--no-drop 指定不清理测试数据
--iterations 指定测试运行的次数
--number-of-queries 指定每一个线程执行的查询数量
--debug-info 指定输出额外的内存及CPU统计信息
--number-int-cols 指定测试表中包含的INT类型列的数量
--number-char-clos 指定测试表中包含的varchar类型的数量
--create-schama 指定了用于执行测试的数据库的名字
--query 用于指定自定义SQL的脚本
--only-print 并不运行测试脚本，而是把生成的脚本打印出来
(2) 实例：
a. 实例一：
mysqlslap -uroot -pcaoxuejin -a --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
运行结果如下：
Warning: Using a password on the command line interface can be insecure.
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.030 seconds
Minimum number of seconds to run all queries: 0.029 seconds
Maximum number of seconds to run all queries: 0.031 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.063 seconds
Minimum number of seconds to run all queries: 0.061 seconds
Maximum number of seconds to run all queries: 0.066 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.136 seconds
Minimum number of seconds to run all queries: 0.130 seconds
Maximum number of seconds to run all queries: 0.142 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.011 seconds
Minimum number of seconds to run all queries: 0.010 seconds
Maximum number of seconds to run all queries: 0.012 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.045 seconds
Minimum number of seconds to run all queries: 0.040 seconds
Maximum number of seconds to run all queries: 0.049 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.078 seconds
Minimum number of seconds to run all queries: 0.077 seconds
Maximum number of seconds to run all queries: 0.079 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.162 seconds
Minimum number of seconds to run all queries: 0.162 seconds
Maximum number of seconds to run all queries: 0.164 seconds
Number of clients running queries: 200
Average number of queries per client: 0
b. 实例二：在实例一的基础上增加--only-print
mysqlslap -uroot -pcaoxuejin -a --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 --only-print
运行结果：
Warning: Using a password on the command line interface can be insecure.
DROP SCHEMA IF EXISTS `test`;
CREATE SCHEMA `test`;
use test;
set storage_engine=`myisam`;
CREATE TABLE `t1` (id serial,intcol1 INT(32) ,intcol2 INT(32) ,intcol3 INT(32) ,intcol4 INT(32) ,intcol5 INT(32) ,charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128),charcol4 VARCHAR(128),charcol5 VARCHAR(128));
INSERT INTO t1 VALUES (NULL,1804289383,846930886,1681692777,1714636915,1957747793,'vmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBLb97R','GHZ65mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11Wy50','w46i58Giekxik0cYzfA8BZBLADEg3JhzGfZDoqvQQk0Akcic7lcJInYSsf9wqin6LDC1vzJLkJXKn5onqOy04MTw1WksCYqPl2Jg2eteqOqTLfGCvE4zTZwWvgMz4D','Ph7kD1E6f4MMQk1ioopsoIIcoD83DD8Wu7689K6oHTAjD3Hts6lYGv8x9G0EL0k87q8G2ExJjz2o3KhnIJBbEJYFROTpO5pNvxgyBT9nSCbNO9AiKL9QYhi0x3hL9W','lwRHuWm4HE8leYmg66uGYIp6AnAr0BDd7YmuvYqCfqp9EbhKZRSymA4wx6gpHlJHI53DetH9j7Ixar90Jey5outd1ZIAJdJTjMaD7rMiqYXHFhHaB7Xr1HKuqe51GG');
.......................................由于内容过多，这里省略显示，包含了所执行的所有SQL脚本句....................................................
DROP SCHEMA IF EXISTS `test`;
8、MySQL基准测试工具之sysbench
(1) 安装sysbench
wget https://github.com/akopytov/sysbench/archive/0.5.zip -O "sysbench-0.5.zip"
unzip sysbench-0.5.zip
cd sysbench-0.5
./autogen.sh
./configure && make && make install
[[email protected] sysbench-0.5]# sysbench --version
sysbench 0.5
(2) 常用参数
--test 用于指定所要执行的测试类型，支持以下参数：
Fileio 文件系统I/O性能测试
cpu cpu性能测试
memory 内存性能测试
Oitp 测试要指定具体的lua脚本
Lua脚本位于sysbench-0.5/sysbench/tests/db
--mysql-db 用于指定执行基准测试的数据库名
--mysql-table-engin 用于指定所使用的存储引擎
--oltp-tables-count 执行测试的表的数量
--oltp-table-size 指定每个表中的数据行数
--num-threads 指定测试的并发线程数量
--max-time 指定最大的测试时间(以秒为单位)
--report-interval 指定间隔多长时间输出一次统计信息(以秒为单位)
--mysql-user 指定执行测试的MySQL用户
--mysql-password 指定执行测试的MySQL用户的密码
--prepare 用于准备测试数据
--run 用于实际进行测试
--cleanup 用于清理测试数据
(3) 实例
a. 实例一：
sysbench --test=cpu --cpu-max-prime=10000 run
b. 实例二
free -m
df -lh
cd /tmp
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
c. 实例三
mysql -u root -p
create database benchmark_test;
grant all privileges on *.* to [email protected]'localhost' identified by 'caoxuejin';
cd sysbench-0.5/sysbench/tests/db
sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=benchmark_test --mysql-user=test --mysql-password=caoxuejin --oltp-tables-count=10 --mysql-socket=/var/lib/mysql/mysql.sock prepare
use benchmark_test;
show tables;
select * from sbtest1 limit 1;
select count(*) from sbtest1;
Get_Test_info.sh
#!/bin/bash
INTERVAL=5
PREFIX=/root/benchmarks/$INTERVAL-sec-status
RUNFILE=/root/benchmarks/running
echo "1" > $RUNFILE
MYSQL=/usr/bin/mysql
$MYSQL -e "show global variables" >> mysql-variables
while test -e $RUNFILE; do
file=$(date +%F_%I)
sleep=$(date +%s.%N | awk '{print 5 - ($1 % 5)}')
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\G" >> $PREFIX-${file}-processlist &
echo $ts
done
echo Exiting because $RUNFILE does not exists
bash ./Get_Test_info.sh &
sysbench --test=./oltp.lua --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=benchmark_test --mysql-user=test --mysql-password=caoxuejin --oltp-tables-count=10 --mysql-socket=/var/lib/mysql/mysql.sock run
benchmarks下自动建立了一些系统性能状态的文件
数据库执行了一些查询和插入操作
对收集到的信息进行处理：
analyze.sh
#!/bin/bash
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;
printf "\n%s %s %s %s",ts,$3,$4,substr($load,1,length($load)-1);
prev_ts=ts;
}
/Queries/{
printf fmt,($2-Queries)/diff;
Queries=$2
}
' "[email protected]"