« MySQL Central @OpenWorld 2014 was simply great! | Main | Speaking about MySQL 5.7 Performance @Percona Live London 2014 »

Wednesday, 08 October, 2014

Indeed, MySQL 5.7 rocks : OLTP_RO/RW 1-table Benchmarks

This is the next part of the stories about MySQL 5.7 Performance..

So far, the previous story was about reaching 645K QPS with SQL queries, while in reality it's only a half of the full story ;-) -- because when last year we've reached 500K QPS due a huge improvement on the TRX-list code, the same improvement made a negative impact on the all single-table test workloads..

What happened finally :

  • the new code changes dramatically lowered contention on TRX-list (trx_sys mutex)
  • which is made MDL related locking much more hot..
  • and if one table becomes hot on a workload, MDL lock contention then is hitting its highest level..

So far, it was clear that MDL is needed a fix. Specially seeing that on 8-tables workload we're reaching 645K QPS. However there was a dilemma: should we push the TRX-list change to the 5.7 code, or wait first for MDL improvement?.. -- and we finally decided to push the changes, even if for some period of time we'll need to accept a regression on all single-table workload..

So, what we got the last year :
  • 500K QPS on 8-tables point-select
  • less than 200K QPS on 1-table.. - which was even worse than in MySQL 5.6 ;-)

It was clear, we have to fix MDL code asap, but the MDL story was not that simple either :
  • Dmitry Lenev made a quick dirty patch just removing completely all MDL related code from the source tree..
  • so, of course, we all expected to see a huge QPS jump after that on this experimental code, right? ;-)
  • however, for our big surprise, QPS just remained on exactly the same level...
  • what is going odd?..
  • in fact instead of the MDL contention we moved to the THR_lock mutex contention!
  • what is interesting that until the MDL code is present, we don't see any sign of THR_lock ;-)
  • and it's only since MDL is no more here -- THR_lock is firing!..
  • finally Dmitry made another dirty patch removing all THR_lock related code too..
  • and bingo! - without MDL & THR_lock code we doubled QPS at once, and it became clear that both contentions should be fixed to bring a speed-up on single-table workloads...

All my kudos here to Dmitry Lenev, who worked very hard to find the most optimal solution to fix both problems. There were several prototypes, less or more successful, until the final solution out-passed all our expectations -- just because it provided us the same QPS level as the binary which has no MDL nor THR_lock code at all!!! - and this is awesome! ;-))

I was happy to follow all this work very closely and play with each new update. Let me show you now just a short "making off" which is representing pretty well a summary of the one step in the progress in this work:


Observations :
  • there are 4 tests running the same OLTP_RO point-select workload on 8/16/32..1024 users :
    • #1 is using the original 5.7 code
    • #2 is using #1 + MDL fix
    • #3 is using #2 + removed THR_lock code
    • #4 is using #1 + removed MDL code + removed THR_lock code

  • as you can see, the original code (#1) is suffering of MDL rwlock contention and not out-passing 200K QPS..
  • the MDL fix in #2 is helping little bit to reach more than 200K QPS, but then the THR_lock mutex contention is killing an overall performance..
  • by removing completely the THR_lock code (#3) we can see the potential level of performance we should have if THR_lock contention was also fixed
  • then in #4 we can see the QPS level on 5.7 if there was no MDL & THR_lock code at all..
  • and what remarkable here that #3 and #4 are showing exactly the same performance!
  • so, the MDL fix worked extremely well to run as efficiently as if MDL code was removed ;-)
  • the next step was to do the same with THR_lock to get the solution simply perfect! ;-)
  • and it's exactly what was done finally in 5.7 and can be seen now in DMR5..

May only say kudos MySQL Runtime Team! kudos Dmitry Lenev!!! ;-))

Let me show you now the final impact of all these changes (and keep in mind that just a year ago the MySQL 5.7 results here were worse on all the following tests comparing to MySQL 5.6)..

Sysbench OLTP_RO 1-table 10M :


Sysbench OLTP_RO Point-Selects 1-table 10M :


Sysbench OLTP_RO Simple-Ranges 1-table 10M :


Sysbench OLTP_RW 1-table 10M trx2 :


Sysbench OLTP_RW 1-table 10M trx1 :

As you can see, the same performance improvement we can see on OLTP_RW workloads as well, where :
  • trx2 : means innodb_flush_log_at_trx_commit=2
  • trx1 : means innodb_flush_log_at_trx_commit=1
More about RW workloads in the next articles (and about "MySQL-5.7-futexV3" too ;-))

to be continued..
Posted by Dimitri at 11:22
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..