mysql压力(并发)测试--mysqlslap

最新推荐文章于 2024-05-29 16:53:40 发布

原创最新推荐文章于 2024-05-29 16:53:40 发布 · 4.1k 阅读

6 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

30 篇文章

订阅专栏

本文通过mysqlslap工具测试了不同并发量下MySQL InnoDB引擎的性能表现。结果显示，在单节点数据库中，1000并发量运行平稳，但当并发量增加到12000时，数据库开始出现连接数过多的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[root@document-system bin]# mysqlslap -hlocalhost -uroot -proot -P3306 --concurrency=1000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=1000 --debug-info
Warning: Using a password on the command line interface can be insecure.
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.060 seconds
	Minimum number of seconds to run all queries: 0.060 seconds
	Maximum number of seconds to run all queries: 0.060 seconds
	Number of clients running queries: 1000
	Average number of queries per client: 1


User time 0.08, System time 0.43
Maximum resident set size 28220, Integral resident set size 0
Non-physical pagefaults 7459, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 16324, Involuntary context switches 39

上图中是在mysql的bin目录下执行的命令。我们可以看出1000的并发量对于单个数据库而言是没有问题的，下面看5000的并发量：

[root@document-system bin]# mysqlslap -hlocalhost -uroot -proot -P3306 --concurrency=5000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000 --debug-info
Warning: Using a password on the command line interface can be insecure.
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 0.598 seconds
	Minimum number of seconds to run all queries: 0.598 seconds
	Maximum number of seconds to run all queries: 0.598 seconds
	Number of clients running queries: 5000
	Average number of queries per client: 1


User time 0.53, System time 1.88
Maximum resident set size 134844, Integral resident set size 0
Non-physical pagefaults 30313, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 96141, Involuntary context switches 33

这时候数据库就有点吃力了，再看12000的并发量：

[root@document-system bin]# mysqlslap -hlocalhost -uroot -proot -P3306 --concurrency=12000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=12000 --debug-info
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
mysqlslap: Error when connecting to server: 1040 Too many connections
Benchmark
	Running for engine innodb
	Average number of seconds to run all queries: 1.141 seconds
	Minimum number of seconds to run all queries: 1.141 seconds
	Maximum number of seconds to run all queries: 1.141 seconds
	Number of clients running queries: 12000
	Average number of queries per client: 1


User time 1.68, System time 4.36
Maximum resident set size 303504, Integral resident set size 0
Non-physical pagefaults 68515, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 271921, Involuntary context switches 81

这时候MySQL就拒绝了部分请求。所以单节点数据库的查询并发超过10000，就扛不住了。

参考：https://blog.youkuaiyun.com/zhang123456456/article/details/73089195