How to tune performance of MySQL server

1. Get the latest MySQL Server(5.1)

    a. Download the src from mysql website

   b. Remove the old mysql installation

       - backup you data (mysqldump) first

       - if you compiled and install from src before, go to the src tree

          $sudo make uninstall

       - $sudo launchctl unload -w /Library/LaunchDaemons/com.mysql.mysqld.plist

       - $sudo rm /Library/LaunchDaemons/com.mysql.mysqld.plist

    c. Go to you src tree

       - config (note: I am compiling it with innodb support)

           CC=gcc CFLAGS="-O3 -fno-omit-frame-pointer" CXX=gcc \

CXXFLAGS="-O3 -fno-omit-frame-pointer -felide-constructors \

-fno-exceptions -fno-rtti" \

./configure --prefix=/usr/local/mysql \

--with-extra-charsets=complex --enable-thread-safe-client \

--enable-local-infile --disable-shared --with-innodb

      - $make

      - $sudo make install

    d. Create default database

        $cd /usr/local/mysql

$sudo ./bin/mysql_install_db --user=mysql

$sudo chown -R mysql ./var

    e. Create launchd item

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE< plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">

<plist version="1.0">

<dict>

<key>KeepAlive</key>

<true/>

<key>Label</key>

<string>com.mysql.mysqld</string>

<key>Program</key>

<string>/usr/local/mysql/bin/mysqld_safe</string>

<key>RunAtLoad</key>

<true/>

<key>UserName</key>

<string>mysql</string>

<key>WorkingDirectory</key>

<string>/usr/local/mysql</string>

</dict>

</plist>

      and save it to /Library/LaunchDaemons

$sudo chown root /Library/LaunchDaemons/com.mysql.mysqld.plist

   f. start mysql

      $sudo launchctl load -w /Library/LaunchDaemons/com.mysql.mysqld.plist

   g. pump in your backup date

       you probably want to use the innodb engine, so you have to modify your 

         backup data if you were using MyISAM engine:

      $sed -e "s/MyISAM/INNODB/g" -i bak YOUR_DUMPED_DATA_FILE

      then import in with mysql command

NOTE: I would like to give the credit to Dan Benjamin for how to installing mysql

http://hivelogic.com/articles/2007/11/installing-mysql-on-mac-os-x

2. Enable query log for both all and slow

    a. find which my.cnf you are using:

        $mysqld --verbose --help | grep -A 1 'Default options'

         NOTE: you might have to set /usr/local/mysql/bin on your path 

         and symbol link the mysqld from /usr/local/mysql/libexec to 

            /usr/local/mysql/bin

    b. I am using /etc/my.cnf

          log = /var/log/mysql/all-queryies.log

        log-slow-queries = /var/log/mysql/slow-queries.log

long_query_time = 1 # this is second

log-queries-not-using-indexes

log-slow-admin-statements

      Those variables also will put the queries who are not using indexes into the slow log.

   c. change owner

        $mkdir /var/log/mysq

         $touch /var/log/mysql/all-queryies.log

        $touch /var/log/mysql/slow-queries.log

        $chown -R mysql /var/log/mysql

    d. Restart the mysql server

         $mysqladmin shutdown

        then the launchd will restart it automatically

    e. Verify the new config took effectived

              $mysqladmin variables | grep slow

                  | log_slow_queries       | ON               |

                  | slow_launch_time      | 2                   |

                   | slow_query_log          | ON               |

                  | slow_query_log_file | /var/log/mysql/slow-queries.log

              $tail -f /var/log/mysql/slow-queries.log

3. Install monitoring tools

    There are a lot of tools you can choose from. But my favorite one is innotop

     a. Install DBD by CPAN

         $sudo perl -MCPAN -e 'install Bundle::DBD::mysql'

   b. Above step will fail on my Leopard, but you can get ~/.cpan directory

         $cd ~/.cpan/build/DBD-mysql-4.008

    c. Do this to get you make through

         $sudo ln -s /usr/local/mysql/lib /usr/local/mysql/lib/mysql

         $sudo ln -s /usr/local/mysql/include /usr/local/mysql/include/mysql

    d. Make and install

         $perl Makefile.PL

         $sudo make install

    e. Verify

         $innotop

4. Tuning mysql variables and your query statements

Well, this is really something really hardware, application, query statements specific. 

You have to do it by yourself with the tools and logs. But I'v found the following 

variables really matters

       - innodb_buffer_pool_size

      - thread_cache_size

      - key_buffer_size

      - sort_buffer_size

      - query_cache_size

      - read_buffer_size

Do not forget to restart you mysql server after you make any change. I would suggest

you using a version control system to log your my.cnf history.

And for my application and database, adjust following really boots up the performance

       - Adding indexes for the queries who are executing most

        - think before you use SELECT COUNT(*), if you are querying too much data?