(1). Sysbench是什么?
Sysbench是一个模块化的、跨平台、多线程基准测试工具，主要用于评估测试各种不同系统参数下的数据库负载情况.
(2). Sysbench命令格式
lixin-macbook:sysbench lixin$ sysbench --help
Usage:
sysbench [options]... [testname] [command]
# ************************************************************
# command是sysbench要执行的命令,包括:prepare/run/cleanup
# ************************************************************
Commands implemented by most tests: prepare run cleanup help
General options:
--threads=N number of threads to use [1]
--events=N limit for total number of events [0]
--time=N limit for total execution time in seconds [10]
--forced-shutdown=STRING number of seconds to wait after the --time limit before forcing shutdown, or 'off' to disable [off]
--thread-stack-size=SIZE size of stack per thread [64K]
--rate=N average transactions rate. 0 for unlimited rate [0]
--report-interval=N periodically report intermediate statistics with a specified interval in seconds. 0 disables intermediate reports [0]
--report-checkpoints=[LIST,...] dump full statistics and reset all counters at specified points in time. The argument is a list of comma-separated values representing the amount of time in seconds elapsed from start of test when report checkpoint(s) must be performed. Report checkpoints are off by default. []
--debug[=on|off] print more debugging info [off]
--validate[=on|off] perform validation checks where possible [off]
--help[=on|off] print help and exit [off]
--version[=on|off] print version and exit [off]
--config-file=FILENAME File containing command line options
--tx-rate=N deprecated alias for --rate [0]
--max-requests=N deprecated alias for --events [0]
--max-time=N deprecated alias for --time [0]
--num-threads=N deprecated alias for --threads [1]
Pseudo-Random Numbers Generator options:
--rand-type=STRING random numbers distribution {uniform,gaussian,special,pareto} [special]
--rand-spec-iter=N number of iterations used for numbers generation [12]
--rand-spec-pct=N percentage of values to be treated as 'special' (for special distribution) [1]
--rand-spec-res=N percentage of 'special' values to use (for special distribution) [75]
--rand-seed=N seed for random number generator. When 0, the current time is used as a RNG seed. [0]
--rand-pareto-h=N parameter h for pareto distribution [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=STRING specifies database driver to use ('help' to get list of available drivers) [mysql]
--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=STRING MySQL user [sbtest]
--mysql-password=STRING MySQL password []
--mysql-db=STRING MySQL database name [sbtest]
--mysql-ssl[=on|off] use SSL connections, if available in the client library [off]
--mysql-ssl-cipher=STRING use 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
(3). 准备数据库
# 准备测试数据库(sysbench_test)
lixin-macbook:sysbench lixin$ mysqladmin -h 127.0.0.1 -u root -p create sysbench_test
(4). 准备数据
# 准备数据
# --tables=5 # 准备5张表
# --table_size=10000 # 每张表有1W条数据
# --mysql-db=sysbench_test # 在哪个库上创建
# --mysql-host=127.0.0.1
# --mysql-user=root
# --mysql-password=123456
# prepare # 准备阶段
lixin-macbook:~ lixin$ sysbench /usr/local/Cellar/sysbench/1.0.20/share/sysbench/oltp_read_write.lua --tables=5 --table_size=10000 --mysql-db=sysbench_test --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=123456 prepare
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
(5). 运行读写性能测试
# 读写性能测试
# --threads=10 10个并发线程
# --time=30 执行多长时间(30秒)
# --report-interval=3 间隔3秒输出一次测试信息
# run 运行阶段
lixin-macbook:~ lixin$ sysbench /usr/local/Cellar/sysbench/1.0.20/share/sysbench/oltp_read_write.lua --tables=5 --table_size=10000 --mysql-db=sysbench_test --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=123456 --threads=10 --time=30 --report-interval=3 run
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 10 # 10线程
Report intermediate results every 3 second(s) # 每3秒打印一次
Initializing random number generator from current time
# 中间执行过程信息
[ 3s ] thds: 10 tps: 230.96 qps: 4676.71 (r/w/o: 3279.65/931.81/465.24) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 10 tps: 244.38 qps: 4891.96 (r/w/o: 3421.67/981.53/488.76) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 10 tps: 248.96 qps: 4977.84 (r/w/o: 3484.09/995.84/497.92) lat (ms,95%): 57.87 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 10 tps: 237.91 qps: 4759.58 (r/w/o: 3332.10/951.65/475.82) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 15s ] thds: 10 tps: 246.72 qps: 4914.05 (r/w/o: 3439.07/981.54/493.44) lat (ms,95%): 61.08 err/s: 0.00 reconn/s: 0.00
[ 18s ] thds: 10 tps: 242.61 qps: 4869.92 (r/w/o: 3409.91/974.78/485.23) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 21s ] thds: 10 tps: 249.39 qps: 4975.90 (r/w/o: 3485.19/991.91/498.79) lat (ms,95%): 62.19 err/s: 0.00 reconn/s: 0.00
[ 24s ] thds: 10 tps: 262.03 qps: 5254.52 (r/w/o: 3675.70/1054.77/524.05) lat (ms,95%): 51.94 err/s: 0.00 reconn/s: 0.00
[ 27s ] thds: 10 tps: 256.95 qps: 5129.93 (r/w/o: 3592.92/1023.12/513.89) lat (ms,95%): 56.84 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 10 tps: 255.34 qps: 5115.88 (r/w/o: 3579.15/1026.04/510.69) lat (ms,95%): 52.89 err/s: 0.00 reconn/s: 0.00
# 结果信息
SQL statistics:
queries performed:
read: 104118
write: 29748
other: 14874
total: 148740
#### *****************************************
### 比较关心的指标:transactions/queries/Latency
# transactions : 每秒处理的事务数(在分布式系统中,一个事务,可能包含多个请求)
# 30秒总共执行事务,7437次"事务",平均每秒处理:247
transactions: 7437 (247.48 per sec.)
# 30秒总共执行了148740次"请求",平均每秒处理:4949
# queries : 每秒处理的请求数
queries: 148740 (4949.51 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 30.0500s # 总的执行时间
total number of events: 7437 # 总的事务数
Latency (ms):
min: 18.06
avg: 40.36
# 最大响应时间为:166毫秒
max: 166.54
# 95%的用户,58毫秒内返回.
95th percentile: 58.92
sum: 300155.31
Threads fairness:
events (avg/stddev): 743.7000/4.50
execution time (avg/stddev): 30.0155/0.01
(6). 清理阶段
# 4. 清理阶段
# cleanup 清理阶段
lixin-macbook:~ lixin$ sysbench /usr/local/Cellar/sysbench/1.0.20/share/sysbench/oltp_read_write.lua --tables=5 --table_size=10000 --mysql-db=sysbench_test --mysql-host=127.0.0.1 --mysql-user=root --mysql-password=123456 --threads=10 --time=30 --report-interval=3 cleanup
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
