Mysql 数据库压力测试工具 - MySqlSlap
原创
©著作权归作者所有：来自51CTO博客作者wx63647ca229299的原创作品，请联系作者获取转载授权，否则将追究法律责任
MySQLSlap是从MySQL的5.1.4版引入的压力测试工具。该工具我们可以在Mysql的安装目录的bin目录里面找到，如C:\Program Files\MySQL\MySQL Server 5.7\bin>。
名词解释
-
TPS：Transactions Per Second（每秒传输的事物处理个数），这是指服务器每秒处理的事务数，支持事务的存储引擎如InnoDB等特有的一个性能指标。 TPS = (COM_COMMIT + COM_ROLLBACK)/UPTIME
-
QPS：Queries Per Second（每秒查询处理量）同时适用与InnoDB和MyISAM 引擎。QPS=QUESTIONS/UPTIME
- 等待时间：执行Sql等待返回结果之间的等待时间
主要功能
- 创建测试库、测试表、测试表结构 ；
- 运行负载测试，可以使用多个并发客户端连接；
- 测试环境清理（删除创建的数据、表等，断开连接）;
主要参数
…参数
| 作用
|
--create-schema=name | 指定测试的数据库名，默认是mysqlslap。
|
--engine=engine_name,-e engine_name | 代表要测试表的引擎，可以有多个用逗号分隔符隔开，如：--engines=myisam,innodb。 |
--concurrency=N,-c N | 模拟N个客户端并发执行。可指定多个值，以逗号隔开，如：--concurrency=1,50,100,200。 |
--number-of-queries=N | 总的测试查询次数(并发客户数×每客户查询次数)，比如并发是10，总次数是100，那么10个客户端各执行10个
|
--iterations=N,-i N | 迭代执行的次数，即重复的次数（相同的测试进行N次，求一个平均值），指的是整个步骤的重复次数，包括准备数据、测试load、清理。
|
--commit=N | 执行N条DML后提交一次。
|
--auto-generate-sql, -a | 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
|
--auto-generate-sql-load-type=name | 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。
取值包括：read(scan tables), write (insert into tables), key(read primary keys), update(update primary keys), or mixed(half inserts, half scanning selects). 默认值是：mixed。 |
--auto-generate-sql-add-auto-increment | 对生成的表自动添加auto_increment列。
|
--number-char-cols=name,-x N | 自动生成的测试表中包含N个字符类型的列，默认1。
|
--number-int-cols=name,-y N | 自动生成的测试表中包含N个数字类型的列，默认1。
|
--debug-info | 打印内存和CPU的信息。
|
--compress, -C | 如果服务器和客户端支持都压缩，则压缩信息传递。
|
--only-print | 只打印测试语句而不实际执行。
|
命令示例
- 并发数100，循环10次，主动建表和生成数据，指定表引擎为innodb，总的查询次数为100：
mysqlslap -uroot -proot --concurrency=100 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=100
-uroot -proot：是数据库用户名和密码
输出结果：
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> ./mysqlslap -uroot -proot --concurrency=100 --iterations 10 -a --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=100
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.175 seconds
Minimum number of seconds to run all queries: 0.031 seconds
Maximum number of seconds to run all queries: 1.078 seconds
Number of clients running queries: 100
Average number of queries per client: 1
-
Running for engine innodb：运行的存储引擎是innodb。 -
Average number of seconds to run all queries: 0.175 seconds：运行所有查询的平均秒数。 -
Minimum number of seconds to run all queries: 0.031 seconds：运行所有查询的最小秒数。 -
Maximum number of seconds to run all queries: 1.078 seconds：运行所有查询的最大秒数。 -
Number of clients running queries: 100：运行查询的客户端数（并发数）。 -
Average number of queries per client: 1：平均每个客户的查询数量。
- 分别执行1,50,100,200的并发，循环3次，表有5个字符串字段，5个int型字段，自动创建建数据，新增自动自动增长列，分别测试myisam,innodb两个引擎，指定测试库的名称为enjoytest1，输出内存情况
mysqlslap -uroot -proot --concurrency=1,50,100,200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --create-schema='enjoytest1' --debug-info
输出结果：
PS C:\Program Files\MySQL\MySQL Server 5.7\bin> ./mysqlslap -uroot -proot --concurrency=1,50,100,200 --iterations=3 --number-char-cols=5 --number-int-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --create-schema='enjoytest1'
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.010 seconds
Minimum number of seconds to run all queries: 0.000 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.375 seconds
Minimum number of seconds to run all queries: 0.031 seconds
Maximum number of seconds to run all queries: 1.047 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.041 seconds
Minimum number of seconds to run all queries: 0.015 seconds
Maximum number of seconds to run all queries: 0.093 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.427 seconds
Minimum number of seconds to run all queries: 0.015 seconds
Maximum number of seconds to run all queries: 1.125 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.010 seconds
Minimum number of seconds to run all queries: 0.000 seconds
Maximum number of seconds to run all queries: 0.016 seconds
Number of clients running queries: 1
Average number of queries per client: 0
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.494 seconds
Minimum number of seconds to run all queries: 0.156 seconds
Maximum number of seconds to run all queries: 1.172 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.343 seconds
Minimum number of seconds to run all queries: 0.328 seconds
Maximum number of seconds to run all queries: 0.359 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: 1.250 seconds
Minimum number of seconds to run all queries: 0.593 seconds
Maximum number of seconds to run all queries: 1.579 seconds
Number of clients running queries: 200
Average number of queries per client: 0
