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