Is MySQL 5.6 slower than MySQL 5.5?

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST

Comments

  1. says

    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.

  2. 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.

  3. says

    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

  4. says

    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.

  5. 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

  6. says

    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.

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

  8. says

    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 : 0x5
    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.

  9. 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.

  10. 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

  11. 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.

  12. says

    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 ?

  13. says

    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.

  14. 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.

  15. 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.

  16. 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

  17. says

    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.

  18. 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

  19. 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?

  20. 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?

  21. says

    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.

  22. Senthil says

    Hi Peter.,

    Is there any mentioned performance issue still exist in the current available version 5.6.21(wrt 5.5).Or Its been fixed.

    Senthil

Leave a Reply

Your email address will not be published. Required fields are marked *