September 1, 2014

Is MySQL 5.6 slower than MySQL 5.5?

There have been a number reports/benchmarks showing MySQL 5.6 to be slower than MySQL 5.5 on variety of workloads. There are many possible reasons and I believe we will learn about many of them in the next few weeks and months as MySQL 5.6 is starting to get production battle-tested and there is inflow of real world production performance data hitting MySQL Support Team at Oracle. For a number of simple workloads I would expect to see some slowdown – MySQL 5.6 Optimizer got a lot smarter and the more plans have to be considered for the query the more time the query optimization is likely to take. I also would expect newer code to benefit from “polishing” and clean up to achieve the best performance possible. At Percona we surely will be doing fine tuning for our Percona Server for MySQL version 5.6 release.

Another thing to remember about MySQL 5.6 is – it comes with Performance Schema enabled by default. Even though Performance Schema overhead was reduced in MySQL 5.6 it is still not free and it will cause an overhead, yet hopefully having more information about system operation at your fingertips will help you to achieve gains higher than the slowdown it causes.

I decided to do my own quick check into the problem using SysBench. It is by no means reflection of any production workload but we found it is a good measure of the overhead, especially for simple queries. I ran small (1M rows) benchmark so data was well fitting in memory and Read Only one to simplify things and get more repeatable results without spending too much time on it. I used Ubuntu 12.04 on some old Dell PowerEdge 2850 server. The questions I tried to answer are how much slower 5.6 is for such simple workload ? How much of this slowness comes from Performance Schema ? Whenever we get difference higher or lower with high concurrency compared to single thread ?

Note Sysbench is simple enough it does not take advantage of any of new MySQL 5.6 optimizations so it is geared towards worse case scenario for MySQL 5.6 performance.

Here is Sysbench command I used:

56vs55_single_thread

For single thread MySQL 5.5 is 11% faster than MySQL 5.6. If you disable Performance Schema in MySQL 5.6 comes to just about 3% showing Performance Schema itself is responsible for 7.5% overhead in this case. This is quite reasonable result and I do not think 3% difference will be noticed in most production cases.

56vx55_64_threads

For benchmark with 64 threads MySQL 5.5 is 26% faster compared to MySQL 5.6 and if you disable Performance Schema the difference is still 11% with 13% difference between MySQL 5.6 with Performance Schema enabled and Disabled. The difference of over 25% is rather substantial and if fair amount of workloads will see similar kind of overhead I expect a lot of grief.

It is also unfortunate to see the difference for this specific benchmark actually growing with increased contention even though MySQL 5.6 is positioned as improving scalability over MySQL 5.5. Also number of expected slowdowns such as more work done at optimizer phase should not increase with contention.

Granted I have not tuned MySQL 5.6 for best performance going with defaults outside of few variables MySQL Sandbox sets explicitly but yet this is likely course of action for many MySQL users out there, also was not change of defaults in MySQL 5.6 had more reasonable defaults as its goal ?

Summary: Well, to be frank I expected more from MySQL 5.6 release – more of cleaned up code and better out of the box performance. Yet I’m still very excited about all architecture changed implemented in MySQL 5.6 and new feature and I believe when we look at real workloads we’ll see more cases when MySQL 5.6 excels. I also expect some analyses and cleanup done over next few months which should help to improve MySQL 5.6 performance.

About Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.

Comments

  1. Dimitri says:

    Hi Peter,

    an how many cores are on your server?
    did you use jemalloc?

    Rgds,
    -Dimitri

  2. Mr C says:

    What’s the best way to install 5.6 on Ubuntu 12.04 (fresh install)?

  3. Dimitry,

    It is just 4 cores per box of model name : Intel(R) Xeon(TM) CPU 3.00GHz

    Linux dpe01 3.2.0-37-generic #58-Ubuntu SMP Thu Jan 24 15:28:10 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux

    I just downloaded binaries:
    -rw-rw-rw- 1 root root 186641309 Jan 16 21:04 mysql-5.5.30-linux2.6-x86_64.tar.gz
    -rw-rw-rw- 1 root root 303263359 Jan 23 03:55 mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz

    As I stated in the post this is not about testing the best tuned 5.5 vs 5.6 but rather to see how out of the box performance would look like.

  4. Marc Alff says:

    Hi Peter.

    According to my math, in the single thread case, 1-331/367 is 9.8 percent. You may round that up to 10%, but this is not 11%.

    Likewise, for the 64 threads case, 1-709/891 is 20.41 percent, definitively not 26.

    According to your math, taking 5.6 as a base, a 5.6 QPS which is the 2x from a 5.5 QPS (a 100% improvement in my book) would be counted as only 50 % improvement … humm.

    Math and point of reference apart, a 20 or 26 percent overhead with the performance schema is inconsistent with our experience, hence my follow up question:

    Could you post the my.cnf relevant config for the performance schema.

    Claiming “with performance schema” is meaningless these days, considering how many different instrumentation may or may not be enabled. If the default out of the box configuration was used, please clarify that, or if something else was used, please indicate the details.

    Thanks,
    – Marc Alff.

  5. Marc,

    First about math. Might be wording is not aligned with this. What I did is 367/331= 1.10876 which I rounded to 11% yes… Perhaps I should say MySQL 5.5 is 11% faster than MySQL 5.6 rather than 5.6 is slower than 5.5…. Typically the point is to show how much faster something is growing from lower to higher numbers. There is no intent here to show worse numbers than they are :)

    If MySQL 5.6 QPS would be 2x of 5.5 we could call it 5.6 is 100% faster or we can call it 5.5 is 50% slower both are relevant.

    Now regarding performance schema. I used MySQL Sandbox set up with MySQL 5.6 and 5.5 in their default settings.
    I did not do anything to tune performance schema comparing it out of the box. when I disabled it I did it by adding “skip-performance-schema” to my.cnf

    I think you guys in development team grossly overestimate how much tuning the general user is going to do :)

    Here is complete config file from sandbox:

    [mysqld]
    user = root
    port = 5610
    socket = /tmp/mysql_sandbox5610.sock
    basedir = /mnt/nfs/dist/5.6.10
    datadir = /mnt/data/sandboxes/msb_5_6_10/data
    tmpdir = /mnt/data/sandboxes/msb_5_6_10/tmp
    pid-file = /mnt/data/sandboxes/msb_5_6_10/data/mysql_sandbox5610.pid
    #log-slow-queries = /mnt/data/sandboxes/msb_5_6_10/data/msandbox-slow.log
    #log = /mnt/data/sandboxes/msb_5_6_10/data/msandbox.log
    #
    # additional options passed through ‘my_clause’
    #
    log-error=msandbox.err
    #skip-performance-schema

  6. Marc,

    I have now updated the language to state it is 5.5 being X% faster not 5.6 being X% slower as this is how math was done.

  7. This is disappointing. I (as I’m sure many others) am very excited about the changes in 5.6. Hopefully there’s some simple (a relative term obviously) changes to bring this back up to 5.5 standards – at least.

  8. Dimitri says:

    Peter, there should be really something wrong…

    on MySQL 5.6 (PFS=off) with a single thread I’m getting:
    – 380 TPS on 2.27Ghz CPU server..
    – 550 TPS on 2.9Ghz CPU server..

    so, seems pretty strange that you’re getting only 361 TPS on 3.0Ghz CPU server..

    seems to me I have to investigate it in details..

    Rgds,
    -Dimitri

  9. Dimitry,

    You can’t just look at Ghz. as stated this is Dell PowerEdge 2850 which is about 5 years old box (or more). The per Ghz frequency has improved dramatically with the recent years.

  10. Dimitri says:

    Peter,

    my servers are also 3-4 years old,
    that’s why I have yet more reasons to be surprised ;-))

    as I said, I’ll investigate it..

    Rgds,
    -Dimitri

  11. Thanks Dimitri !

    Here is exact CPU information for mine if you’re interested:
    processor : 3
    vendor_id : GenuineIntel
    cpu family : 15
    model : 4
    model name : Intel(R) Xeon(TM) CPU 3.00GHz
    stepping : 3
    microcode : 0×5
    cpu MHz : 2992.776
    cache size : 2048 KB
    physical id : 3
    siblings : 2
    core id : 0
    cpu cores : 1
    apicid : 7
    initial apicid : 7
    fpu : yes
    fpu_exception : yes
    cpuid level : 5
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx lm constant_tsc pebs bts nopl pni dtes64 monitor ds_cpl cid cx16 xtpr
    bogomips : 5985.40
    clflush size : 64
    cache_alignment : 128
    address sizes : 36 bits physical, 48 bits virtual
    power management:

    There was one specific time few years ago when CPU architectures changed from P4 architecture to “Core” architectures which offered substantially better performance for lower clock frequency.

  12. Justin Swanhart says:

    That appears to be a P4, the end of the Netburst architecture before core was introduced. Memory speed/bandwidth may have a big part in performance here.

  13. I reopened a bug I opened for 5.6.6. There is still too much mutex contention on the MDL code for some workloads — http://bugs.mysql.com/bug.php?id=66473

    I also created a bug for a long known behavior (see http://www.mysqlperformanceblog.com/2011/04/25/performance-schema-overhead/). The overhead from PS can be more than 10%. I think that is too much — http://bugs.mysql.com/bug.php?id=68413

  14. Steve Jackson says:

    A bit off-topic Peter, but will Percona be getting Handlersocket to compile with 5.6?

    I am sure many would agree that Oracles “NoSQL” layer doesn’t even come close to what handlersocket has already achieved… not in terms of performance, or flexibility…

    Cheers

    //Steve

  15. mike morse says:

    we’ve found the drag on performance from Performance Schema in a production environment can vary dramatically depending on your workload, and can be quite significant for high volume simple key read/writes on beefier hardware.. more so than these particular benchmarks (on 1 core/ 4 logical cpus I’m guessing) would indicate.

  16. I’m still digging into what could be the problem here. One thing I noticed the official “General Linux” binaries are using different compilers. MySQL 5.5.30 is using GCC 4.3.4 and MySQL 5.6.10 is using GCC 4.1.2 (from 2008) This surely can be part of explanation. Anyone knows if there is a reason for this difference or if it is mistake ?

  17. Well, now here’s something interesting:

    MySQL 5.6.10 (PFS enabled): 666.95 TPS
    MySQL 5.6.10 (PFS disabled): 622.77 TPS
    MySQL 5.5.30: 842.47 TPS

    processor : 7
    vendor_id : GenuineIntel
    cpu family : 6
    model : 42
    model name : Intel(R) Core(TM) i7-2600K CPU @ 3.40GHz
    stepping : 7
    cpu MHz : 1600.000
    cache size : 8192 KB
    physical id : 0
    siblings : 8
    core id : 3
    cpu cores : 4
    apicid : 7
    initial apicid : 7
    fpu : yes
    fpu_exception : yes
    cpuid level : 13
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm sse4_1 sse4_2 popcnt aes xsave avx lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid
    bogomips : 6784.40
    clflush size : 64
    cache_alignment : 64
    address sizes : 36 bits physical, 48 bits virtual
    power management:

    The 5.6.10 results make no sense to me unless it’s just measurement error; I’m going to have to try this again from runlevel 1 without all the extra desktop stuff running.

  18. Steve,

    Thanks for interest. We have not decided on Handlersocket inclusion yet. It will depend on user and customer interest.

  19. Greg says:

    These are incredibly concerning figures.

    I’ve been reading about how much faster 5.6 supposedly is with mutli-threading but from reading the above I am going to steer clear of MySQL 5.6 in production for now.

    Quick question – how can i generate these QPS figures similar to the above? I have a large number of 12 & 16 core Gentoo servers with GCC 4.7 that i’d be keen to test source built (not precompiled) 5.5 vs 5.6 performance on.

  20. Lilian says:

    Hi,

    I found the same result but during some crash tests, with innodb_flush_log_at_trx_commit=2 and innodb_flush_method=O_DIRECT, there are uncommited data present into table in 5.5 release.
    In 5.6, all uncommited data are rollbacked after a crash.

    So, for me, 5.6 release is expected version to have performance AND data coherence in case of crash. Good alternative to Oracle database.

  21. Vladislav Dimitrov says:

    Hi Peter.
    As Dimitri stated,it seams to be something whrong … with your build of mysql-5.6!
    On 64bit Win7 host running 512 MB VirtulBox Ubuntu 12.04 guest(3.2.0-39-generic #62-Ubuntu SMP Thu Feb 28 00:28:53 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux) using VBoxHeadless.exe with 1 core
    # cat /proc/cpuinfo
    processor : 0
    vendor_id : GenuineIntel
    cpu family : 6
    model : 23
    model name : Intel(R) Core(TM)2 Duo CPU E8400 @ 3.00GHz
    stepping : 10
    cpu MHz : 2878.300
    cache size : 6144 KB
    fpu : yes
    fpu_exception : yes
    cpuid level : 5
    wp : yes
    flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 syscall nx lm constant_tsc up rep_good nopl pni monitor ssse3 lahf_lm
    bogomips : 5756.60
    clflush size : 64
    cache_alignment : 64
    address sizes : 36 bits physical, 48 bits virtual

    I get this as result:
    transactions: 148503 (495.00 per sec.)
    transactions: 161630 (538.76 per sec.) # using skip-performance-schema
    with standard mysql-5.6.10-debian6.0-x86_64.deb package !

    You may consider rerun your tests.

    Cheers.

    Vladislav

  22. Vladislav – thank you for the results. The PS reduces QPS by more than 8% for your tests. Did you enable anything special or was it collecting the default data? I think 8% is too much overhead.

  23. Vladislav Dimitrov says:

    I forgot to mention – the results from previous comment are for 1 thread.
    Same VM with PS off
    1 core, 1 thread
    mysql-server-core-5.5_5.5.29-0ubuntu0.12.04.2_amd64.deb 564 PTS
    mysql-5.6.10-debian6.0-x86_64.deb 538 PTS

    1 core, 64 threads:
    mysql-server-core-5.5_5.5.29-0ubuntu0.12.04.2_amd64.deb 575 PTS
    mysql-5.6.10-debian6.0-x86_64.deb 572 PTS

    2 cores, 64 threads:
    mysql-server-core-5.5_5.5.29-0ubuntu0.12.04.2_amd64.deb 911 PTS
    mysql-5.6.10-debian6.0-x86_64.deb 918 PTS

  24. Asle Ommundsen says:

    @Peter Zaitsev, in a reply you say this: “I did not do anything to tune performance schema comparing it out of the box. when I disabled it I did it by adding “skip-performance-schema” to my.cnf”. This confuses me, because when I read http://dev.mysql.com/doc/refman/5.6/en/performance-schema-startup-configuration.html it say that it should be: performance_schema=off

    I am considering upgrading from 5.5 to 5.6 and disable performance schema, so which way is correct? Adding performance_schema=off or your skip-performance-schema to my.cnf?

  25. phd says:

    Hi Peter,
    I was just wondering whether there is a significant difference in partition select performance between Mysql 5.5 and Mysql 5.6 since select operations against partitioned tables are not implemented the same way in both versions. In Mysql 5.6, the statement mentions the partition name rather than the “partitioned_column=value” criteria. Technically speaking, does it mean that a select statement against a Mysql 5.6 partitioned table would be somewhat as fast as/equivalent to the “classic” form of the same select statement against a non-partitioned table holding the same data as the partition?

  26. phd,

    The feature you are talking about is for explicit partition elimination. Explicit partition elimination still applies.

    For example, you could partition by year and have the following query:
    select count(*) from some_table where year in (2001,2002,2003,2004)

    For simplicity say that returns 4000 (exactly 1000 per year).

    In MySQL 5.6 you could say:
    select count(*) from some_table PARTITION(p2001,p2002) where year in (2001,2002,2003,2004)

    This would return 2000 because you have “whitelisted” only p2001 and p2002 partitions.

    Rows from only the p2003 and p2004 partitions would be included if the query said:
    select count(*) from some_table PARTITION(p2003,p2004) where year in (2001,2002,2003,2004)

    and the result would be 2000

    and likewise:
    select count(*) from some_table PARTITION(p2004) where year in (2001,2002,2003,2004)

    would result in 1000

    This new hint can also be used for examining only one partition of a hash partition for example, something that can not be done with implicit partition elimination.

Speak Your Mind

*