Tutoriel pour apprendre à optimiser les performances de MySQL

Tout n'est pas à prendre au pied de la lettre, mais si le serveur SQL tourne depuis quelque temps (au moins quelques heures, au mieux quelques jours), un petit coup de MySQLTuner peut fournir un bon aperçu des réglages de base, de son usage et des axes d'amélioration potentiels.

Une bonne partie des variables qui pourront nous intéresser pour le diagnostic sont les variables de global status, on les affichera donc avec un petit :

SHOW GLOBAL STATUS;

On pourra éventuellement filtrer tout cela avec l'opérateur like si l'on cherche quelque chose de précis.

MySQLTuner vous l'a sûrement déjà mentionné, mais on peut vérifier le nombre maximum de connexions autorisées. Et le nombre maximum de connexions qui ont été ouvertes en même temps.

SELECT @@max_connections;
+-------------------+
| @@max_connections |
+-------------------+
|               100 |
+-------------------+

SHOW GLOBAL STATUS like "%used_connections";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 98    |
+----------------------+-------+

Dans le cas présent, il peut s'avérer judicieux d'augmenter la limite. Cela n'allégera certainement pas les ressources consommées par le serveur, tout au contraire, mais permettra de servir plus de clients à la fois. Sinon, lorsque le max_connections est atteint, les nouvelles connexions sont mises en file d'attente jusqu'à ce qu'une place se libère.

Il ne s'agit donc pas d'augmenter cette limite à l'infini, mais si votre serveur peut tolérer plus de connexions et que vous vous approchez ou atteignez le maximum autorisé, n'hésitez pas à l'augmenter.

table_open_cache

• Pour voir la limite du table-open-cache :

SELECT @@table_open_cache;
+--------------------+
| @@table_open_cache |
+--------------------+
|                400 |
+--------------------+

Chaque session concurrente accède aux tables de manière indépendante, cela permet un gain de performance au détriment d'une consommation mémoire un peu plus importante (forcement, lorsque l'on met des choses en cache…).

table_open_cache indique le nombre maximum de tables que le serveur peut laisser ouvertes. Ainsi, ces tables sont réutilisées au lieu d'être rouvertes. Lorsque ce chiffre est atteint, toute nouvelle session éjecte la table qui n'a pas été utilisée depuis le plus longtemps.

Afin de savoir s'il peut être bénéfique d'augmenter ce nombre, on regarde le nombre de tables ouvertes :

show global status like 'opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 211   |
+---------------+-------+

Il est évidemment à mettre en relation avec le temps depuis lequel la base de données est démarrée. Quelques centaines de tables ouvertes en plusieurs semaines ne posent pas de problème. En revanche, si ce nombre augmente rapidement, on aura tout intérêt à augmenter ce chiffre jusqu'à ce que le nombre de tables ouvertes se stabilise.

query_cache

• Le query_cache sauvegarde les résultats des SELECT et permet de les retourner ensuite bien plus efficacement et rapidement lors des SELECT consécutifs. Il peut être très intéressant de l'activer dans le cas de site web où les mêmes requêtes sont souvent répétées.
  Le query_cache n'est pas actif par défaut sur toutes les versions de MySQL ou MariaDB. Avant toute chose, voyons donc s'il est disponible et actif :

SELECT @@have_query_cache;
+--------------------+
| @@have_query_cache |
+--------------------+
| YES                |
+--------------------+

# il est dispo, est-il actif ?
SELECT @@query_cache_size;
+--------------------+
| @@query_cache_size |
+--------------------+
|                  0 |
+--------------------+

À partir de la version 10.1.7 de MariaDB, le serveur active automatiquement le cache si le query_cache_size est supérieur à zéro. L'état du cache peut être vérifié avec la variable @@query_cache_state.

# réglage du cache à 1MB
SET GLOBAL query_cache_size = 1000000;

tmp_table_size

• Bien qu'il ne s'agisse pas de cache à proprement parler, tmp_table_size définit la taille maximale que les tables temporaires peuvent prendre en RAM. Au-delà elles sont écrites sur le disque.
  Il suffit de comparer les variables Created_tmp_disk_tables et Created_tmp_tables afin de voir si une grosse proportion des tables est écrite sur disque. Si c'est le cas, il peut être intéressant d'accorder un espace plus important à tmp_table_size et max_heap_table_size. Cette seconde variable concerne les tables MEMORY, mais la plus basse de ces deux variables s'appliquera aux tables temporaires.

innodb_buffer_pool_size

• Le buffer_pool stocke les données et les index en mémoire de manière à éviter au maximum les I/O disque. Vous l'aurez donc compris, c'est un élément essentiel des performances du moteur InnoDB.
  Sur un serveur dédié à la base de données, on pourra lui consacrer 70 à 80 % du total de la RAM du serveur. Sur une machine où les ressources sont partagées avec d'autres services, à vous de voir combien allouer à la BDD et combien vous devez réserver pour le reste. Gardez à l'esprit que la mémoire allouée sera généralement 10 % supérieure à ce que vous spécifiez, car le système utilise de la mémoire supplémentaire pour les structures de contrôle et les buffers.

innodb_flush_method

• Cette variable spécifie pour les systèmes UNIX (aucun effet sur Windows) la manière dont InnoDB lit les données et vide le cache. Généralement O_DIRECT est conseillé afin d'éviter que l'OS mette en buffer des données déjà cachées par InnoDB.

innodb_log_file_size

• Avec le buffer_pool_size, ces deux paramètres conditionnent une grosse partie des performances d'InnoDB. Le moteur de table fonctionne en mémoire, cependant, pour survivre à un crash, toutes les modifications sont loggées dans un fichier de transactions.
  Néanmoins, plus ce fichier est gros, plus le temps de récupération après crash sera élevé.

innodb_flush_log_at_trx_commit

• Sur un système avec beaucoup d'écritures, si les propriétés ACID ne sont pas primordiales et que vous pouvez vous permettre de perdre la dernière seconde de transaction en cas de crash de l'OS (ou de coupure de courant), vous pouvez mettre cette valeur à 2 plutôt qu'à 1 (par défaut). Le buffer de transactions sera alors écrit après chaque commit plutôt qu'après chaque transaction, vous gagnerez donc un peu en I/O.

InnoDB peut créer de nombreux threads, chacun consommant du temps processeur. Il est possible de limiter ce nombre, auquel cas, lorsque la valeur d'innodb_thread_concurrency est atteinte, les processus à créer sont placés dans une file d'attente. La valeur conseillée est de deux fois le nombre de CPU plus le nombre de disques.

La base peut être aussi bien optimisée que possible, s'il s'avère qu'elle est requêtée n'importe comment, sans index, etc. il ne sera pas possible de tirer de bonnes performances.

On peut se faire une idée rapide des requêtes en cours d'exécution avec SHOW PROCESSLIST. Par ailleurs, dans le fichier de config /etc/mysql/my.cnf, on pourra activer le slow query log afin de voir quelles requêtes sont lentes à exécuter, et consomment donc des ressources.

Une fois des requêtes potentiellement problématiques identifiées, vous pourrez les profiler directement dans la console SQL. La commande SHOW PROFILE détaille les ressources utilisées par la base de données pour l'exécution d'une requête donnée.

# on active le profiling
sql > SET profiling = 1;

# on exécute ensuite la requête à examiner
sql > SELECT id, fname, slug, address JOIN Meta on user.id = meta.user_id;

# on affiche ensuite le résultat du profilage
sql > SHOW PROFILE;

SHOW PROFILE affiche la dernière requête profilée. Vous pouvez utiliser SHOW PROFILES pour afficher l'ensemble des requêtes profilées et ensuite fournir l'id de la requête à examiner à la commande SHOW PROFILE.

Enfin, afin de comprendre comment le moteur SQL exécute la requête, on utilisera la commande EXPLAIN.

sql > EXPLAIN SELECT id, fname, slug, address JOIN Meta on user.id = meta.user_id;

Ainsi, fort de détails sur les étapes d'exécution de notre requête, nous pourrons sans doute l'améliorer.

Théoriquement, vous avez là déjà des réglages qui permettront de bien adapter votre base à son usage ainsi qu'en adéquation avec le matériel sur lequel elle est installée. Bien entendu, des centaines d'autres paramètres existent, mais nous avons là passé en revue les principaux concernant InnoDB.

N'hésitez pas à faire part de votre expérience en commentaire. Et si quelques optimisations que je n'ai pas listées donnent de bons résultats, share the love!

Nous tenons à remercier Quentin Busuttil qui nous a aimablement autorisés à publier son tutoriel : Optimiser les performances de MySQL. Nous remercions également Winjerome pour la mise au gabarit et Jacques Jean pour la relecture orthographique