Chinaunix首页 | 论坛 | 博客
  • 博客访问: 526176
  • 博文数量: 100
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1172
  • 用 户 组: 普通用户
  • 注册时间: 2016-09-22 09:50
个人简介

一个守望数据库的老菜鸟

文章分类

全部博文(100)

文章存档

2019年(21)

2018年(17)

2017年(38)

2016年(24)

我的朋友

分类: Mysql/postgreSQL

2017-03-16 18:53:33

博客文章除注明转载外,均为原创。转载请注明出处。
本文链接地址: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-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
阅读(2142) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~