为了账号安全,请及时绑定邮箱和手机立即绑定

用mysqlslap对MySQL进行压力测试

标签:
MySQL


MySQL5.1地的确提供了好多有力的工具来帮助我们DBA进行数据库管理。

现在看一下这个压力测试工具mysqlslap.

关于他的选项手册上以及--help介绍的很详细。

我解释一下一些常用的选项。

这里要注意的几个选项:

--concurrency代表并发数量,多个可以用逗号隔开,当然你也可以用自己的分隔符隔开,这个时候要用到--delimiter开关。

--engines代表要测试的引擎,可以有多个,用分隔符隔开。

--iterations代表要运行这些测试多少次。

--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。

--auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的(read,write,update,mixed)

--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。比如倒数第二个结果2=200/100。

--debug-info 代表要额外输出CPU以及内存的相关信息。

--number-int-cols 代表示例表中的INTEGER类型的属性有几个。

--number-char-cols 意思同上。

--create-schema 代表自己定义的模式(在MySQL中也就是库)。

--query 代表自己的SQL脚本。

--only-print 如果只想打印看看SQL语句是什么,可以用这个选项。

现在来看一些我测试的例子。

1、用自带的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 结果

mysql> show processlist;

+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+

| Id   | User | Host               | db                 | Command | Time  | State              | Info                                                                                                 |

+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+

…………

| 3177 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3178 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3179 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3181 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3180 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3182 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3183 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3187 | root | %                  | t_girl             | Query   |     0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3186 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3194 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3203 | root | %                  | t_girl             | Query   |     0 | NULL               | deallocate prepare s1                                                                                | 

…………

| 3221 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3222 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3223 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3224 | root | %                  | t_girl             | Query   |     0 | removing tmp table | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3225 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

| 3226 | root | %                  | t_girl             | Query   |     0 | NULL               | select a.* from article as a inner join (select a.aid from article as a  inner join (select cid from | 

+------+------+--------------------+--------------------+---------+-------+--------------------+------------------------------------------------------------------------------------------------------+

55 rows in set (0.00 sec)

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

[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”的详细数据报告。

而且如果你想对于多个引擎的性能。这个工具再好不过了。

©著作权归作者所有:来自51CTO博客作者david_yeung的原创作品,如需转载,请与作者联系,否则将追究法律责任

MySQL数据库休闲MySQL性能优化


点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消