DimitriK's (dim) Weblog

MySQL Performance: Table Open Cache in 5.6

Over this summer I've observed several very curious MySQL performance issues, but time was short to blog about (and vacation period is not motivating to it either ;-))

But here is a one of them I'd like to share.

The following graphs are representing a MySQL test case running on 12cores Linux box from the same workload: simple Sysbench OLTP_RO benchmark with a growing number of concurrent users (150, 200, 250, 300, 350):
the only difference here that in the fist case (the left part of the graph) MySQL performance is dropping down since 300 concurrent users, while in the second case it's fixed and remaining stable as expected (well, in reality the issue came up since 1024 concurrent users, but I've forced the config params to bring it earlier and to simplify explanation ;-)

Once again, MySQL Performance Schema (PFS) is helping here to fire the hottest contention in this workload:

In the "broken" case the top contention is firing on the LOCK_table_cache mutex!..

Why?..

The exact answer is coming from the new status variables introduced since MySQL 5.6.6 as a part of solution fixing the old and so painful for many years LOCK_open mutex contention:

there are now table_open_cache related status counters reporting the levels of cache hits/misses and overflows (see: http://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html#statvar_Table_open_cache_hits). And as you can see from the graphs, in the "broken" case the hits were out-passed by overflows, which is clearly indicating that the table_open_cache variable was simply not set high enough!..

But which value is "high enough" ?.. - it depends :-) depends on your workload and your System/OS/HW capacities (and of course on the MySQL version you're using ;-)) -- currently to obtain the presented graphs I've set it to 100 - sure, pretty small, but it was still enough to keep the load correctly up to 250 concurrent users anyway ;-) While the current default value in MySQL 5.6 is 400, and in the real test case it was still good enough to keep up to 1024 users! - but was not enough for more.. However, personally, from a long date I'm setting "table_open_cache" to several thousands during my tests, and that's why I've never observed this issue before :-)

But well, since MySQL 5.6 you have now a good indicator from status variables (or I_S) to see if your setting is good enough or not. While some may still say me that it was always a good practice to set "table_open_cache" to something big enough, and I may only agree (as I'm always doing the same myself too).. - but let me now to present you another issue ;-))

Same workload scenario, Sysbench OLTP_RO, table_open_cache is set to 8000, but now I'm running the test on 32cores server:

Observations :

• on the left side the LOCK_table_cache mutex wait time is on the second position after MDL_lock::rwlock
• there is no more issues with cache hits, and table_open_cache=8000 setting is more than enough..
• in this case the LOCK_table_cache mutex becomes really much more hot due a much higher concurrency involved on 32cores (vs what is possible on 12cores)
• so, this contention can be fixed now by using a bigger table_open_cache_instances value (default is 1) -- setting it to 16 is completely removing the LOCK_table_cache mutex wait events (as you can see on the right side) and giving at once 10% performance improvement! ;-)
• the table_open_cache_instances setting was introduced since MySQL 5.6.6 (http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_open_cache_instances)

On the same time in the presented test case this mutex contention was not the top one, but only on the second position. So, of course, the gain may be even bigger when LOCK_table_cache mutex time arrives to the #1 in your workload ;-)

For example, over 15-20% gain on the following POINT-SELECT test workload:

Observations :

• LOCK_table_cache mutex contention is #1 here (left side)
• and completely disappearing (right side) once I've set table_open_cache_instances=16 and bringing 15-20% performance gain!

Looks to me like we should adjust little bit the default settings within MySQL 5.6 to get all these features out-if-the-box. While I want also to note that PFS instrumentation is working better and better now! - during presented workloads I've used mutex instrumentation only within PFS, which lowered my base performance level by near 10%.. - however, keeping in mind that the instrumentation code path of the most hot mutexes was executed here over several tens of millions times per second(!) - 10% regression is looking in this case pretty acceptable for the amount of information PFS is providing for my analysis ;-)

Well, would be great now to remove contention on the MDL_lock::rwlock.. - but it's an another story ;-)

Rgds,
-Dimitri