MySQL：使用mysqlslap进行性能测试

太过爱你忘了你带给我的痛 2022-03-18 18:56 257阅读 0赞

# 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