MySQL压测工具--sysbench

  1. 云栖社区>
  2. 袋鼠云技术团队>
  3. 博客>
  4. 正文

MySQL压测工具--sysbench

powdba 2017-10-29 19:03:05 浏览3162
展开阅读全文

SysBench是一个模块化的、跨平台、多线程基准测试工具,主要用于评估测试各种不同系统参数下的数据库负载情况。它主要包括以下几种方式的测试: 

1、cpu性能 
2、磁盘io性能 
3、调度程序性能 
4、内存分配及传输速度 
5、POSIX线程性能 
6、数据库性能(OLTP基准测试) 
1、安装
安装方法1:yum
#sudo yum -y install sysbench

安装方法2:二进制包
#yum -y install make automake libtool pkgconfig libaio-devel vim-common
#yum -y install mariadb-devel
#yum -y install postgresql-devel
#./autogen.sh
#./configure
#make
#make install

2、General syntax
The general command line syntax for sysbench is:
sysbench [options]... [testname] [command]
  • testname is an optional name of a built-in test (e.g. fileio, memory, cpu, etc.), or a name of one of the bundled Lua scripts (e.g. oltp_read_only), or a path to a custom Lua script. If no test name is specified on the command line (and thus, there is no command too, as in that case it would be parsed as a testname), or the test name is a dash ("-"), then sysbench expects a Lua script to execute on its standard input.
  • command is an optional argument that will be passed by sysbench to the built-in test or script specified with testname.command defines the action that must be performed by the test. The list of available commands depends on a particular test. Some tests also implement their own custom commands.
    Below is a description of typical test commands and their purpose:
    • prepare: performs preparative actions for those tests which need them, e.g. creating the necessary files on disk for thefileio test, or filling the test database for database benchmarks.
    • run: runs the actual test specified with the testname argument. This command is provided by all tests.
    • cleanup: removes temporary data after the test run in those tests which create one.
    • help: displays usage information for the test specified with the testname argument. This includes the full list of commands provided by the test, so it should be used to get the available commands.
  • options is a list of zero or more command line options starting with '--'. As with commands, the sysbench testname help command should be used to describe available options provided by a particular test.
    See General command line options for a description of general options provided by sysbench itself.
You can use sysbench --help to display the general command line syntax and options.

3、MySQL测试:
[root@172-16-10-204 /usr/local/share/sysbench]
#sysbench --test=oltp_insert.lua --mysql-host=127.0.0.1 --mysql-socket=/data/mysql/run/mysql.sock --mysql-db=test --mysql-user=newdba --tables=1 --table-size=5000000 --threads=1500 --report-interval=1 --time=10 --mysql-password=yuelei66 prepare
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0-76eeace (using bundled LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 5000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...

[root@172-16-10-204 /usr/local/share/sysbench]
#sysbench --test=oltp_insert.lua --mysql-host=127.0.0.1 --mysql-socket=/data/mysql/run/mysql.sock --mysql-db=test --mysql-user=newdba --tables=1 --table-size=5000000 --threads=1500 --report-interval=1 --time=10 --mysql-password=yuelei66 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0-76eeace (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1500
Report intermediate results every 1 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 1s ] thds: 1500 tps: 1014.63 qps: 1014.63 (r/w/o: 0.00/1014.63/0.00) lat (ms,95%): 995.51 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1500 tps: 3261.88 qps: 3261.88 (r/w/o: 0.00/3261.88/0.00) lat (ms,95%): 1589.90 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1500 tps: 5367.33 qps: 5367.33 (r/w/o: 0.00/5367.33/0.00) lat (ms,95%): 1648.20 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1500 tps: 3507.11 qps: 3507.11 (r/w/o: 0.00/3507.11/0.00) lat (ms,95%): 977.74 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1500 tps: 6685.48 qps: 6685.48 (r/w/o: 0.00/6685.48/0.00) lat (ms,95%): 1069.86 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1500 tps: 6392.54 qps: 6392.54 (r/w/o: 0.00/6392.54/0.00) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1500 tps: 6156.24 qps: 6156.24 (r/w/o: 0.00/6156.24/0.00) lat (ms,95%): 746.32 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1500 tps: 7873.09 qps: 7873.09 (r/w/o: 0.00/7873.09/0.00) lat (ms,95%): 787.74 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1500 tps: 6311.22 qps: 6313.22 (r/w/o: 0.00/6313.22/0.00) lat (ms,95%): 646.19 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1500 tps: 6545.22 qps: 6543.22 (r/w/o: 0.00/6543.22/0.00) lat (ms,95%): 601.29 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           54405
        other:                           0
        total:                           54405
    transactions:                        54405  (5133.03 per sec.)
    queries:                             54405  (5133.03 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      5133.0279
    time elapsed:                        10.5990s
    total number of events:              54405

Latency (ms):
         min:                                  3.36
         avg:                                279.83
         max:                               4505.81
         95th percentile:                    960.30
         sum:                            15224111.99

Threads fairness:
    events (avg/stddev):           36.2700/6.32
    execution time (avg/stddev):   10.1494/0.11

[root@172-16-10-204 /usr/local/share/sysbench]
#sysbench --test=oltp_read_only.lua --mysql-host=127.0.0.1 --mysql-socket=/data/mysql/run/mysql.sock --mysql-db=test --mysql-user=newdba --tables=1 --table-size=5000000 --threads=1500 --report-interval=1 --time=10 --mysql-password=yuelei66 run
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.1.0-76eeace (using bundled LuaJIT 2.1.0-beta3)

Running the test with following options:
Number of threads: 1500
Report intermediate results every 1 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 1s ] thds: 1500 tps: 17.14 qps: 5214.99 (r/w/o: 3956.14/0.00/1258.85) lat (ms,95%): 1109.09 err/s: 0.00 reconn/s: 0.00
[ 2s ] thds: 1500 tps: 169.16 qps: 5539.21 (r/w/o: 5200.89/0.00/338.32) lat (ms,95%): 2120.76 err/s: 0.00 reconn/s: 0.00
[ 3s ] thds: 1500 tps: 286.12 qps: 5839.38 (r/w/o: 5267.15/0.00/572.23) lat (ms,95%): 3040.14 err/s: 0.00 reconn/s: 0.00
[ 4s ] thds: 1500 tps: 334.88 qps: 5908.95 (r/w/o: 5239.19/0.00/669.77) lat (ms,95%): 3982.86 err/s: 0.00 reconn/s: 0.00
[ 5s ] thds: 1500 tps: 387.77 qps: 6935.90 (r/w/o: 6161.36/0.00/774.54) lat (ms,95%): 5033.35 err/s: 0.00 reconn/s: 0.00
[ 6s ] thds: 1500 tps: 556.79 qps: 8732.39 (r/w/o: 7618.81/0.00/1113.58) lat (ms,95%): 6026.41 err/s: 0.00 reconn/s: 0.00
[ 7s ] thds: 1500 tps: 527.97 qps: 8097.57 (r/w/o: 7040.62/0.00/1056.94) lat (ms,95%): 6835.96 err/s: 0.00 reconn/s: 0.00
[ 8s ] thds: 1500 tps: 588.31 qps: 9055.04 (r/w/o: 7878.42/0.00/1176.63) lat (ms,95%): 7895.16 err/s: 0.00 reconn/s: 0.00
[ 9s ] thds: 1500 tps: 609.69 qps: 10273.50 (r/w/o: 9054.12/0.00/1219.38) lat (ms,95%): 8484.79 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 1500 tps: 608.94 qps: 9583.36 (r/w/o: 8503.34/0.00/1080.03) lat (ms,95%): 7215.39 err/s: 0.00 reconn/s: 0.00
[ 11s ] thds: 1500 tps: 963.83 qps: 8534.32 (r/w/o: 7570.49/0.00/963.83) lat (ms,95%): 5033.35 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            76384
        write:                           0
        other:                           10912
        total:                           87296
    transactions:                        5456   (462.84 per sec.)
    queries:                             87296  (7405.38 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      462.8360
    time elapsed:                        11.7882s
    total number of events:              5456

Latency (ms):
         min:                                  3.39
         avg:                               2960.73
         max:                              11392.48
         95th percentile:                   7086.63
         sum:                            16153720.07

Threads fairness:
    events (avg/stddev):           3.6373/1.30
    execution time (avg/stddev):   10.7691/0.44

网友评论

登录后评论
0/500
评论
powdba
+ 关注
所属团队号: 袋鼠云技术团队