江苏龙网

  1. 主页 > 生活百科 > >

MySQL压力测试工具，值得收藏( 三 )

mysql压力测试


所以 ， 我这里用oltp_read_write.lua脚本来做读、写测试 。还有很多其它类型的测试 ， 比如只读测试、只写测试、删除测试、大批量插入测试等等 。可找到对应的lua脚本进行调用即可 。
#执行测试命令如下：
[root@mysql ~]# sysbench --threads=4
> --time=20
> --report-interval=5
> --mysql-host=127.0.0.1
> --mysql-port=3306
> --mysql-user=root
> --mysql-password=123
> /usr/share/sysbench/oltp_read_write.lua
> --tables=10
> --table_size=100000
> run
上述命令返回的结果如下：
[root@mysql ~]# sysbench --threads=4 --time=20 --report-interval=5 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=123 /usr/share/sysbench/oltp_read_write.lua --tables=10 --table_size=100000 run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 4
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
#以下是每5秒返回一次的结果 ， 统计的指标包括：
# 线程数、tps(每秒事务数)、qps(每秒查询数)、
# 每秒的读/写/其它次数、延迟、每秒错误数、每秒重连次数
[ 5s ] thds: 4 tps: 1040.21 qps: 20815.65 (r/w/o: 14573.17/4161.25/2081.22) lat (ms,95%): 7.17 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 4 tps: 1083.34 qps: 21667.15 (r/w/o: 15165.93/4334.55/2166.68) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 4 tps: 1121.57 qps: 22429.09 (r/w/o: 15700.64/4485.30/2243.15) lat (ms,95%): 6.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 4 tps: 1141.69 qps: 22831.98 (r/w/o: 15982.65/4566.16/2283.18) lat (ms,95%): 6.09 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 307146 # 执行的读操作数量
write: 87756 # 执行的写操作数量
other: 43878 # 执行的其它操作数量
total: 438780
transactions: 21939 (1096.57 per sec.) # 执行事务的平均速率
queries: 438780 (21931.37 per sec.) # 平均每秒能执行多少次查询
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 20.0055s # 总消耗时间
total number of events: 21939 # 总请求数量(读、写、其它)
Latency (ms):
min: 1.39
avg: 3.64
max: 192.05
95th percentile: 6.67 # 采样计算的平均延迟
sum: 79964.26
Threads fairness:
events (avg/stddev): 5484.7500/15.12
execution time (avg/stddev): 19.9911/0.00
4、cpu/io/内存等测试
sysbench内置的几个测试指标如下：
[root@mysql ~]# sysbench --help
.......... # 省略部分内容
Compiled-in tests:
fileio - File I/O test
cpu - CPU performance test
memory - Memory functions speed test
threads - Threads subsystem performance test
mutex - Mutex performance test
可以直接help输出测试方法 ， 例如 ， fileio测试：
[root@mysql ~]# sysbench fileio help
sysbench 1.0.17 (using system LuaJIT 2.0.4)
fileio options:
--file-num=N number of files to create [128]
--file-block-size=N block size to use in all IO operations [16384]
--file-total-size=SIZE total size of files to create [2G]
--file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
--file-io-mode=STRING file operations mode {sync,async,mmap} [sync]
--file-async-backlog=N number of asynchronous operatons to queue per thread [128]
--file-extra-flags=[LIST,...] list of additional flags to use to open files {sync,dsync,direct} []
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100]
--file-fsync-all[=on|off] do fsync() after each write operation [off]
--file-fsync-end[=on|off] do fsync() at the end of test [on]
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0]
--file-rw-ratio=N reads/writes ratio for combined test [1.5]
1）测试io性能
例如 ， 创建5个文件 ， 总共2G ， 每个文件大概400M 。
[root@mysql ~]# sysbench fileio --file-num=5 --file-total-size=2G prepare
[root@mysql ~]# ll -lh test*
-rw------- 1 root root 410M May 26 16:05 test_file.0
-rw------- 1 root root 410M May 26 16:05 test_file.1
-rw------- 1 root root 410M May 26 16:05 test_file.2
-rw------- 1 root root 410M May 26 16:05 test_file.3
-rw------- 1 root root 410M May 26 16:05 test_file.4
然后运行测试：
[root@mysql ~]# sysbench --events=5000


推荐阅读


上一篇：当下已是最美,龙润茶业最美姓氏茶代言人

下一篇：用了它，一行代码就能恢复 Windows 误删文件