Sysbench压测工具常用版本有0.4(较旧),0.5(据说非官方正宗开源版),1.0(最新的官方开源版本),不同版本之间的系统压测差异较小。但对数据库的压测参数、结果具有较大差异,这里针对sysbench压测MYSQL数据库做下安装、使用介绍。
二、压测环境
硬件环境:
测试机 |
Dell PowerEdge R730xd |
CPU |
Intel(R) Xeon(R) CPU E5-2620 v3 @ 2.40GHz (2物理CPU,每个6核心,每个核心2线程,总共24processor) |
内存 |
128G |
RAID卡 |
PERC H730P Mini Memory Size : 2048MB |
raid级别 |
Raid-5 |
文件系统 |
xfs |
硬盘 |
4T SAS * 12 |
操作系统 |
Red Hat Enterprise Linux Server release 6.5 (Santiago) |
内核 |
2.6.32-431.el6.x86_64 |
MYSQL版本 |
5.7.17 |
压测工具 |
sysbench 1.0.3 |
四、压测模型
测试工具 |
sysbench |
测试记录数 |
1,000,000 |
测试模式 |
complex |
run time(秒) |
300 |
最大请求数 |
100,000 |
并发线程数 |
1000 |
以oltp_read_write.lua压测为案例
1、sysbench1.0压测工具安装——请参照压测工具之Sysbench(1_系统压测)
2、sysbench oltp压测数据准备
sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.1.0.10 --mysql-port=3306 --mysql-user=dbuser --mysql-password=abc.1234 --mysql-db=dbtest --tables=10 --table-size=1000000 --report-interval=10 --threads=1000 --time=300 prepare
点击(此处)折叠或打开
-
[root@DB_TEST sysbench]# sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.1.0.10 --mysql-port=3306 --mysql-user=dbuser --mysql-password=abc.1234 --mysql-db=dbtest --tables=10 --table-size=1000000 --report-interval=10 --threads=1000 --time=300 prepare
-
sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)
-
-
Initializing worker threads...
-
-
Creating table 'sbtest7'...Creating table 'sbtest6'...
-
-
Creating table 'sbtest2'...
-
Creating table 'sbtest10'...
-
Creating table 'sbtest8'...
-
Creating table 'sbtest1'...
-
Creating table 'sbtest4'...
-
Creating table 'sbtest3'...
-
Creating table 'sbtest5'...
-
Creating table 'sbtest9'...
-
Inserting 1000000 records into 'sbtest3'
-
Inserting 1000000 records into 'sbtest8'
-
Inserting 1000000 records into 'sbtest2'
-
Inserting 1000000 records into 'sbtest6'
-
Inserting 1000000 records into 'sbtest10'
-
Inserting 1000000 records into 'sbtest7'
-
Inserting 1000000 records into 'sbtest1'
-
Inserting 1000000 records into 'sbtest5'
-
Inserting 1000000 records into 'sbtest9'
-
Inserting 1000000 records into 'sbtest4'
-
Creating a secondary index on 'sbtest1'...
-
Creating a secondary index on 'sbtest7'...
-
Creating a secondary index on 'sbtest5'...
-
Creating a secondary index on 'sbtest3'...
-
Creating a secondary index on 'sbtest4'...
-
Creating a secondary index on 'sbtest9'...
-
Creating a secondary index on 'sbtest6'...
-
Creating a secondary index on 'sbtest10'...
-
Creating a secondary index on 'sbtest2'...
- Creating a secondary index on 'sbtest8'...
sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.1.0.10 --mysql-port=3306 --mysql-user=dbuser --mysql-password=abc.1234 --mysql-db=dbtest --tables=10 --table-size=1000000 --report-interval=10 --threads=1000 --time=300 run
点击(此处)折叠或打开
-
[root@DB_TEST sysbench]# sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.1.0.10 --mysql-port=3306 --mysql-user=dbuser --mysql-password=abc.1234 --mysql-db=dbtest --tables=10 --table-size=1000000 --report-interval=10 --threads=1000 --time=300 run
-
sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)
-
-
Running the test with following options:
-
Number of threads: 1000
-
Report intermediate results every 10 second(s)
-
Initializing random number generator from current time
-
-
-
Initializing worker threads...
-
-
Threads started!
-
##下面每隔10S产生的压测报告数据也与之前版本有较大差别
-
[ 10s ] thds: 1000 tps: 1290.73 qps: 27199.93 (r/w/o: 19259.14/5258.29/2682.51) lat (ms,95%): 2680.11 err/s: 1.30 reconn/s: 0.00
-
[ 20s ] thds: 1000 tps: 1291.33 qps: 25644.68 (r/w/o: 17913.30/5146.02/2585.36) lat (ms,95%): 2120.76 err/s: 2.70 reconn/s: 0.00
-
[ 30s ] thds: 1000 tps: 1749.09 qps: 34514.35 (r/w/o: 24078.49/6932.57/3503.28) lat (ms,95%): 1258.08 err/s: 5.10 reconn/s: 0.00
-
...
-
...
-
[ 280s ] thds: 1000 tps: 1608.70 qps: 32380.81 (r/w/o: 22645.91/6509.20/3225.70) lat (ms,95%): 2680.11 err/s: 8.30 reconn/s: 0.00
-
[ 290s ] thds: 1000 tps: 1441.71 qps: 28987.87 (r/w/o: 20274.92/5823.03/2889.92) lat (ms,95%): 1803.47 err/s: 6.50 reconn/s: 0.00
-
[ 300s ] thds: 1000 tps: 1794.92 qps: 36050.03 (r/w/o: 25318.80/7126.59/3604.64) lat (ms,95%): 3639.94 err/s: 14.80 reconn/s: 0.00
-
SQL statistics:
-
queries performed:
-
read: 7030618 ##总select数量
-
write: 2000604 ##总update/insert/delete语句数量
-
other: 1001648 ##包含commit/unlock tables以及其他mutex语句
-
total: 10032870
-
transactions: 499461 (1662.65 per sec.) ##TPS
-
queries: 10032870 (33398.37 per sec.) ##QPS
-
ignored errors: 2726 (9.07 per sec.) ##忽略的错误数
-
reconnects: 0 (0.00 per sec.)
-
-
General statistics:
-
total time: 300.3980s ##压测总时间
-
total number of events: 499461 ##总事件数量,一般与transactions数值相同
-
-
Latency (ms):
-
min: 2.57
-
avg: 601.02 ##%95语句RT,平均响应时间,单位是毫秒,可通过–percentile=100调节百分比
-
max: 13302.01
-
95th percentile: 1739.68
-
sum: 300186288.55
-
-
Threads fairness:
-
events (avg/stddev): 499.4610/27.20
- execution time (avg/stddev): 300.1863/0.07
3、sysbench oltp压测数据清理
sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.1.0.10 --mysql-port=3306 --mysql-user=dbuser --mysql-password=abc.1234 --mysql-db=dbtest --tables=10 --table-size=1000000 --report-interval=10 --threads=1000 --time=300 cleanup
点击(此处)折叠或打开
-
[root@DB_TEST sysbench]# sysbench /usr/local/sysbench/share/sysbench/oltp_read_write.lua --mysql-host=10.1.0.10 --mysql-port=3306 --mysql-user=dbuser --mysql-password=abc.1234 --mysql-db=dbtest --tables=10 --table-size=1000000 --report-interval=10 --threads=1000 --time=300 cleanup
-
sysbench 1.0.3 (using bundled LuaJIT 2.1.0-beta2)
-
-
Dropping table 'sbtest1'...
-
Dropping table 'sbtest2'...
-
Dropping table 'sbtest3'...
-
Dropping table 'sbtest4'...
-
Dropping table 'sbtest5'...
-
Dropping table 'sbtest6'...
-
Dropping table 'sbtest7'...
-
Dropping table 'sbtest8'...
-
Dropping table 'sbtest9'...
- Dropping table 'sbtest10'...
1、压测lua脚本
bulk_insert.lua
oltp_common.lua
oltp_delete.lua
oltp_insert.lua
oltp_point_select.lua
oltp_read_only.lua
oltp_read_write.lua
oltp_update_index.lua
oltp_update_non_index.lua
oltp_write_only.lua
select_random_points.lua
select_random_ranges.lua
2、其他重要参数
--histogram[=on|off] ##是否开启生成直方图,默认是off
--percentile=N ##调整响应时间的百分比,默认是95
--events=N ##最大的request数值,默认是0,表示无最大事件限制,别名是--max-requests=N
--db-driver=STRING ##数据库驱动mysql/oracle/pg
暂时简单的做下新版本的测试使用,后续完善压测脚本、压测结果整理...