wiki:DatabaseBenchmarks
Last modified 9 years ago Last modified on 04/26/2010 09:26:46 PM

All tests using Sysbench, lsst10, 16 threads, myisam engine, 60M table rows, 11GB of table data, 1GB of index data.

Script

  • Setup
    • Create database sbtest;
    • Create user sysbench, set password (see MikeF)
    • Grant all on database sbtest to user sysbench
$ cat sysbench-database-tests.sh
#!/bin/sh

case "$1" in
  prepare)
    sysbench --test=oltp \
             --mysql-socket=/tmp/mysql.sock --mysql-table-engine=myisam \
             --mysql-user=sysbench --mysql-password=xxx \
             --oltp-table-size=60000000 \
             --myisam-max-rows=60000000 \
             prepare
    ;;
  run-simple)
    sysbench --test=oltp \
             --mysql-socket=/tmp/mysql.sock --mysql-table-engine=myisam \
             --mysql-user=sysbench --mysql-password=xxx \
             --oltp-table-size=60000000 \
             --myisam-max-rows=60000000 \
             --oltp-test-mode=simple \
             --num-threads=16 --max-time=600 --max-requests=1000000 \
             run
    ;;
  run-complex)
    sysbench --test=oltp \
             --mysql-socket=/tmp/mysql.sock --mysql-table-engine=myisam \
             --mysql-user=sysbench --mysql-password=xxx \
             --oltp-table-size=60000000 \
             --myisam-max-rows=60000000 \
             --oltp-test-mode=complex \
             --num-threads=16 --max-time=600 --max-requests=1000000 \
             run
    ;;
  cleanup)
    sysbench --test=oltp \
             --mysql-socket=/tmp/mysql.sock \
             --mysql-user=sysbench --mysql-password=xxx \
             cleanup
    ;;
esac

Simple Test

This test is a simple select using the index.

Apr 25, 2010:

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running simple 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 1000000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            1000135
        write:                           0
        other:                           0
        total:                           1000135
    transactions:                        1000135 (28700.03 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1000135 (28700.03 per sec.)
    other operations:                    0      (0.00 per sec.)

Test execution summary:
    total time:                          34.8479s
    total number of events:              1000135
    total time taken by event execution: 554.6464
    per-request statistics:
         min:                                  0.09ms
         avg:                                  0.55ms
         max:                                 49.71ms
         approx.  95 percentile:               0.64ms

Threads fairness:
    events (avg/stddev):           62508.4375/28.50
    execution time (avg/stddev):   34.6654/0.00

Complex Test

This test is a combination of selects, updates, deletes, and inserts.

Apr 25, 2010:

sysbench 0.4.12:  multi-threaded system evaluation benchmark

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16

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 1000000
Threads started!
Time limit exceeded, exiting...
(last message repeated 15 times)
Done.

OLTP test statistics:
    queries performed:
        read:                            1786904
        write:                           638180
        other:                           255272
        total:                           2680356
    transactions:                        127636 (211.53 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 2425084 (4019.08 per sec.)
    other operations:                    255272 (423.06 per sec.)

Test execution summary:
    total time:                          603.3921s
    total number of events:              127636
    total time taken by event execution: 9602.1900
    per-request statistics:
         min:                                  3.74ms
         avg:                                 75.23ms
         max:                               5961.36ms
         approx.  95 percentile:              48.38ms

Threads fairness:
    events (avg/stddev):           7977.2500/0.43
    execution time (avg/stddev):   600.1369/0.82