MySQL 5.7 (4sysbench安装和性能测试)

sysbench安装和性能测试


下载地址
http://sourceforge.net/projects/sysbench 
https://dev.mysql.com/downloads/benchmarks.html
https://github.com/akopytov/sysbench


sysbench-0.4.12.14.tar.gz


tar zxf sysbench-0.4.12.14.tar.gz 
cd sysbench-0.4.12.14




[root@vm-test1 sysbench-0.4.12.14]# ./autogen.sh 
libtoolize 1.4+ wasn't found, exiting
缺少libtool软件包:
[root@vm-test1 ~]# yum install libtool


./autogen.sh
./configure && make && make install
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib && make && make install
另外，如果想要让 sysbench 支持 pgsql/oracle 的话，就需要在编译的时候加上参数
--with-pgsql
或者
--with-oracle
这2个参数默认是关闭的，只有 MySQL 是默认支持的。
[root@vm-test1 sysbench-0.4.12.14]# ll /usr/local/bin/sysbench
-rwxr-xr-x 1 root root 1239259 Sep  5 21:09 /usr/local/bin/sysbench
[root@vm-test1 sysbench-0.4.12.14]# 
[root@vm-test1 sysbench-0.4.12.14]# ldd /usr/local/bin/sysbench
        linux-vdso.so.1 =>  (0x00007fff4b3ff000)
        libmysqlclient.so.20 => not found
        librt.so.1 => /lib64/librt.so.1 (0x0000003b49a00000)
        libaio.so.1 => /lib64/libaio.so.1 (0x0000003b48e00000)
        libm.so.6 => /lib64/libm.so.6 (0x0000003b49e00000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003b49600000)
        libc.so.6 => /lib64/libc.so.6 (0x0000003b49200000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003b48a00000)
[root@vm-test1 sysbench-0.4.12.14]# 
[root@vm-test1 sysbench-0.4.12.14]# ll /usr/local/mysql/lib/
total 985036
-rw-r--r-- 1 mysql mysql  20051248 Jun 22 23:00 libmysqlclient.a
lrwxrwxrwx 1 mysql mysql        20 Aug 31 20:23 libmysqlclient.so -> libmysqlclient.so.20
lrwxrwxrwx 1 mysql mysql        24 Aug 31 20:23 libmysqlclient.so.20 -> libmysqlclient.so.20.3.6
-rwxr-xr-x 1 mysql mysql   9255841 Jun 22 23:00 libmysqlclient.so.20.3.6
-rw-r--r-- 1 mysql mysql 580037378 Jun 22 23:11 libmysqld.a
-rw-r--r-- 1 mysql mysql 399257714 Jun 22 22:52 libmysqld-debug.a
-rw-r--r-- 1 mysql mysql     41678 Jun 22 22:54 libmysqlservices.a
drwxr-xr-x 4 mysql mysql      4096 Aug 31 20:23 mecab
drwxr-xr-x 2 mysql mysql      4096 Aug 31 20:23 pkgconfig
drwxr-xr-x 3 mysql mysql      4096 Aug 31 20:23 plugin
[root@vm-test1 sysbench-0.4.12.14]# 
export LD_LIBRARY_PATH=/usr/local/mysql/lib/
[root@vm-test1 sysbench-0.4.12.14]# ldd /usr/local/bin/sysbench
        linux-vdso.so.1 =>  (0x00007fff4e79a000)
        libmysqlclient.so.20 => /usr/local/mysql/lib/libmysqlclient.so.20 (0x00007f8b27c41000)
        librt.so.1 => /lib64/librt.so.1 (0x0000003b49a00000)
        libaio.so.1 => /lib64/libaio.so.1 (0x0000003b48e00000)
        libm.so.6 => /lib64/libm.so.6 (0x0000003b49e00000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x0000003b49600000)
        libc.so.6 => /lib64/libc.so.6 (0x0000003b49200000)
        libdl.so.2 => /lib64/libdl.so.2 (0x0000003b28a00000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x0000003b54e00000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x0000003b54200000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003b48a00000)
[root@vm-test1 sysbench-0.4.12.14]# 


/usr/local/bin/sysbench --help
sysbench --help
[root@vm-test1 sysbench-0.4.12.14]# sysbench --help
Usage:
  sysbench [general-options]... --test=<test-name> [test-options]... command


General options:
  --num-threads=N             number of threads to use [1]
  --max-requests=N            limit for total number of requests [10000]
  --max-time=N                limit for total execution time in seconds [0]
  --forced-shutdown=STRING    amount of time to wait after --max-time before forcing shutdown [off]
  --thread-stack-size=SIZE    size of stack per thread [32K]
  --init-rng=[on|off]         initialize random number generator [off]
  --seed-rng=N                seed for random number generator, ignored when 0 [0]
  --tx-rate=N                 target transaction rate (tps) [0]
  --tx-jitter=N               target transaction variation, in microseconds [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. []
  --test=STRING               test to run
  --debug=[on|off]            print more debugging info [off]
  --validate=[on|off]         perform validation checks where possible [off]
  --help=[on|off]             print help and exit
  --version=[on|off]          print version and exit


Log options:
  --verbosity=N      verbosity level {5 - debug, 0 - only critical messages} [4]


  --percentile=N      percentile rank of query response times to count [95]


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
  oltp - OLTP test


Commands: prepare run cleanup help version


See 'sysbench --test=<name> help' for a list of options for each test.


[root@vm-test1 sysbench-0.4.12.14]# 
/tools/sysbench-0.4.12.14/sysbench/tests
[root@vm-test1 tests]# ll
total 96
-rw-r--r-- 1 222933 31415    70 Sep 23  2010 CMakeLists.txt
drwxr-xr-x 3 222933 31415  4096 Sep  5 21:08 cpu
drwxr-xr-x 3 222933 31415  4096 Sep  5 21:08 fileio
-rw-r--r-- 1 root   root  18844 Sep  5 21:08 Makefile
-rw-r--r-- 1 222933 31415   782 Sep 23  2010 Makefile.am
-rw-r--r-- 1 root   root  18298 Sep  5 21:08 Makefile.in
drwxr-xr-x 3 222933 31415  4096 Sep  5 21:08 memory
drwxr-xr-x 3 222933 31415  4096 Sep  5 21:08 mutex
drwxr-xr-x 3 222933 31415  4096 Sep  5 21:08 oltp
-rw-r--r-- 1 222933 31415   829 Sep 23  2010 sb_cpu.h
-rw-r--r-- 1 222933 31415  1222 Sep 23  2010 sb_fileio.h
-rw-r--r-- 1 222933 31415  1218 Sep 23  2010 sb_memory.h
-rw-r--r-- 1 222933 31415   956 Sep 23  2010 sb_mutex.h
-rw-r--r-- 1 222933 31415  2658 Mar 17  2015 sb_oltp.h
-rw-r--r-- 1 222933 31415   943 Sep 23  2010 sb_threads.h
drwxr-xr-x 3 222933 31415  4096 Sep  5 21:08 threads
[root@vm-test1 tests]# 
1、cpu性能测试


sysbench --test=cpu --cpu-max-prime=20000 run
cpu测试主要是进行素数的加法运算，在上面的例子中，指定了最大的素数为 20000，自己可以根据机器cpu的性能来适当调整数值。


[root@vm-test1 sysbench-0.4.12.14]# sysbench --test=cpu --cpu-max-prime=20000 run
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 1
Random number generator seed is 0 and will be ignored




Doing CPU performance benchmark


Primer numbers limit: 20000


Threads started!
Done.




General statistics:
    total time:                          26.6349s
    total number of events:              10000
    total time taken by event execution: 26.6138
    response time:
         min:                                  2.41ms
         avg:                                  2.66ms
         max:                                  4.80ms
         approx.  95 percentile:               2.99ms


Threads fairness:
    events (avg/stddev):           10000.0000/0.00
    execution time (avg/stddev):   26.6138/0.00


[root@vm-test1 sysbench-0.4.12.14]# 


2、线程测试
sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run
[root@vm-test1 sysbench-0.4.12.14]# sysbench --test=threads --num-threads=64 --thread-yields=100 --thread-locks=2 run
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 64
Random number generator seed is 0 and will be ignored




Doing thread subsystem performance test
Thread yields per test: 100 Locks used: 2
Threads started!
Done.




General statistics:
    total time:                          0.7089s
    total number of events:              10000
    total time taken by event execution: 45.1602
    response time:
         min:                                  0.02ms
         avg:                                  4.52ms
         max:                                 42.67ms
         approx.  95 percentile:              16.43ms


Threads fairness:
    events (avg/stddev):           156.2500/18.52
    execution time (avg/stddev):   0.7056/0.00


[root@vm-test1 sysbench-0.4.12.14]# 
3、磁盘IO性能测试
sysbench --test=fileio --num-threads=8 --file-total-size=300M --file-test-mode=rndrw prepare
314572800 bytes written in 2.73 seconds (109.86 MB/sec).
sysbench --test=fileio --num-threads=8 --file-total-size=300M --file-test-mode=rndrw run
[root@vm-test1 test]# sysbench --test=fileio --num-threads=8 --file-total-size=300M --file-test-mode=rndrw run
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 8
Random number generator seed is 0 and will be ignored




Extra file open flags: 0
128 files, 2.3438Mb each
300Mb total file size
Block size 16Kb
Number of random requests for random IO: 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
Threads started!
Done.


Operations performed:  5999 reads, 4001 writes, 12800 Other = 22800 Total
Read 93.734Mb  Written 62.516Mb  Total transferred 156.25Mb  (151.68Mb/sec)
 9707.35 Requests/sec executed


General statistics:
    total time:                          1.0301s
    total number of events:              10000
    total time taken by event execution: 0.4638
    response time:
         min:                                  0.00ms
         avg:                                  0.05ms
         max:                                  8.94ms
         approx.  95 percentile:               0.11ms


Threads fairness:
    events (avg/stddev):           1250.0000/176.44
    execution time (avg/stddev):   0.0580/0.01


sysbench --test=fileio --num-threads=8 --file-total-size=300M --file-test-mode=rndrw cleanup
上述参数指定了最大创建16个线程，创建的文件总大小为3G，文件读写模式为随机读。


4、内存测试


sysbench --test=memory --memory-block-size=8K --memory-total-size=3300M --num-threads=2 run
上述参数指定了本次测试整个过程是在内存中传输 4G 的数据量，每个 block 大小为 8K。
[root@vm-test1 test]# sysbench --test=memory --memory-block-size=8K --memory-total-size=3300M --num-threads=2 run
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark


Running the test with following options:
Number of threads: 2
Random number generator seed is 0 and will be ignored




Doing memory operations speed test
Memory block size: 8K


Memory transfer size: 3300M


Memory operations type: write
Memory scope type: global
Threads started!
Done.


Operations performed: 422400 (1074155.60 ops/sec)


3300.00 MB transferred (8391.84 MB/sec)




General statistics:
    total time:                          0.3932s
    total number of events:              422400
    total time taken by event execution: 0.6542
    response time:
         min:                                  0.00ms
         avg:                                  0.00ms
         max:                                  0.39ms
         approx.  95 percentile:               0.00ms


Threads fairness:
    events (avg/stddev):           211200.0000/960.00
    execution time (avg/stddev):   0.3271/0.00


[root@vm-test1 test]# 
3、结果分析
5、OLTP测试


OLTP测试前准备数据
初始化测试库环境（总共10个测试表，每个表 100000 条记录，填充随机生成的数据）：


数据准备
sysbench --test=tests/db/parallel_prepare.lua --max-time=100 --oltp-dist-type=uniform --max-requests=0 --mysql-user=test --mysql-password=test --mysql-table-engine=innodb --oltp-table-size=100 --oltp-tables-count=8 --oltp-range-size=90 --oltp-point-selects=1 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1 --oltp-distinct-ranges=1 --oltp-non-index-updates=10 --num-threads=20  --mysql-host=127.0.0.1 --mysql-port=3306 prepare


sysbench --mysql-user=root --mysql-password=123456 --mysql-table-engine=innodb --mysql-socket=/tmp/mysql3306.sock --test=oltp  --oltp-table-size=10000  --num-threads=20 --max-time=120 --max-requests=0 --mysql-db=tcyang prepare


测试 OLTP 时，可以自己先创建数据库 sbtest ，或者自己用参数 --mysql-db 来指定其他数据库。


./sysbench --mysql-host=1.2.3.4. --mysql-port=3306 --mysql-user=tpcc \
--mysql-password=tpcc --test=tests/db/oltp.lua --oltp_tables_count=10 \
--oltp-table-size=10000000 --num-threads=8 --oltp-read-only=off \
--report-interval=10 --rand-type=uniform --max-time=3600 \
 --max-requests=0 --percentile=99 run >> ./log/sysbench_oltpX_8_20140921.log
 
 sysbench --test=oltp --oltp_tables_count=10 --oltp-table-size=100000 --mysql-user=root --mysql-password=123456 --num-threads=20 --max-time=120 --max-requests=0 --oltp-test-mode=complex run 


 
 sysbench --mysql-user=root --mysql-password=123456 --mysql-table-engine=innodb --mysql-socket=/tmp/mysql3306.sock --test=oltp  --oltp-table-size=10000  --num-threads=2 --max-time=12 --max-requests=0 --mysql-db=tcyang --oltp-test-mode=complex run 


 [root@vm-test1 tcyang]#  sysbench --mysql-user=root --mysql-password=123456 --mysql-table-engine=innodb --mysql-socket=/tmp/mysql3306.sock --test=oltp  --oltp-table-size=10000  --num-threads=2 --max-time=12 --max-requests=0 --mysql-db=tcyang --oltp-test-mode=complex run 
sysbench 0.4.12.10:  multi-threaded system evaluation benchmark


No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 2
Random number generator seed is 0 and will be ignored




Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Using 1 test tables
Threads started!
Time limit exceeded, exiting...
(last message repeated 1 times)
Done.


OLTP test statistics:
    queries performed:
        read:                            83384
        write:                           29780
        other:                           11912
        total:                           125076
    transactions:                        5956   (496.16 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 113164 (9427.07 per sec.)
    other operations:                    11912  (992.32 per sec.)


General statistics:
    total time:                          12.0042s
    total number of events:              5956
    total time taken by event execution: 23.8967
    response time:
         min:                                  2.23ms
         avg:                                  4.01ms
         max:                                 17.17ms
         approx.  95 percentile:               4.90ms


Threads fairness:
    events (avg/stddev):           2978.0000/1.00
    execution time (avg/stddev):   11.9483/0.02


[root@vm-test1 tcyang]#