You are here: Home / Debian GNU/Linux / Servers / MySQL / Setup a regular analyze and optimization / defragmentation of MySQL tables

Setup a regular analyze and optimization / defragmentation of MySQL tables

by Pierre-Yves Landuré last modified Nov 14, 2017 01:39

With the flow of inserts and deletes, the MySQL tables performances can drop. Regularly optimize and analyze MySQL tables allow to rebuild the indexes and limit the disk space used by MySQL on the server.

This howto is tested on :

  • Debian 6.0 Squeeze
  • Debian 7.0 Wheezy


This howto recommends:


Download the mysql-defragger script:

command wget '' \
--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]
password=${MYSQL_PASSWORD}" \
    > "${HOME}/.my.cnf"
command chmod 400 "${HOME}/.my.cnf"

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


These books can help you: