sysbench mysql 结果_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-oracle或者–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

阅读(2625) | 评论(0) | 转发(0) |