5.5.3 How Compiling and Linking Affects the Speed of MySQL

Most of the following tests are done on Linux with the MySQL benchmarks, but they should give some indication for other operating systems and workloads.

You get the fastest executable when you link with -static.

On Linux, you will get the fastest code when compiling with pgcc and -O3. To compile `sql_yacc.cc' with these options, you need about 200M memory because gcc/pgcc needs a lot of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library (it is not needed). Note that with some versions of pgcc, the resulting code will only run on true Pentium processors, even if you use the compiler option that you want the resulting code to be working on all x586 type processors (like AMD).

By just using a better compiler and/or better compiler options you can get a 10-30% speed increase in your application. This is particularly important if you compile the SQL server yourself!

We have tested both the Cygnus CodeFusion and Fujitsu compilers, but when we tested them, neither was sufficiently bug free to allow MySQL to be compiled with optimisations on.

When you compile MySQL you should only include support for the character sets that you are going to use. (Option --with-charset=xxx.) The standard MySQL binary distributions are compiled with support for all character sets.

Here is a list of some measurements that we have done:

• If you use pgcc and compile everything with -O6, the mysqld server is 1% faster than with gcc 2.95.2.
• If you link dynamically (without -static), the result is 13% slower on Linux. Note that you still can use a dynamic linked MySQL library. It is only the server that is critical for performance.
• If you strip your mysqld binary with strip libexec/mysqld, the resulting binary can be up to 4% faster.
• If you connect using TCP/IP rather than Unix sockets, the result is 7.5% slower on the same computer. (If you are connection to localhost, MySQL will, by default, use sockets.)
• If you connect using TCP/IP from another computer over a 100M Ethernet, things will be 8-11% slower.
• When running our benchmark tests using secure connections (all data encrypted with internal SSL support) things were 55% slower.
• If you compile with --with-debug=full, you will lose 20% for most queries. Some queries may take substantially longer (for example, the MySQL benchmarks ran 35% slower). If you use --with-debug, then you will only lose 15%. For a mysqld version that has been compiled with --with-debug=full, you can disable memory checking at runtime by starting it with the --skip-safemalloc option. The end result in this case should be close to when configuring with --with-debug.
• On a Sun UltraSPARC-IIe, Forte 5.0 is 4% faster than gcc 3.2
• On a Sun UltraSPARC-IIe, Forte 5.0 is 4% faster in 32 bit mode than in 64 bit mode.
• Compiling with gcc 2.95.2 for UltraSPARC with the option -mcpu=v8 -Wa,-xarch=v8plusa gives 4% more performance.
• On Solaris 2.5.1, MIT-pthreads is 8-12% slower than Solaris native threads on a single processor. With more load/CPUs the difference should get bigger.
• Running with --log-bin makes mysqld 1% slower.
• Compiling on Linux-x86 using gcc without frame pointers -fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp makes mysqld 1-4% faster.

The MySQL-Linux distribution provided by MySQL AB used to be compiled with pgcc, but we had to go back to regular gcc because of a bug in pgcc that would generate the code that does not run on AMD. We will continue using gcc until that bug is resolved. In the meantime, if you have a non-AMD machine, you can get a faster binary by compiling with pgcc. The standard MySQL Linux binary is linked statically to get it faster and more portable.

User Comments

Did anyone compile MySQL with Intel Compiler for Linux and did some comparing/benchmarking ?

> If you connect using TCP/IP from another computer over a 100M Ethernet, things will be 8-11% slower.

Slower than what? Slower than if connected to localhost via TCP/IP or locally via unix socket?

Add your own comment.