Tuesday, 09 May, 2017

MySQL Performance: 8.0-dev Progress Details

As promised, here is the follow up of the MySQL 8.0-dev Progress teaser ;-)

so, yes, we expect to see the most hot contentions gone, as it's seen from the following graph :

Observations :

  • the graph is representing the spin waits / spin rounds events happening during the same Sysbench Update-NoKEY workload
  • the load is progressing from 8 concurrent users to 16, 32, .. 512
  • 3 engines are tested one after one : MySQL 5.7, MySQL 8.0 (current DMR), MySQL 8.0-dev (current prototype)
  • first all 3 engines are running on 22cores-HT only (1 CPU socket)
  • then, the second time : on 44cores-HT (2 CPU sockets)
  • and the most important thing to retain about this graph is that on 8.0-dev we see all main hot contentions gone ;-)
And not having all these contentions is resulting in the following :

Observations :
  • one of the most painful things about current InnoDB WRITE performance is its scalability.. (in fact the absence of scalability ;-))
  • the problem is not new, just that since READ scalability was greatly improved since MySQL 5.7, seeing WRITE to remain not scaling become even more painful..
  • and this is because of all overhead and contentions we have on REDO level, transactions & lock management, etc..
  • so, MySQL 5.7 and 8.0 DMR are limited by this, and moving from 1 CPU socket to 2 CPU socket cannot help here (in fact the things may become only worse as with more CPU cores all these contentions will become only higher)..
  • while 8.0-dev code is giving us a huge expectation to finally see all these problems gone, and potentially get x2 times better performance even on a single CPU socket !! ;-))
  • NOTE : we're not running for "high numbers" here, there was a long and hard battle to see performance improvement since a low load as well, so a positive difference is already seen on 8 users, and even more seen since 16 ;-)

However, this is not the only benefit.. -- the story is going way more far, because all this deep remastering is potentially allowing us to get a rid of all the overhead we see when READ COMMITTED (RC) transaction isolation is used.. - example of such an overhead you can see from here :
- http://dimitrik.free.fr/blog/archives/2015/02/mysql-performance-impact-of-innodb-transaction-isolation-modes-in-mysql-57.html

and this was one of many reasons why REPEATABLE READ (RR) transaction isolation is historically used within InnoDB by default (and still continue to be the default in MySQL 5.7)..

While potentially with 8.0-dev the things could finally change ;-)

The following graphs are representing the results from Sysbench OLTP_RW workload comparing MySQL 5.7 and 8.0-dev :

Observations :
  • first the test is executed with RR isolation on both 5.7 and 8.0-dev
  • yes, 8.0-dev is doing better than 5.7, but the most important is the next ;-)
  • the next step both are executed with RC isolation
  • and then you can see TPS on 5.7 going lower..
  • while on 8.0-dev TPS level remains just the same on RC as on RR !! ;-)
So, crossing fingers, if all is going as expected, this could allow us for the first time to have RC isolation in InnoDB as default.. -- what does it mean for all MySQL users and their apps ?.. => huge overall experience changes + less headache + less locking conflict, etc. etc. etc. (and to not go very far, just to mention the yesterday's article from Alex : https://www.percona.com/blog/2017/05/08/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/)..

Well, work in progress, crossing fingers, stay tuned ;-))

the last remark: just to bring your attention once more that MySQL 8.0 is moved to have UTF8 charset by default !!! => so, if you're planning to run any tests with 8.0 DMR, please, mind to remember this !! - so use UTF8 on both sides (server and client) and consider the UTF8 overhead, or switch the server back to latin1 if your "client" apps are latin1.. -- otherwise you'll not be happy ;-))

more benchmark results and further observations about MySQL 8.0-dev you can find in my slides :
- MySQL-8.0-dev-Benchmarks-Scalability-Apr.2017-dim.pdf

Thank you for using MySQL ! and Go MySQL !! ;-))

Posted by Dimitri at 21:05 - Comments...
Categories: MySQL

Friday, 14 April, 2017

MySQL Performance: 8.0-dev Progress..

As you already know, a new MySQL-8.0 milestone release is available (and hope you did not miss all the news coming from MySQL Server Team site - starting by what's new article and followed by many others (and you'll see yet more to come ;-))..

There are also many good changes improving overall MySQL 8.0 Performance. However to see a real boost on OLTP workloads you'll need to have little bit more of patience.. -- we're attacking InnoDB fundamentals.. -- the parts of design which are probably remained mostly unchanged since InnoDB creation ;-)) -- you can easily understand that such a work has a long road from idea/ prototype to a final release.. On the same time our "Preview" results are looking very encouraging, and I'll be happy to say you more about during my talk @PerconaLive, 27/Apr 1:50pm :

- https://www.percona.com/live/17/sessions/mysql-80-performance-scalability-benchmarks

to give you a first idea about what is coming, I'll post here a singe "teaser" graph about a pure UPDATE performance on 22cores-HT (1CPU socket), then 44cores-HT server (2CPU sockets (kind of today's "commodity" HW ;-)) -- the graph is taken from the results comparing MySQL 5.7 vs current 8.0 vs 8.0-dev (which is yet in dev progress), but instead of TPS/QPS you can see here just levels about InnoDB internal contentions :

Your browser is not supporting SVG..

to help you little bit to find the diff within these graphs, in short :

  • yes, log_sys contention is gone in MySQL 8.0-dev ;-)
  • and log_write too..
  • and trx_sys..
  • and lock_sys..
  • and...

If it's saying you something, you could already get a first idea about what kind of TPS boost you may expect and what kind of possibilities it opens.. ;-) -- but if not, don't worry.. -- I'll tell you all this during my talk in 2 weeks ;-)

(yes, slides will be published, no worry; and yes, this article will be also updated with more details)

until then, stay tuned.. ;-))

And THANK YOU for using MySQL !!!

UPDATE: the follow up article is here - http://dimitrik.free.fr/blog/archives/2017/05/mysql-performance-80dev-progress-details.html


Posted by Dimitri at 2:10 - Comments...
Categories: MySQL

Tuesday, 17 January, 2017

dim_STAT v.9.0-u16-12 CoreUpdate is available !

I'm happy to announce the new CoreUpdate-16.12 for dim_STAT v9 is available !! ;-))

in reality there were many small changes/fixes made in the code over the past year, and I even started to include the "month number" to numerate the update versions -- however, it was still looking for me as "intermediate" steps, until reaching the "release" level by the end of the year ;-)) However, the "releasing" itself of this update took me much more time than expected, as I've started a progressive move to deliver a fully 64bit stuff on all levels..

NOTE: this is not about delivering a new dim_STAT version, but about a full 64bit re-packaging (with some binaries upgraded to a newer version, while some are just recompiled in 64bit, etc.)) the first candidates are Linux/x64 and MacOSX/x64 (or macOS) -- Solaris x64 and SPARC will come later..

So far, what is new coming with this CoreUpdate ?..

While there are many small changes (or code remastering), I'll like to bring your attention to the following long awaited and key "visual" features: Graph Bottom Label & Overlap Time Periods !.. ;-)

to show both of them in action, let me do it by example :

so far, I have a STATS collect in my dim_STAT instance, and I want to compare 2 test workloads together (in the current example OLTP_RW workload was tested on MySQL 5.7, and in the first test InnoDB was configured with trx_commit=1, and in the second trx_commit=2 (if you're not MySQL user, no need to go to details, just keep in mind there were 2 tests executed with different config parameters, and we're wanting to compare both of them ;-))

for each executed test I have a logged detailed message in dim_STAT, so it's easy to select the time period when each test was started, so I'm just selecting corresponded LOG Messages for each test and using "22min after the message" time interval, as you can see from the following screenshot:

then, as usually, I'm selecting corresponding Graph Style options and entering the Title for generated graph(s):

after what I'm just selecting the STATs I want to see:

  • MySQL Commit/sec
  • MySQL Sessions
  • CPU Usage%

which is giving me the following graphs:

and as you can see, the only way until now to put any details about what I want to show on the graphs was to put all of them to the Title (or use some external image editing tools to annotate some parts of graphs, etc.)..

things are changing since the new CoreUpdate ;-))

on the Graph Style form you can select now the "Bottom Label" to be placed on the graph, and enter explicit text to use or get the text "automagically" generated from the LOG Messages (in fact it'll be based on the difference present in the messages you're using, but lat's see it later)..

in the following example I'll just use a text "test1 | test2" :

and you can see it immediately added to the bottom of all my graphs:

now, let's see what will be the result if I'll select the auto generated text from the LOG Messages difference :

all the graphs are now having the "trx=1 | trx=2" text on the bottom (and, indeed, the only difference between the messages I've selected from the beginning of this demo is "trx=1" vs "trx=2):

however, "trx" is just my own abbreviation to name "trx_commit" setting used in my test, so for a more "human friendly" graphs I'd rather prefer to show "trx_commit" instead ofr just "trx" ;-))

and this also can be automated via "replace" option:

and we can see the expected result :

this was about the new "Bottom Label" feature! ;-))

now, going more far, from a long time I wanted to be able to "combine" several workload stats from different time periods into a single graph, but not one-after-one (as you see it above), but rather one-on-another (e.g. to see the above MySQL Commit/sec graph on the same "scale", for a better "visibility").. -- and, indeed, this is also possible now sine the new CoreUpdate ! ;-))

you can select now the "Overlap Time" option and assign corresponding Labels to time periods - directly from the list of words, or again, "automagically" from the LOG Messages differences..

in the following example I'll assign "test1" to the first tested workload (trx_commit=1) and test2 to the second one (trx_commit=2):

as you can see from the following graphs, such a data representation can be much more "spelling" for result analyze (for ex. it clearly seen now how faster 10K TPS level is reached with trx_commit=2 settings (following the Sessions level )):

NOTE: however, you may see that the graph is more "readable" if only one single stats metric is used, and not many (e.g. Commit/sec graph is more readable than CPU Usage% :-)) -- so, it's always preferable to select only one stat when generating "overlapping" graphs..

and as in Bottom Label, an "automated" labeling can be obtained also from the LOG Messages as well :

and the result:

so, it was about "Bottom Label" and "Overlap Time Intervals" features.. -- let me know if it was clear enough, and don't hesitate to share your own experience ;-))

and there are few more thing in the new CoreUpdate as well related to Snapshots:
  • you can now add new Snapshots to already "published" ones (e.g. keep all you need in a single document)
  • you can re-edit a "published" document and now also re-order listed Snapshots in the document by holding your mouse ;-))
  • any "published" document can be now also protected by password from accident editing by someone else ;-))
  • once you're done with document editing, you can click on [Finish] button now and prohibit any further editing..

well, this is all about the new CoreUpdate where I wanted to bring your attention ;-))

you can download and install the latest CoreUpdate right now :

the remastered 64bit versions for Linux and MacOSX can be found here:

NOTE: dim_STAT for Linux x64 now could be installed on most of latest Linux distros out-of-the-box (no any additional libs are required (well, crossing fingers ;-))

as well a short reminder about updated STAT-service tarballs for all the platforms:

New STAT-service rev.5.5 kit (Agent) Updated! (Dec.2016)
and of course, all the links to other stuff and previous releases is always available from the main site: http://dimitrik.free.fr ;-))

enjoy & have fun !..
and as usual, any feedback is welcome ! ;-))


Posted by Dimitri at 21:10 - Comments...
Categories: dim_STAT

Saturday, 01 October, 2016

My slides about MySQL Performance from Oracle OpenWorld Sep.2016 (#OOW2016)

Seems like this post did not appear on time, fixing now ;-)

  • MySQL Performance : Demystified Tuning & Best Practices [ PDF ]


Posted by Dimitri at 16:27 - Comments...

Tuesday, 03 May, 2016

My slides about MySQL Performance from #PerconaLive Apr.2016 US

As promised, here are my slides from Percona Live Conference in US, Apr.2016 :

  • MySQL 5.7 Performance & Scalability Benchmarks (PDF)
  • MySQL 5.7 Demystified Tuning (PDF)

Feel free to ask any questions or details you're needing, etc..

Also, not really related to MySQL, but as I was asked so many times about "how did you manage to project your slides from Mac, but drive it an annotate via iPad?" - here is a short HOWTO:
  • you need to have Keynote app installed on both your Mac and iPad
  • you create your own WiFi Network on your Mac (MenuBar->WiFi->Create Network...)
  • once done, connect to this WiFi Network your iPad
  • (having your own network is getting a rid of any potential sync issues, removing any dependency on wifi availability in a room, as well allowing you to walk way far from your Mac and still keep a control on your slides ;-))
  • then you're starting your Keynote presentation projection on your Mac
  • after what opening Keynote app on your iPad
    • "clicking" on Keynote Remote
    • selecting your Mac from the list of available devices
    • and you're getting hands on your currently projected slides ;-))
    • you can select then a preferred layout: current slide, current + next, current + notes, etc.
    • AND on any slide you can involve an annotation and draw over the slide with pencils of different color to point on one or another part of your slides
    • (of course, the drawing you're doing remains only during annotation and not destroying your slides ;-))
  • have fun! ;-))

What else to say? The conference was really great and I may only admit that Percona is doing it better and better from year to year.. Huge amount of very interesting talks, great technical content mostly everywhere, a lot of innovation, new ideas, deep discussions, etc. etc.. -- you don't know what you're missing if you were not there ;-))

Well, time for the rest now, and as a final point - a "Bloody Cheesecake" on my departure from SFO Airport
(for those who understand ;-))

Posted by Dimitri at 16:39 - Comments...
Categories: MySQL