When I published the MariaDB-5.3.4 sysbench results I said “if your workload includes complex (sub)queries, then you will probably benefit more from MariaDBs new optimizer features”. Today I will present some benchmark results for complex workload.

The benchmark is DBT3, an implementation of the TPC-H specification. DBT3 is written in C and hosted at Sourceforge.

The DBT3 benchmark can run at different scale factors – defining the size of the database. I used a scale factor of 30 which yields ~30GB of raw data and ~48GB of disk footprint. The machine running the benchmark had 16G of memory.

InnoDB has the problem of fluctuating table statistics, leading to rather unpredictable query plans. For this reason the benchmark tables were created with the MyISAM engine. I will definitely run this benchmark vs. InnoDB in the future, but this requires some changes in our DBT3 automation scripts.

Both MySQL and MariaDB had to be tuned to use the new optimizer features. Specifically I turned on multi-range-read and batched-key-access. The MRR cost estimation was turned off because it’s not production ready in neither MySQL-5.6 nor MariaDB. For details see the tarball with the configuration files.

The following concentrates on the DBT3 Power Test which consists of 22 rather heavy JOIN queries. Each query was run 5 times on cold caches (server restart + file system cache cleared). Query execution time was limited by a 2 hour timeout. Execution time varies between 10 seconds and >2000 seconds, so for the following picture the execution time was normalized (MariaDB = 100%). The colored bar shows the median and the whiskers show min and max:

DBT3 Power Test, large scale

Here we can already see that MySQL-5.5 cannot cope well with this type of load. MySQL-5.6 has improved on many queries, but then again for query 3 or 8 it is even worse than 5.5. Rather a mystery is query 22 where MySQL 5.5 comes in first.

For more details, here is a table with the numbers. I have colored the rows to mark the winner (same colors as the diagram). If the difference is below 5% then it’s a draw. Those lines are not colored.

MariaDB-5.3.5 MySQL-5.6.4 MySQL-5.5.21
Query Seconds Seconds relative Seconds relative
1 289 286 -1.04% 319 +10.38%
2 46 48 +4.35% 490 +965.2%
3 243 1322 +444% 534 +119.7%
4 138 141 +2.17% 185 +34.06%
5 187 1232 +558.8% >7200
6 199 191 -4.02% 284 +42.71%
7 861 777 -9.76% 803 -6.74%
8 288 1628 +465.3% 742 +157.6%
9 268 307 +14.55% >7200
10 818 1504 +83.86% 1083 +32.40%
11 13 14 +7.69% 342 +2531%
12 213 206 -3.29% 452 +112.2%
13 250 230 -8.00% 1576 +530.4%
14 90 92 +2.22% >7200
15 194 190 -2.06% 401 +106.7%
16 129 149 +15.50% 148 +14.73%
17 800 797 -0.38% 867 +8.38%
18 284 >7200 >7200
19 62 61 -1.61% 2013 +3147%
20 >7200 >7200 >7200
21 185 182 -1.62% >7200
22 13 14 +7.69% 10 -23.08%

Query 11 is a draw because the difference is only 1 second (the granularity of time measurement). Query 11 showed a strange phenomenon for MariaDB: the first run took 43 seconds, subsequent runs only 13 seconds. This is probably a caching effect of the disk controller.

Neither MySQL version finishes query 18 within the time limit. None of the 3 candidates finishes query 20 within the limit.

Query execution plans differed between the candidates. Sometimes I could tweak the results by forcing the known good join order (using straight_join). I.e. on query 7 MariaDB used a different join order. When I forced the MySQL plan, then MariaDB needed only ~70% of the MySQL time. Or for query 12 MySQL 5.5 used a different plan as the others. After forcing the better join order, all 3 came it at the same time.

Finally I have another diagram, this time showing the details for the region around 100%.

DBT3 power test, small scale

If you want to run the DBT3 benchmark on your own, then you can use our DBT3 scripts. I have tarred up the configuration files for those scripts and the extracted raw data for download here.

MariaDB-5.5.21-beta is the first MariaDB release featuring the new thread pool. Oracle offers a commercial thread pool plugin for MySQL Enterprise, but now MariaDB brings a thread pool implementation to the community!

If you are not familiar with the term, please read the Knowledge Base article about it.

The main design goal of the thread pool is to increase the scalability of the MariaDB server with many concurrent connections. In order to test and demonstrate this, I have run the sysbench OLTP RO benchmark with up to 4096 threads to compare the new pool-of-threads and the traditional thread-per-connection scheduler:

OLTP(ro) MariaDB-5.5.21 pool-of-threads vs. thread-per-connection

Benchmark description:

  • sysbench multi table OLTP, readonly
  • 16 tables, totaling 40 mio rows (~10G of data)
  • 16G buffer pool – result is independent of disk performance
  • mysqld bound to 16 cpu cores, sysbench to the other 8

Read/write OLTP benchmark results will be published as soon as they are available.

Raw benchmark results and the scripts used can be downloaded here


Let me add few more words about binding cpu cores and thread pool configuration.

Sysbench-0.5 which was used for this benchmark, turned out to be quite a cpu hog; especially for high concurrency levels. If both mysqld and sysbench are allowed to run on all cpu cores, they will fight for resources – which will result in artificially bad results with many threads. With 4 cores for sysbench and 20 for mysqld, sysbench itself became the bottleneck. So I ended with 8 cores for sysbench (leaving some room here) and 16 for mysqld.

On UNIX, the MariaDB thread pool normally needs no configuration because it autoconfigures thread-pool-size to the number of cpu cores (this depends on sysconf(3) to implement _SC_NPROCESSORS_ONLN) which is in almost all cases the optimal size. If one binds mysqld to a subset of cpu cores, one should set thread-pool-size manually to the number of cpu cores given to mysqld.

Last weekend Vadim from Percona published his MariaDB 5.3.4 benchmark results. As the new benchmark guy at Monty Program I take this oportunity to add some more results of my own.

One question in the comments to Vadim was if it is fair to compare MariaDB-5.3 with MySQL-5.5. Or if this comparison should be done with MySQL-5.1. The answer is: it does not matter much. MySQL-5.5 and MySQL-5.1 show very similar results in the Sysbench OLTP benchmark.

So I created a Sysbench environment pretty much like Vadims and tested the following versions of the MySQL Server:

  • MariaDB-5.3.4 – the Monty Program release candidate, both with XtraDB and the InnoDB plugin
  • Percona-Server 5.1.61 because it is based on the same XtraDB version as MariaDB-5.3
  • Percona-Server 5.5.20 – the current Percona flagship
  • MySQL-5.5.20 – the current Oracle flagship

The result:

Indeed MariaDB-5.3.4 scales significantly worse than MySQL-5.5.20. However Percona-Server 5.1.61 behaves nearly identical and Percona-Server 5.5.20 is only slightly better. If we run MariaDB-5.3.4 with the InnoDB plugin instead of the default XtraDB, then MariaDB outperforms Percona-Server 5.5.20.

As a consequence, we will deliver the InnoDB plugin with future binary releases of MariaDB. MySQL-5.1.61 with the InnoDB plugin (not shown in the graph) is between MariaDB-5.3.4(InnoDB-plugin) and MySQL-5.5.20. We are currently investigating why MySQL-5.1 is faster than MariaDB-5.3, even with the same storage engine.

Please keep in mind that Sysbench is a highly synthetical benchmark. Real world workload will probably yield different results. Also this benchmark used solid state disks. A storage system with spinning disks might saturate much earlier and would then flatten out the peaks.

If I should draw a conclusion, then I would say that for highest concurrency levels you should go for MySQL-5.5. Or wait for MariaDB-5.5 :)

If your workload includes complex (sub)queries, then you will probably benefit more from MariaDBs new optimizer features. And if you are running replication, then MariaDB 5.3 with group-commit will greatly outperform any other MySQL version.

Benchmark details: sysbench OLTP multi-table. 16 tables with 250K rows each. 20G InnoDB buffer pool. Data and logs on SSD. Dual socket hexacore Xeon with HT enabled (24 logical cores). You can download the tarball with benchmark scripts, config and raw results.