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