Optimizing MySQL - Compiling and Configuring MySQL
( Page 2 of 6 )

While many novice developers think of source compilation as a necessary evil, it also plays a significant role in the performance of the resulting compiled program. Compare this process to building the same model car on two different assembly lines. The first line is riddled with lazy workers, faulty assemblage equipment, and implements a poor assembly strategy. The second line consists of knowledgeable workers who use the best tools and strategies to ensure the best possible product. While on the outside, both resulting cars may look the same, it is likely that there will be vast differences in performance. The same concept applies to compilers. Some just do a much better job than their counterparts.

Also, you should carefully consider all available compiler options. Chances are very good that you either will not need several of the default options, or should consider modifying some of those options to better suit your particular needs.

With these points in mind, what exactly can you do to help make your MySQL database blazing fast? I’ll conclude this section with several tips:

By just using a better compiler and/or better compiler options you can get a 10-30% speed increase in your application." -- MySQL documentation

Use pgcc (Pentium GCC) is the GCC compiler
(http://www.gnu.org/software/gcc/gcc.html) optimized for those programs which will be used on systems using the Pentium processor. Using pgcc to compile the MySQL code will result in overall performance gains surpassing 10%! You can find more information about pgcc at http://www.goof.com/pcg/. Of course, if your server does not use the Pentium processor, don’t bother; pgcc was written specifically for Pentium systems (thus the name).

Compile MySQL only with the character sets you intend on using
MySQL currently offers quite 24 different character sets, offering users worldwide with the possibility to insert and view the table data in their native language. By default, MySQL installs all of these charsets, however, chances are pretty good that you’re only going to need one. You can disable all character sets except for the default ‘Latin1’ set by including the following option within the configuration line:

%>./configure -with-extra-charsets=none [--other-configuration-options]

Compile the mysqld executable statically
Compiling the mysqld executable without the shared libraries can also result in more efficient performance. You can compile mysqld statically by including the following option within the configuration line:

%>./configure -with-mysqld-ldflags=-all-static [--other-configuration-options]

Sample Configuration
The following configuration command is commonly used to speed performance:

%>CFLAGS="-O6 -mpentiumpro -fomit-frame-pointer" CXX=gcc CXXFLAGS="-O6
-mpentiumpro -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti" 
./configure --prefix=/usr/local --enable-assembler --with-mysqld-ldflags=-all-static 
--disable-shared

Explaining what exactly each of the gcc flags do is out of the scope of this article. However, all are explained in glorious detail within the gcc manual (http://gcc.gnu.org/), however I would not recommend curling up under the blankets with this one. Alternatively, you can review a list of all gcc options by executing man gcc at the command prompt.

>>> More MySQL Articles          >>> More By W.J. Gilmore