Quelques optimisations MySQL pour un Drupal 8 performant

Que celui qui n'a jamais vu un serveur MySQL avec ses paramètres par défaut sur un serveur Quadri Coeur et 128 Go de RAM lève la main.

Il n'est pas rare que le serveur MySQL soit oublié à la mise en place d'un projet, Drupal 8 ou autres. Découvrons quelques paramètres de base à affiner selon votre environnement et votre projet pour booster les performances de votre site ou application Internet.

Les valeurs indiquées ci-dessous sont bien entendu à ajuster en fonction de votre projet et des ressources disponibles sur votre serveur. Disons qu'elles peuvent constituer un minimum pour des performances sans commune mesure avec les paramètres par défaut..

[mysqld]
# Nombre de connexions simultanées
max_connections=100
# Total query cache size
query_cache_size=128M
# Taille limite pour cachez une simple requête
query_cache_limit=1M
# nombre de tables à garder en cache
table_open_cache=3000
# nombre de définitions de table à garder en cache
table_definition_cache=2500
# Taille des tables temporaires à garder en mémoire
tmp_table_size=150M
# Taille maximum des HEAP des tables en mémoire
max_heap_table_size=150M
# Taille du cache pour les key/index
key_buffer_size=96M
# Taille du cache pour les jointures
join_buffer_size=128K
# Taille maximum des packets envoyés à MySQL 
max_allowed_packet=16M

# Enregistrer les requêtes lentes
slow_query_log                  = 1
slow_query_log_file             = /var/log/mysql/slow.log
long_query_time                 = 5

# Taille du buffer InnoDB. Si vous avez un serveur MySQL dédié avec beaucoup de mémoire
# vous pouvez le paramétrer avec toute la mémoire disponible (avec une réserve pour le système),
# ou sinon, en tout cas, avec suffisamment de mémoire pour y charger toutes les données.
innodb_buffer_pool_size=2048M
# Si vous n'avez pas besoin du modèle ACID, ce paramètre peut être mis à 0. Cela signifie que vous perdrez les écritures
# des dernières secondes (1-2 s) en cas de problème, mais rend beaucoup plus performant et rapide MySQL.
innodb_flush_log_at_trx_commit=0
# Taille des fichiers de log. Attention le serveur MySQL doit être arrêté afin de pouvoir changer ce paramètre.
innodb_log_file_size=512M

# Divers paramètres d'optimisation. innodb_file_per_table permet de stocker chaque table dans un fichier distinct et
# ainsi si vous supprimez un table ou une base de données, celle-ci sera effectivement supprimée. Dans le cas contraire
# la table ou la base de données restera stockée dans le fichier général des data de MySQL.
innodb_file_per_table
innodb_log_buffer_size=4M
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT

# Ignorer les résolutions de noms lentes.
skip-name-resolve

A noter le cas particulier du paramètre innodb_log_file_size qui nécessite une procédure spécifique pour le changer

1. Arrêter le serveur MySQL
2. Déplacer les fichiers de log existants (ib_logfile0 et ib_logfile1) dans un répertoire quelconque de sauvegarde, pour être en mesure de les restaurer au cas où
3. Modifier le paramètre innodb_log_file_size et lui attribuer une valeur suffisamment grande pour y enregistrer au moins une heure de logs des transactions (voir Comment calculer une bonne taille pour vos fichiers de log)
4. Démarrer le serveur MySQL. Celui va récréer les fichiers de log, avec la nouvelle taille paramétrée
5. Vous pouvez supprimer les anciens fichiers de log que vous aviez déplacé dans un répertoire de sauvegarde

A noter également que le paramètre innodb_file_per_table permettant de modifier le mode de stockage des bases de données et tables sur disque. Ce paramétrage doit être réalisé dès l'installation du serveur MySQL. Modifier ce paramètre le serveur MySQL arrêté. Si vous souhaitez changer ce paramètre pour des bases de données existantes, il n'y a pas 36 solutions. Il faut sauvegarder toutes vos bases de données, tout supprimer sur votre serveur MySQL, modifier le paramètre, relancer le serveur MySQL puis restaurer vos bases de données. Autant dire que si vous y pensez avant de mettre en production votre serveur MySQL, cela vous fera quelques suées en moins.

Avec ces quelques paramètres, vous obtiendrez une réelle différence au niveau des performances de votre projet, comparé avec les paramètres par défaut de MySQL.