使用 sysbench 測試 MySQL 的效能

2   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

 

[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

[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

[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在這種併發下並沒用宣傳得那麼好，還得找時間好好研究一下，再測試一下。