Mysql 数据库压力测试工具 - MySqlSlap

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​ ​	只打印测试语句而不实际执行。

命令示例

1. 并发数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. 分别执行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