Monday, 21 September, 2015
I may only admit that #PerconaLive Conference becomes only better and
better year to year. And I'm happy to be there and have an occasion to
tell you all the latest news about MySQL 5.7 Performance as it for
And of course, if we're speaking about benchmarks, I'll not speak only about MySQL 5.7, I'll not miss either Percona Server 5.6 and MariaDB 10.1 ;-) -- hope to publish all these results later, over a time.. I've realized that I'm blogging less this year -- most of the time is going today to dig the performance problems we're hitting and looking for real solutions for them. Hope to get both fixed soon ;-) While during the conference, please, don't hesitate to ask questions and report your problems -- many persons from #MySQLTeam will be there, so be sure you'll be listened! And if you will don't miss their talks as well - this way you'll don't miss them for sure ;-)
Well, time to leave a sunny Paris..
And join a sunny Amsterdam ;-)
But who cares about weather ? ;-)
Day #1 is ready to start..
My talk is on Wednesday, just before a lunch. So, please, come hungry (for questions and sharing I mean ;-))
UPDATE : the slides from my talk are now available from here :
Tuesday, 14 April, 2015
MySQL 5.7-RC1 is available now since the last week and you may find a lot about all the new improvement coming from our Team Blog and many other blog posts on this site as well. While in this article I'll just mention that with MySQL 5.7 all scalability limits are going more and more far! -- it's not that we resolved all of them, no, just that we become better and better ;-)
And one of the huge win coming now with MySQL 5.7-RC1 is that for the
first time ever we're getting the same performance levels on a single
hot table as on several tables used in parallel!
Here is the Max QPS result on the Sysbench RO Point-Select workload with 8-tables running on 40cores-HT server :
And here is the same load, but bombarding only one single table :
Now a full Sysbench OLTP_RO workload on 8-tables :
And on 1-table :
The story is pretty fun here, and I'll tell it in its full version (and many other "fun" stories) this afternoon (1:20pm) during my talk at Percona Live'15:
UPDATE: Slides are available from here: http://dimitrik.free.fr/Presentations/MySQL_Perf-Benchmarks-PLive_2015-dim.pdf
What to add.. - MySQL 5.7 really rocks!! kudos MySQL Team! ;-)
Tuesday, 17 February, 2015
There were so many valuable articles already written by others over past
years explaining all details about InnoDB transaction isolation modes
and how to deal with this. So, I'll avoid to repeat what was already
said ;-) -- my attention attracted the performance study made by PeterZ
and published in the following article: http://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/
-- the article is very good and providing a good analyze of the observed
problem which is solved by using READ-COMMITTED transaction isolation
instead of REPEATABLE-READ (which is default in InnoDB).. The natural
question is coming then: why don't we have then the READ-COMMITTED mode
by default?.. Is there any danger?..
Let's then investigate together..
First of all, you should keep in your mind not only the theory, but also a way in which all this stuff is implemented within InnoDB :
- transaction isolation / MVCC in InnoDB is implemented via ReadViews
- every time a ReadView is created, a mutex (trx_sys) should be acquired
- in REPEATABLE-READ mode a ReadView is created on transaction start
- in READ-COMMITTED mode a ReadView is created on every statement(!)
- means, if your statements are short -- you may hit a storm on trx_sys mutex contention in your workload..
Of course, all depends on a workload, and if you're lucky, you will probably see only a benefit here (all is possible, right? - at least in theory)..
Let me show you now some cases where you'll feel yourself much less lucky ;-)
For my test cases I'll use :
- 40cores-HT server
- running OEL 6.5 2.6.32-504 kernel
- extremely fast flash storage (Fusion-io ioMemory)
- each workload is using 64 concurrent users
3 test cases executed on each workload :
- REPEATABLE-READ mode is configured (RR)
- READ-COMMITTED mode is configured (RC)
- READ-UNCOMMITTED mode is configured (RU)
DBT-2 500W Workload (TPC-C) :
- you may already observe here a slightly lower TPS on both 2nd (RC) and 3rd (RU) test cases comparing to the first one (RR)
- the "regression" is not very big, but notable
- let's get a look now on internal lock contentions within InnoDB..
- a jumping contention on trx_sys mutex is very well seen for RC and RU tests
- however it's not yet too big to make a serious damage..
Now, let's move to a heavy Sysbench OLTP_RW -- I've slightly changed the "classic" OLTP_RW here by adding a Read/Write ratio in my tests :
- initially the load is starting with 128:1 ratio (mostly Reads, less Writes)
- then 16:1
- then 4:1
- then 2:1
- and finally 1:1
I'm using this test case also to evaluate the impact of writes in transactions, etc..
Sysbench OLTP_RW 32x10M-tables, rw128/16/4/2/1 :
- here the impact is more then just notable ;-)
- and this is only due trx_sys mutex contention? or something else?..
- oh, indeed, trx_sys is jumping too high now!..
- and could it be even more worse??
let's see ;-)
The next workload has a code name "OLTP_RW-p_sel10" - all reads in this test are replaced by 10 point-selects, that's all, making the load much more aggressive on writes and short and fast on reads :
Sysbench OLTP_RW-p_sel10 32x10M-tables, rw128/16/4/2/1 :
- indeed, seeing a x2 time worse performance is really killing..
- and still due trx_sys mutex??
no comments ;-))
Well, you may still say that it's just because this server is too big and that's why I'm observing all these contentions, and you'll be not far from the reality -- on smaller machines all these contentions are, of course, lower - but! still "notable" ;-))
The same OLTP_RW-p_sel10, but on 20cores-HT :
(while many x2 CPU Intel machines today are having more than 32cores-HT in total, so a "small" HW becomes a big one ;-))
INSTEAD OF SUMMARY :
- so, what should we finally conclude from all this presented stuff???..
- PeterZ told us a so nice story, and now you're coming with your b*** and showing that PeterZ was wrong...
- Stop, guys, PeterZ was not wrong!!! ;-)
- ?? -- so, you're lying ??????
- and I'm not lying either ;-))
- well, what you should keep in mind is that there is no a "silver bullet" and the most universal answer in most of the cases will be "it depends" ;-))
- and with InnoDB Transaction Isolation is the same story here!
THE GENERAL RULE could be the following :
- if your queries and transactions are short : use rather the default REPEATABLE-READ mode!
- if your queries are long and reading a lot of data which are likely to be modified by other transactions in parallel : then use the READ-COMMITTED mode - this will allow you to read already committed changes while your query is progressing and avoid to be lost in scanning of the old page images instead (as PeterZ well showed you in his story ;-))
- Repeatable Read Isolation Level in InnoDB - How Consistent Read View Works
- Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
- The basics of the InnoDB undo logging and history system
- If Eventual Consistency Seems Hard, Wait Till You Try MVCC
- SET TRANSACTION Syntax
As usual, any comments are welcome!
Monday, 17 November, 2014
It was very fun for me to read last week the announces about RDS Aurora - specially the parts related to its performance gain over MySQL: Aurora is claimed to show x5 times better performance than MySQL! However, without publishing any details about ;-) -- and the only details I was able to find until now and group together are the following:
- Aurora is a proprietary closed source database engine, "compatible" with MySQL (so, not an improved MySQL fork, as many expected..)
- Sysbench workloads were used to evaluate Aurora performance
currently published Sysbench results are the following:
- 500K SELECT/sec
- 100K UPDATE/sec
- again, no details about any of the tests..
- in some articles instead of UPDATE/sec performance was mentioned INSERT/sec, but keeping in mind that there is simply no pure INSERT test available by default within Sysbench workloads, I may only suppose the mentioned 100K writes/sec are corresponding to the UPDATE/sec performance..
Fine then. But, so what?..
Let's see now where we are with MySQL Performance for today?.. ;-))
Yet more than a year ago we already announced 500K SELECT/sec with MySQL 5.7 on Sysbench (and you may read a full story about, if you got it missed). And this year during MySQL Central @OpenWorld we already presented 645K SELECT/sec with the latest MySQL 5.7! - here are the graphs taken from a live load just this morning :
- the first graphs is showing the SELECT/sec level
- the second graph - the corresponding amount of concurrent users (sessions) starting with 8, then 16, 32, 64, 128, 256 users
Sysbench SELECT Performance @MySQL 5.7 :
This is a single MySQL instance running on a single HW server. And as you see, we're reaching 645K SELECT/sec since 128 concurrent users. Is the 645K QPS level is the max limit for this server?.. - of course not, it should be possible to do yet more better here by improving our SQL layer, because on the same HW we're able to reach 1M QPS via InnoDB Memcached plugin (where all the data are going the same way directly from InnoDB, but bypassing all the SQL layers).. Work in progress ;-))
Then, what about UPDATE/sec performance?..
Again, a single HW server, single MySQL 5.7 instance, few simple SSD disks (even not a super fast flash storage as we have from Fusion-io and LSI in our LAB) :
- the first graphs is showing the UPDATE/sec level
- the second graph - the corresponding amount of concurrent users (sessions) starting with 8, then 16, 32, 64, 128, 256 users
Sysbench UPDATE Performance @MySQL 5.7 :
As you can see, we're doing even slightly more than 100K UPDATE/sec here ;-)
And you may ask then: Why MySQL Team @Oracle did not publish such a great result on UPDATE performance?.. - Well, just because there is nothing to be proud of for the moment.. ;-)) Of course, if you'll run the same workload on HDD storage you'll get much more worse results (for ex. on this server with HDD I have only 2K UPDATE/sec).. - so, for sure, SSD is much more efficient for random I/O writes. However, we're not doing better when using a much more faster flash storage than SSD, and this is our main headache today for all IO-bound workloads ;-) -- the limit today is still in the MySQL/InnoDB code itself.. - and we know where, and we're working hard to get it fixed.. And once this stuff will be improved, then we'll have something to be proud of, and then you'll hear about us for sure ;-))
Now, looking back to RDS Aurora results on Sysbench.. - so, what?.. MySQL 5.7 is already doing better! ;-)
Well, still looking for claimed x5 times performance difference..
(NOTE: if you have any troubles to reach over 500K SELECT/sec or 100K UPDATE/sec on MySQL 5.7, I have a webinar right this Wednesday to tell you all about MySQL/InnoDB internals and related tuning, don't hesitate to join: http://www.mysql.com/news-and-events/web-seminars/tuning-and-best-practices-for-developers-and-dbas/)
Wednesday, 29 October, 2014
Percona Live London 2014 is starting next week and I'm happy to speak there about MySQL 5.7 Performance, Scalability & Benchmarks latest news. Indeed, MySQL 5.7 has a lot of positive changes and impressive improvements. And, looking on MySQL 5.7 Performance, there are many cases where the gap comparing to MySQL 5.6 today is simply amazing! ;-) -- but, of course, there are still some pending issues which are requiring yet more work to reach the expected speed-up.. So, I'll tell you all about the most hot stories around, while I'm also always curious about your issues you're hitting on your production systems. Please, don't hesitate to share them - this is helping us to make MySQL yet more better! ;-)
UPDATE - my slides are here : http://dimitrik.free.fr/Presentations/MySQL_Perf-Benchmarks-PLUK_2014-dim-key.pdf
Wednesday, 08 October, 2014
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:
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
to be continued..
Tuesday, 07 October, 2014
And I'm not saying this because I'm MySQL/Oracle employee ;-)) - just
expressing my personal feeling while flying back to Paris..
I really enjoyed this conference now much more than all previous years :
first of all having 5 days instead of 2 allows to have much more
networking than before (and usually it's the main goal for every
conference to create a lot of informal discussions and exchange on
then, the content - all excellent, no words.. there was so much new
stuff to discover and learn from other experiences, that I'm pretty
sure that each person at one moment during the conference should feel
a kind of "brain overflow" ;-) and what is good that there were talks
for any levels of skills -- so, it was very easy to progress on
understanding of each topic quickly and smoothly..
organization was just perfect : all in one place, no more running over
different floors/buildings, etc. ;-) having 30min breaks between talks
is really good as it allows to have an exchange related to presented
topics without impacting timing of the next talk / session (while we
could probably finish little bit later as well to have yet more
MySQL Customer Advisory Forum (CAF) - yet bigger and very well
organized as usually (while I'm not sure if all customers / users are
aware about this great possibility to discuss directly with MySQL
engineering, express all your wishes & issues, get the info from the
source about what is new, what is coming, what is planned, etc.) --
well, I know that many regretted on the last minute to have missed
subscription, so keep in mind this for the next year ;-))
- MySQL Reception - really good! specially that doors were open to anyone, including persons who are not attending the conference -- a true MySQL Community event!
Few wishes anyway for the next year :
would be great to have MySQL posters around the place where conference
is going ;-)
- would be great to have a kind of "closing conference" keynote(s), etc. to make a summary, to thank all attendees & organizers, to have some fun instead just see doors closing and people leaving..
But well, all-in-all - simply great MySQL Conference, which may become only better over a time! so, book your agenda ahead for the next year -- pretty sure it'll be yet more fun ;-)
As promised, here are my presentation slides from my talks on MySQL Central @OpenWord 2014 :
Feel free to ping me if you have any questions, while I'll also try to
cover some of explained topics within the next blog posts, so stay tuned
Monday, 29 September, 2014
Indeed, MySQL 5.7 rocks ;-)
This is the part1 of the following blog posts about various benchmark results on MySQL 5.7 - and this particular one is dedicated to the Sysbench OLTP_RO Point-Select 8-tables workload.
We've already published the over 500K QPS on SQL Point-Selects before @32cores-HT server, so may reconfirm it again with MySQL 5.7 DMR5 -vs- other engines :
and now on a similar server, but with 40cores-HT we're able to confirm 645 QPS on the same workload!
If you missed the long story about how we arrived on such a performance level and how to reproduce the test - you may find all here. This workload is the most killing from all Read-Only Sysbench OLTP tests.. And it's really for the first time we started to scale here with MySQL! - look here (all results are from the 40cores-HT server) :
MySQL 5.5 :
MySQL 5.6 :
MySQL 5.7 :
Percona 5.6 :
MariaDB 10.1 :
As you can see, only MySQL 5.7 rocks here ;-)
However, if you remember the full story, this workload is extremely depending on every extra bits and even insignificant overhead.. - personally it's very rare that I saw a RDBMS workload to be so close to HPC problematic ;-)) -- to remind you that to reach 500K QPS (and 645K QPS here) was necessary to use UNIX socket instead of IP socket and an older Sysbench version binary (v0.4.8) which has just little bit less instructions than the v0.4.13 I'm currently use and will be able to get only 575K QPS on this server..
will tell you yet more during my talk on Wednesday, 1/Oct.
to be continued..
Sunday, 28 September, 2014
Oracle OpenWorld 2014 is starting today, and MySQL Central Conference since this year is taking a full part of it. I'm very happy to be there and have 2 talks :
- MySQL 5.7 : Performance and Scalability Benchmarks | Wed, 1/Oct. | 2:00 PM - 2:45 PM | Moscone South - 252 | CON5066
- MySQL Performance : Demystified Tuning and Best Practices | Thu, 2/Oct. | 10:45 AM - 11:30 AM | Moscone South - 252 | CON5097
so I'll be able to say more this time and mostly focus on the Benchmarks results & analyzes on the first talk, and then mainly focus on the tuning and the latest MySQL 5.7 design improvements to allow you to reach yet more higher & stable performance levels.
I have several "fun" stories to tell you around the latest findings around MySQL 5.7 Performance we made, and problems we've solved (or newly found ;-)) Don't know if I'll have a time to blog about all of this one day.. - while telling you these stories live during the conference is much more easier, so hope to keep you awake and have fun ;-) Indeed, there is no doubt, MySQL 5.7 rocks and it will be the next the best MySQL release ever.. - and I'll tell you why ;-)
Then, once again, I'm regretting I cannot clone myself to attend all the conference talks in parallel.. - there are so many valuable presentations in the program that it's really hard to decide where to go.. I may only advice you to attend all of them (you and your clones ;-)) -- this excellent Agenda Summary is telling all..
And don't miss the MySQL Community Reception @OOW 30/Sep. 7PM-9PM (you need just to register yourself via this link to help organizers with logistics ;-))
see you there!