使用sysbench测试mysql性能

上一篇 / 下一篇  2016-05-26 15:52:57 / 个人分类:Mysql

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

它主要包括以下几种方式的测试:

1、cpu性能

2、磁盘io性能

3、调度程序性能

4、内存分配及传输速度

5、POSIX线程性能

6、数据库性能(OLTP基准测试)


sysbench下载地址:http://downloads.mysql.com/source/sysbench-0.4.12.10.tar.gz

安装过程:

tar xzvf sysbench-0.4.12.10.tar.gz 

cd sysbench-0.4.12.10

./configure 

make

make install


确认是否安装成功:

# sysbench --version

sysbench 0.4.12.10


sysbench参数:

# sysbench

Missing required command argument.

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.


这里进行oltp测试:

PS:联机事务处理OLTP(on-line transaction processing)主要是执行基本的、日常的事务处理,比如数据库记录的增、删、改、查。


# sysbench --test=oltp help

sysbench 0.4.12.10:  multi-threaded system evaluation benchmark


oltp options:

  --oltp-test-mode=STRING                  test type to use {simple,complex,nontrx,sp} [complex]

  --oltp-reconnect-mode=STRING             reconnect mode {session,transaction,query,random} [session]

  --oltp-sp-name=STRING                    name of store procedure to call in SP test mode []

  --oltp-read-only=[on|off]                generate only 'read' queries (do not modify database) [off]

  --oltp-avoid-deadlocks=[on|off]          generate update keys in increasing order to avoid deadlocks [off]

  --oltp-skip-trx=[on|off]                 skip BEGIN/COMMIT statements [off]

  --oltp-range-size=N                      range size for range queries [100]

  --oltp-point-selects=N                   number of point selects [10]

  --oltp-use-in-statement=N                Use IN-statement with 10 PK lookups per query [0]

  --oltp-simple-ranges=N                   number of simple ranges [1]

  --oltp-sum-ranges=N                      number of sum ranges [1]

  --oltp-order-ranges=N                    number of ordered ranges [1]

  --oltp-distinct-ranges=N                 number of distinct ranges [1]

  --oltp-index-updates=N                   number of index update [1]

  --oltp-non-index-updates=N               number of non-index updates [1]

  --oltp-nontrx-mode=STRING                mode for non-transactional test {select, update_key, update_nokey, insert, delete} [select]

  --oltp-auto-inc=[on|off]                 whether AUTO_INCREMENT (or equivalent) should be used on id column [on]

  --oltp-connect-delay=N                   time in microseconds to sleep after connection to database [10000]

  --oltp-user-delay-min=N                  minimum time in microseconds to sleep after each request [0]

  --oltp-user-delay-max=N                  maximum time in microseconds to sleep after each request [0]

  --oltp-table-name=STRING                 name of test table [sbtest]

  --oltp-table-size=N                      number of records in test table [10000]

  --oltp-dist-type=STRING                  random numbers distribution {uniform,gaussian,special} [special]

  --oltp-dist-iter=N                       number of iterations used for numbers generation [12]

  --oltp-dist-pct=N                        percentage of values to be treated as 'special' (for special distribution) [1]

  --oltp-dist-res=N                        percentage of 'special' values to use (for special distribution) [75]

  --oltp-point-select-mysql-handler=[on|off]Use MySQL HANDLER for point select [off]

  --oltp-point-select-all-cols=[on|off]    select all columns for the point-select query [off]

  --oltp-secondary=[on|off]                Use a secondary index in place of the PRIMARY index [off]

  --oltp-num-partitions=N                  Number of partitions used for test table [0]

  --oltp-num-tables=N                      Number of test tables [1]


General database options:


  --db-driver=STRING  specifies database driver to use ('help' to get list of available drivers)

  --db-ps-mode=STRING prepared statements usage mode {auto, disable} [auto]


Compiled-in database drivers:

  mysql - MySQL driver


mysql options:

  --mysql-host=[LIST,...]       MySQL server host [localhost]

  --mysql-port=N                MySQL server port [3306]

  --mysql-socket=STRING         MySQL socket

  --mysql-user=STRING           MySQL user [sbtest]

  --mysql-password=STRING       MySQL password []

  --mysql-db=STRING             MySQL database name [sbtest]

  --mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated} [innodb]

  --mysql-engine-trx=STRING     whether storage engine used is transactional or not {yes,no,auto} [auto]

  --mysql-ssl=[on|off]          use SSL connections, if available in the client library [off]

  --myisam-max-rows=N           max-rows parameter for MyISAM tables [1000000]

  --mysql-create-options=STRING additional options passed to CREATE TABLE []

  

常用的参数列表


--mysql-host=test.mysql.rds.aliyuncs.com #数据库host 

--mysql-port=3306 #数据库端口 

--mysql-user=your_username #数据库用户名 

--mysql-password=your_password #数据库密码 

--mysql-db=your_db_for_test #数据库名 

--mysql-table-engine=STRING   storage engine to use for the test table {myisam,innodb,bdb,heap,ndbcluster,federated},默认存储引擎为[innodb]

--oltp-tables-count=10 #模拟的表的个数,规格越高该值越大 

--oltp-table-size=6000000 #模拟的每张表的行数,规格越高该值越大 

--oltp-table-name=sbtest #测试表名,默认为[sbtest]

--num-threads=50 #模拟的并发数量,规格越高该值越大 

--max-requests=100000000 #最大请求次数 

--max-time=20 #最大测试时间(与--max-requests只要有一个超过,则退出) 

--report-interval=1 #每1秒打印一次当前的QPS等值 

--test=oltp #OLTP基准测试

[prepare | run | cleanup] #prepare准备数据,run执行测试,cleanup清理数据


测试样例:


准备数据:

sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000 --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam prepare


执行测试:

sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000 --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam run


测试结果:

# time sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000  --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam 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: 300

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 "LOCK TABLES WRITE" for starting transactions

Using auto_inc on the id column

Maximum number of requests for OLTP test is limited to 100000

Using 1 test tables

Threads started!

Done.


OLTP test statistics:

    queries performed:

        read:                            1400000

        write:                           500000

        other:                           200000

        total:                           2100000

    transactions:                        100000 (455.68 per sec.)

    deadlocks:                           0      (0.00 per sec.)

    read/write requests:                 1900000 (8657.91 per sec.)

    other operations:                    200000 (911.36 per sec.)


General statistics:

    total time:                          219.4526s

    total number of events:              100000

    total time taken by event execution: 65719.8341

    response time:

         min:                                 39.30ms

         avg:                                657.20ms

         max:                                763.08ms

         approx.  95 percentile:             685.38ms


Threads fairness:

    events (avg/stddev):           333.3333/0.47

    execution time (avg/stddev):   219.0661/0.19


清理数据:

sysbench --test=oltp --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=postfix --mysql-password=postfix --mysql-db=test --oltp-table-size=4000000 --oltp-table-name=yuntest --max-requests=100000 --num-threads=300 --mysql-table-engine=myisam cleanup


TAG: 性能 使用 MySQL mysql sysbench 测试

 

评分:0

我来说两句

Open Toolbar