Configurer l'analyse et l'optimisation / défragmentation régulière des tables MySQL

Ce guide est testé sur:

• Debian 6.0 Squeeze
• Debian 7.0 Wheezy

Prérequis

Ce guide nécessite:

• un serveur MySQL, comme décrit par Installer et configurer MySQL sur Debian.

Installation

Détectez le proxy de commande (command ou sudo):

cmdProxy='command'
command type -f 'sudo' &>'/dev/null' && cmdProxy='sudo'

Téléchargez le script mysql-defragger:

${cmdProxy} wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/mysql-defragger' \
 --quiet --no-check-certificate --output-document='/usr/local/bin/mysql-defragger'

Rendez-le executable:

${cmdProxy} chmod +x '/usr/local/bin/mysql-defragger'

mysql-defragger permet d'effectuer des actions sélective d'optimisation des tables MySQL.

Maintenance ponctuelle

Tables InnoDB

A savoir : Il est impossible de calculer la fragmentation d'une table InnoDB. La fragmentation se détecte par la baisse de performance des requêtes et des accès disques plus nombreux.

La maintenance des tables InnoDB doit être effectuée prudemment. L'option --optimize de mysql-defragger va recréer les tables InnoDB. Ce processus entraîne une duplication de la table, puis la suppression de l'original. Cet action défragmente effectivement la table. Les inconvénients sont les suivants:

• La table est verrouillée pour toute la durée du processus.
• L'espace disque utilisé par les données de la table est doublé.

N'effectuez cette action que sur les bases n'utilisant pas des tables InnoDB de taille très importante, sous peine de surcharger le système, et rendre non fonctionnelles les applications utilisant cette table.

Il est toutefois intéressant d'effectuer une optimisation des tables InnoDB tous les 6 mois environ, lors d'une maintenance serveur programmée. Pour optimiser les tables InnoDB, utilisez (danger !):

command mysql-defragger --innodb --optimize

D'une manière générale, il est préférable de se contenter d'une analyze des tables InnoDB:

command mysql-defragger --innodb --analyze

Tables MyISAM

Les tables MyISAM fragmentent facilement avec les suppression fréquentes (requêtes DELETE). Pour défragmenter les tables MyISAM, utilisez:

command mysql-defragger --myisam --optimize

Maintenance régulière

J'utilise les réglages suivants pour la maintenance d'un serveur MySQL. Ce sont mes réglages actuels et ils ne sont peut être pas pertinents. Ils sont toutefois fonctionnels:

• Analyse hebdomadaire des tables MyISAM et InnoDB.
• Optimisation mensuelle des tables MyISAM uniquement.

J'effectue manuellement l'optimisation des tables InnoDB.

Renseignez le mot de passe root pour MySQL:

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

Créez le fichier .my.cnf de l'utilisateur root de MySQL dans votre compte:

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

Configurez cron pour lancer la maintenance régulière du serveur MySQL:

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/local/bin/mysql-defragger ] && /usr/local/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/local/bin/mysql-defragger ] && /usr/local/bin/mysql-defragger --myisam --optimize -q -q" \
    | ${cmdProxy} tee '/etc/cron.d/mysql-defragger'

Rechargez la configuration de cron:

${cmdProxy} service cron reload

Références

Ces livres peuvent vous aider:

• High Performance MySQL (en)
• MySQL High Availability: Tools for Robust Data Centers (en)

Remerciements

• Merci à Phil Dufault (en) pour le développement de MySQLFragFinder (en) (A Defragmentation Script to Optimize Fragmented Tables in MySQL (en)).