mysql性能测试工具：sysbench

安装

我的环境是centos7

yum -y install make automake libtool pkgconfig libaio-devel

github下载：
https://github.com/akopytov/s...

解压：

tar -zxvf sysbench-1.0.14.tar.gz

进入解压后的目录，安装：

cd /data/sysbench-1.0.14
./autogen.sh
# Add --with-pgsql to build with PostgreSQL support
./configure
make -j
make install

hello world

[root@betacat sysbench-1.0.14]# sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=Root@1234 --mysql-db=test oltp_read_only prepare
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
[root@betacat sysbench-1.0.14]# sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=Root@1234 --mysql-db=test oltp_read_only run
sysbench 1.0.14 (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!

SQL statistics:
    queries performed:
        read:                            128128
        write:                           0
        other:                           18304
        total:                           146432
    transactions:                        9152   (914.91 per sec.)
    queries:                             146432 (14638.50 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0015s
    total number of events:              9152

Latency (ms):
         min:                                    0.88
         avg:                                    1.09
         max:                                   12.01
         95th percentile:                        1.50
         sum:                                 9981.59

Threads fairness:
    events (avg/stddev):           9152.0000/0.00
    execution time (avg/stddev):   9.9816/0.00

如何使用命令

初接触该工具最好先看看帮助，学习如何使用sysbench命令。sysbench --help

Usage:
  sysbench [options]... [testname] [command]

上面显示，sysbench命令后面依次跟选项、测试名、命令

选项options

其中，常用的选项有：

mysql相关的选项（通过sysbench --help能找到）：

--db-driver=mysql             # 数据库类型
--mysql-host=localhost        # 数据库主机
--mysql-port=3306             # 数据库端口
--mysql-user=root             # 登录名
--mysql-password=Root@1234    # 登录密码
--mysql-db=test               # 测试数据库名称

通用的选项：

控制生成测试数据时，如何生成随机数的选项：

测试名testname

sysbench有如下测试可供使用：

内置的测试：:

还有一些oltp（在线事务处理）相关的测试脚本可以在github上找到（https://github.com/akopytov/s...）。由于安装sysbench后已经有这些脚本了，我们只需要在sysbench中用这些脚本的名称即可。

oltp相关的测试：:

select_random_points
select_random_ranges

命令command

sysbench --help中已经说得很清楚了：

Commands implemented by most tests: prepare run cleanup help

注意，help命令跟sysbench --help两者是不一样的。--help是sysbench命令的选项。这里的help命令可以帮助我们了解测试的用法。

例如：

[root@betacat data]# sysbench oltp_read_write help
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)

oltp_read_write options:
  --distinct_ranges=N           Number of SELECT DISTINCT queries per transaction [1]
  --sum_ranges=N                Number of SELECT SUM() queries per transaction [1]
  --skip_trx[=on|off]           Don't start explicit transactions and execute all queries in the AUTOCOMMIT mode [off]
  --secondary[=on|off]          Use a secondary index in place of the PRIMARY KEY [off]
  --create_secondary[=on|off]   Create a secondary index in addition to the PRIMARY KEY [on]
  --index_updates=N             Number of UPDATE index queries per transaction [1]
  --range_size=N                Range size for range SELECT queries [100]
  --auto_inc[=on|off]           Use AUTO_INCREMENT column as Primary Key (for MySQL), or its alternatives in other DBMS. When disabled, use client-generated IDs [on]
  --delete_inserts=N            Number of DELETE/INSERT combinations per transaction [1]
  --tables=N                    Number of tables [1]
  --mysql_storage_engine=STRING Storage engine, if MySQL is used [innodb]
  --non_index_updates=N         Number of UPDATE non-index queries per transaction [1]
  --table_size=N                Number of rows per table [10000]
  --pgsql_variant=STRING        Use this PostgreSQL variant when running with the PostgreSQL driver. The only currently supported variant is 'redshift'. When enabled, create_secondary is automatically disabled, and delete_inserts is set to 0
  --simple_ranges=N             Number of simple range SELECT queries per transaction [1]
  --order_ranges=N              Number of SELECT ORDER BY queries per transaction [1]
  --range_selects[=on|off]      Enable/disable all range SELECT queries [on]
  --point_selects=N             Number of point SELECT queries per transaction [10]

小结

所以sysbench命令的样子就是：sysbench 选项 测试名 命令，示例：

[root@betacat data]# sysbench --db-driver=mysql --mysql-host=localhost --mysql-port=3306 --mysql-user=root --mysql-password=Root@1234 --mysql-db=test oltp_read_write run
sysbench 1.0.14 (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!

SQL statistics:
    queries performed:
        read:                            68376
        write:                           19536
        other:                           9768
        total:                           97680
    transactions:                        4884   (488.21 per sec.)
    queries:                             97680  (9764.27 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0021s
    total number of events:              4884

Latency (ms):
         min:                                    1.41
         avg:                                    2.04
         max:                                   38.73
         95th percentile:                        2.57
         sum:                                 9984.55

Threads fairness:
    events (avg/stddev):           4884.0000/0.00
    execution time (avg/stddev):   9.9845/0.00