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

$ 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  
[...] 

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

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

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.

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

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.

mysql> SET SESSION...

Autres paramètres à configurer