sysbench测试工具

还记得刚到公司的第一个任务就是搞性能测试,断断续续也是搞了很久,sysbench这个工具也是用了有一段时间了,不敢说熟练,但是也是踩了一些坑,在此做些整理,欢迎诸位指正补充…

sysbench主要包括一下几种方式的测试:

cpu性能
磁盘io性能
调度程序性能
内存分配及传输速度
POSIX线程性能
数据库性能(OLTP基准测试)

先说安装一般我是直接从github上copy下来,自己编译

git clone https://github.com/akopytov/sysbench.git 
// 如果使用0.5 版本: git branch 0.5 
./autogen.sh 
./configure 
make 
make install

只说 oltp 
sysbench 的 oltp 主要用于评估测试各种不同系统参数下的数据库负载情况。目前sysbench的数据库测试支持 Mysql、PostgreSQL、Oracle (我的任务主要是做 mysql &; tidb 的对比测试), 相比 0.4 版本,后续的版本 oltp 测试主要结合了 lua 脚本,不需要修改源码,通过自定义lua脚本就可以实现不同业务类型的测试。

参数 
sysbench 版本

sysbench --version 
sysbench 1.0.1-7fe53e4 
具体参数

sysbench --help 
Usage: 
sysbench [options]... [testname] [command] 
Commands implemented by most tests: prepare run cleanup help 
General options: 
--threads=N 创建测试线程的数量, 默认为 1 
--events=N事件最大数量,默认为 0 ,不限制 
--time=N最大执行时间,单位是 s ,默认是 0 ,不限制 
--forced-shutdown=STRING超过max-time强制中断, 默认是 off 
--thread-stack-size=SIZE每个线程的堆栈大小, 默认是 64k 
--rate=Naverage transactions rate. 0 for unlimited rate [0] 
--report-interval=N 报告中间统计信息间隔, 0 代表禁止, 默认为 0 
--report-checkpoints=[LIST,...] 转储完全统计信息并在指定时间点复位所有计数器。 参数是逗号分隔值的列表,表示从必须执行报告检查点的测试开始所经过的时间(以秒为单位)。 默认情况下,报告检查点处于关闭状态 
--debug[=on|off]是否显示更多的调试信息, 默认是off 
--validate[=on|off] 在可能情况下执行验证检查, 默认是off。 
--help[=on|off] 输出 help 信息, 并退出 
--version[=on|off]输出版本信息, 并退出 
--config-file=FILENAME配置文件 
--tx-rate=N deprecated alias for --rate [0] 
--max-requests=Ndeprecated alias for --events [0] 
--max-time=Ndeprecated alias for --time [0] 
--num-threads=N deprecated alias for --threads [1] 
Pseudo-Random Numbers Generator options: 
--rand-type=STRING 分布的随机数{uniform(均匀分布),Gaussian(高斯分布),special(空间分布)}。默认是special 
--rand-spec-iter=N 产生数的迭代次数。默认是12 
--rand-spec-pct=N值的百分比被视为’special’ (for special distribution)。默认是1 
--rand-spec-res=N'special'的百分比值。默认是75 
--rand-seed=Nseed for random number generator. When 0, the current time is used as a RNG seed. [0] 
--rand-pareto-h=N参数h用于 pareto 分布[0.2] 
Log options: 
--verbosity=N verbosity level {5 - debug, 0 - only critical messages} [3] 
--percentile=N percentile to calculate in latency statistics (1-100). Use the special value of 0 to disable percentile calculations [95] 
--histogram[=on|off] print latency histogram in report [off] 
General database options: 
--db-driver=STRINGspecifies database driver to use ('help' to get list of available drivers) 
--db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto] 
--db-debug[=on|off] print database-specific debug information [off] 
General database options: 
--db-driver=STRINGspecifies database driver to use ('help' to get list of available drivers) 
--db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto] 
--db-debug[=on|off] print database-specific debug information [off] 
Compiled-in database drivers: 
mysql - MySQL driver 
mysql options: 
--mysql-host=[LIST,...]MySQL server host [localhost] 
--mysql-port=[LIST,...]MySQL server port [3306] 
--mysql-socket=[LIST,...]MySQL socket 
--mysql-user=STRINGMySQL user [sbtest] 
--mysql-password=STRINGMySQL password [] 
--mysql-db=STRINGMySQL database name [sbtest] 
--mysql-ssl[=on|off] use SSL connections, if available in the client library [off] 
--mysql-ssl-cipher=STRINGuse specific cipher for SSL connections [] 
--mysql-compression[=on|off] use compression, if available in the client library [off] 
--mysql-debug[=on|off] trace all client library calls [off] 
--mysql-ignore-errors=[LIST,...] list of errors to ignore, or "all" [1213,1020,1205] 
--mysql-dry-run[=on|off] Dry run, pretend that all MySQL client API calls are successful without executing them [off] 
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 
See 'sysbench <testname> help' for a list of options for each test. 
sysbench ./lua/oltp_read_write.lua help 
oltp_read_write.lua options: 
--distinct_ranges=N Number of SELECT DISTINCT queries per transaction [1] 
--sum_ranges=NNumber of SELECT SUM() queries per transaction [1] 
--skip_trx[=on|off] Don't start explicit transactions and execute all queries as in the AUTOCOMMIT mode [off] 
--secondary[=on|off]Use a secondary index in place of the PRIMARY KEY [off] 
--create_secondary[=on|off] Create a secondary index in addition to the PRIMARY KEY [on] 
--index_updates=N Number of UPDATE index queries per transaction [1] 
--range_size=NRange size for range SELECT queries [100] 
--auto_inc[=on|off] Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on] 
--delete_inserts=NNumber of DELETE/INSERT combination per transaction [1] 
--tables=NNumber of tables [1] 
--mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb] 
--non_index_updates=N Number of UPDATE non-index queries per transaction [1] 
--table_size=NNumber of rows per table [10000] 
--pgsql_variant=STRINGUse this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0 
--simple_ranges=N Number of simple range SELECT queries per transaction [1] 
--order_ranges=NNumber of SELECT ORDER BY queries per transaction [1] 
--range_selects[=on|off]Enable/disable all range SELECT queries [on] 
--point_selects=N Number of point SELECT queries per transaction [10]

参数太多实在不想一句句翻译了…

开测 sysbench的测试流程

prepare(准备数据) -> run(运行测试) -> cleanup(清理数据) 目前社区提供的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

以oltp_read_only.lua 为例

./sysbench ./lua/oltp_read_only.lua / 
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=root --mysql-password=000000 / 
--mysql-db=test --tables=10 --table-size=10000 / 
--report-interval=10 / 
--threads=128 --time=120 / 
prepare/run/cleanup

结果解读

sysbench 1.0.1-7fe53e4 (using bundled LuaJIT 2.1.0-beta2) 
Running the test with following options: 
Number of threads: 128 
Report intermediate results every 10 second(s) 
Initializing random number generator from current time 
Initializing worker threads... 
Threads started! 
[ 10s ] thds: 128 tps: 1208.30 qps: 19436.82 (r/w/o: 17007.47/0.00/2429.35) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00 
[ 20s ] thds: 128 tps: 1264.26 qps: 20233.57 (r/w/o: 17705.05/0.00/2528.52) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00 
[ 30s ] thds: 128 tps: 1273.70 qps: 20375.11 (r/w/o: 17827.81/0.00/2547.30) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00 
[ 40s ] thds: 128 tps: 1287.20 qps: 20598.39 (r/w/o: 18023.89/0.00/2574.50) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00 
[ 50s ] thds: 128 tps: 1281.13 qps: 20491.35 (r/w/o: 17929.10/0.00/2562.26) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00 
[ 60s ] thds: 128 tps: 1267.04 qps: 20276.48 (r/w/o: 17742.41/0.00/2534.07) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00 
[ 70s ] thds: 128 tps: 1257.80 qps: 20127.47 (r/w/o: 17611.96/0.00/2515.51) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00 
[ 80s ] thds: 128 tps: 1243.93 qps: 19894.40 (r/w/o: 17406.43/0.00/2487.96) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00 
[ 90s ] thds: 128 tps: 1237.60 qps: 19807.67 (r/w/o: 17332.67/0.00/2475.00) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00 
[ 100s ] thds: 128 tps: 1053.60 qps: 16850.29 (r/w/o: 14742.89/0.00/2107.40) lat (ms,95%): 193.38 err/s: 0.00 reconn/s: 0.00 
[ 110s ] thds: 128 tps: 1001.41 qps: 16021.33 (r/w/o: 14018.60/0.00/2002.73) lat (ms,95%): 207.82 err/s: 0.00 reconn/s: 0.00 
[ 120s ] thds: 128 tps: 859.76 qps: 13750.85 (r/w/o: 12034.73/0.00/1716.12) lat (ms,95%): 235.74 err/s: 0.00 reconn/s: 0.00 
SQL statistics: 
queries performed: 
read:1994860// 总 select 数量 
write: 0// 总update、insert、delete语句数量 
other: 284980 //commit、unlock tables以及其他mutex的数量 
total: 2279840 
transactions:142490 (1186.20 per sec.) //通常需要关注的数字(TPS) 
queries: 2279840 (18979.13 per sec.) //通常需要关注的数字(QPS) 
ignored errors:0(0.00 per sec.)//忽略的错误数 
reconnects:0(0.00 per sec.) 
General statistics: 
total time:120.1216s//即time指定的压测实际 
total number of events:142490 //总的事件数,一般与transactions相同 
Latency (ms): 
min:8.52 
avg:107.84 
max:480.08 
95th percentile:170.48//95%的语句的平均响应时间 
sum:15365843.76 
Threads fairness: 
events (avg/stddev): 1113.2031/14.76 
execution time (avg/stddev): 120.0457/0.04

我们一般关注的指标主要有:

response time avg: 平均响应时间。(后面的95%的大小可以通过–percentile=98的方式去更改)
transactions: 精确的说是这一项后面的TPS 。但如果使用了-skip-trx=on,这项事务数恒为0,需要用total number of events 去除以总时间,得到tps(其实还可以分为读tps和写tps)
queries: 用它除以总时间,得到吞吐量QPS
当然还有一些系统层面的cpu,io,mem相关指标

建议

sysbench 在最近刚升级了最新版(以上使用的就是最新版),有很多改进之处,让我最惊喜的是prepare数据相比之前变为多线程,准备数据更快, 降低了在prepare数据上耗费的时间(虽然之前自己用别的办法加速prepare数据,但是方法比较挫,这里就不做介绍了)

注意新版本相比 1.0 和 0.5 有许多参数的改变当然你也可以指定自己定义的lua脚步,实现不同业务类型的测试

参考
github doc
sysbench 0.5使用手册
使用sysbench对mysql压力测试