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

Aperi Discovery and Probe Videocast

As a follow up to my first introductory video on using Aperi, I now have a follow-up videocast on how to add storage systems to your Aperi installation. The process is two-stage, first you have to run a discovery process (to find the devices) and then a probe (to find out information about those devices, such as the specific configuration of the SAN). You can view the videocast online: Watch the “Discovery and Probe” demoOr you can download it for viewing at your leisure: Download the “Discovery and Probe” demo (13.3MB)

Systems Administration Toolkit: Network scanning

A new article on scanning your network for information, both in a general device/service level and at a more detailed data level is now available.

Discover how to scan your network for services and how to regularly monitor your services to keep uptimes to a maximum. A key way of ensuring the security of your network is to know what is on your network and what services individual machines are at risk of exposure. Unauthorized services, such as Web servers or file sharing solutions, not only degrade performance, but others can use these services as routes into your network. In this article, learn how to use these same techniques to ensure that genuine services remain available.

Read: Systems Administration Toolkit: Network scanning

Systems Administration Toolkit: Monitor user usage

Do you know what your users are doing? Do you know how frequently people are using your systems? As I explain in the intro:

Explore new ways to record UNIX logins and other system activities in a number of different logs, and take advantage of this information to monitor user usage. This can be helpful from a number of perspectives, either to use for chargeback reporting or just to get an idea of how busy and active individual users are on the system to help when planning and allocating resources.

Read: Systems Administration Toolkit: Monitor user usage

System Administration Toolkit: Distributed administration using SSH

System administration and SSH is a topic I’ve covered before (see System Administration Toolkit: Set up remote access in UNIX through OpenSSH), but what I didn’t cover in that article was how you can use SSH as a solution for running commands remotely over one, or more, machines simultaneously. From the new article:

Use Secure Shell (SSH) to run commands on remote UNIX systems and, with some simple scripts, put together a system that enables you to manage many systems simultaneously from one machine without having to log in directly to the machines themselves. Also examine the basics of a distributed management system and some scripts and solutions using the technique.

Read: System Administration Toolkit: Distributed administration using SSH

Aperi Download and Install Videocast

Aperi is an open source storage management framework that provides you with a single interface to managing all of your different storage solutions. You can get more information from the Aperi Homepage. The Aperi application is based on Eclipse, and is an excellent example of an application built on top of the core Eclipse framework (technically, the IDE is another example of this, but many people confuse the Eclipse IDE and the Eclipse Framework as the same thing). To help you understand and use Aperi, I’ve created a video that shows how to install and setup Aperi ready to start using it. You can view the videocast online: Watch the “Download and Install” demoOr you can download it for viewing at your leisure: Download the “Download and Install” demo (16.8MB)

Voice enabling XML, Part 4: Develop a Web search application for VoiceXML

The final part of the four-part series on developing VoiceXML applications, this time covering an interface to an internet search system, is available:

In this final article of a four-part series, develop an application that takes VoiceXML as input and queries the Yahoo Search API for both basic Web searches and Yahoo local searches. The query returns information about businesses within a specific location and region. The application then reads the results to the caller after submission.Internet searching is taken for granted these days, with numerous services available for searching. Web searching has also expanded. With so many companies that have a Web presence, many companies now merge their Web and traditional offline data, like business directories, and map and location information to make it possible to search for a variety of businesses and information.This information is perfect to use VoiceXML (VXML) to submit your searches and listen to the returned results. In this article, you will create an application that does this, and you will also:

  • Review a Web searching workflow
  • Create a generic class for outputting VXML form elements
  • Create VXML grammar that supports a wide range of input
  • Use the Yahoo search interface
  • Run Web searches using VXML and Yahoo search
  • Run local searches using VXML and Yahoo search

Read: Voice enabling XML, Part 4: Develop a Web search application for VoiceXML

Systems Administration Toolkit: Testing system validity

I recently had a hacking attempt on my machine, perpetrated by somebody noticing the open SSH port and trying every possible user/password and combination trick to try and get in. They didn’t succeed, and I only know that because I was able to check the validity of my machine by using an existing set of recorded information to verify my configuration and program data. This is a technique that I’ve distilled down into an article within the System Administration Toolkit series, called Testing System Validity:

Examine methods of storing and later checking the validity of your configuration files. Despite all the security systems you have in place, it is still possible that somebody has accessed your system and changed your configuration or security settings.

Read: Systems Administration Toolkit: Testing system validity

Can a website have a box?

I’ve been sent a couple of books to review for O’Reilly, which I’ll get to soon, but I’m intrigued by the text on the front of the book. Both are for websites – Wikipedia and Facebook – and are both part of the Missing Manual series. I’m confused though – both also quote the Missing Manual mantra:

The book that should have been in the box

Can a website have a box?

Develop with Java and PHP technology on AIX Version 5.3, Part 6: Building the Java business application

The final part of the PHP/Java series is now available. This last part of the series covers the modification of our original application so that it employs the PHP/Java bridge as the interface between the core Java application and PHP. A detailed overview of the content:

  • Comparing connection technologies
    • The Web service method
    • The PHP Java Bridge
    • Differences and similarities
    • Performance
    • Java and PHP development

  • Developing a Web interface
    • Revisiting the Web interface for the Java environment
    • Parsing the responses
    • Re-deploying in PHP

  • Creating a PHP interface to the Java survey data
    • Creating a new instance of the Survey application
    • Generating the Survey form through a single method
    • Generating the form by using SurveyQuestion methods directly
    • Submitting survey responses back to the Java classes
    • Submitting a survey response

Read Develop with Java and PHP technology on AIX Version 5.3, Part 6: Building the Java business application