【MySQL】mysqlslap 对 MySQL 进行压力测试

mysqlslap 是 mysql 自带的压测工具，可以模拟多个客户端同时向服务器发起请求。

命令选项：

• --concurrency 并发数量，多个用 , 隔开。
• --engines 要测试的引擎，多个用 , 隔开。
• --iterations 测试次数。
• --auto-generate-sql 用系统自己生成的 SQL 脚本来测试。
• --auto-generate-sql-add-autoincrement 使用自增字段。
• --auto-generate-sql-load-type 测试模式（read,write,update,mixed）。
• --number-of-queries 全部查询的次数。
• --debug-info 额外输出CPU以及内存的相关信息，需要开启 Debug 模式。
• --number-int-cols 测试表 int 型字段数量。
• --number-char-cols 测试表 char 型字段数量。
• --create-schema 测试的 db。
• --query 使用自己的 SQL 脚本执行测试。
• --no-defaults 禁止使用默认设置。
• --only-print 仅打印查询语句。

可以使用 --only-print 先看看自动生成的 SQL 是怎样的，如果不满意可以使用自己的 SQL 脚本。

单机读并发测试

这里用的是本机基于 WSL2 的 Docker 中的 MySQL 服务器，因此性能会相比主机运行 MySQL 要差些。

• 100 并发： mysqlslap --no-defaults --iterations=5 --concurrency=100 --number-of-queries=2000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 10.662 seconds Minimum number of seconds to run all queries: 10.141 seconds Maximum number of seconds to run all queries: 11.484 seconds Number of clients running queries: 100 Average number of queries per client: 20 复制代码
• 500 并发： mysqlslap --no-defaults --iterations=5 --concurrency=500 --number-of-queries=10000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 51.981 seconds Minimum number of seconds to run all queries: 51.454 seconds Maximum number of seconds to run all queries: 52.500 seconds Number of clients running queries: 500 Average number of queries per client: 20 复制代码
• 1000 并发： mysqlslap --no-defaults --iterations=5 --concurrency=1000 --number-of-queries=20000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 102.453 seconds Minimum number of seconds to run all queries: 102.016 seconds Maximum number of seconds to run all queries: 102.797 seconds Number of clients running queries: 1000 Average number of queries per client: 20 复制代码
• 1500 并发： mysqlslap --no-defaults --iterations=5 --concurrency=1500 --number-of-queries=30000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 157.340 seconds Minimum number of seconds to run all queries: 155.532 seconds Maximum number of seconds to run all queries: 163.485 seconds Number of clients running queries: 1500 Average number of queries per client: 20 复制代码
• 2000 并发： mysqlslap --no-defaults --iterations=5 --concurrency=2000 --number-of-queries=40000 --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=read -uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 213.437 seconds Minimum number of seconds to run all queries: 208.406 seconds Maximum number of seconds to run all queries: 222.453 seconds Number of clients running queries: 2000 Average number of queries per client: 20 复制代码

各并发度下的表现为：

MySQL 最大连接数由 max_connections 设置，最大为 16384，因此单机 MySQL 最大并发数是 16384，但应该需要根据操作系统提供的资源而定。我跑的 docker 在跑到读并发 8000 时就崩了。

单机写并发测试

• 100 插入并发： mysqlslap --no-defaults --iterations=5 --concurrency=100 --number-of-queries=2000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 2.034 seconds Minimum number of seconds to run all queries: 1.969 seconds Maximum number of seconds to run all queries: 2.094 seconds Number of clients running queries: 100 Average number of queries per client: 20 复制代码
• 500 插入并发： mysqlslap --no-defaults --iterations=5 --concurrency=500 --number-of-queries=10000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 6.562 seconds Minimum number of seconds to run all queries: 6.297 seconds Maximum number of seconds to run all queries: 6.672 seconds Number of clients running queries: 500 Average number of queries per client: 20 复制代码
• 1000 插入并发： mysqlslap --no-defaults --iterations=5 --concurrency=1000 --number-of-queries=20000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 12.643 seconds Minimum number of seconds to run all queries: 12.172 seconds Maximum number of seconds to run all queries: 13.875 seconds Number of clients running queries: 1000 Average number of queries per client: 20 复制代码
• 1500 插入并发： mysqlslap --no-defaults --iterations=5 --concurrency=1500 --number-of-queries=30000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 20.003 seconds Minimum number of seconds to run all queries: 19.781 seconds Maximum number of seconds to run all queries: 20.328 seconds Number of clients running queries: 1500 Average number of queries per client: 20 复制代码
• 2000 插入并发： mysqlslap --no-defaults --iterations=5 --concurrency=2000 --number-of-queries=40000 --auto-generate-sql-add-autoincrement --auto-generate-sql --number-int-cols=10 --number-char-cols=10 --auto-generate-sql-load-type=write uroot -h 127.0.0.1 -p123456 mysqlslap: [Warning] Using a password on the command line interface can be insecure. Benchmark Average number of seconds to run all queries: 35.859 seconds Minimum number of seconds to run all queries: 30.281 seconds Maximum number of seconds to run all queries: 43.938 seconds Number of clients running queries: 2000 Average number of queries per client: 20 复制代码

各并发度下的表现为：