Optimiser MySQL sur Debian

Ce guide est testé sur:

• Debian 6.0 Squeeze
• Debian 7.0 Wheezy

À savoir

L'optimisation d'un serveur MySQL pour le matériel sur lequel il est installé est la cerise sur le gâteau du travail d'optimisation. Le nerf de la guerre est l'optimisation des requêtes SQL et de la structure des bases de données.

Ces articles décrivent les bonnes pratiques de développement MySQL:

• Optimiser MySQL (fr)
• 10 Tips for Optimizing MySQL Queries (That don't suck) (en)
• MySQL Optimization Hints (en)
• The MySQL Query Cache (en)
• MySQL Performance Blog (en)

L'optimisation de la configuration du serveur ne remplace pas l'utilisation de requêtes bien conçues !

Prérequis

Ce guide nécessite:

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

Ce guide recommande:

• la disponibilité d'une importante quantité de RAM. Suivant la charge du serveur MySQL, la quantité recommandée peut varier de 1 Go à 8 Go de mémoire vive attribués à MySQL.
• la maintenance régulière des tables MySQL, comme décrit par Configurer l'analyse et l'optimisation / défragmentation régulière des tables MySQL.

Système de fichiers

Assurez-vous que le système de fichiers contenant le dossier "/var/lib/mysql" soit monté avec les options suivantes:

• noatime : Très important : désactive la mise à jour des dates d'accès des fichiers. Évite un ralentissement dû à trop d'accès disque.
• nodiratime : Même chose que noatime pour les dossiers.
• barrier=1 : Assure la validité des données.
• data=ordered : Inscrit les données sur le disque avant de mettre à jour le journal.

Soit dans /etc/fstab:

/dev/sda3   /var    ext4    defaults,noatime,nodiratime,barrier=1,data=ordered                0       1

Activez l'écriture en temps réel du binlog MySQL pour assurer la pérénité des données, et écrivez les données des tables InnoDB sur le disque à chaque commit:

${cmdProxy} tee '/etc/mysql/conf.d/io-optimisations.cnf' <<< "[mysqld]
# See https://serverfault.com/questions/363355/io-wait-causing-so-much-slowdown-ext4-jdb2-at-99-io-during-mysql-commit

# Enable binary log for point in time recovery.
# see : https://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html
# Note: set sync_binlog to 0 for better performances at the cost of perfect data integrity.
log_bin = /var/log/mysql/mysql-bin.log
sync_binlog=1

# Limit binary log size to prevent databases lock ups at binary log rotation.
# see : http://www.mysqlperformanceblog.com/2012/05/24/binary-log-file-size-matters/
max_binlog_size = 100M

# Write InnoDB data to disk at commit.
# Note: set to 2 for better performance at the cost of perfect data integrity.
innodb_flush_log_at_trx_commit = 1"

N'oubliez pas de recharger la configuration du serveur MySQL:

${cmdProxy} service mysql restart

Pour plus d'informations, lisez:

• What is the best Linux filesystem for MariaDB (en).
• Réponse de JinnKo à la question IO Wait causing so much slowdown (EXT4 JDB2 at 99% IO ) During Mysql Commit (en).

Diagnostic

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

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

MySQLTuner

MySQLTuner est un script Perl proposant des recommandations pour la configuration d'un serveur MySQL. Il fournit certaines informations intéressantes, mais me semble moins intéressant que l'outil Tuning Primer présenté dans la suite de cet article.

Important: Patientez au moins 48h après le démarrage du serveur MySQL pour utiliser cet outil, ceci afin que les données d'utilisation accumulées soient pertinentes.

Installez MySQLTuner:

${cmdProxy} wget 'https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl' \
    --quiet --no-check-certificate --output-document='/usr/local/bin/mysqltuner'

Rendez-le exécutable:

${cmdProxy} chmod +x '/usr/local/bin/mysqltuner'

Lancez MySQLTuner:

command mysqltuner

L'outil analyse la configuration du serveur MySQL et affiche les points pouvant être améliorés.

Tuning Primer

Tuning Primer est un script diagnostiquant la configuration du serveur MySQL et proposant des améliorations adaptées à l'utilisation qui en est faite.

Important: Patientez au moins 48h après le démarrage du serveur MySQL pour utiliser cet outil, ceci afin que les données d'utilisation accumulées soient pertinentes. Vérifiez toutefois immédiatement la consommation mémoire maximale du serveur après un redémarrage.

Installez les outils nécessaires à Tuning Primer:

${cmdProxy} apt-get install bc

Installez Tuning Primer:

${cmdProxy} wget 'http://www.day32.com/MySQL/tuning-primer.sh' \
    --output-document='/usr/local/bin/tuning-primer'

Rendez-le exécutable:

${cmdProxy} chmod +x '/usr/local/bin/tuning-primer'

Lancez Tuning Primer et répondez aux questions:

command tuning-primer

L'outil analyse la configuration du serveur MySQL et affiche les points pouvant être améliorés.

PHPMyAdmin

PHPMyAdmin est une application Web d'administration d'un serveur MySQL.

Vous pouvez trouver de précieuses recommandations sur la configuration du serveur MySQL dans l'onglet "État" disponible dans la page d'accueil de l'outil.

Optimisations

Ajustement de la configuration

Créez le fichier modèle des optimisations possibles (à compléter selon les résultats de TUNING Primer):

${cmdProxy} wget 'https://raw.github.com/biapy/howto.biapy.com/master/mysql/tuning.cnf' \
    --quiet --no-check-certificate --output-document='/etc/mysql/conf.d/tuning.cnf'

Modifiez le fichier conformément aux options recommandées par Tuning Primer et MySQL Tuner:

${cmdProxy} vim '/etc/mysql/conf.d/tuning.cnf'

Redémarrez MySQL:

${cmdProxy} service mysql reload

Patientez 48h, et utilisez à nouveau Tuning Primer et MySQLTuner pour vérifier que les réglages sont bon. Vérifiez toutefois immédiatement à l'aide de Tuning Primer que la mémoire maximale utilisée par le serveur MySQL ne dépasse pas les limites du système.

Références

Ces livres peuvent vous aider:

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

Remerciements

• Merci aux développeurs de TUNING Primer (en).
• Merci aux développeurs de MySQLTuner (en).
• Merci à Ludovic de Serveur Linux (fr) pour MySQL 5.0 (fr).