mysql性能测试工具:sysbench

minerk 2019-06-27

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

通用的选项:

OptionDescriptionDefault value
--threadsThe total number of worker threads to create1
--eventsLimit for total number of requests. 0 (the default) means no limit0
--timeLimit for total execution time in seconds. 0 means no limit10
--warmup-timeExecute 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 up0
--rateAverage 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 possible0
--thread-init-timeoutWait time in seconds for worker threads to initialize30
--thread-stack-sizeSize of stack for each thread32K
--report-intervalPeriodically 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 reports0
--debugPrint more debug infooff
--validatePerform validation of test results where possibleoff
--helpPrint help on general syntax or on a specified test, and exitoff
--verbosityVerbosity level (0 - only critical messages, 5 - debug)4
--percentilesysbench 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 count95
--luajit-cmdperform a LuaJIT control command. This option is equivalent to luajit -j. See LuaJIT documentation for more information

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

OptionDescriptionDefault value
--rand-typerandom 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-seedseed for random number generator. When 0, the current time is used as an RNG seed.0
--rand-spec-iternumber of iterations for the special distribution12
--rand-spec-pctpercentage of the entire range where 'special' values will fall in the special distribution1
--rand-spec-respercentage of 'special' values to use for the special distribution75
--rand-pareto-hshape parameter for the Pareto distribution0.2
--rand-zipfian-expshape parameter (theta) for the Zipfian distribution0.8`

测试名testname

sysbench有如下测试可供使用:

内置的测试::

测试名描述
fileioFile I/O test
cpuCPU performance test
memoryMemory functions speed test
threadsThreads subsystem performance test
mutexMutex 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

相关推荐