MySQL的压测工具

2019独角兽企业重金招聘Python工程师标准>>>

前言

    mysqlslap是mysql自带的一款性能压测工具，通过模拟多个并发客户端访问MySQL来进行压力测试，同时提供了详细的数据性能报告。此工具可以自动生成测试表和数据，并且可以模拟读写、混合读写、查询等不同的使用场景，也能够很好的对比多个存储引擎在相同环境的并发压力下的性能差异。本文是使用的《分布式服务架构——原理、设计与实践》的案例。

单线程测试

λ mysqlslap -a -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.141 seconds
        Minimum number of seconds to run all queries: 0.141 seconds
        Maximum number of seconds to run all queries: 0.141 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

    测试结果显示单线程连接一次服务器需要141ms。

多线程测试

λ mysqlslap -a -c 100 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 5.500 seconds
        Minimum number of seconds to run all queries: 5.500 seconds
        Maximum number of seconds to run all queries: 5.500 seconds
        Number of clients running queries: 100
        Average number of queries per client: 0

    可以看到使用100个线程同时连接一次服务器需要5500ms，同单线程比，响应时间大幅度提升，这个不知道是什么原因，需要后续查询下。

多次测试求平均值

λ mysqlslap -a -i 10 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.370 seconds
        Minimum number of seconds to run all queries: 0.140 seconds
        Maximum number of seconds to run all queries: 1.203 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

    最小响应时间和单线程测试一样，但是平均响应时间还是有差，我的数据库可能被下毒了。

读操作的性能

λ mysqlslap -a -c10 --number-of-queries=1000 --auto-generate-sql-load-type=read -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 0.062 seconds
        Minimum number of seconds to run all queries: 0.062 seconds
        Maximum number of seconds to run all queries: 0.062 seconds
        Number of clients running queries: 10
        Average number of queries per client: 100

    读操作在并发数是10，进行1000次的读操作，每次的读需要62ms。

写操作的性能

λ mysqlslap -a -c10 --number-of-queries=1000 --auto-generate-sql-load-type=write -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 11.875 seconds
        Minimum number of seconds to run all queries: 11.875 seconds
        Maximum number of seconds to run all queries: 11.875 seconds
        Number of clients running queries: 10
        Average number of queries per client: 100

    毫无疑问，我这台机子的瓶颈在写，平均写时间要花掉11.875秒，苍天啊，下面用混合的方式印证下。

混合操作性能

λ mysqlslap -a -c10 --number-of-queries=1000 --auto-generate-sql-load-type=mixed -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 5.844 seconds
        Minimum number of seconds to run all queries: 5.844 seconds
        Maximum number of seconds to run all queries: 5.844 seconds
        Number of clients running queries: 10
        Average number of queries per client: 100

    基本上，在混合模式下的性能和在多线程情况下的性能是相同，推测在单线程的时候，是随机读写，在多线程下是混合模式操作的，对单线程进行反复的执行，印证了，单线程是随机读写测试的。

不同数据库引擎的性能

λ mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --engine=myisam,innodb --iterations=5
-uroot -proot
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.115 seconds
        Minimum number of seconds to run all queries: 0.109 seconds
        Maximum number of seconds to run all queries: 0.125 seconds
        Number of clients running queries: 50
        Average number of queries per client: 20

Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.121 seconds
        Minimum number of seconds to run all queries: 0.094 seconds
        Maximum number of seconds to run all queries: 0.156 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 4.434 seconds
        Minimum number of seconds to run all queries: 3.797 seconds
        Maximum number of seconds to run all queries: 5.421 seconds
        Number of clients running queries: 50
        Average number of queries per client: 20

Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 3.759 seconds
        Minimum number of seconds to run all queries: 2.969 seconds
        Maximum number of seconds to run all queries: 4.250 seconds
        Number of clients running queries: 100
        Average number of queries per client: 10

    上面现实了myisam和innodb两种引擎的性能，具体差异需要查阅mysql的原理的书籍查找原因。

总结

    数据库作为应用系统的性能的重要一环，掌握其数据库的性能测试方法有助于判断系统的性能瓶颈所在，已经系统架构中，数据库的部署。

附录

mysqlslap测试的参数的解释，我们可以通过使用mysqlslap --help来显示使用方法：

Default options are read from the following files in the given order:
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

--c代表并发数量，多个可以用逗号隔开，concurrency=10,50,100, 并发连接线程数分别是10、50、100个并发。

--engines代表要测试的引擎，可以有多个，用分隔符隔开。
--iterations代表要运行这些测试多少次。
-- a 指的是auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
--debug-info 代表要额外输出CPU以及内存的相关信息。
--number-int-cols ：创建测试表的 int 型字段数量
--auto-generate-sql-add-autoincrement : 代表对生成的表自动添加auto_increment列，从5.1.18版本开始
--number-char-cols 创建测试表的 char 型字段数量。
--create-schema 测试的schema，MySQL中schema也就是database。
--query  使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--only-print 如果只想打印看看SQL语句是什么，可以用这个选项。

本文最后参考了：https://blog.csdn.net/jjwen/article/details/51569234

转载于:https://my.oschina.net/u/3470849/blog/2223119