Mysql压测工具mysqlslap工具使用方法详解-百分百源码网

Mysql压测工具mysqlslap工具使用方法详解

发布时间：01/15 来源：浏览：关键词：

mysqlslap是mysql中的一个压力测试工具了它可以测试单mysql服务器也可以测试集群服务器，下面一聚教程小伙伴就为各位介绍mysqlslap工具使用详解。

mysqlslap是从5.1.4版开始的一个MySQL官方提供的压力测试工具。通过模拟多个并发客户端访问MySQL来执行压力测试，同时详细的提供了“高负荷攻击MySQL”的数据性能报告。并且能很好的对比多个存储引擎在相同环境下的并发压力性能差别。通过mysqlslap –help可以获得可用的选项，这里列一些主要的参数，更详细的说明参考官方手册。如果是系统自带或者使用rpm包安装的mysql，安装了MySQL-client端的包就有mysqlslap这个工具。如果是源码安装mysqlslap就直接在bin目录下，如果是rpm安装，需要单独安装rpm包。

mysqlslap参数详细说明：

–auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
–auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
–auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
–number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
–number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
–number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
–query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
–create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
–commint=N 多少条DML后提交一次。
–compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
–concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者–delimiter参数指定的值做为分隔符。例如：–concurrency=100,200,500。
–engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：–engines=myisam,innodb。
–iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
–only-print 只打印测试语句而不实际执行。
–detach=N 执行N条语句后断开重连。
–debug-info, -T 打印内存和CPU的相关信息。

mysqlslap测试举例

单线程测试。测试做了什么。

# mysqlslap -a -uroot -p123456

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

# mysqlslap -a -c 100 -uroot -p123456

迭代测试。用于需要多次执行测试得到平均值。

# mysqlslap -a -i 10 -uroot -p123456
# mysqlslap —auto-generate-sql-add-autoincrement -a -uroot -p123456
# mysqlslap -a –auto-generate-sql-load-type=read -uroot -p123456
# mysqlslap -a –auto-generate-secondary-indexes=3 -uroot -p123456
# mysqlslap -a –auto-generate-sql-write-number=1000 -uroot -p123456
# mysqlslap –create-schema world -q "select count(*) from City" -uroot -p123456
# mysqlslap -a -e innodb -uroot -p123456
# mysqlslap -a –number-of-queries=10 -uroot -p123456

测试同时不同的存储引擎的性能进行对比：

# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –engine=myisam,innodb –debug-info -uroot -p123456

执行一次测试，分别50和100个并发，执行1000次总查询：

# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info -uroot -p123456

50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:

# mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –iterations=5 –debug-info -uroot -p123456

如下图是执行/usr/local/mysql/bin/mysqlslap -a -c 500 -i 10 -uroot -p

再补充一些测试实例:用自带的SQL脚本来测试。

MySQL版本为5.1.23

[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=50,100,200 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=200 --debug-info -uroot -p1 -S/tmp/mysql_3310.sock
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.063 seconds
        Minimum number of seconds to run all queries: 0.063 seconds
        Maximum number of seconds to run all queries: 0.063 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.070 seconds
        Minimum number of seconds to run all queries: 0.070 seconds
        Maximum number of seconds to run all queries: 0.070 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 0.092 seconds
        Minimum number of seconds to run all queries: 0.092 seconds
        Maximum number of seconds to run all queries: 0.092 seconds
        Number of clients running queries: 200
        Average number of queries per client: 1
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.115 seconds
        Minimum number of seconds to run all queries: 0.115 seconds
        Maximum number of seconds to run all queries: 0.115 seconds
        Number of clients running queries: 50
        Average number of queries per client: 4
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.134 seconds
        Minimum number of seconds to run all queries: 0.134 seconds
        Maximum number of seconds to run all queries: 0.134 seconds
        Number of clients running queries: 100
        Average number of queries per client: 2
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.192 seconds
        Minimum number of seconds to run all queries: 0.192 seconds
        Maximum number of seconds to run all queries: 0.192 seconds
        Number of clients running queries: 200
        Average number of queries per client: 1
User time 0.06, System time 0.15
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 5803, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 8173, Involuntary context switches 528

我来解释一下结果的含义。拿每个引擎最后一个Benchmark示例。对于INNODB引擎，200个客户端同时运行这些SQL语句平均要花0.192秒。相应的MYISAM为0.092秒。

2、用我们自己定义的SQL 脚本来测试。

这些数据在另外一个MySQL实例上。版本为5.0.45先看一下这两个表的相关数据。
1）、总记录数。

mysql> select table_rows as rows from information_schema.tables where table_schema='t_girl' and table_name='article';
+--------+
| rows |
+--------+
| 296693 |
+--------+
1 row in set (0.01 sec)
mysql> select table_rows as rows from information_schema.tables where table_schema='t_girl' and table_name='category';
+------+
| rows |
+------+
| 113 |
+------+
1 row in set (0.00 sec)

2）、总列数。

mysql> select count(*) as column_total from information_schema.columns where table_schema = 't_girl' and table_name = 'article';
+--------------+
| column_total |
+--------------+
| 32 |
+--------------+
1 row in set (0.01 sec)
mysql> select count(*) as column_total from information_schema.columns where table_schema = 't_girl' and table_name = 'category';
+--------------+
| column_total |
+--------------+
| 9 |
+--------------+
1 row in set (0.01 sec)

3）、调用的存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS `t_girl`.`sp_get_article`$$
CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_article`(IN f_category_id int,
IN f_page_size int, IN f_page_no int
)
BEGIN
set @stmt = 'select a.* from article as a inner join ';
set @stmt = concat(@stmt,'(select a.aid from article as a ');
if f_category_id != 0 then
set @stmt = concat(@stmt,' inner join (select cid from category where cid = ',f_category_id,' or parent_id = ',f_category_id,') as b on a.category_id = b.cid');
end if;
if f_page_size >0 && f_page_no > 0 then
set @stmt = concat(@stmt,' limit ',(f_page_no-1)*f_page_size,',',f_page_size);
end if;

set @stmt = concat(@stmt,') as b on (a.aid = b.aid)');
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
END$$
DELIMITER ;

4）、我们用mysqlslap来测试

以下得这个例子代表用mysqlslap来测试并发数为25,50,100的调用存储过程，并且总共调用5000次。

[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --query='call t_girl.sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info -uroot -p -S/tmp/mysql50.sock
Enter password:
Benchmark
        Average number of seconds to run all queries: 3.507 seconds
        Minimum number of seconds to run all queries: 3.507 seconds
        Maximum number of seconds to run all queries: 3.507 seconds
        Number of clients running queries: 25
        Average number of queries per client: 200

平均每个并发运行200个查询用了3.507秒。

Benchmark
        Average number of seconds to run all queries: 3.742 seconds
        Minimum number of seconds to run all queries: 3.742 seconds
        Maximum number of seconds to run all queries: 3.742 seconds
        Number of clients running queries: 50
        Average number of queries per client: 100
Benchmark
        Average number of seconds to run all queries: 3.697 seconds
        Minimum number of seconds to run all queries: 3.697 seconds
        Maximum number of seconds to run all queries: 3.697 seconds
        Number of clients running queries: 100
        Average number of queries per client: 50
User time 0.87, System time 0.33
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 1877, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 27218, Involuntary context switches 3100

看一下SHOW PROCESSLIST 结果

上面的测试语句其实也可以这样写

[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --create-schema='t_girl' --query='call sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info -uroot -p -S/tmp/mysql50.sock

小总结一下。mysqlslap对于模拟多个用户同时对MySQL发起“进攻”提供了方便。同时详细的提供了“高负荷攻击MySQL”的详细数据报告。而且如果你想对于多个引擎的性能。这个工具再好不过了。

对于程序员和DBA，可以把自己写的SQL语句写入到一个脚本中去，然后再通过mysqlslap工具对这些语句进行高并发的测试，大家就能通过报告找出在高并发下导致MYSQL运行缓慢的原因，这个工具非常有用哟

打赏