sysbench
#############################################
sysbench 用法讲解
sysbench 命令语法如下：
sysbench [options]... [testname] [command]
命令中的testname是测试项名称。sysbench 支持的测试项包括：
-
*.lua：数据库性能基准测试。
-
fileio：磁盘 IO 基准测试。
-
cpu：CPU 性能基准测试。
-
memory：内存访问基准测试。
-
threads：基于线程的调度程序基准测试。
-
mutex：POSIX 互斥量基准测试。
command是 sysbench 要执行的命令，支持的选项有：prepare，prewarm，run，cleanup，help。注意，不是所有的测试项都支持这些选项。
options是配置项。sysbench 中的配置项主要包括以下两部分：
1. 通用配置项。这部分配置项可通过 sysbench --help 查看。例如，
# sysbench --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]
2. 测试项相关的配置项。各个测试项支持的配置项可通过 sysbench testname help 查看。例如，
# sysbench memory help sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2) memory options: --memory-block-size=SIZE size of memory block for test [1K] --memory-total-size=SIZE total size of data to transfer [100G] --memory-scope=STRING memory access scope {global,local} [global] --memory-hugetlb[=on|off] allocate memory from HugeTLB pool [off] --memory-oper=STRING type of memory operations {read, write, none} [write] --memory-access-mode=STRING memory access mode {seq,rnd} [seq]
sysbench --db-driver=mysql --time=200 --threads=220 --report-interval=1 --mysql-host=172.17.139.152 --mysql-port=16310 --mysql-user=admin --mysql-password='123456' --mysql-db=test2 --tables=20 --table_size=10000000 oltp_read_write --mysql-ignore-errors=all --db-ps-mode=disable run
1.oltp_read_write
用来压测 OLTP 场景。在 sysbench 1.0 之前， 该场景是通过 oltp.lua 这个脚本来测试的。不过该脚本在 sysbench 1.0 之后就被废弃了，但为了跟之前的版本兼容，该脚本放到了 /usr/local/share/sysbench/tests/include/oltp_legacy/ 目录下
鉴于 oltp_read_write.lua 和 oltp.lua 两者的压测内容完全一致。从 sysbench 1.0 开始，压测 OLTP 场景建议直接使用 oltp_read_write。
看看使用 sysbench 对 MySQL 进行基准测试的四个标准步骤：
（1）生成：prepare
生成压测数据，默认情况下，sysbench 是通过 INSERT INTO 命令来导入测试数据的。如果是使用 LOAD DATA LOCAL INFILE 命令来导入，sysbench 导数速度能提升30%，具体可参考：使用 LOAD DATA LOCAL INFILE，sysbench 导数速度提升30%
sysbench oltp_read_write --mysql-host=10.0.0.64 --mysql-port=3306 --mysql-user=admin --mysql-password=Py@123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=30 prepare
命令中各个选项的具体含义如下：
- oltp_read_write：测试项，对应的是
/usr/local/share/sysbench/oltp_read_write.lua。这里也可指定脚本的绝对路径名。 - --mysql-host、--mysql-port、--mysql-user、--mysql-password：分别代表 MySQL 实例的主机名、端口、用户名和密码。
- --mysql-db：库名。不指定则默认为
sbtest。 - --tables ：表的数量，默认为 1。
- --table-size ：单表的大小，默认为 10000。
- --threads ：并发线程数，默认为 1。注意，导入时，单表只能使用一个线程。
- prepare：执行准备工作。
（2）预热：prewarm
主要是将磁盘中的数据加载到内存中。除了需要将命令设置为 prewarm，其它配置与 prepare 中一样。
sysbench oltp_read_write --mysql-host=10.0.0.64 --mysql-port=3306 --mysql-user=admin --mysql-password=Py@123456 --mysql-db=sbtest --tables=30 --table-size=1000000 --threads=30 prewarm
（3）压测：run
sysbench oltp_read_write --mysql-host=10.0.0.64 --mysql-port=3306 --mysql-user=admin --mysql-password=Py@123456 \
--mysql-db=sbtest --tables=30 --table-size=1000000 --threads=64 --time=60 --report-interval=10 run
-
--time ：压测时间。不指定则默认为 10 秒。除了 --time，也可通过 --events 限制需要执行的 event 的数量。
-
--report-interval=10 ：每 10 秒输出一次测试结果，默认为 0，不输出。
（4）清理：cleanup
sysbench oltp_read_write --mysql-host=10.0.0.64 --mysql-port=3306 --mysql-user=admin --mysql-password=Py@123456 --mysql-db=sbtest --tables=30 cleanup
这里只需指定 --tables ，sysbench 会串行执行 DROP TABLE IF EXISTS sbtest 操作。
（5）结果
下面我们分析下 oltp_read_write 场景下的压测结果。注：右滑可以看到每个指标的具体含义。
Threads started! [ 10s ] thds: 64 tps: 5028.08 qps: 100641.26 (r/w/o: 70457.59/20121.51/10062.16) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 # thds 是并发线程数。tps 是每秒事务数。qps 是每秒操作数，等于 r（读操作）加上 w（写操作）加上 o（其他操作，主要包括 BEGIN 和 COMMIT）。
# lat 是延迟，(ms,95%) 是 95% 的查询时间小于或等于该值，单位毫秒。err/s 是每秒错误数。reconn/s 是每秒重试的次数。 [ 20s ] thds: 64 tps: 5108.93 qps: 102192.09 (r/w/o: 71533.28/20440.64/10218.17) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 30s ] thds: 64 tps: 5126.50 qps: 102505.50 (r/w/o: 71756.30/20496.60/10252.60) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00 [ 40s ] thds: 64 tps: 5144.50 qps: 102907.20 (r/w/o: 72034.07/20583.72/10289.41) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00 [ 50s ] thds: 64 tps: 5137.29 qps: 102739.80 (r/w/o: 71916.99/20548.64/10274.17) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00 [ 60s ] thds: 64 tps: 4995.38 qps: 99896.35 (r/w/o: 69925.98/19979.61/9990.75) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 4276622 # 读操作的数量 write: 1221892 # 写操作的数量 other: 610946 # 其它操作的数量 total: 6109460 # 总的操作数量，total = read + write + other transactions: 305473 (5088.63 per sec.) # 总的事务数（每秒事务数） queries: 6109460 (101772.64 per sec.) # 总的操作数（每秒操作数） ignored errors: 0 (0.00 per sec.) # 忽略的错误数（每秒忽略的错误数） reconnects: 0 (0.00 per sec.) # 重试次数（每秒重试的次数） General statistics: total time: 60.0301s # 总的执行时间 total number of events: 305473 # 执行的 event 的数量 # 在 oltp_read_write 中，默认参数下，一个 event 其实就是一个事务 Latency (ms): min: 5.81 # 最小耗时 avg: 12.57 # 平均耗时 max: 228.87 # 最大耗时 95th percentile: 17.32 # 95% event 的执行耗时 sum: 3840044.28 # 总耗时 Threads fairness: events (avg/stddev): 4773.0156/30.77 # 平均每个线程执行 event 的数量 # stddev 是标准差，值越小，代表结果越稳定。 execution time (avg/stddev): 60.0007/0.01 # 平均每个线程的执行时间
输出中，重点关注三个指标：
- 每秒事务数，即我们常说的 TPS。
- 每秒操作数，即我们常说的 QPS。
- 95% event 的执行耗时。
TPS 和 QPS 反映了系统的吞吐量，越大越好。执行耗时代表了事务的执行时长，越小越好。在一定范围内，并发线程数指定得越大，TPS 和 QPS 也会越高。
向mysql的apple库插入1000万数据：
sysbench --db-driver=mysql --mysql-user=igoodful --mysql-password=123456 --mysql-db=apple --table_size=10000000 --tables=1 oltp_read_write --mysql-host=10.10.10.10 --mysql-port=3306 prepare
sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 \ --mysql-user=root --mysql-password=your_password \ --mysql-db=test --table-size=10000000 --tables=1 \ --threads=1 --time=300 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua prepare
上述命令将生成一个包含 1000 万条记录的测试表，并将数据插入到名为 test 的数据库中。
sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 \ --mysql-user=root --mysql-password=your_password \ --mysql-db=test --table-size=10000000 --tables=1 \ --threads=32 --time=300 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua run
上述命令将使用 32 个线程运行测试，持续 300 秒，统计 MySQL 实例的 QPS。
/home/work/app/sysbench/bin/sysbench --db-driver=mysql --mysql-user=root --mysql-password=xiaomi
--mysql-socket=/home/work/data/tmp/mysql5533.sock --mysql-db=sysbench --range_size=100 --table_size=100000
--tables=10 --threads=8 --events=0 --time=60 --rand-type=uniform /home/work/app/sysbench/share/sysbench/oltp_read_write.lua run
/home/work/app/sysbench/bin/sysbench --db-driver=mysql --mysql-user=root --mysql-password=xiaomi
--mysql-socket=/home/work/data/tmp/mysql5711.sock --mysql-db=sysbench --range_size=100 --table_size=100000
--tables=10 --threads=8 --events=0 --time=60 --rand-type=uniform /home/work/app/sysbench/share/sysbench/oltp_read_write.lua run
/home/work/app/sysbench/bin/sysbench --db-driver=mysql --mysql-user=root --mysql-password=xiaomi
--mysql-socket=/home/work/data/tmp/mysql3306.sock --mysql-db=sysbench --range_size=100 --table_size=100000
--tables=10 --threads=8 --events=0 --time=60 --rand-type=uniform /home/work/app/sysbench/share/sysbench/oltp_read_write.lua run
帮助文档：
shell> sysbench --help
Usage: sysbench [options]... [testname] [command] 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 pgsql - PostgreSQL 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] pgsql options: --pgsql-host=STRING PostgreSQL server host [localhost] --pgsql-port=N PostgreSQL server port [5432] --pgsql-user=STRING PostgreSQL user [sbtest] --pgsql-password=STRING PostgreSQL password [] --pgsql-db=STRING PostgreSQL database name [sbtest] 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.
测试QPS：
[work@a8-cloud-dba-db08 tmp]$ sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=dba --mysql-password=dba \
--mysql-db=testdb --table-size=10000000 --tables=1 --threads=1 --time=300 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua prepare sysbench 1.0.17 (using system LuaJIT 2.0.4) Creating table 'sbtest1'... Inserting 10000000 records into 'sbtest1' Creating a secondary index on 'sbtest1'... [work@a8-cloud-dba-db08 tmp]$ sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=dba --mysql-password=dba \
--mysql-db=testdb --table-size=10000000 --tables=1 --threads=32 --time=300 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua run sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 32 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 3690568 write: 1054448 other: 527224 total: 5272240 transactions: 263612 (878.64 per sec.) queries: 5272240 (17572.71 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.0221s total number of events: 263612 Latency (ms): min: 2.24 avg: 36.41 max: 2892.04 95th percentile: 69.29 sum: 9599210.76 Threads fairness: events (avg/stddev): 8237.8750/70.99 execution time (avg/stddev): 299.9753/0.01
4核CPU
[work@a8-cloud-dba-db08 tmp]$ cat /proc/cpuinfo processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz stepping : 9 microcode : 0x21 cpu MHz : 1199.865 cache size : 3072 KB physical id : 0 siblings : 4 core id : 0 cpu cores : 2 apicid : 0 initial apicid : 0 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm
pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu
pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c
rdrand lahf_lm epb ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt dtherm ida arat pln pts md_clear spec_ctrl intel_stibp flush_l1d bogomips : 5188.07 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz stepping : 9 microcode : 0x21 cpu MHz : 1238.903 cache size : 3072 KB physical id : 0 siblings : 4 core id : 1 cpu cores : 2 apicid : 2 initial apicid : 2 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2
ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq
dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand
lahf_lm epb ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt dtherm ida arat pln pts md_clear spec_ctrl intel_stibp flush_l1d bogomips : 5188.07 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: processor : 2 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz stepping : 9 microcode : 0x21 cpu MHz : 1200.024 cache size : 3072 KB physical id : 0 siblings : 4 core id : 0 cpu cores : 2 apicid : 1 initial apicid : 1 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall
nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64
monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand
lahf_lm epb ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt dtherm ida arat pln pts md_clear spec_ctrl intel_stibp flush_l1d bogomips : 5188.07 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management: processor : 3 vendor_id : GenuineIntel cpu family : 6 model : 58 model name : Intel(R) Core(TM) i5-3230M CPU @ 2.60GHz stepping : 9 microcode : 0x21 cpu MHz : 1200.024 cache size : 3072 KB physical id : 0 siblings : 4 core id : 1 cpu cores : 2 apicid : 3 initial apicid : 3 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp
lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx est
tm2 ssse3 cx16 xtpr pdcm pcid sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm epb ssbd ibrs ibpb stibp
tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt dtherm ida arat pln pts md_clear spec_ctrl intel_stibp flush_l1d bogomips : 5188.07 clflush size : 64 cache_alignment : 64 address sizes : 36 bits physical, 48 bits virtual power management:
[work@a8-cloud-dba-db08 tmp]$ sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=dba --mysql-password=dba \
--mysql-db=testdb --table-size=10000000 --tables=1 --threads=4 --time=300 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua run sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 4 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 3752462 write: 1072132 other: 536066 total: 5360660 transactions: 268033 (893.43 per sec.) queries: 5360660 (17868.56 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.0035s total number of events: 268033 Latency (ms): min: 2.10 avg: 4.47 max: 1471.22 95th percentile: 6.21 sum: 1198892.08 Threads fairness: events (avg/stddev): 67008.2500/597.55 execution time (avg/stddev): 299.7230/0.00 [work@a8-cloud-dba-db08 tmp]$ sysbench --db-driver=mysql --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=dba --mysql-password=dba \
--mysql-db=testdb --table-size=10000000 --tables=1 --threads=2 --time=300 --rand-type=uniform /usr/share/sysbench/oltp_read_write.lua run sysbench 1.0.17 (using system LuaJIT 2.0.4) Running the test with following options: Number of threads: 2 Initializing random number generator from current time Initializing worker threads... Threads started! SQL statistics: queries performed: read: 2704534 write: 772724 other: 386362 total: 3863620 transactions: 193181 (643.93 per sec.) queries: 3863620 (12878.62 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.0012s total number of events: 193181 Latency (ms): min: 2.11 avg: 3.10 max: 1872.33 95th percentile: 3.68 sum: 599270.07 Threads fairness: events (avg/stddev): 96590.5000/213.50 execution time (avg/stddev): 299.6350/0.00
##################################
igoodful@qq.com
