September 2011

MySQL Performance: High Load & New Thread Pool in 5.5

This post is the following part of the previous one (http://dimitrik.free.fr/blog/archives/2010/11/mysql-performance-55-and-innodb-thread-concurrency.html) discussing about concurrency management within MySQL/InnoDB. At the end of the article I've expressed my expectations to see one day a kind of "Advanced Thread Pool" integrated within MySQL. Today this expectation become a reality with newly available Thread Pool extension for MySQL (see more about on http://blogs.oracle.com/MySQL/entry/new_commercial_extensions_for_mysql and Sysbench benchmark results on http://www.mysql.com/products/enterprise/scalability.html).

And of course, to follow my previous blog post, I'll present you my own results obtained on the same dbSTRESS workload as before. The following graphs are representing MySQL 5.5 performance levels under dbSTRESS workload while Thread Pool is used or not. As you may read from my previous post, using "innodb_thread_concurrency" setting is not helping anymore to keep performance stable on a high number of concurrent user sessions. And the most optimal solution was to limit MySQL server execution within a limited set of CPU/cores (if contention cannot be reduced, we may at least avoid to amplify it by reducing concurrency). Let's see now how the Thread Pool extension is helping here..

And few test graphs will tell you it better than many words ;-))

There are 4 parts on each graph corresponding to 4 tests running one after other:

• MySQL is running within 8 cores taskset
• MySQL is running within 16 cores taskset
• MySQL is running on 32 cores without taskset
• MySQL is running on 32 cores without any taskset and with Thread Pool enabled (with thread_pool_size=16)

On each test the load is growing step by step from 1 to 2, 4, 8, 16, 32 .. 1024 concurrent user sessions. While the load is growing, InnoDB internal contention become more and more hot. Limiting MySQL server to 8 cores is giving the most stable result on the "standard" MySQL configuration. While the Thread Pool is "naturally" helping here to keep the level of contentions more constant whatever load we have and avoid performance drop on a high number of concurrent sessions.

And it's very clearly seen from graphs how drastically the InnoDB mutex contention is reduced.


Read-Only Workload


Observations :

• using taskset on 8 cores gives the most stable result on the "standard" MySQL 5.5 configuration (20.000 TPS, 21.000 TPS in peak), however performance is decreasing on 1024 sessions (under 15.000 TPS)..
• then when Thread Pool is used the peak performance is higher: 27.000 TPS vs 21.000, and on 1024 sessions it's still higher as well (17.500 vs 14.000 TPS)
• the gain with Thread Pool here is due a dramatic drop on kernel_mutex contention!..

Read+Update Workload


Observations :

• "standard" 5.5 is more optimal on 8 cores taskset again
• but with Thread Pool you have yet more better overall performance: 40.000 TPS vs 35.000, and way better result on 1024 sessions: 27.000 TPS vs 10.000..
• the obtained gain is again due lower contention on the kernel_mutex

Read+Write Workload

Observations :

• again taskset on 8 cores is more optimal for "standard" MySQL 5.5
• and Thread Pool again gives the best results: 22.500 TPS vs 19.000, then overall performance is better too, and finally on 1024 sessions the result is better again: 10.000 TPS vs 3.000 TPS
• once again the gain is due a lower mutex contention! (kernel_mutex + index mutex)