MySQL：使用mysqlslap进行性能测试

mysqlslap

查看使用方法：mysqlslap --help
1）单线程测试

$ mysqlslap -a -uroot -h127.0.0.1 -p
Enter password: 
Benchmark
	Average number of seconds to run all queries: 0.049 seconds   -- 运行所有语句的平均秒数
	Minimum number of seconds to run all queries: 0.049 seconds   --运行所有语句的最小秒数
	Maximum number of seconds to run all queries: 0.049 seconds   --运行所有语句的最大秒数
	Number of clients running queries: 1    -- 客户端数量
	Average number of queries per client: 0  --每个客户端运行查询的平均数

2）多线程测试，使用–concurrency来模拟并发连接

[root@centos7 ~]# mysqlslap -uroot -p -a -c 500
Enter password: 
Benchmark
        Average number of seconds to run all queries: 3.384 seconds
        Minimum number of seconds to run all queries: 3.384 seconds
        Maximum number of seconds to run all queries: 3.384 seconds
        Number of clients running queries: 500
        Average number of queries per client: 0

3）同时测试不同的存储引擎的性能进行对比
–concurrency代表并发数量，多个可以用逗号隔开，当然你也可以用自己的分隔符隔开，这个时候要用到–delimiter开关。
–number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。比如倒数第二个结果2=200/100。
–iterations代表要运行这些测试多少次。
–engines代表要测试的引擎，可以有多个，用分隔符隔开。
–debug-info 代表要额外输出CPU以及内存的相关信息。

$ mysqlslap -uroot -p -a --concurrency=100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb -h127.0.0.1 
Enter password: 
Benchmark
	Running for engine myisam
	Average number of seconds to run all queries: 1.229 seconds
	Minimum number of seconds to run all queries: 1.133 seconds
	Maximum number of seconds to run all queries: 1.377 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: 0.961 seconds
	Minimum number of seconds to run all queries: 0.824 seconds
	Maximum number of seconds to run all queries: 1.073 seconds
	Number of clients running queries: 100
	Average number of queries per client: 10

4）执行一次测试，分别50和20个并发，执行5000次总查询

$ mysqlslap -uroot -p -a --concurrency=10,20 --number-of-queries 5000 -h127.0.0.1
Enter password: 
Benchmark
	Average number of seconds to run all queries: 5.907 seconds
	Minimum number of seconds to run all queries: 5.907 seconds
	Maximum number of seconds to run all queries: 5.907 seconds
	Number of clients running queries: 10
	Average number of queries per client: 500

Benchmark
	Average number of seconds to run all queries: 6.373 seconds
	Minimum number of seconds to run all queries: 6.373 seconds
	Maximum number of seconds to run all queries: 6.373 seconds
	Number of clients running queries: 20
	Average number of queries per client: 250

5）迭代测试

$ mysqlslap -uroot -p -a --concurrency=50 --number-of-queries 500 --iterations=5 -h127.0.0.1
Enter password: 
Benchmark
	Average number of seconds to run all queries: 0.468 seconds
	Minimum number of seconds to run all queries: 0.411 seconds
	Maximum number of seconds to run all queries: 0.593 seconds
	Number of clients running queries: 50
	Average number of queries per client: 10

6） 根据具体业务编写脚本测试

---
stock.sql 的内容
set @id=rand()*9999+1
select * from test.test where id = floor(@id)
---
$ mysql <stock.sql
$ mysqlslap --query=stock.sql -c1024 --number-of-queries=1000000
 -- --number-of-queries 执行次数
$ mysqladmin extended-status -r -i 1 | grep '%question%'
$ mysqladmin extended-status -r -i 1 | grep Com_select

error:

(1): mysqlslap: Error when connecting to server: 2001 Can’t create UNIX socket (24)
查询了很多资料，结果是/etc/security/limits.conf 这里面的限制，把限制增大就可以了，前提不能超过系统的最大限制cat /proc/sys/fs/file-max
https://blog.csdn.net/zhizhengguan/article/details/88352900

$ cat /proc/sys/fs/file-max 
6565506
$  sudo gedit  /etc/security/limits.conf
* 	soft 	nofile            20000     #表示任何一个用户可以打开的最大的文件描述符数量
*	hard    nofile         20000

参考：https://www.cnblogs.com/L-dongf/p/9192724.html