DimitriK's (dim) Weblog

MySQL Performance: Analyzing Benchmarks, part-1 : MySQL 5.6 and MariaDB 5.5

Inspired by long debates about differences in my benchmark results on MySQL 5.6, MySQL 5.5 and MariaDB 5.5 and MariaDB benchmark results, I've decided to start a series of articles helping people to reproduce various test cases and better understand some of fun stuff around.. ;-)

This article is fully dedicated to the MySQL 5.6/5.5 vs MariaDB tests.

So far, generally, when you're trying to understand a source of gap in performance results, you should try to start from the most simple case for both "solutions", "platforms", and so on.. For databases the most "simple" case is Read-Only (RO) workload, specially the OLTP RO workload which is fully cached by RDBMS buffers and no more depending on your storage performance, redo/undo design/options, and so on..

So, MariaDB is publishing the result of Sysbench-0.5 OLTP_RO test with 8 tables, running on a 16cores server and showing on MySQL 5.6 performance lower than 4500 TPS . And the most "closest" test I have in my list to match the same test conditions is OLTP_RO-trx 8-tables (also with 8 tables), but using Sysbench-0.4.13 version, and on my server on 16cores it showing more then 7500 TPS. Of course, I was surprised.. And even more surprised that MySQL 5.5 is performing better than MySQL 5.6..

However, here I should make an important remark :

• there is no miracle ;-) the code path in MySQL 5.6 is more longer then in MySQL 5.5 (due all new features added)
• so, we may expect some additional overhead here, and 5.6 may run slightly slower in workloads with a low level of contentions (such a single user session, or small/old servers (8cores and less)..
• but such an overhead should not be bigger than 3%, and all our QA tests on 8cores servers confirmed it..
• now, if we meet today some cases showing a bigger overhead - just log a bug and we'll analyze and fix it ;-)
• however, on bigger servers, with 16cores and more, you'll see the gap between MySQL 5.6 and MySQL 5.5 more an more bigger!
• our goal in MySQL 5.6 was to scale more far than 5.5.. - keeping in mind that 24cores (and bigger) servers become a today's commodity, it was really pity if you have a "big" server and unable to use it on its full power.. (and many users reported it)..

Well, let's come back to MariaDB test.. - we're not using the same HW, we're not using the same Sysbench versions, and so on.. And, discussions about these differences may be endless.. So, to understand exactly what is going on, I've decided to replay exactly the same OLTP RO scripts provided by MariaDB on my own server.

Remark regarding PFS :

• if you're comparing MySQL 5.6 and MySQL 5.5 keep in mind Performance Schema (PFS) overhead..
• in MySQL 5.5 this overhead is very big, and by default PFS in 5.5 is turned off..
• in MySQL 5.6 PFS overhead is much lower, and that's why by default PFS is turned on in 5.6 with a minimal instrumentation enabled..
• however, this overhead is still present! ;-) (normally should be around 5%, but depends on a workload)
• so, for a honest comparison PFS should be disabled on both engines..

So, keeping this in mind, I've started the same OLTP_RO test via MariaDB's script, same test conditions (12cores attributed to MySQL server, 4cores to sysbench), but still keeping my own my.conf setting as before (and PFS=off). Here is the result:

  Users       MySQL 5.6      MySQL 5.5     MariaDB 5.5
---------------------------------------------------------
    8 :          3460           3680          3430
   16 :          5270           5479          4860
   32 :          5990           6120          5250 
   64 :          6000           6150          5260
  128 :          5900           6030          5200
  256 :          5800           5900          5140
  512 :          5500           5640          4940
---------------------------------------------------------

So :

• yes, there is a small regression here in MySQL 5.6 results comparing to 5.5..
• but regression is not out-passing 3%..
• and, curiously, TPS is still better than under 4500 TPS level published by MariaDB..

Now : if you're really looking to get the Max performance from your 16cores server, you should not limit MySQL server to only 12cores (except if your goal is to see the max TPS on 12cores ;-)) -- the best performance will be usually seen when all 16cores are fully available to both: "server" and "client"! (as they're working in ping-pong: query/answer each one on its turn, and so on)..

So, here is the result when both MySQL server and sysbench are using the same 16cores :

  Users       MySQL 5.6      MySQL 5.5     MariaDB 5.5
---------------------------------------------------------
    8 :          3460           3620           3330
   16 :          6740           6730           5350
   32 :          6680           6650           5310 
   64 :          6670           6640           5350
  128 :          6570           6550           5330
  256 :          6510           6510           5300
  512 :          6460           6450           5280
---------------------------------------------------------

Ok, looks better:

• now you can see that MySQL 5.6 is running most of the time same or better than MySQL 5.5 ;-)
• and if you'll look attentively on my initial graphs with benchmark results, you'll see exactly the same (pretty similar performance on 8 and 16cores, then growing TPS gap since 16cores)..

What about 24cores now within the same test case?..

Here is the result:

  Users       MySQL 5.6      MySQL 5.5     MariaDB 5.5
---------------------------------------------------------
    8 :          3470           3600           3210 
   16 :          6790           6040           3940 
   32 :          9980           5320           3640 
   64 :          9880           5370           3640
  128 :          9830           5390           3650
  256 :          9690           5480           3680
  512 :          9610           5390           3670
---------------------------------------------------------

As you can see :

• near 10,000 TPS and over 45% improvement comparing to 16cores result (6740 TPS Max)..
• while MySQL 5.5 is unable to follow...

NOTE:

• all these results are obtained on the same OLTP RO test case provided by MariaDB..
• but using my own MySQL configuration settings..

Now, what will be changed if I'll use the my.conf file used by MariaDB in the testing?..

Bingo!...

• as soon as I've started to use "original" my.conf from MariaDB my performance was dropped to 4000 TPS only on 16cores..
• which is matching perfectly the numbers reported by MariaDB..
• so, for sure there is something wrong in the my.conf file.. but what?..
• ... after some tweaking and testing.. ;-)) ...
• I've found that the problem is coming from the sort_buffer_size value !!!
• I've used 32K for both sort_buffer_size and join_buffer_size
• while MariaDB used 4M for both..
• don't think it was intentional, but seems to me the main error was here..
• once leaving it by default (256K) or setting to mine (32K), I've quickly reached the same near 7000 TPS level as I've observed before!

The problem with "sort_buffer_size" is not new, and if you want to read more about, I'm inviting you to start from the "More on understanding sort_buffer_size" article by Ronald Bradford. This article also containing a lot of links to other posts (and I remember there was also an article testing impact of different sort_buffer_size values on OLTP workload, but I did not find it.. - but from a while I'm using 32K values for both "sort_buffer_size" and "join_buffer_size" on OLTP tests)..

INSTEAD OF SUMMARY :

• don't forget to turn Performance Schema off in MySQL 5.6 if you're comparing it with previous MySQL versions (otherwise it'll not be apples-to-apples comparison)..
• if you're looking for a full power TPS results on your HW server - share the same CPU cores for both "client" and "server" (or use another server for "client", but be sure your "client" activity (here sysbench) is not blocked by any HW or SW limits other than MySQL server itself)..
• check your MySQL Server configuration settings (mine is here, but I'm not pretending to have the most adapted one, and believe me, you will always be able to find a better tuning for your workload by yourself ;-))
• adopt jemalloc library from the beginning -- it may save you a lot of headache time! ;-)

So, the "mystery" with MariaDB benchmark results is finished for me now.. Focusing now on Peter's and Mark's problems..

Rgds,
-Dimitri