« MySQL Performance: Overhead of Optimizer Tracing in MySQL 5.6 | Main | MySQL Performance: 5.6-labs is opening a new era.. »

Tuesday, 10 April, 2012

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 ;-))

And, finally, the mutex waits seen by InnoDB for all 4 tests are looking like following:

Quite surprising impact on "btr_search_latch" contention ;-))

However, there are many other contentions as well, and this post is just a short example about.. - I'll use it as a reference in following posts this week and in a near future when discussing about various "lock contentions impact" in MySQL ;-))


Posted by Dimitri at 12:37
Categories: MySQL
blog comments powered by Disqus
Note: if you don't see any "comment" dialog above, try to access this page with another web browser, or google for known issues on your browser and DISQUS..