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% |
| transactions | |||
| 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
e
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.