GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

MySQL 5.5 Vs 5.6 Performance

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL 5.5 Vs 5.6 Performance

    MySQL 5.6 has huge performance gain over 5.5, it is more than 100% as per 5.6 performance white paper.

    I did benchmarking using Sysbench on VMWare Instance and old physical machine with Opetron processor.

    My sysbench results are very surprising, on both machine MySQL 5.5 have slight edge over 5.6.

    I am using 1000000 rows for test cases and following sysbench command.

    sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=? run

    Xeon(R) CPU X5670@2.93GHz 3 CPUVMWare Instance
    MySQL 5.6.10 MySQL 5.5.28
    Read R/W Read R/W
    Thread Rows TPS TPS TPS TPS
    4 1000000 2049 1036 2417 955
    8 1000000 2071 1171 2457 1129
    16 1000000 2077 1234 2509 1243
    32 1000000 2013 1244 2437 1311
    64 1000000 1990 1274 2424 1332
    128 1000000 1900 1259 2416 1294
    256 1000000 1763 1167 2416 1202
    4 2000000 1980 853 2213 823
    8 2000000 1968 1018 2213 945
    16 2000000 1927 1052 2216 1080
    32 2000000 1883 1099 2200 1198
    64 2000000 1870 1152 2239 1221
    128 2000000 1819 1069 2257 1255
    256 2000000 1668 1021 2217 1209

    Dual-Core AMD Opteron(tm) Processor 2216 4 CPU
    MySQL 5.6.10 MySQL 5.5.28
    Read R/W Read R/W
    Thread Rows TPS TPS TPS TPS
    4 1000000 1887 1026 2148 1063
    8 1000000 1894 1043 2169 1128
    16 1000000 1892 1067 2163 1154
    32 1000000 1800 1041 2083 1143
    64 1000000 1687 1009 1953 1103
    128 1000000 1608 969 1845 1035
    256 1000000 1572 898 1786 946
    4 2000000 1718 700 1900 736
    8 2000000 1735 791 1822 777
    16 2000000 1701 786 1763 800
    32 2000000 1623 780 1718 809
    64 2000000 1381 722 1658 800
    128 2000000 1210 503 1537 781
    256 2000000 960 562 1459 732



    Am I not using SysBench properly? Why the results are so discouraging?

  • #2
    The difference between MySQL 5.5 and 5.6 performance may depend on many factors like workload, hardware, exact MySQL version as well as whether exactly the same variables were used. Note that some variables defaults were changed in MySQL 5.6 and may affect the behaviour. For example Performance Schema overhead is lower in 5.6, hence it was decided to leave it enabled by default. But still some performance overhead exists.

    You can find a deeper view into this problem in following articles:
    http://www.mysqlperformanceblog.com/...han-mysql-5-5/
    http://dimitrik.free.fr/blog/archive...ariadb-55.html
    http://bugs.mysql.com/bug.php?id=66473

    You may also find this news interesting:
    http://www.mysqlperformanceblog.com/...r-5-5-30-30-2/
    Basically, latest Percona Server 5.5 has some important improvements for high concurrency workloads, so you may want to try it out.

    Btw. the way you pasted your results make it unclear which ones are for 5.5 and which for 5.6. Can you re-format them?
    Last edited by przemek; 04-14-2013, 03:51 PM.

    Comment


    • #3
      I am using default parameter with both mysql 5.5 and 5.6.
      What I was expecting from benchmark is mysql 5.6 performance should be more atleast 1-2 % than 5.5 with default installations if mysql white paper claiming 230% performance improvements in 5.6.
      Here are my results: http://mysqlyogi.blogspot.in/2013/04...r-than-55.html
      Which particular parameter should I tune in 5.6 for better sysbench results?

      Comment


      • #4
        Originally posted by yogesh777 View Post
        mysql white paper claiming 230% performance improvements in 5.6.
        Well, first, I would not trust any marketing white paper without an exact method to reproduce results. The 230% can be true, but for very particular workloads.
        On my tests, P_S in 5.6 still have a 5-10% overhead, which is more or less the same that Percona and Facebook are getting for 5.6.10 (see links from przemek). P_S is disabled by default in 5.5.
        We may have to wait a little more to get those issues solved. For now, you can already try 5.6.11 and see if something has changed, and follow the status of several of those MySQL issues.

        Comment


        • #5
          Hi I am back again
          Earlier i was doing tests on virtual machine and on low configuration machine, i thought low hardware might be reason of bad sysbench results.
          Now I have new physical machine with Intel(R) Xeon(R) CPU E5-2667 0 @ 2.90GHz 24 core , 72 GB RAM with SAN partition mounted.
          The results are again disappointing, MySQL 5.5 is again winner. this time it is MySQL 5.6.12 latest version.

          Any views??

          Comment


          • #6
            Did you try disabling Performance Schema in 5.6 and rerunning your tests?

            Comment


            • #7
              As previously mentioned, MySQL 5.6 has quite a few default value changes / new "auto sizing" variables that could result in worse performance with specific work loads / configurations / etc. What I would do is get a list of the variables that did have their default values changed, and make sure to set them to the same thing in both 5.5 and 5.6 for your testing. If the results change at that point, then I would start to remove those default values one at a time until there is a noticeable change. That (potentially) will narrow down what could be affecting your results.

              http://dev.mysql.com/doc/refman/5.6/...t-changes.html

              Comment


              • #8
                Hi

                The default value changes / new "auto sizing" variables changed to default value in 5.5, here are the parameters set in my.cnf

                performance_schema=OFF
                max_connections=1100
                back_log=50
                binlog_checksum=NONE
                host_cache_size=128
                innodb_autoextend_increment=8
                innodb_buffer_pool_instances=1
                innodb_concurrency_tickets=500
                innodb_log_file_size=5MB
                innodb_old_blocks_time=0
                innodb_stats_on_metadata=ON
                join_buffer_size=128KB
                secure_auth=OFF
                table_open_cache=400
                thread_cache_size=0
                table_definition_cache=400
                sync_relay_log_info=0
                sync_relay_log=0
                sync_master_info=0
                query_cache_type=ON
                max_allowed_packet=1MB
                max_connect_errors=0
                innodb_open_files=300

                the sysbench results are disappointing compare to 5.6 with default parameter values.
                MySQL 5.5.30 5.6.10 default values 5.6.10 default value changes to5.5.30 default values
                Thread RO TPS R/W TPS RO TPS R/W TPS RO TPS R/W TPS
                1 616 390 497 311 432 330
                4 2148 1063 1887 1026 735 981
                8 2169 1128 1894 1043 961 1028
                16 2163 1154 1892 1067 780 1022
                32 2083 1143 1800 1041 676 1026
                64 1953 1103 1687 1009 640 1018
                128 1852 1035 1608 969 616 980
                256 1843 946 1572 898 592 896
                512 1832 828 1493 823 565 531
                1024 1521 601 1541 366 545 534

                MySQL 5.6 proved to be low performance versions in all conditions than 5.5.30

                Comment


                • #9
                  query_cache_type plays important role in performance, with query_cache_type=ON ( default value in 5.6 is query_cache_type=OFF) reduces the performance by 50 %. In previous post the stats in last two column are when query_cache_type=ON that is why performance was low. However, in both cases performance is lower than 5.5.30

                  Comment


                  • #10
                    Sorry for the beginner question. I installed mysql 5.5 on windows, found sysbench was not available on that platform. so installed unbuntu under vitual box. Installed mysql and sysbench using the ubuntu installer (which gives no options).

                    I can run mysql and mysql admin, and can see I have a two databases:
                    information_schema
                    test.

                    When I try and run:

                    sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=1 run

                    I get the following error:

                    $ sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --max-time=60 --max-requests=0 --num-threads=1 run
                    sysbench 0.4.12: multi-threaded system evaluation benchmark

                    No DB drivers specified, using mysql
                    ALERT: Error: failed to determine table 'sbtest' type!
                    ALERT: MySQL error:
                    FATAL: failed to get database capabilities!

                    Anyone know what this error means? The schema is there, and is viewable by the user I am logged in as using mysql command.

                    If I do:
                    use test;
                    show tables;
                    I get: Empty Set.

                    Is this the problem? that I need to create the test database first? If so what tables/rows etc should I create?

                    Comment


                    • #11
                      Hi, all, I encounter the same issue, tested the same SQL on same physic machine, percona mysql 5.6 Vs MySQL 5.6 Vs MySQL 5.5.34,
                      same configuration, but MySQL 5.5 performance is the best, percona and MySQL 5.6 almost the same. any ideas? thanks.

                      Comment


                      • #12
                        We are getting ready to migrate from 5,5 to either MySQL Community Server 5.6 or Percona Server 5.6, but now we are concerned about the implications of this thread. WIll be monitoring this thread to see if more updates are coming soon in this regard, please do!

                        Comment


                        • #13
                          I migrated one of the highly used word press blog to MySQL 5.6 Community Edition recently in a hope for better performance specially with FULL TEXT SEARCH in InnoDB, within 15 minutes database server hangs and forced to reboot. after proper analysis i found FULL TEXT SEARCH ( InnoDB support full text searching in 5.6 ) queries piles up and consumed all 500 connections. reverting table engine back to MyISAM database was normal.
                          I repeat the same activity one more time and analyze the behavior for 1-2 hr, there was no performance gain. Finally i revery to MyISAM.

                          To be very honest FULL TEXT SEARCH in InnoDB kills!!!
                          Last edited by yogesh777; 02-07-2014, 12:59 AM.

                          Comment

                          Working...
                          X