How Much Can Galera Sysbench?

..we wanted to find this out, and stressed Galera cluster heavily with sysbench oltp complex mode test. We ran the same test against plain MySQL 5.1.28rc and Galera clusters configured with 1-4 nodes. The results show that Galera scales steadily with this load and already 2 node Galera cluster was always faster than plain MySQL 5.1.28.rc.

sysbench

For testing we used the sysbench 0.4.8 release from: http://sysbench.sourceforge.net. However, when running sysbench in multithread mode we soon hit duplicate key warnings, which made sysbench to stop execution. To fix this issue, we patched sysbench with Venu Anuganti's sysbench patch
(http://venublog.com/2007/12/22/sysbench-test-bails-on-duplicate-key-entr...). This solved our problem, and we were able to run the full test suite by ignoring the duplicate key warnings.

Sysbench has nice multi-host property, which lets the sysbench client to open connections (in round robin fashion) to separate hosts. This is very good for Galera testing, as we don't need to use any load balancers for accessing the cluster.

We ran several test with different test parameters. I present here benchmark figures obtained with 10K, 50K and 100K table sizes. They clearly show how deadlock rate with smaller table size hurts the performance. Here is the command, used to prepare 10K test

sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --oltp-table-size=10000 --oltp-auto-inc=off prepare

And here is the invocation to run plain MySQL 5.1.28rc test:

sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb
--oltp-table-size=10000 --oltp-test-mode=complex --oltp-read-only=off --mysql-ignore-duplicates=on --num-threads=6 --max-time=300 --max-requests=0 run

We tested with different concurrency options and 6 threads seemed to result in best MySQL 5.1.28rc performance. We therefore decided to go ahead with the full test suite using 6 threads per node in all Galera tests as well.

Test Setup

Our test cluster is the same hardware antiques section as used with previous benchmarks, containing 4 HP proliant servers (3 GHz hyperthreading CPU, 1 G RAM) and one similar HP proliant as test client. Not actual workhorses, but reasonably good to show the scaling.

We let the tests run for 5 minutes and after each session we verified the database consistency. No consistency violations were detected.

10K rows, plenty of deadlocks

Here are results obtained with the default 10000 rows table size. The graph shows how transactions/sec figure increases when more nodes are added in the cluster. The horizontal line shows the transaction rate measured for plain MySQL 5.1.28rc.





Here is the statistics output for plain MySQL 5.1.28rc test run

OLTP test statistics:
    queries performed:
        read:                            692776
        write:                           247407
        other:                           98963
        total:                           1039146
    transactions:                        49479  (164.92 per sec.)
    deadlocks:                           5      (0.02 per sec.)
    read/write requests:                 940183 (3133.68 per sec.)
    other operations:                    98963  (329.85 per sec.)

Test execution summary:
    total time:                          300.0248s
    total number of events:              49479
    total time taken by event execution: 1799.2139
    per-request statistics:
         min:                            0.0115s
         avg:                            0.0364s
         max:                            0.1501s
         approx.  95 percentile:         0.0556s

Threads fairness:
    events (avg/stddev):           8246.5000/44.87
    execution time (avg/stddev):   299.8690/0.01

Here is the statistics output for the 4 node Galera cluster test run

OLTP test statistics:
    queries performed:
        read:                            1818432
        write:                           601698
        other:                           238832
        total:                           2658962
    transactions:                        97029  (323.37 per sec.)
    deadlocks:                           32859  (109.51 per sec.)
    read/write requests:                 2420130 (8065.54 per sec.)
    other operations:                    238832 (795.95 per sec.)

Test execution summary:
    total time:                          300.0578s
    total number of events:              97029
    total time taken by event execution: 7197.8598
    per-request statistics:
         min:                            0.0137s
         avg:                            0.0742s
         max:                            1.0877s
         approx.  95 percentile:         0.1570s

Threads fairness:
    events (avg/stddev):           4042.8750/245.57
    execution time (avg/stddev):   299.9108/0.02

50K rows, less deadlocks

Here is the statistics output for plain MySQL 5.1.28rc test run

OLTP test statistics:
    queries performed:
        read:                            707714
        write:                           252755
        other:                           101102
        total:                           1061571
    transactions:                        50551  (168.49 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 960469 (3201.30 per sec.)
    other operations:                    101102 (336.98 per sec.)

Test execution summary:
    total time:                          300.0247s
    total number of events:              50551
    total time taken by event execution: 1799.1562
    per-request statistics:
         min:                            0.0115s
         avg:                            0.0356s
         max:                            0.1594s
         approx.  95 percentile:         0.0548s

Threads fairness:
    events (avg/stddev):           8425.1667/43.26
    execution time (avg/stddev):   299.8594/0.01

Here is the statistics output for the 4 node Galera cluster test run:

OLTP test statistics:
    queries performed:
        read:                            1747172
        write:                           613732
        other:                           244943
        total:                           2605847
    transactions:                        116571 (388.51 per sec.)
    deadlocks:                           8227   (27.42 per sec.)
    read/write requests:                 2360904 (7868.47 per sec.)
    other operations:                    244943 (816.35 per sec.)

Test execution summary:
    total time:                          300.0461s
    total number of events:              116571
    total time taken by event execution: 7197.1675
    per-request statistics:
         min:                            0.0140s
         avg:                            0.0617s
         max:                            0.5251s
         approx.  95 percentile:         0.1045s

Threads fairness:
    events (avg/stddev):           4857.1250/131.00
    execution time (avg/stddev):   299.8820/0.01

100K rows, reasonable deadlock rate

Here is the statistics output for plain MySQL 5.1.28rc test run:

OLTP test statistics:
    queries performed:
        read:                            712110
        write:                           254325
        other:                           101730
        total:                           1068165
    transactions:                        50865  (169.54 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 966435 (3221.30 per sec.)
    other operations:                    101730 (339.08 per sec.)

Test execution summary:
    total time:                          300.0141s
    total number of events:              50865
    total time taken by event execution: 1799.1171
    per-request statistics:
         min:                            0.0118s
         avg:                            0.0354s
         max:                            0.1853s
         approx.  95 percentile:         0.0542s

Threads fairness:
    events (avg/stddev):           8477.5000/68.46
    execution time (avg/stddev):   299.8528/0.00

Here is the statistics output for the 4 node Galera cluster test run:

OLTP test statistics:
    queries performed:
        read:                            1749020
        write:                           619224
        other:                           247361
        total:                           2615605
    transactions:                        120553 (401.78 per sec.)
    deadlocks:                           4377   (14.59 per sec.)
    read/write requests:                 2368244 (7892.89 per sec.)
    other operations:                    247361 (824.41 per sec.)

Test execution summary:
    total time:                          300.0479s
    total number of events:              120553
    total time taken by event execution: 7197.1319
    per-request statistics:
         min:                            0.0176s
         avg:                            0.0597s
         max:                            0.3320s
         approx.  95 percentile:         0.0943s

Threads fairness:
    events (avg/stddev):           5023.0417/135.40
    execution time (avg/stddev):   299.8805/0.01

Transaction Rates

Here is a summary of transaction rates for each test case.

Deadlock Rates

Here is a summary of deadlock rates for each test case.