一、简介
Sysbench压测工具常用版本有0.4(较旧),0.5(据说非官方正宗开源版),1.0(最新的官方开源版本),不同版本之间的系统压测差异较小。但对数据库的压测参数、结果具有较大差异,这里针对sysbench压测MYSQL数据库做下安装、使用介绍。
二、压测环境
硬件环境:
软件环境:
三、MYSQL核心参数
四、压测模型
五、MYSQL压测过程
以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
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 run
备注:需要重点关注的数据有执行总时间、TPS
:如果使用-skip-trx=on,这项事务数恒为0,需要用total number of events 去除以总时间,得到tps(其实还可以分为读tps和写tps),QPS和RT(平均响应时间),以及系统压测指标CPU、内存、IO等。
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
六、MYSQL压测附录
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
暂时简单的做下新版本的测试使用,后续完善压测脚本、压测结果整理...
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
暂时简单的做下新版本的测试使用,后续完善压测脚本、压测结果整理...
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27067062/viewspace-2135183/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27067062/viewspace-2135183/