Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 7 Optimization :: 7.5 Optimizing the MySQL Server :: 7.5.1 How Compiling and Linking Affects the Speed of MySQL
7.5.2 System Factors and Startup Parameter Tuning »
Section Navigation      [Toggle]

7.5.1. How Compiling and Linking Affects the Speed of MySQL

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

You obtain the fastest executables when you link with -static.

On Linux, it is best to compile the server with pgcc and -O3. You need about 200MB memory to compile sql_yacc.cc with these options, because gcc or pgcc needs a great deal of memory to make all functions inline. You should also set CXX=gcc when configuring MySQL to avoid inclusion of the libstdc++ library, which is not needed. Note that with some versions of pgcc, the resulting binary runs only on true Pentium processors, even if you use the compiler option indicating that you want the resulting code to work on all x586-type processors (such as AMD).

By using a better compiler and compilation options, you can obtain a 10–30% speed increase in applications. This is particularly important if you compile the MySQL server yourself.

When we tested both the Cygnus CodeFusion and Fujitsu compilers, neither was sufficiently bug-free to allow MySQL to be compiled with optimizations enabled.

The standard MySQL binary distributions are compiled with support for all character sets. When you compile MySQL yourself, you should include support only for the character sets that you are going to use. This is controlled by the --with-charset option to configure.

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

  • 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 dynamically linked MySQL library for your client applications. It is the server that is most critical for performance.

  • For a connection from a client to a server running on the same host, if you connect using TCP/IP rather than a Unix socket file, performance is 7.5% slower. (On Unix, if you connect to the host name localhost, MySQL uses a socket file by default.)

  • For TCP/IP connections from a client to a server, connecting to a remote server on another host is 8–11% slower than connecting to a server on the same host, even for connections faster than 100Mb/s Ethernet.

  • When running our benchmark tests using secure connections (all data encrypted with internal SSL support) performance was 55% slower than with unencrypted connections.

  • If you compile with --with-debug=full, most queries are 20% slower. Some queries may take substantially longer; for example, the MySQL benchmarks run 35% slower. If you use --with-debug (without =full), the speed decrease is only 15%. For a version of mysqld that has been compiled with --with-debug=full, you can disable memory checking at runtime by starting it with the --skip-safemalloc option. The execution speed should then be close to that obtained when configuring with --with-debug.

  • On a Sun UltraSPARC-IIe, a server compiled with Forte 5.0 is 4% faster than one compiled with gcc 3.2.

  • On a Sun UltraSPARC-IIe, a server compiled with 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 -mcpu=v8 -Wa,-xarch=v8plusa options gives 4% more performance.

  • On Solaris 2.5.1, MIT-pthreads is 8–12% slower than Solaris native threads on a single processor. With greater loads or more CPUs, the difference should be larger.

  • Compiling on Linux-x86 using gcc without frame pointers (-fomit-frame-pointer or -fomit-frame-pointer -ffixed-ebp) makes mysqld 1–4% faster.

Binary MySQL distributions for Linux that are provided by us used to be compiled with pgcc. We had to go back to regular gcc due to a bug in pgcc that would generate binaries that do 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 build a faster binary by compiling with pgcc. The standard MySQL Linux binary is linked statically to make it faster and more portable.


User Comments

Posted by P Otto on January 8 2008 9:57am[Delete] [Edit]

I benchmark my MySQL database regularly using the sysbench facility and I have been testing various software configurations on the same box trying to achieve the best performance. Having found this reference I wish to share my test results I produced over a few months, testing performance while using various compilation options.

The host used for testing was http://www.securitybay.co.uk/ running RedHat Enterprise/Linux Kernel 2.6.9-55.0.2.ELsmp and the 5.0.45 MySQL with the following hardware configuration:
- Dual Intel Xeon Processor 3.4GHz with 2MB Cache
- 8 GB RAM
- Dual 136 GB SCSI Drive

Tests were performed while MySQL server was under insignificant load generated by the Internet traffic during night hours, utilizing no more than a fraction of per cent of the server power at the testing time.

I got these interesting results showing that using pgcc instead of gcc can have a significant impact on the Linux/MySQL server performance.

The testing application was sysbench with the maximum number of requests limited to 10000 for testing.

Benchmark results for the out-of-the box, pre-compiled RedHat Enterprise/MySQL 5.0.45:

OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (183.60 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (3018.38 per sec.)
other operations: 20000 (307.20 per sec.)

Test execution summary:
total time: 62.5700s
total number of events: 10000
total time taken by event execution: 62.2566
per-request statistics:
min: 0.0026s
avg: 0.0062s
max: 0.4933s
approx. 95 percentile: 0.0085s

Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 62.2566/0.00

Benchmark results for RedHat Enterprise/MySQL 5.0.45 compiled with pgcc and everything with -O6:

OLTP test statistics:
queries performed:
read: 140000
write: 50000
other: 20000
total: 210000
transactions: 10000 (193.30 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 190000 (3201.42 per sec.)
other operations: 20000 (329.70 per sec.)

Test execution summary:
total time: 59.6500s
total number of events: 10000
total time taken by event execution: 59.3844
per-request statistics:
min: 0.0020s
avg: 0.0061s
max: 0.4522s
approx. 95 percentile: 0.0081s

Threads fairness:
events (avg/stddev): 10000.0000/0.00
execution time (avg/stddev): 59.3844/0.00

The above shows a slight, but visible performance gain when using pgcc instead of the gcc compiler. Further tests only confirmed this observation, and the overall performance increase was between 2% to 6% for the same hardware.

There is a built-in support for some Intel Pentium-specific operations in pgcc compiler, which allow code to be compiled with various Pentium optimizations. Thus the execution speed of the binaries is higher on a Pentium box.

P Otto

Add your own comment.