mysqlslap工具测试mysql DB的性能

来源：互联网发布：手机淘宝缓存文件在哪编辑：程序博客网时间：2024/05/21 05:57

在mysql5.1以后的版本：客户端带了个工具mysqlslap可以对mysql进行压力测试：

可以使用mysqlslap --help来显示使用方法：

Default options are read from the following files in the givenorder:
/etc/mysql/my.cnf /etc/my.cnf~/.my.cnf

--concurrency代表并发数量，多个可以用逗号隔开，concurrency=10,50,100,并发连接线程数分别是10、50、100个并发。

--engines代表要测试的引擎，可以有多个，用分隔符隔开。
--iterations代表要运行这些测试多少次。
--auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
--auto-generate-sql-load-type代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
--number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。
--debug-info 代表要额外输出CPU以及内存的相关信息。
--number-int-cols ：创建测试表的 int 型字段数量
--auto-generate-sql-add-autoincrement: 代表对生成的表自动添加auto_increment列，从5.1.18版本开始
--number-char-cols 创建测试表的 char 型字段数量。
--create-schema 测试的schema，MySQL中schema也就是database。
--query 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--only-print 如果只想打印看看SQL语句是什么，可以用这个选项。

mysqlslap -umysql-p123 --concurrency=100 --iterations=1--auto-generate-sql --auto-generate-sql-add-autoincrement--auto-generate-sql-load-type=mixed --engine=myisam--number-of-queries=10 --debug-info

或：

指定数据库和sql语句:

mysqlslap -h192.168.3.18 -P4040 --concurrency=100 --iterations=1--create-schema='test'--query='select * from test;'--number-of-queries=10 --debug-info -umysql -p123

要是看到底做了什么可以加上：--only-print

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

以上表明100个客户端同时运行要25秒

再如:

mysqlslap -uroot -p123456 --concurrency=100 --iterations=1--engine=myisam --create-schema='haodingdan112' --query='select * From order_boxing_transit where id = 10'--number-of-queries=1 --debug-info

如下应用场景：
VTService性能瓶颈SQL查询慢测试环境模拟测试

场景: select
a.takedownNotice_id id from takedownNoticeItem a, matchedVideo bwhere a.company_id = 4 and
b.company_id = 4 and a.matchedVideo_id = b.id anda.takedownNotice_id in (7570310,8036040) and
b.takeoff_time = 0 group by a.takedownNotice_id
该SQL目的是检查在线的notice,如果notice下面的item均下线,更新该notice为下线。

测试数据:
通过spawner工具在matchedVideo表中导入200000条match,均为116公司同个meta.同时关联takedownNoticeItem表,创建两个notice,对应item下记录数分别为120001,110013.与线上保持一致
root@(none) 06:36:07>select count(*) fromtracker5_623.matchedVideo;
+----------+
| count(*) |
+----------+
|   200635 |
+----------+
1 row in set (0.35 sec)
root@(none) 06:46:41>select count(*) fromtracker5_623.takedownNoticeItem where takedownNotice_id in(999);
+----------+
| count(*) |
+----------+
|   120001 |
+----------+
1 row in set (0.00 sec) root@(none) 06:46:49>select count(*)from tracker5_623.takedownNoticeItem where takedownNotice_id in(1120);
+----------+
| count(*) |
+----------+
|   110013 |
+----------+
1 row in set (0.05 sec)

测试方法:
通过mysql压力测试工具 mysqlslap进行多并发查询测试

测试结果:
单个用户单次查询最长时间:0.950 seconds: xiang_sha@ubuntu:~/3.9$ mysqlslap-h192.168.1.54 --concurrency=1 --iterations=1--create-schema='tracker5_623' --query='select a.takedownNotice_idid from takedownNoticeItem a, matchedVideo b where a.company_id =116 and b.company_id = 116 and a.matchedVideo_id = b.id anda.takedownNotice_id in (1120,999) and b.takeoff_time = 0 group bya.takedownNotice_id;' --number-of-queries=1 --debug-info -uabc-p123
Benchmark
   Averagenumber of seconds to run all queries: 0.950 seconds
   Minimumnumber of seconds to run all queries: 0.950 seconds
   Maximumnumber of seconds to run all queries: 0.950 seconds
   Number ofclients running queries: 1
   Averagenumber of queries per client: 1
多个用户并发单次查询:
50个并发查询最长时间:0.035 seconds/16.830 seconds
90个并发查询最长时间:4.599 seconds/33.076 seconds
200个并发查询最长时间:81.776 seconds Benchmark
   Averagenumber of seconds to run all queries: 0.035 seconds
   Minimumnumber of seconds to run all queries: 0.035 seconds
   Maximumnumber of seconds to run all queries: 0.035 seconds
   Number ofclients running queries: 50　　
   Averagenumber of queries per client: 0
Benchmark
   Averagenumber of seconds to run all queries: 16.830 seconds
   Minimumnumber of seconds to run all queries: 16.830 seconds
   Maximumnumber of seconds to run all queries: 16.830 seconds
   Number ofclients running queries: 50
   Averagenumber of queries per client: 0
Benchmark
   Averagenumber of seconds to run all queries: 4.599 seconds
   Minimumnumber of seconds to run all queries: 4.599 seconds
   Maximumnumber of seconds to run all queries: 4.599 seconds
   Number ofclients running queries: 90
   Averagenumber of queries per client: 0 Benchmark
   Averagenumber of seconds to run all queries: 33.076 seconds
   Minimumnumber of seconds to run all queries: 33.076 seconds
   Maximumnumber of seconds to run all queries: 33.076 seconds
   Number ofclients running queries: 90
   Averagenumber of queries per client: 0 Benchmark
   Averagenumber of seconds to run all queries: 81.776 seconds
   Minimumnumber of seconds to run all queries: 81.776 seconds
   Maximumnumber of seconds to run all queries: 81.776 seconds
   Number ofclients running queries: 200
   Averagenumber of queries per client: 0
多个用户并发连续多次查询:
20个并发查询最长时间:0.027 seconds
50个并发查询最长时间:0.065 seconds
100个并发查询最长时间:4.605 seconds
150个并发查询最长时间:7.050 seconds
200个并发查询最长时间:43.716 seconds xiang_sha@ubuntu:~/3.9$ mysqlslap-h192.168.1.54 --concurrency=20,50,100,150,200--iterations=10 --create-schema='tracker5_623' --query='selecta.takedownNotice_id id from takedownNoticeItem a, matchedVideo bwhere a.company_id = 116 and b.company_id = 116 anda.matchedVideo_id = b.id and a.takedownNotice_id in (1120,999) andb.takeoff_time = 0 group by a.takedownNotice_id;'--number-of-queries=1 --debug-info -uabc -p123
Benchmark
   Averagenumber of seconds to run all queries: 0.025 seconds
   Minimumnumber of seconds to run all queries: 0.024 seconds
   Maximumnumber of seconds to run all queries: 0.027 seconds
   Number ofclients running queries: 20
   Averagenumber of queries per client: 0
Benchmark
   Averagenumber of seconds to run all queries: 0.062 seconds
   Minimumnumber of seconds to run all queries: 0.061 seconds
   Maximumnumber of seconds to run all queries: 0.065 seconds
   Number ofclients running queries: 50
   Averagenumber of queries per client: 0
Benchmark
   Averagenumber of seconds to run all queries: 4.561 seconds
   Minimumnumber of seconds to run all queries: 4.201 seconds
   Maximumnumber of seconds to run all queries: 4.605 seconds
   Number ofclients running queries: 100
   Averagenumber of queries per client: 0
Benchmark
   Averagenumber of seconds to run all queries: 5.431 seconds
   Minimumnumber of seconds to run all queries: 4.599 seconds
   Maximumnumber of seconds to run all queries: 7.050 seconds
   Number ofclients running queries: 150
   Averagenumber of queries per client: 0
Benchmark
   Averagenumber of seconds to run all queries: 11.176 seconds
   Minimumnumber of seconds to run all queries: 4.603 seconds
   Maximumnumber of seconds to run all queries: 43.716 seconds
   Number ofclients running queries: 200
   Averagenumber of queries per client: 0

测试结果分析:
1.在持续1小时的测试中,选取多组数据平均数值作为结果,可以看到,随着并发数的增加,查询时间明显增加
2.由于测试环境tracker库总量为350Ｗ,线上tracker库总量为36亿.无法完全模拟线上环境数据的多样性
3.测试结果受到硬性,网络环境,ＤＢ当前负载等因素影响,此次测试结果仅供参考

0 0