{"id":1164,"date":"2008-02-25T09:39:18","date_gmt":"2008-02-25T09:39:18","guid":{"rendered":"http:\/\/coalface.mcslp.com\/2008\/02\/25\/comparing-32-bit64-bit-mysql-on-opensolaris\/"},"modified":"2008-02-25T09:39:18","modified_gmt":"2008-02-25T09:39:18","slug":"comparing-32-bit64-bit-mysql-on-opensolaris","status":"publish","type":"post","link":"https:\/\/planet.mcb.guru\/?p=1164","title":{"rendered":"Comparing 32-bit\/64-bit MySQL on OpenSolaris"},"content":{"rendered":"

I’ve been working with the folks working on OpenSolaris for a few months now providing advice and input on getting MySQL and the connectors (C\/ODBC and C\/J) installed as a standard component. Having got the basics in, the team are now looking at adding both 32-bit and 64-bit packages. <\/p>\n

The question raised at the end of last week was whether OpenSolaris should enable 64-bit builds by default in 64-bit installations, and whether there was a noticeable performance difference that would make this worthwhile. <\/p>\n

I did some initial tests on Friday which showed that there was a small increase (10-15%) of the packaged 64-bit installations over 32-bit under x86 using snv_81. Tests were executed using the included sql-bench tool, and this was a single execution run of each package for 5.0.56. Transactions are missing because I hadn’t enabled transactions in the tests. <\/p>\n\n\n\n\n\n\n\n\n\n\n\n
Test (x86, binary packages)<\/th>\n32-bit<\/th>\n64-bit<\/th>\n+\/-<\/th>\n<\/tr>\n
ATIS<\/td>\n20<\/td>\n17<\/td>\n17.65%<\/td>\n<\/tr>\n
alter-table<\/td>\n18<\/td>\n15<\/td>\n20.00%<\/td>\n<\/tr>\n
big-tables<\/td>\n14<\/td>\n11<\/td>\n27.27%<\/td>\n<\/tr>\n
connect<\/td>\n134<\/td>\n121<\/td>\n10.74%<\/td>\n<\/tr>\n
create<\/td>\n348<\/td>\n348<\/td>\n0.00%<\/td>\n<\/tr>\n
insert<\/td>\n1038<\/td>\n885<\/td>\n17.29%<\/td>\n<\/tr>\n
select<\/td>\n399<\/td>\n257<\/td>\n55.25%<\/td>\n<\/tr>\n
transactions<\/td>\n<\/tr>\n
wisconsin<\/td>\n10<\/td>\n8<\/td>\n25.00%<\/td>\n<\/tr>\n<\/table>\n

There are some significant differences there (like the 55% increase on SELECT speeds, for example), but a single execution is never a good test. Also, it’s unclear whether the differences are between the compilations, the platform or just pure coincidence. This requires further investigation. <\/p>\n

As a coincidence, Krish Shankar posted these notes<\/a> on using SunStudio 11 and SunStudio 12 and the right compiler flags to get the best optimization. <\/p>\n

I decided to do 10-pass iterations of sql-bench and compare both 32-bit and 64-bit standard builds, the 32-bit standard builds against Krish’s optimizations, and finally 32-bit and 64-bit optimized builds. <\/p>\n

\n

\nSome notes on all the tests: <\/p>\n

    \n
  • All builds are 5.0.56<\/li>\n
  • All tests are run on SunOS 5.11, snv_81<\/li>\n
  • Tests are executed on the same OS and machine running in 64-bit. The SPARC tests are on an UltraSPARC IIIi@1.28GHz Workstation with 1GB RAM; x86 are on a Dell T105, Opteron 1212 with 4GB RAM. Of course we’re not comparing machine speed, just 32-bit binaries over 64-bit. <\/li>\n
  • All results are in seconds; lower values mean faster performance.\n<\/li>\n
  • In all tests I’m using the built-in defaults (i.e. no my.cnf anywhere) so as to simulate a standardized installation.<\/li>\n<\/ul>\n<\/blockquote>\n

    Let’s first look at x86 and the 32-bit standard and 32-bit optimized builds: <\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    Test (x86, 32-bit)<\/th>\n32-bit (standard)<\/th>\n32-bit (optimized)<\/th>\n+\/-<\/th>\n<\/tr>\n
    ATIS<\/td>\n15.4<\/td>\n21<\/td>\n-26.67%<\/td>\n<\/tr>\n
    alter-table<\/td>\n15<\/td>\n16.3<\/td>\n-7.98%<\/td>\n<\/tr>\n
    big-tables<\/td>\n13.7<\/td>\n12.5<\/td>\n9.60%<\/td>\n<\/tr>\n
    connect<\/td>\n77.6<\/td>\n133<\/td>\n-41.65%<\/td>\n<\/tr>\n
    create<\/td>\n343.7<\/td>\n350.6<\/td>\n-1.97%<\/td>\n<\/tr>\n
    insert<\/td>\n760.3<\/td>\n1043.8<\/td>\n-27.16%<\/td>\n<\/tr>\n
    select<\/td>\n394.8<\/td>\n384.2<\/td>\n2.76%<\/td>\n<\/tr>\n
    transactions<\/td>\n10.8<\/td>\n18.6<\/td>\n-41.94%<\/td>\n<\/tr>\n
    wisconsin<\/td>\n6.6<\/td>\n10.1<\/td>\n-34.65%<\/td>\n<\/tr>\n<\/table>\n

    The standard build uses gcc instead of SunStudio, but I don’t get the same performance increases that Krish saw – in fact, I see reductions in performance, not improvements at all. I’m going to rebuild and retest, because I’m convinced there’s a problem here with the builds that I’m not otherwise seeing. I certainly don’t expect to get results that show a 27% reduction in insert speed. That said, a 10% big-table increase is interesting. I’ll redo these builds and find out if the slow down is as marked as it here.<\/p>\n

    Here’s the comparison for standard builds between 32-bit and 64-bit standard builds on x86: <\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    Test (x86, standard)<\/th>\n32-bit<\/th>\n64-bit<\/p>\n+\/-<\/th>\n<\/td>\n<\/tr>\n
    ATIS<\/td>\n15.4<\/td>\n13.5<\/td>\n14.07%<\/td>\n<\/tr>\n
    alter-table<\/td>\n15<\/td>\n10.6<\/td>\n41.51%<\/td>\n<\/tr>\n
    big-tables<\/td>\n13.7<\/td>\n10.6<\/td>\n29.25%<\/td>\n<\/tr>\n
    connect<\/td>\n77.6<\/td>\n76.4<\/td>\n1.57%<\/td>\n<\/tr>\n
    create<\/td>\n343.7<\/td>\n346<\/td>\n-0.66%<\/td>\n<\/tr>\n
    insert<\/td>\n760.3<\/td>\n681.6<\/td>\n11.55%<\/td>\n<\/tr>\n
    select<\/td>\n394.8<\/td>\n254.8<\/td>\n54.95%<\/td>\n<\/tr>\n
    transactions<\/td>\n10.8<\/td>\n10.7<\/td>\n0.00%<\/td>\n<\/tr>\n
    wisconsin<\/td>\n6.6<\/td>\n5.8<\/td>\n13.79%<\/td>\n<\/tr>\n<\/table>\n

    There are some incredible differences here – more than 50% increase in SELECT, and 30% for the big-tables test show that there is some advantage to having the 64-bit builds on x86 enabled.<\/p>\n

    Unfortunately I’ve had problems with the 64-bit optimized builds on my machine, so I haven’t completed optimized test comparisons.<\/p>\n

    On SPARC, Sun Studio is used as the default compiler, and the standard 32-bit and 64-bit show little difference: <\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    Test (SPARC, standard)<\/th>\n32-bit<\/th>\n64-bit<\/th>\n+\/-<\/th>\n<\/tr>\n
    ATIS<\/td>\n28.6<\/td>\n27.5<\/td>\n4.00%<\/td>\n<\/tr>\n
    alter-table<\/td>\n27<\/td>\n26.7<\/td>\n1.12%<\/td>\n<\/tr>\n
    big-tables<\/td>\n26.9<\/td>\n29.4<\/td>\n-8.50%<\/td>\n<\/tr>\n
    connect<\/td>\n166.3<\/td>\n173.6<\/td>\n-4.21%<\/td>\n<\/tr>\n
    create<\/td>\n155<\/td>\n143.1<\/td>\n8.32%<\/td>\n<\/tr>\n
    insert<\/td>\n1577.3<\/td>\n1572.3<\/td>\n0.32%<\/td>\n<\/tr>\n
    select<\/td>\n807.4<\/td>\n761.6<\/td>\n6.01%<\/td>\n<\/tr>\n
    transactions<\/td>\n19.5<\/td>\n18.75<\/td>\n4.00%<\/td>\n<\/tr>\n
    wisconsin<\/td>\n11.1<\/td>\n11.4<\/td>\n-2.63%<\/td>\n<\/tr>\n<\/table>\n

    Overall, a pretty insignificant difference here. <\/p>\n

    Now let’s compare the standard and optimized builds using Krish’s flags on SPARC: <\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    Test (SPARC)<\/th>\n32-bit (standard)<\/th>\n32-bit (optimized)<\/th>\n+\/-<\/th>\n<\/tr>\n
    ATIS<\/td>\n28.6<\/td>\n27.75<\/td>\n3.06%<\/td>\n<\/tr>\n
    alter-table<\/td>\n27<\/td>\n26.25<\/td>\n2.86%<\/td>\n<\/tr>\n
    big-tables<\/td>\n26.9<\/td>\n25<\/td>\n7.60%<\/td>\n<\/tr>\n
    connect<\/td>\n166.3<\/td>\n162.5<\/td>\n2.34%<\/td>\n<\/tr>\n
    create<\/td>\n155<\/td>\n145.25<\/td>\n6.71%<\/td>\n<\/tr>\n
    insert<\/td>\n1577.3<\/td>\n1551.5<\/td>\n1.66%<\/td>\n<\/tr>\n
    select<\/td>\n807.4<\/td>\n769.625<\/td>\n4.91%<\/td>\n<\/tr>\n
    transactions<\/td>\n19.5<\/td>\n16.875<\/td>\n15.561%<\/td>\n<\/tr>\n
    wisconsin<\/td>\n11.1<\/td>\n10.875<\/td>\n2.07%<\/td>\n<\/tr>\n<\/table>\n

    The tests here show little significant difference between the standard and the optimized builds, although 6-7% would probably be enough to prefer an optimized build if you wanted to build your own. <\/p>\n

    Now let’s compare the optimized, Sun Studio 12 builds running in 32-bit and 64-bit: <\/p>\n\n\n\n\n\n\n\n\n\n\n\n
    Test (SPARC, optimized)<\/th>\n32-bit<\/th>\n64-bit<\/th>\n+\/-<\/th>\n<\/tr>\n
    ATIS<\/td>\n27.75<\/td>\n27.3<\/td>\n1.65%<\/td>\n<\/tr>\n
    alter-table<\/td>\n26.25<\/td>\n26.6<\/td>\n-1.32%<\/td>\n<\/tr>\n
    big-tables<\/td>\n25<\/td>\n25<\/td>\n0.00%<\/td>\n<\/tr>\n
    connect<\/td>\n162.5<\/td>\n162<\/td>\n0.31%<\/td>\n<\/tr>\n
    create<\/td>\n145.25<\/td>\n154.3<\/td>\n-5.87%<\/td>\n<\/tr>\n
    insert<\/td>\n1551.5<\/td>\n1535.1<\/td>\n1.07%<\/td>\n<\/tr>\n
    select<\/td>\n769.625<\/td>\n771.2<\/td>\n-0.20%<\/td>\n<\/tr>\n
    transactions<\/td>\n16.875<\/td>\n19.1<\/td>\n-11.65%<\/td>\n<\/tr>\n
    wisconsin<\/td>\n10.875<\/td>\n10.7<\/td>\n1.64%<\/td>\n<\/tr>\n<\/table>\n

    The differences are virtually non-existent, and taking the reductions and increases in performance overall, there’s probably little difference.<\/p>\n

    The overall impression is that on x86 the improvement of 64-bit over 32-bit is significant enough that it’s probably a good idea to make 64-bit the default. On SPARC, the difference in the optimized builds is so slight that for compatibility reasons alone, 32-bit would probably make a better default.<\/p>\n

    I’ll probably be re-running these tests over the next week or so (particularly the x86 so I can get a true comparison of the 64-bit optimized improvements), and I’ll try the T1000 which I upgraded to snv_81 over the weekend, but I think indications are good enough to make a reasonable recommendation of 64-bit over 32-bit. <\/p>\n","protected":false},"excerpt":{"rendered":"

    I’ve been working with the folks working on OpenSolaris for a few months now providing advice and input on getting MySQL and the connectors (C\/ODBC and C\/J) installed as a standard component. Having got the basics in, the team are now looking at adding both 32-bit and 64-bit packages.
    \nThe question raised at the end […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[],"tags":[36],"_links":{"self":[{"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=\/wp\/v2\/posts\/1164"}],"collection":[{"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1164"}],"version-history":[{"count":0,"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=\/wp\/v2\/posts\/1164\/revisions"}],"wp:attachment":[{"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1164"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1164"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/planet.mcb.guru\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1164"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}