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

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

• 本文所有结论都由作者实践所得。如有错误，恳请指正，不胜感谢。
• 转载请于文首标明出处：【MySQL】mysqlslap 对 MySQL 进行压力测试 - 掘金 (juejin.cn)
• 文章仍未完工，内容会逐步完善

命令选项：

• --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
  

各并发度下的表现为：

并发度	平均总耗时	平均单个客户端执行耗时	平均单个请求耗时
100	10.662	0.10662	5.331 ms
500	51.981	0.103962	5.1981 ms
1000	102.453	0.102453	5.12265 ms
1500	157.340	0.104893	5.24465 ms
2000	213.437	0.1067185	5.335925 ms

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
  

各并发度下的表现为：

并发度	平均总耗时	平均单个客户端执行耗时	平均单个请求耗时
100	2.034	0.02034	ms
500	6.562		ms
1000	12.643		ms
1500	20.003		ms
2000	35.859		ms