MariaDB-5.5 Thread Pool Performance
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:
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.
its fine only… not great thing
What kind of OS was used? Linux, unix or windows?
Those results are from a Linux machine (pitbull, 24 cores, 24G RAM). There are some more results, also for Windows, here: http://kb.askmonty.org/en/threadpool-benchmarks
It was Linux. Windows actually scaled even better in preliminary tests that I ran couple of month ago : http://kb.askmonty.org/en/threadpool-benchmarks
Very cool.
But really, I though thread pools were commonstay in most DBs.. you’re telling me that a thread is CREATED and DESTROYED per connection normally? Strange.
Dont confuse this with the already existing thread cache . . .
Thread pool is not about creating or destroying threads, but how requests (incoming SQL queries) are mapped to worker threads. Please check the MariaDB Knowledge Base for the details.
Even the traditional thread-per-connection model will not necessarily create a new thread for a new connection, because there is the thread cache. See also here how MySQL/MariaDB handles connections and maps worker threads.
. . . Or with connection pooling on the client side . . .
what is ur program
How does this compare to the thread pool of MySQL Enterprise in terms of performance?
Unfortunately this cannot be published. One can download enterprise trial, but the license forbids publishing results of any tests or benchmarks without prior written approval of Oracle. So if you need that comparison, you’ll need to run your own benchmarks yourself( and never publicly discuss the results;)