Goulots d'étranglement courants des performances MySQL et comment les résoudre

Lorsque MySQL ralentit, le premier symptôme est rarement "la base de données est lente". C'est généralement une page de paiement qui bloque, une file d'attente qui cesse de se vider, un tableau de bord qui expire, ou une API qui a soudainement besoin de trois secondes pour une requête qui se terminait en 80 ms.

Le moyen le plus rapide de perdre du temps est de modifier des paramètres aléatoires avant de savoir où se trouve l'attente. Commencez par poser une question simple : MySQL attend-il sur le travail de requête, les verrous, la mémoire, le disque, le CPU, le réseau, ou trop de connexions ? La correction dépend de la réponse.

1. Requêtes lentes

Les requêtes lentes sont sans doute le goulot d'étranglement de performance le plus courant. Elles peuvent provenir de divers facteurs, notamment une conception de requête inefficace, des index manquants ou des analyses de grandes tables. Identifier ces requêtes est la première étape vers la résolution.

Identifier les requêtes lentes

Le journal des requêtes lentes de MySQL est un outil précieux pour identifier les requêtes qui prennent plus de temps qu'un seuil spécifié pour s'exécuter. Vous pouvez activer et configurer ce journal dans votre fichier de configuration my.cnf (ou my.ini).

Exemple de configuration my.cnf :

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Dans cet exemple :

slow_query_log = 1 : Active le journal des requêtes lentes.
slow_query_log_file : Spécifie le chemin du fichier journal.
long_query_time = 2 : Définit le seuil à 2 secondes. Les requêtes prenant plus de temps seront enregistrées.
log_queries_not_using_indexes = 1 : Enregistre les requêtes qui n'utilisent pas d'index, qui sont souvent des candidates de choix pour l'optimisation.

Après avoir activé le journal, vous pouvez analyser son contenu. Des outils comme mysqldumpslow peuvent aider à résumer et trier le fichier journal, facilitant ainsi l'identification des requêtes les plus problématiques.

Optimiser les requêtes lentes

Une fois les requêtes lentes identifiées, plusieurs stratégies peuvent être employées :

Indexation : Assurez-vous que des index appropriés sont créés pour les colonnes utilisées dans les clauses WHERE, JOIN, ORDER BY et GROUP BY. Utilisez EXPLAIN pour analyser les plans d'exécution des requêtes et identifier les index manquants.
- Exemple : Si une requête filtre fréquemment par user_id sur une grande table orders, un index sur orders(user_id) peut améliorer considérablement les performances.
```
CREATE INDEX idx_user_id ON orders (user_id);
```
Réécriture de requête : Parfois, une requête peut être réécrite pour une meilleure efficacité. Cela peut impliquer de simplifier les jointures, d'éviter SELECT *, ou d'utiliser les sous-requêtes plus judicieusement.
- Exemple : Remplacer une sous-requête corrélée par une jointure peut offrir de meilleures performances.
Conception du schéma de base de données : Revoir le schéma de la base de données pour des problèmes de normalisation ou pour des opportunités de dénormalisation (avec prudence) peut également aider.

2. Indexation inefficace

Bien que l'indexation soit essentielle aux performances des requêtes, des index mal conçus ou excessifs peuvent également devenir un goulot d'étranglement. Les index consomment de l'espace disque et ajoutent une surcharge aux opérations d'écriture (INSERT, UPDATE, DELETE).

Identifier les problèmes d'indexation

Analyse du plan EXPLAIN : Utilisez toujours EXPLAIN avant et après avoir apporté des modifications d'indexation. Recherchez les analyses de table complètes (type: ALL) sur les grandes tables, ou les lignes examinées qui sont beaucoup plus élevées que les lignes retournées.
```
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
```
Index inutilisés : MySQL 5.6+ dispose d'une fonctionnalité pour suivre l'utilisation des index. Vous pouvez vérifier performance_schema.table_io_waits_summary_by_index_usage pour identifier les index qui ne sont jamais ou rarement utilisés.
Index redondants : Les index qui couvrent les mêmes colonnes ou sont des préfixes d'autres index peuvent être redondants.

Meilleures pratiques d'indexation

Indexer sélectivement : Créez des index uniquement là où ils sont vraiment nécessaires en fonction des modèles de requêtes.
Index composites : Pour les requêtes filtrant sur plusieurs colonnes, envisagez des index composites. L'ordre des colonnes dans un index composite est important.
Index couvrants : Visez des index couvrants où toutes les colonnes nécessaires à une requête font partie de l'index. Cela permet à MySQL de récupérer les données directement à partir de l'index sans accéder à la table.
Révision régulière : Examinez périodiquement vos index, surtout après des modifications de schéma ou des changements dans l'utilisation de l'application.

3. Buffer Pool et configuration mémoire

Le buffer pool InnoDB est une zone mémoire critique où InnoDB met en cache les pages de données et d'index. Une taille de buffer pool insuffisante peut entraîner des E/S disque excessives, ralentissant considérablement les opérations.

Réglage du buffer pool InnoDB

Le paramètre innodb_buffer_pool_size est l'un des paramètres les plus importants pour les performances d'InnoDB.

Recommandation : Pour les serveurs de base de données dédiés, définir innodb_buffer_pool_size à 50-75 % de la RAM disponible est un point de départ courant. Certains systèmes peuvent fonctionner plus haut, mais seulement si le système d'exploitation n'utilise pas de swap et que la mémoire de connexion est sous contrôle.

Exemple de configuration my.cnf :

[mysqld]
innodb_buffer_pool_size = 8G

Ceci définit le buffer pool à 8 Gigaoctets.

Surveillance : Observez le modèle de lecture du buffer pool. Un taux de succès très élevé signifie souvent que la plupart des lectures sont servies depuis la mémoire, mais cela ne prouve pas que chaque requête est saine. Vous pouvez surveiller cela en utilisant :

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Le taux de succès peut être calculé comme (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

Autres paramètres mémoire

innodb_log_file_size : Affecte les performances d'écriture et le temps de récupération. Des fichiers plus grands peuvent améliorer le débit d'écriture mais augmentent le temps de récupération après un crash.
innodb_flush_log_at_trx_commit : Contrôle la durabilité par rapport aux performances. Le définir sur 1 (par défaut) garantit une conformité ACID complète mais peut être plus lent. Le définir sur 0 ou 2 peut améliorer les performances au détriment de certaines garanties de durabilité.

4. Problèmes de verrouillage et concurrence

Le verrouillage est essentiel pour la cohérence des données mais peut devenir un goulot d'étranglement s'il n'est pas géré correctement. Un verrouillage excessif peut entraîner des conflits de requêtes, des délais d'attente et des interblocages.

Identifier les problèmes de verrouillage

SHOW ENGINE INNODB STATUS : Cette commande fournit des informations détaillées sur l'état interne d'InnoDB, y compris les transactions actives, les verrous détenus et les attentes de verrou.
Tables de verrouillage Performance Schema : Dans MySQL 8.0, utilisez les tables Performance Schema telles que data_locks et data_lock_waits. Les versions plus anciennes exposaient les informations de verrou via les tables information_schema.
Outils de surveillance : Les outils de surveillance des performances peuvent souvent mettre en évidence des temps d'attente de verrou élevés ou des interblocages.

Résoudre les problèmes de verrouillage

Optimiser les requêtes provoquant des verrous : Des requêtes plus courtes et plus efficaces réduisent le temps pendant lequel les verrous sont détenus.
Gestion des transactions : Gardez les transactions aussi courtes que possible. Évitez les opérations de longue durée dans les transactions qui nécessitent un verrouillage étendu.
Granularité du verrouillage : InnoDB utilise le verrouillage au niveau des lignes pour la plupart des opérations, ce qui est généralement bon pour la concurrence. Cependant, il est important de comprendre comment vos requêtes peuvent escalader vers des verrous de table (par exemple, ALTER TABLE sans DDL en ligne).
Détection et résolution des interblocages : MySQL dispose d'un détecteur d'interblocage. Lorsqu'un interblocage est détecté, InnoDB annule généralement l'une des transactions impliquées, permettant à l'autre de continuer. Analysez les informations d'interblocage de SHOW ENGINE INNODB STATUS pour comprendre la cause et ajuster la logique de l'application ou l'ordre des requêtes.

5. Contention de ressources (CPU, disque, réseau)

Même avec des requêtes optimisées et une configuration appropriée, des ressources matérielles insuffisantes ou une contention pour ces ressources peuvent limiter les performances.

Identifier les goulots d'étranglement de ressources

Utilisation du CPU : Une utilisation élevée du CPU par le processus mysqld peut indiquer des requêtes inefficaces, un tri lourd ou une puissance de traitement insuffisante.
E/S disque : Une activité élevée de lecture/écriture disque, surtout avec de faibles taux de succès du buffer pool, indique que les E/S disque sont un goulot d'étranglement. Recherchez des temps iowait élevés sur les systèmes Linux.
Débit réseau : Un trafic réseau excessif peut se produire avec de grands ensembles de résultats transférés ou un nombre élevé de connexions client.

Résoudre les goulots d'étranglement de ressources

Mises à niveau matérielles : Parfois, la solution la plus simple est d'ajouter du CPU, de la RAM ou un stockage plus rapide. Traitez cela comme une correction uniquement après avoir su que la charge de travail est raisonnable ; le matériel peut cacher une mauvaise requête, mais il la fait rarement disparaître.
Optimisation des requêtes : Réduisez la quantité de données traitées et transférées, ce qui réduit indirectement la charge CPU, disque et réseau.
Pool de connexions : Implémentez un pool de connexions dans votre application pour réduire la surcharge liée à l'établissement de nouvelles connexions et gérer efficacement le nombre de connexions actives.
Réplicas de lecture : Pour les charges de travail à forte lecture, envisagez de configurer des réplicas de lecture pour distribuer la charge de lecture loin du serveur principal.

Un flux de triage qui fonctionne sous pression

Lorsqu'un incident est actif, ne commencez pas par un projet de réglage complet. Obtenez d'abord une image rapide.

Vérifiez les requêtes actives :

SHOW FULL PROCESSLIST;

Si vous voyez de nombreuses sessions bloquées sur la même requête, capturez-la. Si vous voyez de nombreuses sessions en attente de verrous, ne tuez pas les choses au hasard ; identifiez d'abord la transaction bloquante.

Vérifiez l'état d'InnoDB :

SHOW ENGINE INNODB STATUS\G

Recherchez les interblocages, les attentes de verrou, la pression de point de contrôle et les transactions de longue durée. Une transaction ouverte depuis une heure peut retenir le travail de purge et ralentir les requêtes non liées.

Vérifiez si le serveur est saturé :

top
vmstat 1
iostat -xz 1
ss -s

Un CPU élevé avec des E/S faibles pointe généralement vers une exécution de requête coûteuse, un tri, une analyse ou trop de concurrence. Un iowait élevé pointe vers le stockage. L'activité de swap est un signal d'alarme ; MySQL sous pression de swap se comporte souvent de manière imprévisible.

Ensuite, vérifiez le journal des requêtes lentes pour les dernières minutes, pas seulement la requête la plus lente de tous les temps. La requête qui a causé l'incident d'aujourd'hui peut être nouvelle, liée à un déploiement, ou liée à un modèle de trafic qui n'apparaît qu'aux heures de pointe.

Tempêtes de connexions

Un goulot d'étranglement courant de MySQL n'est pas une mauvaise requête, mais trop de connexions d'application effectuant de petites quantités de travail. Si chaque worker web ouvre sa propre connexion et que l'application monte en charge soudainement, MySQL peut passer trop de temps à planifier des sessions et à allouer de la mémoire par connexion.

Les symptômes incluent :

Threads_connected augmentant fortement.
Threads_running restant élevé.
Erreurs d'application telles que Too many connections.
CPU augmentant sans une seule requête lente évidente.

Vérifications utiles :

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

La correction se trouve souvent dans la couche application : utilisez un pool de connexions, définissez des limites de pool raisonnables et rendez les délais d'attente explicites. Augmenter max_connections peut faire gagner du temps, mais peut aussi faire tomber le serveur plus durement si chaque connexion utilise de la mémoire pour les jointures, les tris et les tables temporaires.

Tables temporaires et tris

Les requêtes avec GROUP BY, ORDER BY, DISTINCT ou de grandes jointures peuvent créer des tables temporaires. Certaines tables temporaires restent en mémoire. Les plus grandes débordent sur le disque. Les tables temporaires sur disque ne sont pas automatiquement un désastre, mais une augmentation soudaine explique souvent des pics de latence.

Vérifiez :

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Inspectez ensuite les plans de requête. Si EXPLAIN montre Using temporary et Using filesort, demandez-vous si un index peut prendre en charge le filtre et l'ordre ensemble. Par exemple :

SELECT customer_id, created_at, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Un index sur (status, created_at) peut réduire à la fois le travail de filtrage et de tri. Augmenter tmp_table_size peut aider dans certains cas, mais c'est un risque par session. Si de nombreuses sessions allouent de grandes tables temporaires à la fois, la mémoire disparaît rapidement.

Le retard de réplication comme symptôme de performance

Si les lectures vont vers des réplicas, le retard de réplication peut ressembler à un problème de performance de base de données même lorsque le primaire va bien. Les utilisateurs actualisent une page et ne voient pas leur propre changement. Les jobs en arrière-plan lisent des lignes obsolètes. Les rapports ne concordent pas.

Vérifiez l'état du réplica avec l'outil approprié pour votre version de MySQL :

SHOW REPLICA STATUS\G

Les versions plus anciennes utilisent :

SHOW SLAVE STATUS\G

Le retard peut provenir de requêtes lentes sur le réplica, de grandes transactions provenant du primaire, d'un matériel de réplica insuffisant, de jobs de maintenance ligne par ligne, ou de problèmes réseau. La correction peut être le réglage des requêtes, la division des grandes écritures en morceaux plus petits, l'amélioration des ressources du réplica, ou la modification de l'endroit où les lectures fraîches sont routées.

Que changer en premier

Préférez les correctifs qui réduisent le travail :

Ajoutez ou ajustez un index pour une requête chaude avérée.
Réécrivez une requête pour lire moins de lignes.
Raccourcissez les transactions qui détiennent des verrous.
Limitez la taille du pool de connexions pour que MySQL ne soit pas inondé.
Déplacez les rapports lourds loin du primaire.

Soyez plus prudent avec les correctifs qui n'augmentent que la capacité :

Augmenter max_connections.
Augmenter les tampons de tri et de jointure globalement.
Augmenter les limites des tables temporaires.
Ajouter des réplicas sans corriger la requête qui les affecte.

Les changements de capacité ont leur place, mais ils doivent suivre les preuves. Une bonne session de dépannage MySQL vous laisse avec une plus petite quantité de travail de base de données, pas seulement un plus grand serveur faisant le même travail inutile.