Optimizing MySQL: Hardware and the Mysqld Variables

Part 1 – Compiling MySQL

Like a grand prix driver starting at the back of the track, if
you don’t do things well from the beginning, and prepare hard for
the race, it doesn’t matter how well you do once the race has
started. You’re at the back and are never going to win. So, with
MySQL, it’s extremely
important to start off on the right foot, and that means
compiling MySQL properly. The specific options you use depends on
your setup of course, but here are some pointers to get you
started.

  • Firstly, compile statically (–static). This requires more
    disk space, but runs faster (13% on Linux, according to MySQL
    themselves)
  • Optimize to the highest level possible (-O3 with gcc)
  • Compile without debug (–without-debug). This runs 20-35% faster.
  • Compile without frame pointers. (-fomit-frame-pointer). This
    is from 1-4% faster.
  • Compile with only the minimum character sets that you need
    (e.g. –with-extra- charsets=none)

Here’s a sample configuration I’ve seen work well:

	CFLAGS="-mcpu=i686 -march=i686 -O3 -pipe -fomit-frame-pointer" 
./configure 
        --prefix=/usr/local/build/mysql 
        --with-mysqld-user=mysql 
        --without-debug 
        --with-client-ldflags=-all-static 
        --with-mysqld-ldflags=-all-static 
        --disable-shared 
        --localstatedir=/usr/local/build/mysql/var 
        --with-extra-charsets=none 
        --enable-assembler

Note that if you’re using a Pentium processor, using pgcc seems
to give better results than gcc. There have been problems with
pgcc and AMD processors though. For a list of complete gcc
options, run:

man gcc

Taking the time to compile as well as possible for your setup is
worth it. After all, you’ll probably only do this once, but it
can hamstring your server for life if you get it wrong (it’s not
fun recompiling after your server is setup and running!)

Ian Gilfillan
Ian Gilfillan
Ian Gilfillan lives in Cape Town, South Africa. He is the author of the book 'Mastering MySQL 4', published by Sybex, and has been working with MySQL since 1997. These days he develops mainly in PHP and MySQL, although confesses to starting out with BASIC and COBOL, way back when, and still has a soft spot for Perl. He developed South Africa's first online grocery store, and has developed and taught internet development and other technical courses for various institutions. He has majors in Programming and Information Systems, as well as English and Philosophy. For 5 years he was Lead Developer and IT Manager for Independent Online, South Africa's premier news portal. However, he has now 'retired' from fulltime work, and is hoping that his next book will be more in the style of William Blake and Allen Ginsberg.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles