博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5761420.html
sysbench是一款开源的多工具，作为dba的进行基准测试的必备经典工具，可以 执行以下测试
fileio - 文件 I/O测试
cpu - CPU系能测试
memory - 内存功能速度测试
threads - 线程子系统系能测试
mutex - 互斥性能测试
oltp - 在线交易性能测试
sysbench 0.5 引入了lua脚本进行oltp的测试，sysbench 1.0相对以前版本有了变化，这里介绍sysbench 1.1的使用
一安装
yum install m4 autoconf automake libtool
./autogen.sh
./configure --prefix=/opt/sysbench --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/
默认支持MySQL，如果需要测试Oracle/PostgreSQL，则在configure时需要加上–with-或者–with-pgsql参数
make && make install
如果出现如下问题
[root@yangdong-test-8816 /]# sysbench --version
sysbench: error while loading shared libraries: libmysqlclient.so.18: cannot open shared object file: No such file or directory
[root@yangdong-test-8816 /]#
解决办法：
1.export LD_LIBRARY_PATH=/usr/local/mysql/lib
2.ln -s /usr/local/mysql/lib/libmysqlclient.so.18 /usr/lib64
二 使用说明
1.cpu测试
[root@dbwatcher-test-8816 share]# sysbench --test=cpu --cpu-max-prime=20000 run
sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
...........
General statistics:
total time: 30.8469s
total number of events: 10000
total time taken by event execution: 30.8406s
Latency statistics:
min: 3.07ms
avg: 3.08ms
max: 3.98ms
approx. 95th percentile: 3.13ms
Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 30.8406/0.00
2.线程测试
[root@dbwatcher-test-8816 share]# sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run
sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 64
Initializing random number generator from current time
Initializing worker threads...
Threads started!
General statistics:
total time: 0.5762s
total number of events: 10000
total time taken by event execution: 36.7852s
Latency statistics:
min: 0.03ms
avg: 3.68ms
max: 117.10ms
approx. 95th percentile: 21.89ms
Threads fairness:
events (avg/stddev): 156.2500/23.99
execution time (avg/stddev): 0.5748/0.00
3.IO测试
[root@
dbwatcher-test-8816 share]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw prepare
sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)
128 files, 24576Kb each, 3072Mb total
Creating files for the test...
Extra file open flags: 0
Creating file test_file.0
Creating file test_file.1
Creating file test_file.2
................
Creating file test_file.126
Creating file test_file.127
3221225472 bytes written in 123.25 seconds (24.92 MiB/sec).
[root@
dbwatcher-test-8816 share]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw run
sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 16
Initializing random number generator from current time
Extra file open flags: 0
128 files, 24MiB each
3GiB total file size
Block size 16KiB
Number of IO requests: 10000
Read/Write ratio for combined random IO test: 1.50
Periodic FSYNC enabled, calling fsync() each 100 requests.
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random r/w test
Initializing worker threads...
Threads started!
File operations:
reads/s: 13304.27
writes/s: 8869.52
fsyncs/s: 28382.45
Throughput:
read, MiB/s: 207.88
written, MiB/s: 138.59
General statistics:
total time: 0.4511s
total number of events: 22800
total time taken by event execution: 7.1884s
Latency statistics:
min: 0.00ms
avg: 0.32ms
max: 50.78ms
approx. 95th percentile: 1.44ms
Threads fairness:
events (avg/stddev): 1425.0000/106.58
execution time (avg/stddev): 0.4493/0.00
[root@
dbwatcher-test-8816 share]# sysbench --test=fileio --num-threads=16 --file-total-size=3G --file-test-mode=rndrw cleanup
sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)
Removing test files...
4.内存测试
[root@
dbwatcher-test-8816 sysbench]# sysbench --test=memory --memory-block-size=8k --memory-total-size=4G run
sysbench 1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
Operations performed: 524288 (1031489.88 ops/sec)
4096.00 MiB transferred (8058.51 MiB/sec)
General statistics:
total time: 0.5083s
total number of events: 524288
total time taken by event execution: 0.4211s
Latency statistics:
min: 0.00ms
avg: 0.00ms
max: 0.02ms
approx. 95th percentile: 0.00ms
Threads fairness:
events (avg/stddev): 524288.0000/0.00
execution time (avg/stddev): 0.4211/0.00
三 .oltp测试
oltp在0.5开始，通过一系列LUA脚本来替换之前的oltp，来模拟更接近真实的基准测试环境。
(1)创建测试表和数据
[root@
dbwatcher-test-8816 sysbench]# sysbench /opt/sysbench/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-port=3306 --mysql-socket=/tmp/mysql.sock --mysql-host=localhost --mysql-db=test --tables=10 --table-size=100000 --threads=32 --events=100000 --report-interval=5 prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta2)
Initializing worker threads...
Creating table 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest1'
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest5'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest6'...
Inserting 100000 records into 'sbtest6'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest7'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest8'
Creating a secondary index on 'sbtest7'...
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest10'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest10'
Inserting 100000 records into 'sbtest9'
Creating a secondary index on 'sbtest10'...
Creating a secondary index on 'sbtest9'...
(2)测试
[root@
dbwatcher-test-8816 sysbench]# sysbench /opt/sysbench/share/sysbench/oltp_read_write.lua --mysql-user=root --mysql-port=3306 --mysql-socket=/tmp/mysql.sock --mysql-host=localhost --mysql-db=test --tables=10 --table-size=100000 --threads=8 --report-interval=10 --time=300 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 2
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 10s ] thds: 8 tps: 361.41 qps: 7227.04 (r/w/o: 5058.57/1445.65/722.82) lat (ms,95%): 5.99 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 8 tps: 402.00 qps: 8035.95 (r/w/o: 5625.17/1606.79/804.00) lat (ms,95%): 5.57 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 8 tps: 394.00 qps: 7883.96 (r/w/o: 5519.97/1575.99/788.00) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 8 tps: 399.99 qps: 7998.84 (r/w/o: 5599.69/1599.17/799.98) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 8 tps: 405.20 qps: 8103.18 (r/w/o: 5671.99/1620.80/810.40) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 8 tps: 406.20 qps: 8128.21 (r/w/o: 5690.00/1625.80/812.40) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 8 tps: 397.80 qps: 7957.38 (r/w/o: 5570.59/1591.20/795.60) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 8 tps: 411.80 qps: 8236.36 (r/w/o: 5766.17/1646.59/823.60) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 8 tps: 463.00 qps: 9258.19 (r/w/o: 6480.39/1851.80/926.00) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 8 tps: 455.20 qps: 9103.78 (r/w/o: 6372.59/1820.80/910.40) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 8 tps: 462.38 qps: 9245.28 (r/w/o: 6470.98/1849.54/924.77) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 8 tps: 459.80 qps: 9197.60 (r/w/o: 6438.00/1840.00/919.60) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 8 tps: 456.80 qps: 9135.80 (r/w/o: 6394.80/1827.40/913.60) lat (ms,95%): 4.74 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 8 tps: 423.00 qps: 8461.22 (r/w/o: 5922.41/1692.80/846.00) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 8 tps: 466.41 qps: 9326.42 (r/w/o: 6527.96/1865.64/932.82) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 160s ] thds: 8 tps: 455.20 qps: 9102.40 (r/w/o: 6371.40/1820.60/910.40) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 170s ] thds: 8 tps: 454.40 qps: 9086.21 (r/w/o: 6360.61/1816.80/908.80) lat (ms,95%): 4.57 err/s: 0.00 reconn/s: 0.00
[ 180s ] thds: 8 tps: 461.00 qps: 9219.48 (r/w/o: 6453.45/1844.02/922.01) lat (ms,95%): 4.82 err/s: 0.00 reconn/s: 0.00
[ 190s ] thds: 8 tps: 462.40 qps: 9253.00 (r/w/o: 6478.60/1849.60/924.80) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 200s ] thds: 8 tps: 466.20 qps: 9326.17 (r/w/o: 6528.98/1864.79/932.40) lat (ms,95%): 4.57 err/s: 0.00 reconn/s: 0.00
[ 210s ] thds: 8 tps: 457.20 qps: 9141.42 (r/w/o: 6398.21/1828.80/914.40) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 220s ] thds: 8 tps: 447.00 qps: 8936.55 (r/w/o: 6254.57/1787.99/894.00) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 230s ] thds: 8 tps: 459.60 qps: 9192.00 (r/w/o: 6434.40/1838.40/919.20) lat (ms,95%): 4.65 err/s: 0.00 reconn/s: 0.00
[ 240s ] thds: 8 tps: 453.60 qps: 9072.20 (r/w/o: 6351.40/1813.60/907.20) lat (ms,95%): 4.91 err/s: 0.00 reconn/s: 0.00
[ 250s ] thds: 8 tps: 440.40 qps: 8807.60 (r/w/o: 6165.20/1761.60/880.80) lat (ms,95%): 5.18 err/s: 0.00 reconn/s: 0.00
[ 260s ] thds: 8 tps: 396.80 qps: 7937.82 (r/w/o: 5557.01/1587.20/793.60) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 270s ] thds: 8 tps: 380.80 qps: 7615.15 (r/w/o: 5330.36/1523.19/761.59) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 280s ] thds: 8 tps: 390.60 qps: 7816.96 (r/w/o: 5473.37/1562.39/781.20) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 290s ] thds: 8 tps: 388.80 qps: 7775.59 (r/w/o: 5442.79/1555.20/777.60) lat (ms,95%): 5.47 err/s: 0.00 reconn/s: 0.00
[ 300s ] thds: 8 tps: 404.20 qps: 8084.42 (r/w/o: 5659.22/1616.80/808.40) lat (ms,95%): 5.37 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1790376
write: 511536
other: 255768
total: 2557680
transactions: 127884 (426.27 per sec.) ---TPS
queries: 2557680 (8525.41 per sec.) --QPS
ignored errors: 0 (0.00 per sec.) ---忽略错误数
reconnects: 0 (0.00 per sec.) --重连接数
General statistics:
total time: 300.0066s
total number of events: 127884
Latency (ms): ---延迟信息
min: 3.82
avg: 4.69
max: 829.65
95th percentile: 5.28
sum: 599600.45
Threads fairness:
events (avg/stddev): 63942.0000/105.00
execution time (avg/stddev): 299.8002/0.00
---The END
