Blog ENI : Toute la veille numérique !
💥 Un livre PAPIER acheté
= La version EN LIGNE offerte pendant 1 an !
Accès illimité 24h/24 à tous nos livres & vidéos ! 
Découvrez la Bibliothèque Numérique ENI. Cliquez ici

Configuration du serveur

Introduction

Configurer correctement MySQL est essentiel pour assurer à la fois la performance et la stabilité du serveur. Plusieurs centaines d’options sont disponibles, ce qui rend difficile le choix des paramètres. Par ailleurs, dans de nombreux cas, il n’existe pas une seule bonne valeur que vous pouvez appliquer les yeux fermés : plusieurs valeurs sont acceptables pour une option et la meilleure configuration va dépendre de votre utilisation du serveur.

Cependant, obtenir une configuration saine de son serveur MySQL n’est finalement pas si compliqué. Tout d’abord, sachez qu’il n’existe pas de configuration idéale : si vos requêtes s’exécutent sans lenteur particulière et que le serveur ne montre jamais de signes de surcharge, cela signifie que les paramètres essentiels sont correctement ajustés. Ensuite, sachez que tous les problèmes de performances ne se résolvent pas seulement en modifiant la configuration : d’autres facteurs comme les ressources matérielles, le schéma des tables et les index ont également un impact majeur sur les performances. Enfin, gardez à l’esprit qu’à part une vingtaine de paramètres qu’il est absolument essentiel de connaître et de savoir ajuster, la plupart des options n’ont d’interêt que dans des cas spéciaux...

Généralités

1. Fichier de configuration

Le fichier my.cnf (ou my.ini sous Windows) est le fichier de configuration du serveur MySQL. Les programmes fournis par MySQL (mysqld, mysql, mysqldump...) viennent y chercher leurs directives. Sous Linux, ils recherchent automatiquement le fichier my.cnf dans les répertoires suivants : /etc/, /etc/mysql/ et ~/. Selon les distributions, d’autres répertoires supplémentaires pourront être utilisés. Par exemple, pour Debian, Ubuntu et leurs dérivés, il est possible d’ajouter des fichiers de configuration supplémentaires dans le répertoire /etc/mysql/conf.d. Vous pouvez visualiser la liste des répertoires dans lesquels le serveur cherchera le fichier my.cnf avec la commande mysqladmin --help. Le résultat est très long, mais vous trouverez une partie à l’allure suivante :

$ mysqladmin --help 
...  
Default options are read from the following files in the given order: 
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf  
[...] 

Sous Windows, le principe est similaire. Le serveur va chercher le fichier my.ini dans les répertoires suivants : WINDIR\my.ini, WINDIR\my.cnf, C:\my.ini, C:\my.cnf, INSTALLDIR\my.ini et INSTALLDIR\my.cnf, WINDIR étant le répertoire Windows (en général C:\WINDOWS) et INSTALLDIR le répertoire dans lequel MySQL est installé.

Si votre fichier ne se trouve pas dans l’un de ces répertoires ou s’il ne se nomme pas my.cnf, vous devrez alors indiquer aux programmes (mysqld, mysql, myisamchk...) son emplacement en le plaçant en paramètre lors de l’appel du programme. Utilisez l’option defaults-file qui permet d’indiquer au programme l’emplacement du fichier de configuration. Elle doit toujours être indiquée en première position. L’option defaults-extra-file permet de lire un fichier de configuration secondaire après avoir lu le principal.

$ mysql --defaults-file=/usr2/mysql/conf/mysql_client.cnf ...  
$ mysqld --defaults-file=/usr2/mysql/conf/mysql_server.cnf ... 

2. Structure du fichier de configuration

Le fichier de configuration est organisé en sections (ou groupes). Une section est composée d’un nom (en général, le nom du programme référencé)...

Visualisation de la configuration

Regarder le contenu du fichier my.cnf permet de connaître la valeur de la majorité des options, mais cette technique est loin d’être fiable. En effet, certaines options sont peut-être définies dans des fichiers de configuration supplémentaires, ou peut-être qu’un utilisateur a modifié dynamiquement un paramètre sans faire le changement correspondant dans le fichier my.cnf. Par conséquent, il est préférable de se connecter au serveur et d’exécuter l’une des requêtes suivantes (ici pour connaître la valeur de sort_buffer_size) :

mysql> SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';  
+------------------+---------+  
| Variable_name    | Value   |  
+------------------+---------+  
| sort_buffer_size | 5242880 |  
+------------------+---------+  
mysql> SELECT @@global.sort_buffer_size;  
+----------------------------+  
| @@global.sort_buffer_size  |  
+----------------------------+  
|                    5242880 |  
+----------------------------+ 

Vous pouvez alors comparer avec le fichier my.cnf et ajuster le fichier de configuration si besoin. Pour la valeur d’une variable de session, utilisez SHOW SESSION VARIABLES ou SELECT @@SESSION.

Il existe également une manière...

Configuration d’InnoDB

1. Paramètres essentiels

Les paramètres évoqués dans cette section ne sont pas nécessairement à modifier par rapport à la configuration par défaut, mais il est important que vous ayez réfléchi à leur bonne valeur pour votre application.

  • Taille du cache mémoire (innodb_buffer_pool_size) : il s’agit du cache principal d’InnoDB (buffer pool), où données et index fréquemment accédés sont stockés. Pour un serveur dédié à MySQL, il est courant de lui allouer la majeure partie de la mémoire du serveur (par exemple, environ 25 Go pour un serveur ayant 32 Go de mémoire physique). L’idée principale est que ce cache permet d’éviter les accès au disque : la taille du cache est d’autant plus importante que le disque est lent.

    Si votre base de données est petite (quelques dizaines de Go, par exemple), il est assez simple d’utiliser un serveur ayant suffisamment de mémoire pour que l’ensemble des données et index InnoDB tiennent dans le cache. Sinon, il faut essayer de faire tenir en cache la partie utile des données et index, c’est-à-dire la partie des données et index qui est fréquemment utilisée par l’application. Il est assez fréquent en effet d’avoir par exemple une base de 500 Go contenant l’historique des données sur les cinq dernières années, mais pour laquelle seules les données du dernier mois sont régulièrement consultées, ce qui va représenter 10 Go. Dans ce cas, il est inutile de chercher à allouer 500 Go au buffer pool, 10-15 Go seront suffisants.

  • Taille du journal transactionnel (innodb_log_file_size) : le journal transactionnel (redo log) permet à InnoDB d’offrir de bonnes performances en écriture tout en garantissant l’intégrité des données en cas d’arrêt inopiné (voir chapitre Généralités sur MySQL). Pour rappel, l’idée principale est qu’InnoDB écrit de manière synchrone les modifications dans son journal transactionnel (écritures peu coûteuses puisque séquentielles) et qu’un thread...

La journalisation

Le serveur MySQL utilise quatre types de journaux, qui ont chacun leurs spécialisations : le journal binaire (binary log ou encore binlog), le journal des requêtes lentes (slow query log), le journal général (general query log) et le journal des erreurs (error log), le seul des quatre à être activé par défaut.

Le relay-log, un autre type de journal, est créé par le serveur lors de la réplication. Il est abordé au chapitre Réplication.

1. Le journal binaire

Le journal binaire ou binlog est chargé de stocker, sous un format binaire, toutes les requêtes qui modifient les objets de la base de données (INSERT, UPDATE, DELETE, DROP, CREATE, ALTER...). C’est l’élément central de la réplication MySQL (voir chapitre Réplication). Il est également très utile à la restauration des données (voir chapitre Sauvegarde et restauration). Pour activer la journalisation binaire, utilisez l’option log-bin, et pour définir son fichier d’index, il faut paramétrer l’option log-bin-index. Ce fichier, qui contient la liste de tous les journaux binaires depuis la dernière purge, permet au serveur de connaître le nom du fichier courant qui est obtenu avec la commande SHOW MASTER STATUS, mais aussi d’afficher la liste de tous les journaux binaires présents sur le serveur avec SHOW BINARY LOGS. La journalisation binaire peut être désactivée à chaud, mais seulement pour la session d’un client, avec l’option SQL_LOG_BIN.

Ne confondez pas les journaux binaires, qui enregistrent toutes les écritures, avec les journaux transactionnels d’InnoDB (redo logs), qui n’enregistrent que les écritures sur les tables InnoDB et dont le seul but est d’assurer la restauration automatique d’InnoDB en cas d’arrêt inopiné.

Une configuration de base pourrait être :

[mysqld]  
log-bin = /var/lib/mysql/mysql-bin  

Notez que log-bin-index n’ayant pas été défini, par défaut le fichier sera créé dans le même répertoire que les journaux binaires (/var/lib/mysql ici).

Notez également que vous pouvez indiquer un chemin relatif pour l’option log-bin. Dans ce cas, le chemin...

Le mode SQL

MySQL a pendant longtemps été très permissif en ce qui concerne le contrôle de la cohérence des données insérées. Par exemple, que se passait-il lorsqu’un utilisateur tentait d’enregistrer une chaîne de 15 caractères dans une colonne ne pouvant en accueillir que 10 ? Malheureusement, presque rien ! La chaîne était tronquée à 10 caractères puis enregistrée normalement. Un message d’avertissement était également émis, mais il passait en général inaperçu. Il était bien sûr également possible d’enregistrer une chaîne de caractères dans un champ numérique… Ce comportement de MySQL était particulièrement gênant puisqu’un simple problème applicatif (le développeur n’avait pas prévu que tel champ pourrait avoir plus de 10 caractères) pouvait donc occasionner des corruptions de données importantes.

Pour pallier ces problèmes, MySQL est devenu de plus en plus strict à chaque version. Mais il existe des cas où une application est prévue pour fonctionner avec un serveur SGBD permissif et où il est difficile de changer le code applicatif. Pour cette raison, une variable (sql_mode) a été introduite, permettant de choisir si MySQL doit être plus ou moins strict. La valeur par défaut change régulièrement avec les versions, mais il est toujours possible d’adopter le réglage d’une version précédente.

1. Les modes usuels

Parmi les valeurs possibles de sql_mode, on trouve :

  • NO_ENGINE_SUBSTITUTION : lors d’une commande CREATE TABLE ou ALTER TABLE, si le moteur de stockage demandé lors de la création ou de la modification d’une table n’existe pas, le serveur renvoie une erreur et n’exécute donc pas la commande. Dans le cas contraire, un avertissement est renvoyé (warning) et la table est tout de même créée ou modifiée avec le moteur de stockage par défaut.

    Dans l’exemple suivant, le moteur ARIA est inconnu sur ce serveur. En supprimant le mode SQL NO_ENGINE_SUBSTITUTION, le serveur crée donc la table avec le moteur par défaut, InnoDB.

mysql> SET SESSION...

Autres paramètres à configurer

1. Autres variables

a. Nombre de connexions simultanées

MySQL utilise la variable max_connections pour limiter le nombre de connexions simultanées au serveur.

Deux indicateurs peuvent vous alerter quand cette valeur est trop faible : la variable de statut Max_used_connections vaut ou est proche de max_connections ou le serveur refuse des connexions avec l’erreur Too many connections.

Néanmoins, mieux vaut bien réfléchir aux conséquences possibles d’une augmentation de la valeur de ce paramètre, car son but est d’empêcher le serveur de s’écrouler sous une charge trop importante. Si la valeur de max_connections nécessaire pour éviter l’erreur Too many connections s’avère tellement élevée que les performances de tout le serveur se dégradent, il faut sans doute penser à d’autres modifications (réplication, changement de matériel, changements applicatifs...).

MySQL conserve toujours une connexion disponible en local pour l’utilisateur root, en cas de problème. C’est pourquoi max_connections vaut par défaut 151 et non 150, autorisant effectivement 150 connexions applicatives.

b. Caches de table

Ces caches stockent des informations sur les tables, ce qui permet, quand le serveur a besoin d’utiliser une table, d’en accélérer...