DimitriK's (dim) Weblog

MySQL Performance: Game Of Contentions..

Recently running yet one of series of benchmark on MySQL 5.6, I've met the following case:

• the workload is Read+Write, 256 concurrent user sessions running non-stop..
• Performance Schema (PFS) is enabled and Synch events are monitored (both counted and timed)..
• accidentally I've not fully disabled the query cache (QC) in MySQL config (query_cache_type=0 was missed)
• and at once PFS reported it to be the top wait event during this workload:

Test #1 -- @5.6 with PFS=on, default query_cache_type:
NOTE: we're reaching in this configuration ~100K QPS..

So, of course, I've expected to see much more higher QPS once I've set query_cache_type=0, don' you?.. ;-)
The second graphs are representing the result obtained with these changes:


Test #2 -- @5.6 with PFS=on, query_cache_type=0:


So far:

• 80K-90K QPS instead of 100K QPS observed initially..
• wait time on the "btr_search_latch" lock contention is now replacing the same wait event time as the QC before..

Hm.. - my tuning "improvement" is resulting in a worse performance finally..
But keeping in mind that "btr_search_latch" is a RW-lock (and PFS overhead become higher on hot RW-locks), it's possible to suspect here PFS overhead..

Let's replay with PFS disabled now:

Test #3 -- @5.6 with PFS=off, query_cache_type=0:

Oh! it's much better! - we're reaching 100K QPS again! (well, near reaching 100K, to be honest.. - 98K QPS probably, but well, it seems to be better anyway ;-))

And what if now I'll comment again the "query_cache_type" setting and leave it by default?..

Test #4 -- @5.5 with PFS=off, default query_cache_type:

Hm... - no difference!.. - so, there is no impact at all with QC query_cache_type leaved by default???.. :-))

Let's summarize now:

• 100K QPS with PFS=on, query_cache_type=default
• 90K QPS with PSS=on, query_cache_type=0
• 98K QPS with PFS=off, query_cache_type= default or 0

So far:

• from the last 2 tests: seems that "query_cache_type" setting is not having any performance impact..
• from the first 2 tests: "query_cache_type" has performance impact ;-)
• from the tests #2 and #3: PFS has performance impact when enabled..
• and we still have the best performance result here when PFS is enabled + "query_cache_type" is not turned OFF ;-))
• NOTE: the test workload is exactly the same in all cases..
• NOTE: it's exactly the same MySQL binary used in all cases too, so if PFS has an overhead, it'll be the same one whenever PFS is turned ON.. ;-))

What is wrong here?..

Nothing.. :-)

The problem is that since some time we're entering with MySQL code in a new game of contentions which is very well seen in a presented example:

• PFS overhead is higher whenever RW-lock contention is higher..
• enabling "query_cache_type" is adding an additional overhead (as seen from the Test #1)..
• but this additional overhead by its action is lowering the contention on "btr_search_latch" RW-lock ;-))
• lowering RW-lock contention is reducing on its turn PFS overhead..
• and finally resulting in the better performance, even it sounds surprisingly ;-))