使用 sysbench 測試 MySQL 的效能

aaqwsh發表於2011-10-02
sysbench是一款開源的多執行緒效能測試工具,可以執行CPU/記憶體/執行緒/IO/資料庫等方面的效能測試。資料庫目前支援MySQL/Oracle/PostgreSQL。本文只是簡單演示一下幾種測試的用法,具體的一些引數設定,需要根據不同的測試要求來進行調整。我也是參考了很多網上的文章,本來以為很簡單,實際上比較曲折,還好這幾天不上班,天氣也不好。
 
安裝sysbench
   tar zxvf  sysbench-0.4.12.tar.gz
  cd sysbench-0.4.12
   [root@testdb2 sysbench-0.4.12]# ./autogen.sh
  ./autogen.sh: line 3: aclocal: command not found
 [root@testdb2 sysbench-0.4.12]# cat autogen.sh
#!/bin/sh
aclocal && automake -c --foreign --add-missing && autoheader && autoconf
 
 需要安裝一個包:yum install automake
 
sysbench/tests/cpu/Makefile.am:17: The usual way to define `RANLIB' is to add `AC_PROG_RANLIB'
sysbench/tests/cpu/Makefile.am:17: to `configure.ac' and run `autoconf' again.
sysbench/tests/fileio/Makefile.am:17: library used but `RANLIB' is undefined
 
需要增加一個引數
vi configure.ac
# Checks for programs.
AC_PROG_CC
#AC_PROG_LIBTOOL
AC_PROG_RANLIB
AX_CHECK_DOCBOOK
 
在執行 ./autogen.sh
./configure --prefix=/usr/local/sysbench \
> --with-mysql-includes=/usr/include/mysql \
> --with-mysql-libs=/usr/lib64/mysql
 
make
make install
 
[root@testdb2 bin]# ./sysbench
Missing required command argument.
Usage:
  sysbench [general-options]... --test= [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]
  --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
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= help' for a list of options for each test.
 
  mysql oltp的一些測試選項
 
[root@testdb2 bin]# ./sysbench --test=oltp help
sysbench 0.4.12:  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-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-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]
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 []
 
3  分別測試mysql 5.1.51 和mysql 5.5.16
   前面還是挺順利的,不過在這個伺服器上安裝mysql 5.5.16老是啟動就crashed。只好在另外的機器上裝,暈啊:
my.cnf:
innodb_log_file_size = 200M
innodb_log_files_in_group = 3
innodb_buffer_pool_size = 2048M
innodb_log_buffer_size = 16M
innodb_additional_mem_pool_size = 60M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_thread_concurrency = 0
query_cache_size = 0
init_connect='SET autocommit=0'
 
本機mysql 5.1.51  innodb
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456  --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=15  run 
sysbench 0.4.12:  multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 15
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.
OLTP test statistics:
    queries performed:
        read:                            140042
        write:                           50015
        other:                           20006
        total:                           210063
    transactions:                        10003  (1117.67 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190057 (21235.67 per sec.)
    other operations:                    20006  (2235.33 per sec.)
Test execution summary:
    total time:                          8.9499s
    total number of events:              10003
    total time taken by event execution: 133.9374
    per-request statistics:
         min:                                  5.21ms
         avg:                                 13.39ms
         max:                                 95.67ms
         approx.  95 percentile:              31.75ms
Threads fairness:
    events (avg/stddev):           666.8667/17.47
    execution time (avg/stddev):   8.9292/0.00
 
本機mysql 5.1.51  myisam:
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root --mysql-password=123456  --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=20  run
sysbench 0.4.12:  multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
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 10000
Threads started!
Done.
OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (154.32 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (2931.99 per sec.)
    other operations:                    20000  (308.63 per sec.)
Test execution summary:
    total time:                          64.8023s
    total number of events:              10000
    total time taken by event execution: 1294.2359
    per-request statistics:
         min:                                  9.19ms
         avg:                                129.42ms
         max:                                303.85ms
         approx.  95 percentile:             136.45ms
Threads fairness:
    events (avg/stddev):           500.0000/0.00
    execution time (avg/stddev):   64.7118/0.04
其他機的mysql 5.5.16
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=sbtest --mysql-password=123456 --mysql-host=192.1.1.39 --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=20 --max-requests=100000 --oltp-test-mode=complex run
sysbench 0.4.12:  multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.
OLTP test statistics:
    queries performed:
        read:                            1402492
        write:                           500890
        other:                           200179
        total:                           2103561
    transactions:                        100001 (602.62 per sec.)
    deadlocks:                           177    (1.07 per sec.)
    read/write requests:                 1903382 (11470.01 per sec.)
    other operations:                    200179 (1206.30 per sec.)
Test execution summary:
    total time:                          165.9442s
    total number of events:              100001
    total time taken by event execution: 3317.4742
    per-request statistics:
         min:                                 10.66ms
         avg:                                 33.17ms
         max:                                302.29ms
         approx.  95 percentile:              43.34ms
Threads fairness:
    events (avg/stddev):           5000.0500/15.97
    execution time (avg/stddev):   165.8737/0.01
其他機的mysql 5.1.51 :
[root@testdb2 bin]# ./sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=1000000 --mysql-user=sbtest --mysql-password=123456 --mysql-host=192.1.1.39  --mysql-socket=/var/lib/mysql/mysql.sock --num-threads=20 --max-requests=100000 --oltp-test-mode=complex run
sysbench 0.4.12:  multi-threaded system evaluation benchmark
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 20
Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 100000
Threads started!
Done.
OLTP test statistics:
    queries performed:
        read:                            1400000
        write:                           500000
        other:                           200000
        total:                           2100000
    transactions:                        100000 (542.68 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1900000 (10310.86 per sec.)
    other operations:                    200000 (1085.35 per sec.)
Test execution summary:
    total time:                          184.2717s
    total number of events:              100000
    total time taken by event execution: 3684.1488
    per-request statistics:
         min:                                 12.50ms
         avg:                                 36.84ms
         max:                                426.66ms
         approx.  95 percentile:              45.09ms
Threads fairness:
    events (avg/stddev):           5000.0000/11.43
    execution time (avg/stddev):   184.2074/0.01
 
好像mysl5.5在這種併發下並沒用宣傳得那麼好,還得找時間好好研究一下,再測試一下。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/758322/viewspace-708581/,如需轉載,請註明出處,否則將追究法律責任。

相關文章