MySQL自带压力测试工具mysqlslap

基本说明：

–auto-generate-sql 作用是自动生成测试SQL

测试结果中各项含义：

Average number of … ##运行所有语句的平均秒数
Minimum number of … ##运行所有语句的最小秒数
Maximum number of … ##运行所有语句的最大秒数
Number of clients … ##客户端数量
Average number of queries per client ##每个客户端运行查询的平均数

一、简单用法
[root@mysql8027 logs]# mysqlslap -uroot -p --auto-generate-sql
Enter password:
Enter password:
Benchmark
Average number of seconds to run all queries: 3.419 seconds
Minimum number of seconds to run all queries: 3.419 seconds
Maximum number of seconds to run all queries: 3.419 seconds
Number of clients running queries: 1
Average number of queries per client: 0

二、添加并发
--concurrency=100 指定同时有100个客户端连接; 连接数可以多个，用逗号隔开
--number-of-queries=1000 指定总的测试查询次数（并发客户端数 * 每个客户端的查询次数）
-------------------------------------------------------------------------------------------------------
[root@mysql8027 logs]# mysqlslap --auto-generate-sql --concurrency=100 -uroot -ppassword
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.323 seconds
Minimum number of seconds to run all queries: 0.323 seconds
Maximum number of seconds to run all queries: 0.323 seconds
Number of clients running queries: 100
Average number of queries per client: 0
-------------------------------------------------------------------------------------------------------
[root@mysql8027 logs]# mysqlslap --auto-generate-sql --concurrency=50,100 -uroot -ppassword
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.129 seconds
Minimum number of seconds to run all queries: 0.129 seconds
Maximum number of seconds to run all queries: 0.129 seconds
Number of clients running queries: 50
Average number of queries per client: 0

Benchmark
Average number of seconds to run all queries: 0.128 seconds
Minimum number of seconds to run all queries: 0.128 seconds
Maximum number of seconds to run all queries: 0.128 seconds
Number of clients running queries: 100
Average number of queries per client: 0

-------------------------------------------------------------------------------------------------------
[root@mysql8027 logs]# mysqlslap --auto-generate-sql --concurrency=100 --number-of-queries=1000 -uroot -ppassword
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.152 seconds
Minimum number of seconds to run all queries: 0.152 seconds
Maximum number of seconds to run all queries: 0.152 seconds
Number of clients running queries: 100
Average number of queries per client: 10

三、测试不同的存储引擎的性能
--iterations 模拟迭代
--engine 测试不同的存储引擎的性能进行对比
-------------------------------------------------------------------------------------------------------
[root@mysql8027 logs]# mysqlslap --auto-generate-sql --concurrency=50 --iterations=5 --engine=myisam,innodb -uroot -ppassword
mysqlslap: [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.539 seconds
Minimum number of seconds to run all queries: 0.530 seconds
Maximum number of seconds to run all queries: 0.550 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.092 seconds
Minimum number of seconds to run all queries: 0.077 seconds
Maximum number of seconds to run all queries: 0.116 seconds
Number of clients running queries: 50
Average number of queries per client: 0
-------------------------------------------------------------------------------------------------------
四、自动生成复杂表
自动测试时，创建的表结构非常简单，只有两列，实际的产品环境肯定会更复杂，可以使用参数指定列的数量和类型

--number-int-cols=5 指定生成5个 int 类型的列
--number-char-cols=20 指定生成20个 char 类型的列
-------------------------------------------------------------------------------------------------------
[root@mysql8027 logs]# mysqlslap --concurrency=50 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql -uroot -ppassword
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.279 seconds
Minimum number of seconds to run all queries: 0.279 seconds
Maximum number of seconds to run all queries: 0.279 seconds
Number of clients running queries: 50
Average number of queries per client: 0
-------------------------------------------------------------------------------------------------------
五、使用自己的测试库和测试语句
自动测试可以帮助我们了解硬件层面的状况，对于我们产品特定的情况，还是使用自己的库来测试比较好，可以复制一份产品库过来，然后对此库测试，例如

--create-schema 用来指定测试库名称
--query 是自定义的测试语句

实际使用时，一般是测试多个复杂的语句，可以定义一个脚本文件，例如

echo “SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments;” > ~/select_query.sql

把多个查询语句写入了一个 sql 文件，然后使用此文件执行测试

mysqlslap --concurrency=20 --number-of-queries=1000 --create-schema=employees --query=”select_query.sql” --delimiter=”;”

--query 中指定了sql文件
--delimiter 说明sql文件中语句间的分隔符是什么

使用自定义的sql语句和库测试
-------------------------------------------------------------------------------------------------------
[root@mysql8027 logs]# mysqlslap --concurrency=50 --create-schema="mysql" --query="select * from mysql.user" --number-of-queries=100 -uroot -ppassword
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.012 seconds
Minimum number of seconds to run all queries: 0.012 seconds
Maximum number of seconds to run all queries: 0.012 seconds
Number of clients running queries: 50
Average number of queries per client: 2