Mysql – Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench

benchmarkmariadbMySQLperformancequery-performance

Problem

Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark.

The VM and db's are both fresh installs from vendor apt repos with default configs.

VM [Virtualbox 4.3.10]

1 cpu

ubuntu 14.04 [fresh install guest machine on virtualbox 4.3.10]

sysbench 0.4.12

Methodology

sysbench --test=oltp --oltp-table-size=50000 --mysql-db=test --mysql-table-engine=innodb --mysql-user=root prepare


sysbench --test=oltp --oltp-table-size=50000 --oltp-test-mode=complex --oltp-read-only=on --num-threads=1 --max-time=60 --max-requests=0 --mysql-db=test --mysql-user=root run

MariaDB 10.1.2 Result

OLTP test statistics:
    queries performed:
        read:                            1149876
        write:                           0
        other:                           164268
        total:                           1314144
    transactions:                        82134  (1368.89 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 1149876 (19164.41 per sec.)
    other operations:                    164268 (2737.77 per sec.)

Test execution summary:
    total time:                          60.0006s
    total number of events:              82134
    total time taken by event execution: 59.7102
    per-request statistics:
         min:                                  0.22ms
         avg:                                  0.73ms
         max:                                170.77ms
         approx.  95 percentile:               1.24ms

Threads fairness:
    events (avg/stddev):           82134.0000/0.00
    execution time (avg/stddev):   59.7102/0.00

MySQL 5.6.22 Result

OLTP test statistics:
    queries performed:
        read:                            506296
        write:                           0
        other:                           72328
        total:                           578624
    transactions:                        36164  (602.73 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 506296 (8438.17 per sec.)
    other operations:                    72328  (1205.45 per sec.)

Test execution summary:
    total time:                          60.0007s
    total number of events:              36164
    total time taken by event execution: 59.7937
    per-request statistics:
         min:                                  1.04ms
         avg:                                  1.65ms
         max:                                 97.15ms
         approx.  95 percentile:               2.10ms

Threads fairness:
    events (avg/stddev):           36164.0000/0.00
    execution time (avg/stddev):   59.7937/0.00

Best Answer

The MariaDB has a lot of optimizer features not found in mySQL (especially 5.5, but still compared to 5.6)

See this link for a comparison of optimiser features.

There is also a benchmark results page which shows MariaDB faster in a lot of tests.

So in the end MariaDB has more options when choosing an execution plan.

Related Question