Mysql 数据库压力测试工具 - MySqlSlap
2020-03-13 本文已影响0人
xiaolyuh
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
