MySQL压力测试mysqlslap工具使用

官方：http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html

mysqlslap -uxxx -pxxx --create-schema=test --number-of-queries=5000000 --concurrency=1  -q"insert into t(v)values('aaaaaaa');"

其中: create-schema用来指定mysqlslap需要的数据库，如果不指定，他会默认找mysqlslap这个库。 number-of-queries，要执行的查询次数。 concurrency,并发的链接数。 -q 后面就是要执行的sql语句，也可以指定取一个文件中的数据。

1. 参数说明

一些比较重要的参数：
--defaults-file，配置文件存放位置
--create-schema，测试的schema，MySQL中schema也就是database
--concurrency，并发数
--engines，测试引擎，可以有多个，用分隔符隔开。
--iterations，迭代的实验次数
--socket，socket，文件位置
--debug-info，打印内存和CPU的信息
--only-print，只打印测试语句而不实际执行
--auto-generate-sql，自动产生测试SQL
--auto-generate-sql-load-type，测试SQL的类型。类型有mixed，update，write，key，read。
--number-of-queries，执行的SQL总数量
--number-int-cols，表内int列的数量
--number-char-cols，表内char列的数量
--query=name，使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测

1. 命令

测试，分别50和100个并发，执行1000次总查询：
$mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info
$mysqlslap -a --only-print
测试同时不同的存储引擎的性能进行对比：
$ mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info

这个东东高效方便灵活，是在是dba居家出行必备的杀人工具。

sql0中存放的是类似下面的数据
select id,title from blog_0 where id=2690
sql1中存放的是类似下面的数据
select id,title,body from blog_0 where id=2690
Select id,title from blog_0
[root@SJSWT44-121 stat1.db houjw]# ./mysqlslap --host=blog31.db --create-schema=fb  --query=sql0 --concurrency=100
Benchmark
        Average number of seconds to run all queries: 185.740 seconds
        Minimum number of seconds to run all queries: 185.740 seconds
        Maximum number of seconds to run all queries: 185.740 seconds
        Number of clients running queries: 100
        Average number of queries per client: 100000
Select id,title,body from blog_2
[root@SJSWT44-121 stat1.db houjw]# ./mysqlslap --host=xx.db --create-schema=fb  --query=sql2 --concurrency=100
Benchmark
        Average number of seconds to run all queries: 229.476 seconds
        Minimum number of seconds to run all queries: 229.476 seconds
        Maximum number of seconds to run all queries: 229.476 seconds
        Number of clients running queries: 100
        Average number of queries per client: 100000

##补充个测试结果## innodb,myisam,infobright

innodb表 pool=5G
mysqlslap --create-schema=fb -h 127.0.0.1 -P 3306 -u root --password=pwdxxx --query=test_select_sql.sql --concurrency=50,100 
Benchmark
           Average number of seconds to run all queries: 132.303 seconds
           Minimum number of seconds to run all queries: 132.303 seconds
           Maximum number of seconds to run all queries: 132.303 seconds
           Number of clients running queries: 50
           Average number of queries per client: 100000

Benchmark
           Average number of seconds to run all queries: 242.263 seconds
           Minimum number of seconds to run all queries: 242.263 seconds
           Maximum number of seconds to run all queries: 242.263 seconds
           Number of clients running queries: 100
           Average number of queries per client: 100000
innodb表 pool=10G
mysqlslap --create-schema=fb -h 127.0.0.1 -P 3306 -u root --password=pwdxxx --query=test_select_sql.sql --concurrency=50,100
Benchmark
           Average number of seconds to run all queries: 130.861 seconds
           Minimum number of seconds to run all queries: 130.861 seconds
           Maximum number of seconds to run all queries: 130.861 seconds
           Number of clients running queries: 50
           Average number of queries per client: 100000

Benchmark
           Average number of seconds to run all queries: 239.792 seconds
           Minimum number of seconds to run all queries: 239.792 seconds
           Maximum number of seconds to run all queries: 239.792 seconds
           Number of clients running queries: 100
           Average number of queries per client: 100000

myisam  表
key_buffer_size = 256M
sort_buffer_size = 4M #1M
read_buffer_size = 4M #1M
read_rnd_buffer_size =16M #4M
myisam_sort_buffer_size = 128M #64M
mysqlslap --create-schema=fb -h 127.0.0.1 -P 3306 -u root --password=pwdxxx --query=test_select_sql_myisam.sql --concurrency=50,100                  
Benchmark
           Average number of seconds to run all queries: 145.840 seconds
           Minimum number of seconds to run all queries: 145.840 seconds
           Maximum number of seconds to run all queries: 145.840 seconds
           Number of clients running queries: 50
           Average number of queries per client: 100000

Benchmark
           Average number of seconds to run all queries: 246.108 seconds
           Minimum number of seconds to run all queries: 246.108 seconds
           Maximum number of seconds to run all queries: 246.108 seconds
           Number of clients running queries: 100
           Average number of queries per client: 100000           
infobright
 mysqlslap --create-schema=fb -h xx.db -P 3307 -u xxx --password=xxx --query=test_select_sql_ib.sql --concurrency=50,100                    
Benchmark
           Average number of seconds to run all queries: 10757.161 seconds
           Minimum number of seconds to run all queries: 10757.161 seconds
           Maximum number of seconds to run all queries: 10757.161 seconds           
test_select_sql_xx.sql 内容如下,10w
select id,userid,content,dtime from doing_new_innodb where id=xxx

innodb plugin

 1.8G -rw-rw---- 1 mysql mysql  1.8G Sep 17 14:47 ./doing_new_innodb.ibd       
 977M -rw-rw---- 1 mysql mysql  976M Sep 27 02:16 ./doing_new_innodb_size4.ibd
1013M -rw-rw---- 1 mysql mysql 1012M Sep 27 01:10 ./doing_new_innodb_size8.ibd

 mysqlslap --create-schema=fb -h 127.0.0.1 -P 3306 -u root  --password=xxxxxx --query=test_select_sql_size_8.sql --concurrency=50,100      
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8

Benchmark
         Average number of seconds to run all queries: 173.478 seconds
         Minimum number of seconds to run all queries: 173.478 seconds
         Maximum number of seconds to run all queries: 173.478 seconds
         Number of clients running queries: 50
         Average number of queries per client: 100000

Benchmark
         Average number of seconds to run all queries: 243.434 seconds
         Minimum number of seconds to run all queries: 243.434 seconds
         Maximum number of seconds to run all queries: 243.434 seconds

 mysqlslap --create-schema=fb -h 127.0.0.1 -P 3306 -u root  --password=xxxxxx --query=test_select_sql_size_4.sql --concurrency=50,100      
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

Benchmark
         Average number of seconds to run all queries: 130.866 seconds
         Minimum number of seconds to run all queries: 130.866 seconds
         Maximum number of seconds to run all queries: 130.866 seconds
         Number of clients running queries: 50
         Average number of queries per client: 100000

Benchmark
         Average number of seconds to run all queries: 243.735 seconds
         Minimum number of seconds to run all queries: 243.735 seconds
         Maximum number of seconds to run all queries: 243.735 seconds
         Number of clients running queries: 100
         Average number of queries per client: 100000