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:
- 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
- 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?..
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 ;-))
blog comments powered by DisqusNote: 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..