mysql性能测试工具：sysbench

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               # 测试数据库名称

通用的选项：

Option	Description	Default value
--threads	The total number of worker threads to create	1
--events	Limit for total number of requests. 0 (the default) means no limit	0
--time	Limit for total execution time in seconds. 0 means no limit	10
--warmup-time	Execute events for this many seconds with statistics disabled before the actual benchmark run with statistics enabled. This is useful when you want to exclude the initial period of a benchmark run from statistics. In many benchmarks, the initial period is not representative because CPU/database/page and other caches need some time to warm up	0
--rate	Average transactions rate. The number specifies how many events (transactions) per seconds should be executed by all threads on average. 0 (default) means unlimited rate, i.e. events are executed as fast as possible	0
--thread-init-timeout	Wait time in seconds for worker threads to initialize	30
--thread-stack-size	Size of stack for each thread	32K
--report-interval	Periodically report intermediate statistics with a specified interval in seconds. Note that statistics produced by this option is per-interval rather than cumulative. 0 disables intermediate reports	0
--debug	Print more debug info	off
--validate	Perform validation of test results where possible	off
--help	Print help on general syntax or on a specified test, and exit	off
--verbosity	Verbosity level (0 - only critical messages, 5 - debug)	4
--percentile	sysbench measures execution times for all processed requests to display statistical information like minimal, average and maximum execution time. For most benchmarks it is also useful to know a request execution time value matching some percentile (e.g. 95% percentile means we should drop 5% of the most long requests and choose the maximal value from the remaining ones). This option allows to specify a percentile rank of query execution times to count	95
--luajit-cmd	perform a LuaJIT control command. This option is equivalent to luajit -j. See LuaJIT documentation for more information

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

Option	Description	Default value
--rand-type	random numbers distribution {uniform, gaussian, special, pareto, zipfian} to use by default. Benchmark scripts may choose to use either the default distribution, or specify it explictly, i.e. override the default.	special
--rand-seed	seed for random number generator. When 0, the current time is used as an RNG seed.	0
--rand-spec-iter	number of iterations for the special distribution	12
--rand-spec-pct	percentage of the entire range where 'special' values will fall in the special distribution	1
--rand-spec-res	percentage of 'special' values to use for the special distribution	75
--rand-pareto-h	shape parameter for the Pareto distribution	0.2
--rand-zipfian-exp	shape parameter (theta) for the Zipfian distribution	0.8`

测试名testname

sysbench有如下测试可供使用：

内置的测试：:

测试名	描述
fileio	File I/O test
cpu	CPU performance test
memory	Memory functions speed test
threads	Threads subsystem performance test
mutex	Mutex performance test

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

oltp相关的测试：:

测试名	描述
bulk_insert	-
empty-test	-
oltp_common	-
oltp_delete	-
oltp_insert	-
oltp_point_select	-
oltp_read_only	-
oltp_read_write	-
oltp_update_index	-
oltp_update_non_index	-
oltp_write_only	-
prime-test	-

select_random_points
select_random_ranges

命令command

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

Commands implemented by most tests: prepare run cleanup help

命令	描述
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