注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

-Snoopy's 小宇宙-

, ﹏﹏﹏。~o○ ...叮叮当,开心万岁o. ﹏~

 
 
 

日志

 
 

mysqlslap 压力测试3  

2010-03-13 23:13:07|  分类: MySql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
http://linux.chinaunix.net/techdoc/database/2008/06/16/1011330.shtml
http://blog.chinaunix.net/u/29134/showart_970894.html
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”的详细数据报告。而且如果你想对于多个引擎的性能。这个工具再好不过了。
               
  评论这张
 
阅读(76)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017