Setup a regular analyze and optimization / defragmentation of MySQL tables

This howto is tested on :

• Debian 6.0 Squeeze
• Debian 7.0 Wheezy

Prerequisites

This howto recommends:

• a MySQL server, as described by Install and setup MySQL on Debian.

Installation

Download the mysql-defragger script:

command wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/mysql-defragger' \
 --quiet --no-check-certificate --output-document='/usr/bin/mysql-defragger'
command chmod +x '/usr/bin/mysql-defragger'

mysql-defragger allow to run selective optimizations on MySQL tables.

Run-once maintenance

InnoDB tables

Note : It is impossible to compute a InnoDB table fragmentation. The fragmentation of InnoDB tables is detected by requests performance drop and the raise of disk accesses.

The maintenance of InnoDB tables must be done cautiously. The --optimize option the mysql-defragger tool recreate the InnoDB tables. This process will duplicate the table, and drop the original one. This is an effective defragmentation of the table. The downsides are :

• The table is locked for all of process duration.
• The disk space used by the table datas is doubled.

Use this action only on databases with small to medium InnoDB tables, or you encounter the risk to overload the system, and break the software using the table for the duration of the process.

It is nevertheless interesting to optimize InnoDB tables every 6 months, during a planned server maintenance. To optimize InnoDB tables, use (danger !) :

command mysql-defragger --innodb --optimize

In general, it is best to simply analyze InnoDB tables :

command mysql-defragger --innodb --analyze

MyISAM tables

MyISAM tables grain fragmentation witch each DELETE request. Defragment MyISAM tables with :

command mysql-defragger --myisam --optimize

Regular maintenance

I use the following settings for MySQL server keeping. These are my current settings and they may no be perfect. They are nevertheless working :

• Weekly analyze of MyISAM and InnoDB tables.
• Monthly optimization of MyISAM tables only.

I manually run the optimization of InnoDB tables.

Provide the root password for MySQL :

command read -s -p "Enter password: " MYSQL_PASSWORD

Create the .my.cnf configuration file for root user in your account :

if [ ! -e "${HOME}/.my.cnf" ]; then
  echo "[client]
user=root
password=${MYSQL_PASSWORD}" \
    > "${HOME}/.my.cnf"
  command chmod 400 "${HOME}/.my.cnf"
fi

Setup cron to run a regular maintenance of the MySQL server :

echo "# /etc/cron.d/mysql-defragger: crontab fragment for mysql-defragger
#  Weekly run analyze on all MySQL tables (mysqlcheck is faster and do the same).
# 04 2    * * 7    ${USER}    [ -x /usr/bin/mysql-defragger ] && /usr/bin/mysql-defragger --analyze -q -q
04 2    * * 7    ${USER}    [ -x /usr/bin/mysqlcheck ] && /usr/bin/mysqlcheck --all-databases --analyze --silent
#  Monthly optimize MyISAM tables.
04 3    * * 7    ${USER}    [ -x /usr/bin/mysql-defragger ] && /usr/bin/mysql-defragger --myisam --optimize -q -q" \
    > '/etc/cron.d/mysql-defragger'

Reload cron configuration :

/etc/init.d/cron reload

References

These books can help you:

• Understanding MySQL Internals (en)
• MySQL High Availability: Tools for Building Robust Data Centers (en)
• High Performance MySQL: Optimization, Backups, Replication, and More (en)
  

Thanks

• Thanks to Phil Dufault (en) for developing MySQLFragFinder (en) (A Defragmentation Script to Optimize Fragmented Tables in MySQL (en)).