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

PS:

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.

In our upcoming MariaDB 5.3 release Monty optimized the internal string append code for performance. I tested his patch with a plain MariaDB 5.2 vs. a patched MariaDB 5.2 with sql-bench, which showed an overall performance gain around 3%.

The details of the patch Monty describes like this

Patch to optimize string append:

While examining a trace output from a mysql-test-run case, I noticed a
lot of reallocation calls.  This was strange as MariaDB/MySQL was
designed to do so few malloc/realloc as possible.  The system uses was
to first calculate how big buffer you would need and then allocate a
buffer big enough for most usage. Then there was some safety calls to
extend the buffer if needed.

What had happened in the MySQL code over time was that new coders was
using a buffer that was not preallocated and it was filled with a lot
of append calls(), each which caused a new realloc().

I fixed a couple of the worst cases to prealloc the buffer properly,
and for the other cases I made a general fix by making the
reallocation addaptive:

The essence was to replace the realloc() call with the following function:

In MySQL 5.3, this gave a speedup for some test cases (in debug mode)
with 25 %.  For an optimized version of MySQL we got a general
performance boost of 3 %.  The difference comes from the fact that
we do a lot of extra checks for the malloc() call in a debug build and
fewer malloc() calls gives a notable speed improvement.

The full patch can be seen by doing this in the MariaDB 5.3 tree:
bzr log  -p -r monty@askmonty.org-20101108114354-jl61qx8e36gvbzm7

Here are the detailed results of the sql-bench runs. I ran sql-bench with MariaDB 5.2.2 with default settings and used MyISAM as storage engine.

Column 1 is in seconds. All other columns are presented relative
to this. 1.00 is the same, bigger numbers indicates slower.

1) MariaDB 5.2.2.
2) MariaDB 5.2.2 patched with Monty’s string append optimization.
3) MariaDB 5.2.2 patched with Monty’s string append optimization.
4) MariaDB 5.2.2 patched with Monty’s string append optimization.

The number in () after each tests shows how many SQL commands the particular
test did.  As one test may have many different parameters this gives only
a rough picture of what was done.

I ran the test on a quite fast machine and therefore you can ignore the results with run times around 0 – 3 seconds. I am adjusting the tests to run at least for 5 seconds to have more reliable results.

As machine I used our new benchmark system “pitbull”, which I also used for benchmarking the MyISAM segmented key cache performance.