System administrators constantly worry about optimization with code compilation. This is no different for MySQL. Most systems are fine with the binaries distributed by their flavor of Linux or directly from Oracle. But there are many who need to maximize their hardware usage by pushing their software. I intend to show how to tweak your MySQL compilation to achieve these results.

I will also be showing you how I manage MySQL upgrades and versions on my database machines. This includes keeping the previous version of software for emergency rollback. You never know when an unexpected bug or feature will cause problems for you, so it’s good to keep at least one historical version on hand. I highly suggest that you let the new version of MySQL burn-in for at least 48 hours before migrating the binaries and upgrading the rest of your boxes.

There have been cases where a version of MySQL that I’ve compiled has introduced problems. The majority of the time this is related to replication or collations. Whatever the case, I had the need to rollback to a version that was working fine. You can typically do this for minor upgrades (e.g. 5.1.51 to 5.1.50) with a relative confidence. That level of certainty does not apply to major and major-minor (5.0.x to 5.1.x or 4.x to 5.x).

In order to accomplish this, I keep all versions of MySQL in the same directory with at least one previous version. My versions are installed in /opt. The installation directory is always named mysql-[version]. For example, if I’m installing MySQL 5.1.52, the path to the installation will be /opt/mysql-5.1.52. This let’s me keep a lot of historical versions on hand while also installing and distributing new versions without stopping MySQL. The production version is a symbolic link to the full path of the installation. For example, if version 5.1.51 is the production version, my symbolic link would be created like this:

ln -s /opt/mysql-5.1.51 /opt/mysql

A listing of /opt would yield something like this:

drwxr-xr-x  4 root root 4096 Sep 20 20:52 ./
drwxr-xr-x 25 root root 4096 Sep 23 23:42 ../
lrwxrwxrwx  1 root root   17 Sep 20 20:36 mysql -> /opt/mysql-5.1.51/
drwxrwxr-x  9 root root 4096 Jul 23 21:40 mysql-5.1.50/
drwxrwxr-x  9 root root 4096 Sep 20 20:34 mysql-5.1.51/

Notice that the previous version, 5.1.50, still exists in the event I have to rollback. If this happens, I simply take the following actions:

• Stop the running MySQL instance
• Destroy the existing symbolic link
• Create a new symbolic link that points to the previous version
• Start MySQL via /opt/mysql/bin/mysqld_safe
• Verify the problem no longer exists

The same logic applies to upgrading, but rather than creating a symbolic link to the previous version, it gets pointed to the new.

Prior to actually configuring, compiling and installing the new version of MySQL, we need to set some flags for the C and C++ compilers. This is where much of the optimization will come into play. Keep in mind that this is not all-inclusive, and there’s likely more compiler optimizations you can make for your distribution and hardware.

CPU-specific Flags

Depending on whether you’re using Intel or AMD processors, you will have some different flags to set. In my case, I’m using dual Xeon 5460s. The flag I use is SSE4.1, which is labeled for Core i7, but is generically used for X5400/5500 CPUs as well.

If you’re using AMD, you will have a different list to choose from. The full list of CPU flags can be found here. The generic, x86_64 architecture flag for AMD is athlon64.

Passing these flags to the compiler is done with two Linux environmental variables: CFLAGS and CXXFLAGS. CFLAGS is for the C compiler while CXXFLAGS is for the C++.

Now that we have the CPU-specific flags worked out, let’s discuss additional optimization flags. One of the most important flags you’ll set is the optimization flag (-O). There are technically five levels of optimization, but I’ll discuss only three: -O1, O2 and O3. Each of these levels represents GCC’s attempt to optimize the code produced from the source files. I’ll briefly explain these:

• O1 – Level one optimizes common features that don’t require speed-space tradeoffs. The executable produced by this level is usually faster and smaller in size than with no optimization.
• O2 – Level two includes level one optimizations and many, many others. Generally speaking, this is the route to go with on most distributions.
• O3 – This includes all optimization possibilities. Be careful using this one as is produces a rather large executable. While in some cases this may produce faster code, it has had the opposite affect with me. The only way to truly know if you should choose level two or three is trial and error. Compile the same version of MySQL with both flags and test which is faster.

The other two are O0 or no optimization and Os optimizes to produce the smallest possible executable for systems constrained by memory and disk.

For a full list of features that are optimized by level, see this image.

The next flag is "pipe". Pipe directs GCC to take piped input and use RAM whenever possible rather than disk. In most cases, you won’t see a big performance gain from this, but we’re trying to achieve maximum results. Next we look at C++ flags.

There are two additional flags passed to the C++ compiler in conjunction with those mentioned above: no-exceptions and no-rtti.

The flag no-exceptions removes error handling as it relates to exceptions in the produced C++ code. MySQL actually recommends this flag in order to increase the stability of the produced executable.

Finally we come to no-rtti. The answer to this is simple: MySQL doesn’t use RTTI, so the result is a smaller and potentially faster executable.

Our CFLAGS and CXXFLAGS declaration will look like this for Intel:

CFLAGS="-O2 -msse4 -pipe" CXXFLAGS="${CFLAGS} -fno-exceptions -fno-rtti"

and this for generic AMD:

CFLAGS="-O2 --march=athlon64 -pipe" CXXFLAGS="${CFLAGS} -fno-exceptions -fno-rtti"

Go ahead and execute that in your shell, setting the environmental variables. If you’d like to test that they’re valid, issue the following commands:

echo $CFLAGS
echo $CXXFLAGS

Executing the Configure Script

The configure scripts works to produce a valid makefile. The makefile contains the instructions to compile, install and clean the MySQL distribution you’re working with. There are some important options to pass to this script that will produce a faster binary. Not all fall under this category, and I’ll discuss each option shown in the example below:

./configure \
  --prefix=/opt/mysql-5.1.52 \
  --enable-static \
  --with-charset=utf8 \
  --enable-thread-safe-client \
  --without-debug \
  --with-fast-mutexes \
  --enable-assembler \
  --with-plugins=innobase,partition

Prefix (–prefix) creates a variable that prefixes all installation directives. In the example above, we use /opt/mysql-5.1.52. This means that all executables, man pages, directories and scripts will be placed inside that specified directory.

Enable static (–enable-static) enables static linking to components. There is a trade-off with this. While it produces a faster executable, the footprint of the file is much larger. We will be able to dramatically reduce that later, however.

Setting the character set to UTF-8 (–with-charset=utf8) allows us to support multiple languages and characters outside of the default Latin character set. This is optional and by far not an optimization technique. It could easily be argued that this has the opposite affect, but if you plan to support a global audience, I’d suggest choosing this option.

Enabling a thread-safe client (–enable-thread-safe-client) produces the client software such as mysql, mysqladmin, mysqldump, etc. with thread-safe capabilities.

Disabling debugging (–without-debug) is an obvious performance booster. We most definitely don’t want MySQL to be worrying about providing that kind of information on a production system.

Fast mutexes (–enable-fast-mutexes) is another one of those trial and error situations. Depending on your machine, fast mutexes may or may not provide benefit. As with optimization level three, you will have to experiment to determine whether you should use this option.

Using an assembler (–enable-assembler) tells MySQL to use those versions of some string functions. This provides some optimization but not a lot.

You need to determine which plugins (–with-plugins) to use with your distribution. This is an application-level decision and the less you use the better off you are. InnoDB is the premier transactional engine for MySQL, so I’d highly suggest enabling and using it. The partition plugin allows you to scale your tables based on your defined criteria.

Now that we’ve prepped our software, it’s time to compile and install! This is my favorite part. I truly enjoy watching the makefile instruct GCC to chunk the source code and produce the fastest possible software. This makes me a super-dork, but it is what it is.

Execute the following command in your shell:

make

This starts the compilation of the software. Get up, stretch your legs and grab some coffee. This is going to take a while.

Once the compilation is complete, we’re ready to install the software. This is an easy one:

make install

List your /opt directory to ensure the software is good to go:

ls -al /opt

strip /opt/mysql-5.1.52/libexec/mysqld

You can also execute this on any binary file within your new distribution, but mysqld is certainly the most important.

Activating the New MySQL Version

Activating the new MySQL version is easy:

• Stop the running MySQL instance:

  /opt/mysql/bin/mysqladmin shutdown

• Destroy the existing symbolic link:

  rm /opt/mysql

• Create a new symbolic link that points to the new version:

  ln -s /opt/mysql-5.1.52 /opt/mysql

• Start MySQL via /opt/mysql/bin/mysqld_safe:

  /opt/mysql/bin/mysqld_safe --user=mysql &

There are additional optimization techniques you take use when configuring your MySQL instance (such as CPU priority), but I’ll save that for another entry.

1. Optimizing Your MySQL Compilation | Will Fitch's Blog | mysql

   November 30, 2010 at 1:33 PM

   [...] Optimizing Your MySQL Compilation | Will Fitch's Blog cause-, database-machines-, emergency-rollback-, feature-will, includes, includes-keeping, [...]

   Reply