Comparing 32-bit/64-bit MySQL on OpenSolaris

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

Test (x86, binary packages) 32-bit 64-bit +/-
ATIS 20 17 17.65%
alter-table 18 15 20.00%
big-tables 14 11 27.27%
connect 134 121 10.74%
create 348 348 0.00%
insert 1038 885 17.29%
select 399 257 55.25%
wisconsin 10 8 25.00%

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. As a coincidence, Krish Shankar posted these notes on using SunStudio 11 and SunStudio 12 and the right compiler flags to get the best optimization. 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.

Some notes on all the tests:

  • All builds are 5.0.56
  • All tests are run on SunOS 5.11, snv_81
  • 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.
  • All results are in seconds; lower values mean faster performance.
  • In all tests I’m using the built-in defaults (i.e. no my.cnf anywhere) so as to simulate a standardized installation.

Let’s first look at x86 and the 32-bit standard and 32-bit optimized builds:

Test (x86, 32-bit) 32-bit (standard) 32-bit (optimized) +/-
ATIS 15.4 21 -26.67%
alter-table 15 16.3 -7.98%
big-tables 13.7 12.5 9.60%
connect 77.6 133 -41.65%
create 343.7 350.6 -1.97%
insert 760.3 1043.8 -27.16%
select 394.8 384.2 2.76%
transactions 10.8 18.6 -41.94%
wisconsin 6.6 10.1 -34.65%

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.Here’s the comparison for standard builds between 32-bit and 64-bit standard builds on x86:

Test (x86, standard) 32-bit 64-bit +/-
ATIS 15.4 13.5 14.07%
alter-table 15 10.6 41.51%
big-tables 13.7 10.6 29.25%
connect 77.6 76.4 1.57%
create 343.7 346 -0.66%
insert 760.3 681.6 11.55%
select 394.8 254.8 54.95%
transactions 10.8 10.7 0.00%
wisconsin 6.6 5.8 13.79%

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.Unfortunately I’ve had problems with the 64-bit optimized builds on my machine, so I haven’t completed optimized test comparisons.On SPARC, Sun Studio is used as the default compiler, and the standard 32-bit and 64-bit show little difference:

Test (SPARC, standard) 32-bit 64-bit +/-
ATIS 28.6 27.5 4.00%
alter-table 27 26.7 1.12%
big-tables 26.9 29.4 -8.50%
connect 166.3 173.6 -4.21%
create 155 143.1 8.32%
insert 1577.3 1572.3 0.32%
select 807.4 761.6 6.01%
transactions 19.5 18.75 4.00%
wisconsin 11.1 11.4 -2.63%

Overall, a pretty insignificant difference here. Now let’s compare the standard and optimized builds using Krish’s flags on SPARC:

Test (SPARC) 32-bit (standard) 32-bit (optimized) +/-
ATIS 28.6 27.75 3.06%
alter-table 27 26.25 2.86%
big-tables 26.9 25 7.60%
connect 166.3 162.5 2.34%
create 155 145.25 6.71%
insert 1577.3 1551.5 1.66%
select 807.4 769.625 4.91%
transactions 19.5 16.875 15.561%
wisconsin 11.1 10.875 2.07%

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. Now let’s compare the optimized, Sun Studio 12 builds running in 32-bit and 64-bit:

Test (SPARC, optimized) 32-bit 64-bit +/-
ATIS 27.75 27.3 1.65%
alter-table 26.25 26.6 -1.32%
big-tables 25 25 0.00%
connect 162.5 162 0.31%
create 145.25 154.3 -5.87%
insert 1551.5 1535.1 1.07%
select 769.625 771.2 -0.20%
transactions 16.875 19.1 -11.65%
wisconsin 10.875 10.7 1.64%

The differences are virtually non-existent, and taking the reductions and increases in performance overall, there’s probably littl
difference.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.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.