Announcement

Announcement Module
Collapse
No announcement yet.

MySQL 5.0.67 is faster than 5.5.15

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

  • MySQL 5.0.67 is faster than 5.5.15

    Hi,
    I compare performance of 5.0 and 5.5 at FreeBSD 7.4.
    I have two identical servers:
    2x X5687 3.6GHz
    98GB
    8x HDD 146GB

    RAID at srv01:

    Id Size Level Stripe State Cache Name mfid0 ( 544G) RAID-1 64K OPTIMAL Writes


    RAID at srv02:

    Id Size Level Stripe State Cache Name mfid0 ( 952G) RAID-5 128K OPTIMAL Writes


    I run sysbench as follows:
    MyISAM warmup:

    --test=oltp --mysql-db=test --oltp-table-name=sbtest_myisam --max-time=120 --max-requests=0 --num-threads=8 --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run

    MyISAM r/o test:

    --test=oltp --mysql-db=test --oltp-table-name=sbtest_myisam --max-time=60 --max-requests=0 --num-threads=$i --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run

    InnoDB warmup:

    --test=oltp --mysql-db=test --oltp-table-name=sbtest_innodb --max-time=120 --max-requests=0 --num-threads=8 --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run

    InnoDB r/o test:

    --test=oltp --mysql-db=test --oltp-table-name=sbtest_innodb --max-time=60 --max-requests=0 --num-threads=$i --mysql-socket=/tmp/mysql.sock --oltp-read-only=on run

    InnoDB r/w test:

    --test=oltp --mysql-db=test --oltp-table-name=sbtest_innodb --max-time=60 --max-requests=0 --num-threads=$i --mysql-socket=/tmp/mysql.sock run


    What I get is 5.5 defeat:

    num-threads 1 2 3 4 5 6 7 8 10 12 14 16 18 20 22 24 26 28 30 32 36 40 44 48 52 56 60 64srv01: MySQL 5.0.67MyISAM-RO, tps 426.71 725.57 973.66 1156.96 1272.73 1226.91 1139.41 1037.77 887.3 753.07 627.38 523.83 462.76 400.93 348.95 302.41 289.03 259.91 251.23 234.1 216.87 198.67 194.87 185.09 182.66 176.76 173.1 170.83InnoDB-RO, tps 340.64 677.72 1003.94 1322.52 1579.73 1805.14 2011.87 2216.72 2452.15 2644.93 2807.23 2902.67 2812.12 2738.45 2691.59 2668.98 2654.41 2649.93 2641.42 2619.1 2601.82 2567.7 2456.73 2429.16 2328.02 2285.03 2210.29 2181.58InnoDB-RW, tps 282.55 538.73 757.27 977.01 1169.77 1330.55 1466.86 1575.6 1735.46 1786.18 1761.36 1757.96 1741.73 1722.25 1658.2 1610.09 1583.49 1582.79 1566.43 1547.11 1478.27 1246 1177.73 786.99 753.66 516.18 627.72 683.74srv02: MySQL 5.5.15MyISAM-RO, tps 415.05 716.39 953.74 1164.5 1252.94 1178.99 1054.93 938.62 895.02 813.39 698.7 592.76 537.41 509.51 491.63 473.91 461.51 448.63 438.83 429.39 414.73 398.43 391.88 418.6 473.02 532.58 585.42 633.26InnoDB-RO, tps 329.41 644.44 942.18 1270.56 1536.27 1817.91 2091.28 2360.85 2253.96 2235.38 2227.67 2230.93 2249.48 2257.12 2262.33 2267.69 2277.28 2281.22 2283.75 2282.72 2289.74 2291.23 2289.82 2290.47 2289.81 2286.34 2284.71 2281.92InnoDB-RW, tps 263.08 500.49 725.2 958.81 1141.32 1295.54 1440.64 1557.53 1607.25 1611.72 1619.78 1617.16 1568.26 1417.52 1419.97 1415.19 1400.13 1401.08 1315 1309.76 1302.36 1280.06 1271.39 1259.55 1245.41 1240.26 1218.8 1199.69


    What is a possible reason: misconfiguration, OS, RAID, something else?

    The following my.cnf is used:

    back_log = 100binlog_cache_size = 1Mconnect_timeout = 5delayed_insert_limit = 10000delayed_insert_timeout = 100delayed_queue_size = 8000000expire_logs_days = 5ft_min_word_len = 4innodb_additional_mem_pool_size = 20Minnodb_buffer_pool_size = 40Ginnodb_data_file_path = ibdata1:10M:autoextendinnodb_flush_log_at_trx_comm it = 1innodb_flush_method = O_DIRECTinnodb_lock_wait_timeout = 300innodb_locks_unsafe_for_binlog = 1innodb_log_buffer_size = 16Minnodb_log_file_size = 1900Minnodb_status_file = 0innodb_thread_concurrency = 0interactive_timeout = 120join_buffer_size = 1Mkey_buffer_size = 6Gmax_allowed_packet = 1Mmax_connect_errors = 10max_connections = 200max_heap_table_size = 64Mmax_user_connections = 200myisam_sort_buffer_size = 4Gskip-external-lockingskip-name-resolvesort_buffer_size = 64Ksync_binlog = 1table_cache = 2048thread_cache_size = 286thread_stack = 196Ktmp_table_size = 64Mtransaction_isolation = REPEATABLE-READwait_timeout = 120

  • #2
    Could you check the performance of your disks? In particular, I'm curious about how you got 544G with RAID1. RAID10 is faster than RAID5, in general.

    Comment


    • #3
      Please, what role can play disks in memory-bound test?
      Nevertheless,

      $ iostat -dx 5 extended device statisticsdevice r/s w/s kr/s kw/s wait svc_t %bmfid0 0.8 63.5 8.6 2799.4 0 0.8 1 extended device statisticsdevice r/s w/s kr/s kw/s wait svc_t %bmfid0 0.0 0.0 0.0 0.0 0 0.0 0 extended device statisticsdevice r/s w/s kr/s kw/s wait svc_t %bmfid0 0.0 0.0 0.0 0.0 0 0.0 0 extended device statisticsdevice r/s w/s kr/s kw/s wait svc_t %bmfid0 0.0 2.8 0.0 44.8 0 0.2 0 extended device statisticsdevice r/s w/s kr/s kw/s wait svc_t %bmfid0 0.0 3.4 0.0 9.2 0 0.1 0

      Comment


      • #4
        I don't see which server these resulta are from.

        The RW test is not purely memory-bound. It is well possible that the number of dirty pages increases more rapidly in 5.0, which may cause stalls at some point.

        What you can see is that 5.5 scales much better in your RW benchmark.

        Comment


        • #5
          I see a number of things wrong with your benchmarks.

          You should just ignore MyISAM - it is invalid to test it with sysbench oltp. It is a very badly designed benchmark for that. It uses LOCK TABLES, and everything just becomes single threaded.

          Real applications don't just read data, they write it. So ignore your InnoDB readonly test, too, unless you're doing server performance research to look for some bottleneck in the source code you want to fix yourself.

          Now, a simple set of numbers should suffice to summarize your results, no need for so many different values:


          1 16 32 645.0 282.55 1757.96 1547.11 683.745.5 263.08 1617.16 1309.76 1199.69


          As gmouse says, those numbers don't look so bad after all.

          So what's wrong with the benchmarks?

          * You didn't say how you configured the benchmark. I can't repeat your results, so they are just rumors.

          * RAID levels are different. You're not comparing apples to apples. "Identical" servers, really? What other unimportant things are different?

          * You have not configured MySQL 5.5 to use the improvements available in it, such as multiple buffer pools, so it's not surprising that it doesn't show huge improvements. You are also using some pretty dangerous configuration variables, such as innodb_locks_unsafe_for_binlog -- do you have a justification for that? Don't benchmark on something you wouldn't use in production, and I definitely wouldn't use that in production.

          * You need to run a lot longer than 60 seconds if you want to know how the server really performs! Try at least 2 hours, probably better to do at least 8, and I'd suggest 24 hours if you want to really know how it does over the long term. See http://www.mysqlperformanceblog.com/2011/03/21/choosing-an-a ppropriate-benchmark-length/

          Comment


          • #6
            [quote title=xaprb wrote on Tue, 27 September 2011 16:47]* You didn't say how you configured the benchmark. I can't repeat your results, so they are just rumors.[/qoute]
            I'm sorry.
            I used sysbench-0.4.12 from ports collection.

            $ sysbench --test=oltp --oltp-table-size=10000 --mysql-db=test --mysql-table-engine=myisam --oltp-table-name=sbtest_myisam --mysql-socket=/tmp/mysql.sock prepare$ sysbench --test=oltp --oltp-table-size=10000 --mysql-db=test --mysql-table-engine=innodb --oltp-table-name=sbtest_innodb --mysql-socket=/tmp/mysql.sock prepare


            [quote title=xaprb wrote on Tue, 27 September 2011 16:47]* RAID levels are different. You're not comparing apples to apples. "Identical" servers, really? What other unimportant things are different?[/qoute]
            RAID level is the only difference.
            xaprb wrote on Tue, 27 September 2011 16:47
            * You need to run a lot longer than 60 seconds if you want to know how the server really performs! Try at least 2 hours, probably better to do at least 8, and I'd suggest 24 hours if you want to really know how it does over the long term.
            Unfortunately I don't have a lot of time.

            Comment


            • #7
              OK, I understand. If you don't have a lot of time, I don't mean to be rude, but really, running a 60-second benchmark (which won't match any real workload anyway) on a 10,000-row table is actually wasting your time. You should really just trust benchmarks run by Oracle, or those you can find on the MySQL Performance Blog. You could arrive at very wrong conclusions by doing a hasty benchmark with a trivial dataset and trivial run-time.

              In general, MySQL 5.5 when properly configured is going to be much faster and more scalable, with lots more features, much better quality code, lots fewer nasty stupid bugs and surprising server bottlenecks that will freeze the whole server, and on and on. It's really a much better database server. It isn't perfect, and you probably pay for the improvements in some minor areas, but the benefits far outweigh the costs. If you don't have time to do a scientific evaluation with your own workload, just trust people who know from experience It's at least as safe to trust experts as it is to do benchmarks that you know aren't reliable indicators.

              Comment


              • #8
                Baron,
                Please be so kind to answer the last question: what is the preferred OS for MySQL? You make only Linux builds, is it a hint?

                Comment


                • #9
                  MySQL is developed for linux. Filesystem, raid stripe size, configuration etc are all important. It may be a good idea to buy High Performance MySQL, Second Edition (or wait for the Third Edition, http://www.xaprb.com/blog/2011/09/14/high-performance-mysql- third-edition/ ).

                  Comment


                  • #10
                    We have mostly Linux customers, so that's why we focus on Linux builds. We actually build for Solaris, FreeBSD, Mac... if you don't have a strong preference, Linux is not a bad choice, but I don't think there is a compelling business reason to switch to it if you are already using something else.

                    Comment

                    Working...
                    X