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 :


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 !!!

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: ;-))

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

Tuesday, 16 February, 2016

MySQL Performance: PFS and Checksums impact on OLTP_RW Benchmark with MySQL 5.7

This article is the follow-up on discussion started around MySQL 5.7 results on OLTP_RW Benchmark.. -- the point was about the impact of PERFORMANCE_SCHEMA (PFS) enabled, and InnoDB checksums on MySQL 5.7 performance within this OLTP_RW workload.

As promised, here are the results:

Just in case if the legend naming in graphs is not obvious :

  • PS-off : "performance_schema=off" was used
  • PS-def : "performance_schema=on" was used (default PFS instrumentation)
  • chksum0 : "innodb_checksums=0" was used
  • chksum1 : "innodb_checksums=0, innodb_checksum_algorithm=crc32" was used

OLTP_RW 1M x8-tables MySQL 5.7 (config: trx_commit=2 double_write=0) :

OLTP_RW 1M x8-tables MySQL 5.7 (config: trx_commit=1 double_write=0) :

OLTP_RW 1M x8-tables MySQL 5.7 (config: trx_commit=1 double_write=1) :

  • the impact of checksums is near zero when OLTP_RW workload is running on MySQL 5.7 with a less safe config (trx_commit=2)
  • the checksum impact is bigger on trx_commit=1 (but not growing more since double_write is also enabled)
  • something similar goes with PFS impact as well..
  • however this gives me an expectation that once we'll fix the slowdown issues due trx_commit=1, we'll lower as well PFS impact here too (like it was already happened in the past)..
  • so far, the checksum impact here is around 0-2%, while PFS impact is around 3%-7% (mostly bigger on higher concurrency load)
  • interesting that "combined" impact of both (checksums + PFS=on) is not really much different from just PFS=on
  • NOTE : if you're really worry a lot about CPU cycles used by PFS instrumentation -- since MySQL 5.7 you can compile MySQL binary yourself with various PFS compile flags to completely disable some kind of instrumentations you're sure you'll never use (for ex. mutexes/rw-locks, etc.) -- I'm not doing so, but you can ;-))
  • NOTE : from the past experience I've also observed that compiling MySQL binaries without "-fno-omit-frame-pointer" option may give you additional 5%-10% speed-up !! (however, you'll not be able anymore to get a proper stack trace from a running process or in case of crash..) -- I'm not doing so, but you can ;-))
  • but well, what is important here is that with or without PFS/checksums enabled MySQL 5.7 is still reaching over 40K TPS performance level on this workload, and it's still way bigger than I've ever observed on MySQL until now ;-))
  • and our challenge here is to bring trx_commit=1 to the same level as trx_commit=2, and largely reduce the impact of double_write.. (but this is another story, stay tuned ;-))

As usual, any comments are welcome! And Thank You for using MySQL! ;-))


Posted by Dimitri at 19:22 - Comments...
Categories: MySQL