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压力测试