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?

Tuning MySQL server

After migrating the data from MaxDB to MySQL 5 [Ver 14.12 Distrib 5.0.62, for redhat-linux-gnu (x86_64) using readline 5.1], I've found our application's performance dropped a little bit. Now as every DB admin might do, I am going to start tuning the MySQL server. Here is my plan:

1. Get current DB status/parameters

2. Develop a benchmark suit before tuning

3. Set up a monitoring system to measure if my tuning improves or hurt the performance

4. Enable slow queries logging

5. Tune SQL queries of my app

6. Version control my.cnf and symbol link it.

7. Maybe, just maybe, order new hardware if necessary.

Migrating from MaxDB(SapDB) to MySQL

I spent some time to migrate data from MaxDB to MySQL today. I googled a bit, but nothing seems working for me. So I have to do it in my way. I've been working with SapDB and MaxDB for a while, I am pretty happy with it. The performance is very good, and the maintenance is easy too. The reason I have to migrate is because I want to give DataMapper(Ruby) + Merb a spin. There is no adapter that are satisfying my need right now, and on the other hand, I am really too busy to write one by myself. Since I am pretty familiar with MySQL, I decided to migrate our MaxDB production database server to MySQL, just for testing for the first phase, we have too many lines of Java code to be migrate too.

Here are the steps, I am hoping this is helpful if you have the same situation like mine.

1. Export MaxDB schema(catalog) and data

a. Schema

/opt/sdb/programs/bin/loadercli -b export_catalog.cmd -d MYPRODUCTIONDB -u MYDBADMIN,MYPASSWD -E 20

the export_catalog.cmd looks like:

EXPORT DB

CATALOG OUTSTREAM 'MYPRODUCTIONDB.ddl'

b. Data

/opt/sdb/programs/bin/loadercli -b export_data.cmd -d MYPRODUCTIONDB -s PRODUCTION_SCHEMA -u USER,PASSWD -E 20 -cs \;

the export_data.cmd looks like:

EXPORT TABLE PRODUCTION_SCHEMA.PURCHASE_ORDERS

DATA OUTSTREAM 'PURCHASE_ORDERS.data';'

EXPORT TABLE PRODUCTION_SCHEMA.PURCHASE_ORDER_LINE_ITEMS

DATA OUTSTREAM 'PURCHASE_ORDER_LINE_ITEMS.data';

...

2. Modify the MaxDB schema to satisfy MySQL grammar, and import into MySQL server.

There are some different between MaxDB and MySQL, you have to modify before you can import the ddl file into MySQL. You have to be very careful about  DEFAULT SERIAL (1)/auto_increment, Integer/INT, fixed/decimal, timestamp...

a. Create db in MySQL

$mysql -h hostname -uroot -pPasswd

>create database MYPRODUCTIONDB character set utf8;

>quit

b. Create schema

$mysql -h hostname -uroot -pPasswd MYPRODUCTIONDB < MYPRODUCTIONDB.ddl

3. Format data before you import into mysql

Because I have too many tables(200+), I write a simple shell script to do that for me

format.sh

#!/bin/bash

for file in *.data; do

 vim -e -s $file < format.vim

done

format.vim

:%s/?/NULL/g

:%s/FALSE/0/g

:%s/TRUE/1/g

w

q

This script will format all the MaxDB data file(*.data) in current folder with the vim. It depends on your data, this could be very time consuming, on my 64-bit RH with 16G memory, it took about 4 hours to get this done. So grab a cup of coffee, chat with your favorite girl in office ...

4. Import data into mysql

Now it is time to get the data imported, for the same reason, I am using a shell script

import.sh

#!/bin/bash

for file in *.data; do

 mysqlimport  -h hostname -uroot -pPasswd --fields-optionally-enclosed-by="\"" --fields-terminated-by=, --local MYPRODUCTIONDB $file

done

That is pretty much all of the steps. If you have some columns in your MaxDB with NULL value, you might have to dump the database from mysql again, substitute them all, then import back again.

4th week

Elaine is growing so fast. She put on some weight, her arms and legs are even stronger than before. Now, she likes playing a bit...

Kicking is her favorite exercise...

Sometimes want to talk a while...
Or just staring the toys...

Can I play, Dad? Please...

The umbilical cord

Today is a big day, finally my baby girl's umbilical cord fell off - it was one week longer than normal. Otherwise she is totally healthy :-)

Sleeping in her crib: