« MySQL Performance: Game Of Contentions.. | Main | MySQL Performance: Improved Adaptive Flushing in 5.6-labs »

Tuesday, 10 April, 2012

MySQL Performance: 5.6-labs is opening a new era..

I think every performance engineer in his life is time to time meeting some curious performance problems within an application (not only MySQL) which is looking out of understanding and over a time only continuing to increase a headache.. (until not resolved, of course.. ;-))

I've met many of them in the past while working in the Sun Benchmark Center, but did not expect to meet it so soon once started my official work in MySQL team ;-))

The problem is looked as simple as nothing else, and that's why it was several time more painful than anything else too :-))

Let me explain: since of the beginning of this year I've got for MySQL performance testing a 32cores (bi-thread) Intel server running Oracle Linux. And ss soon I've got it, I've deployed my tools on it and started my tests..

The main surprise for me came on the Read-Only test:
  • what can me more simply than Read-Only workload?..
  • specially when the data work set is small enough to fit the InnoDB Buffer Pool and remain fully cached?..
  • the test case is a simple Sysbench OLTP_RO workload, so nothing surprising here, many times tested by others..
  • running the test on both MySQL 5.5 and 5.6, to be sure nothing wrong came with 5.6 code..
But let's get a look on the difference in results obtained with 1, 16 and 32 concurrent users:
  • well, on a single user thread performance numbers may be quite random and vary from test to test..
  • but what is blocking any performance increase when we're moving from 16 concurrent users to 32?..
  • there are 32cores on the server and from the HW power point there is no limit yet..
  • from the SW point: other applications are scaling well on this server, then regarding InnoDB - if on 5.5 it's at least reporting the increased contention on the "kernel_mutex", then on 5.6 it's reporting just nothing!..
  • same "problem" with PFS (Performance Schema) - no any increased contention is reported..

We're simply lacking and instrumentation somewhere to find the source of the problem..

So, then I've started a long weeks of testing + in depth code profiling to understand what's going on.. Of course, I did not spend all of my time on resolving only this problem :-)) But you may understand as well than any other issues became less critical in my mind, because when you're hitting such a huge scalability limit without any contentions reported by the code.. - do you worry then about response time in your tests or any other benchmarks?.. - it's just looking like a huge bug or invisible contention.. - you're turning around it.. able to see the impact.. but still cannot see why it happens :-))

Well, to make it shorter - at one point of time a quantity of test results and analyzed cases should move to a quality of the final improvement in the code ;-)) so, when there was a time for our planned Performance Meeting in Paris, there was enough of data to discuss from my side, and this scalability limit was one of the main points in agenda..

Our brainstorming meeting was a true success! And I'd say it's a real pleasure when you're living such kind of events in your life.. - summarized all our skills together, we profiled the code dipper and dipper, arriving finally to assembler level to discover what is going on ;-)) There was such a strong complicity in this teamwork that we quickly understood that we simply MUST fix this issue ;-)) And finally we got it fixed!.. - it was even hard to believe the solution was so close.. Well, generally you know that it may happen in "some cases", but that it happens with MySQL code which is RDBMS and not at all HPC related?? (HPC - High Performance Computing)..

The scalability issue came from the effect that we got some code zones (data structures) in MySQL/InnoDB which became too hot on CPU cacheline!.. I'll not repeat the same details explained by Mikael in his blog post, but rather just will present you few most spectacular graphs comparing performance before and after G5 (the code name we used to call this improvement feature):


- over 50% performance improvement!.. ;-))

An even more spectacular on Sysbench RO SELECT RANGES test:


- up to x6 times(!) faster ;-))
And it's for a long time now I did not see something so spectacular in MySQL Performance ;-))

All these improvement and many other are available right now within MySQL 5.6-labs release!
Don't wait to try it!

what else to say?.. - I'm just proud to be part of it :-)) and enjoying my MySQL "full time" more than ever.. ;-))

Stay tuned, more is coming ;-))
We're living a great time..

Rgds,
-Dimitri

P.S. BTW, did I forget to say you we're hiring @MySQL ?.. ;-))

Posted by Dimitri at 17:39
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..