After reading part one and two of our Database Normalization and MySQL series, we could use a little follow up.In this article, we'll discuss those facets of optimizing a MySQL server that relate directly to it's compilation, configuration and subsequent administration.
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:
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:
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.