0. 前言
项目开发时,对某些在数据量较大的数据表的查询,难免想知道sql查询语句到底花费了多少时间,而自己做了一定的优化后又能减少多少时间,前后需要一个时间的对比,是否有这样的工具而不用手写测试代码呢?MySQL自带的性能测试工具mysqlslap正好符合自己的要求。
实际上,mysqlslap也是一款简单的压力测试工具,本文的使用主要用来检验sql的执行时间。
1. mysqlslap简介
mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.
以上是官方介绍,意思为:mysqlslap是一个诊断程序,旨在模拟MySQL服务器的客户端负载并报告每个阶段的时间。它就好像多个客户端正在访问服务器一样。mysqlslap是MySQL自带的可执行文件,位于bin文件夹下。作为一个诊断程序,或者说基准测试程序,mysqlslap主要是通过打印出sql的执行时间来判断服务器的性能,也可以打印出内存和CPU的使用情况。
详细的介绍以及参数请参考官方网站,下面只介绍常用的参数以及一些个人的测试案例。
2. mysqlslap常用参数
参数有两种形式,一种类似-uroot
、-p123456
的简写形式,使用单个-
符号,然后直接写参数,中间加不加空格都可以。另一种形式是全写,类似--user=root
,也可以写成--user root
。下面的介绍两种方式都会说明(但不是所有参数都有简写形式)。
2.1 基本参数
序号 | 参数 | 说明 | 示例 |
---|---|---|---|
1 | –user, -u | 连接MySQL服务器的用户名 | –user=root, -uroot |
2 | –password, -p | 连接MySQL服务器的密码 | –password=123456, -p123456 |
3 | –concurrency, -c | 用户并发量 | –concurrency=10, -c10 |
4 | –iterations, -i | 测试的执行次数 | –iterations=20, -i20 |
5 | –engine, -e | 测试使用的表的引擎。常用引擎:myisam,innodb,默认innodb | –engine=innodb, -e innodb |
6 | –number-of-queries | 测试的总sql执行次数,执行一个sql语句算1次,当只测试一条sql语句时,该值等于并发客户数×每客户查询次数 | –number-of-queries=200 |
7 | –debug-info, -T | 程序退出时打印调试信息和内存以及CPU使用情况统计信息。 | –debug-info, -T |
8 | –only-print | 只查看测试使用的sql语句,并不执行测试 | –only-print |
关于
--debug-info, -T
:使用该参数可能会输出[ERROR] mysqlslap: Option '-T' used, but is disabled
。经网上查阅得知,原因很可能是因为MySQL服务器不支持该参数,可通过手动编译MySQL源码时指定参数-DWITH_DEBUG
,设置为debug模式来支持该参数。
2.2 自动生成sql的相关参数
当使用程序自动生成的sql时,程序自动生成一个名为mysqlslap的数据库,里面有一张测试用表,程序退出就会删除该数据库。
根据官网介绍,默认情况下,自动生成的表有100行,这100条数据使用10条不同的INSERT语句循环插入(即表中最多共有10条不同的行),每行有1列varchar类型,1列int类型,没有自增列,不使用非主键索引,共执行查询10次(实际上最后多了1条INSERT,即11次),而查询一半是全表SELECT,一半是INSERT,并发量为1。整个测试运行一次。(本人尝试把插入条数改大些增长测试时间,结合使用--only-print
参数,并查看生成表的内容,发现确实如此)。程序执行时间远大于打印出来的执行sql时间是因为表的生成(包括建数据库、建表、插入数据)的时间不包含在打印出的sql时间内。
常用的相关参数如下:
序号 | 参数 | 说明 | 示例 |
---|---|---|---|
1 | –auto-generate-sql, -a | 当没有指定脚本文件或者sql语句时,系统自动生成测试的sql语句 | –auto-generate-sql, -a |
2 | –auto-generate-sql-load-type | 当采用自动生成sql时,测试的sql语句是读、写还是读写混合(read,write,update,mixed),默认是mixed | –auto-generate-sql-load-type=read |
3 | –auto-generate-sql-execute-number | 当采用自动生成sql时,指定执行的sql总数,每个测试线程都执行该参数指定的sql数目,默认是1 | –auto-generate-sql-execute-number=5 |
4 | –auto-generate-sql-add-auto-increment | 当采用自动生成sql时,添加自增列 | –auto-generate-sql-add-auto-increment |
5 | –auto-generate-sql-write-number | 当采用自动生成sql时,查询前表中预先插入的行数,默认是100,同时默认生成10不同的插入sql进行插入 | –auto-generate-sql-write-number=200 |
5 | –number-char-cols, -x | 当采用自动生成sql时,varchar列的个数,默认为1 | –number-char-cols=5, -x 5 |
6 | –number-int-cols, -y | 当采用自动生成sql时,int列的个数,默认为1 | –number-int-cols=3, -y 3 |
2.3 指定sql的相关参数
序号 | 参数 | 说明 | 示例 |
---|---|---|---|
1 | –create-schema | 在MySQL中,schema指的就是数据库,该参数实际上是指定测试使用的数据库 | –create-schema=test_db |
2 | –query, -q | 制定执行的sql语句或文件 | –query=”select from t_test”, -q”select from t_test” |
3. 测试案例
声明:测试案例结果因MySQL版本、计算机性能不同而异。
3.1 自动生成sql的测试案例
案例一:1
mysqlslap -uroot --password="password" -a -i 20
上述命令表示使用root用户连接到本地MySQL数据库,使用默认的全套的自动生成sql参数-a
(默认自动生成情况请看2.2),并发量为1,整个测试执行20次,输出如下:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 0.049 seconds
Minimum number of seconds to run all queries: 0.036 seconds
Maximum number of seconds to run all queries: 0.066 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Benchmark表示基准测试,整个输出翻译过来为:1
2
3
4
5
6基准测试
执行全部查询的平均秒数为:0.049秒
执行全部查询的最小秒数为:0.036秒
执行全部查询的最大秒数为:0.066秒
执行查询的客户端数量为:1
平均每个客户端执行的查询数为:0
注意:
- 查询(queries)的意思并不只单指SELECT语句,而是所有sql语句,默认情况下测试执行的sql语句一半为SELECT,一半为INSERT。
- 平均、最小、最大是对于20次测试而言的。
- 平均每个客户端执行的查询数为0,单独使用默认参数-a而不是用其他自动生成sql相关参数就会为0,实际上应该是11,可通过
--only-print
得知。
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
意思为:[警告]明文的在命令行使用连接密码不安全。
建议:当参数值比较长或者参数值含有特殊符号(包括空格),应当使用双引号””。另外,使用mysqlslap命令请cd到mysql目录下的bin文件夹,或者预先指定好环境变量。
案例二:1
2mysqlslap -uroot --password="password" -a --auto-generate-sql-write-number=1000
--auto-generate-sql-execute-number=5
上述命令表示自动生成1000条数据,并发量为1,sql执行数量为5,整个测试执行1次。输出如下:
1 | Benchmark |
案例三:1
2mysqlslap -uroot --password="password" -a --auto-generate-sql-write-number=200 --auto-generate-sql-execute-number=5
--auto-generate-sql-load-type=read -c 10 -i 5
上述命令表示,测试表自动生成200数据,并发量为10,即10个客户端,每个客户端执行5条全表查询(SELECT)sql语句,整个测试重复执行5次。输出结果为:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 0.016 seconds
Minimum number of seconds to run all queries: 0.015 seconds
Maximum number of seconds to run all queries: 0.018 seconds
Number of clients running queries: 10
Average number of queries per client: 5
输出表示,平均每次测试中,10个客户端,平均每个客户端发出5个SELECT语句,共执行50条sql语句一共耗费0.016秒,最少的一次测试耗费0.015秒,最多的一次测试耗费0.018秒。
案例四:1
2mysqlslap -uroot --password="password" -a --auto-generate-sql-write-number=200 --auto-generate-sql-execute-number=5
--auto-generate-sql-load-type=write -c 10 -i 5
该命令与案例三的不同之处在于,sql的执行类型为INSERT而不是三的SELECT,输出结果为:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 0.091 seconds
Minimum number of seconds to run all queries: 0.085 seconds
Maximum number of seconds to run all queries: 0.100 seconds
Number of clients running queries: 10
Average number of queries per client: 5
对比案例三,理所当然的,INSERT比SELECT更耗费时间。
案例五:1
2mysqlslap -uroot --password="password" -a --auto-generate-sql-write-number=200 --auto-generate-sql-execute-number=5
--auto-generate-sql-load-type=read -i 5 -c 3
该命令与案例三的不同之处在于,并发量为3,案例三为10,输出结果为:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 0.005 seconds
Minimum number of seconds to run all queries: 0.005 seconds
Maximum number of seconds to run all queries: 0.006 seconds
Number of clients running queries: 3
Average number of queries per client: 5
对比案例三,理所当然的,并发量低,耗费时间自然少。
案例六:1
2mysqlslap -uroot --password="password" -a --auto-generate-sql-write-number=5 --auto-generate-sql-execute-number=3
--auto-generate-sql-load-type=read --only-print
上述命令表示,自动生成5条数据,测试执行3条SELECT语句,结果只打印sql语句而不执行,输出结果为:1
2
3
4
5
6
7
8
9
10
11
12DROP SCHEMA IF EXISTS `mysqlslap`;
CREATE SCHEMA `mysqlslap`;
use mysqlslap;
CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');
INSERT INTO t1 VALUES (822890675,'97RGHZ65mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11W');
INSERT INTO t1 VALUES (1308044878,'50w46i58Giekxik0cYzfA8BZBLADEg3JhzGfZDoqvQQk0Akcic7lcJInYSsf9wqin6LDC1vzJLkJXKn5onqOy04MTw1WksCYqPl2Jg2eteqOqTLfGCvE4zTZwWvgMz');
INSERT INTO t1 VALUES (964445884,'DPh7kD1E6f4MMQk1ioopsoIIcoD83DD8Wu7689K6oHTAjD3Hts6lYGv8x9G0EL0k87q8G2ExJjz2o3KhnIJBbEJYFROTpO5pNvxgyBT9nSCbNO9AiKL9QYhi0x3hL9');
SELECT intcol1,charcol1 FROM t1;
SELECT intcol1,charcol1 FROM t1;
SELECT intcol1,charcol1 FROM t1;
DROP SCHEMA IF EXISTS `mysqlslap`;
直白易懂,不解释。
3.2 指定sql的测试案例
数据库test_db
中的表t_test
是我自己事先设置好的,有10W条数据,每行数据有15行。
案例一:1
mysqlslap -uroot --password="password" --create-schema=test_db --query="select * from t_test" -i 30
上述命令表示,对数据库test_db
执行select * from t_test
进行测试,重复测试30次。结果如下:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 0.600 seconds
Minimum number of seconds to run all queries: 0.280 seconds
Maximum number of seconds to run all queries: 1.312 seconds
Number of clients running queries: 1
Average number of queries per client: 1
对有10W条数据的大表进行全表扫描查询,速度非常慢,30次查询平均每次0.6秒(最大和最小相差很大,增大了测试次数也是0.6秒左右)
案例二:1
mysqlslap -uroot -peternality --create-schema="test_db" --query="select * from t_test where test_id = 1000 " -i 10 --number-of-queries 20
上述命令表示,对数据库test_db
执行20次select * from t_test where test_id = 1000
进行测试(test_id
不是主键也没有索引,而test_id
相同的平均有3000行),重复测试10次。输出结果如下:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 3.592 seconds
Minimum number of seconds to run all queries: 2.779 seconds
Maximum number of seconds to run all queries: 3.810 seconds
Number of clients running queries: 1
Average number of queries per client: 20
对test_id
列添加B树索引后,执行同样命令,输出结果如下:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 0.015 seconds
Minimum number of seconds to run all queries: 0.000 seconds
Maximum number of seconds to run all queries: 0.031 seconds
Number of clients running queries: 1
Average number of queries per client: 20
没有索引时,平均每个查询花费 3.592s / 20 ≈ 0.18s ,而添加索引后平均每个查询花费不到0.001s,说明索引被正确的建立而且查询使用了索引。
案例三:1
mysqlslap -uroot -peternality --create-schema="test_db" --query="select * from t_test where test_id = 1000 " -i 20 --number-of-queries 500 -c 50
上述命令表示,test_db
执行500次select * from t_test where test_id = 1000
(有索引),并发量为50,即平均每个客户端执行10次sql,重复测试20次。该案例主要用来测试下并发。输出结果如下:1
2
3
4
5
6Benchmark
Average number of seconds to run all queries: 0.292 seconds
Minimum number of seconds to run all queries: 0.125 seconds
Maximum number of seconds to run all queries: 1.140 seconds
Number of clients running queries: 50
Average number of queries per client: 10
Copyright © 2018, GDUT CSCW back-end Kanarien, All Rights Reserved