Home arrow MySQL arrow Page 2 - Optimizing MySQL

Compiling and Configuring MySQL - MySQL

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.

TABLE OF CONTENTS:
  1. Optimizing MySQL
  2. Compiling and Configuring MySQL
  3. Tuning The Server
  4. Table Types
  5. Even More Optimization...
  6. Conclusion
By: W.J. Gilmore
Rating: starstarstarstarstar / 9
January 29, 2001

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: