Comment résoudre les problèmes liés à une requête lente et améliorer ses performances dans Amazon RDS for MySQL ?

Brève description

Dans Amazon RDS, les problèmes suivants peuvent ralentir les performances des requêtes :

• Problèmes liés à la charge de travail et à l'utilisation des ressources, tels qu'une indexation inadéquate et une utilisation inefficace du pool de mémoires tampons
• Plan d'exécution de requêtes inefficace
• Conflit de ressources
• Blocage des transactions

Pour résoudre ces problèmes, consultez les métriques Amazon CloudWatch, Performance Insights, Database Insights et la surveillance améliorée afin d'identifier les goulots d'étranglement en matière de performances. Résolvez ensuite le problème de goulot d'étranglement et optimisez les performances de vos requêtes.

Résolution

Important : Performance Insights atteindra sa fin de vie le 30 juin 2026. Vous pouvez effectuer une mise à niveau vers le mode Avancé de Database Insights avant le 30 juin 2026. Si vous n'effectuez pas de mise à niveau, les clusters de base de données qui utilisent Performance Insights passeront par défaut au mode Standard de Database Insights. Seul le mode Avancé de Database Insights prendra en charge les plans d'exécution et l’analyse à la demande. Si vos clusters passent par défaut en mode Standard, il est possible que vous ne puissiez pas utiliser ces fonctionnalités sur la console. Pour activer le mode Avancé, consultez les sections Activation du mode Avancé de Database Insights pour Amazon RDS et Activation du mode Avancé de Database Insights pour Amazon Aurora.

Remarque : Si des erreurs surviennent lorsque vous exécutez des commandes de l'interface de la ligne de commande AWS (AWS CLI), consultez la section Résoudre des erreurs liées à l’AWS CLI. Vérifiez également que vous utilisez bien la version la plus récente de l'AWS CLI.

Surveiller les performances de vos ressources et de vos bases de données

Pour résoudre les problèmes liés aux performances de vos requêtes, consultez les métriques Amazon CloudWatch afin de déterminer la cause du problème. Pour déterminer quand une requête augmente l'utilisation d'une ressource spécifique ou diminue les performances de la base de données, utilisez la console CloudWatch ou l'AWS CLI pour surveiller les métriques suivantes :

• DatabaseConnections
• NetworkReceiveThroughput
• WriteThroughput
• ReadThroughput
• WriteLatency
• ReadLatency
• WriteIOPS
• ReadIOPS
• FreeStorageSpace
• BurstBalance

Si les performances de votre base de données sont médiocres, vérifiez l'état de l'instance de base de données RDS pour détecter les processus actifs ou planifiés susceptibles d'affecter les performances. Vérifiez également vos événements Amazon RDS pour détecter les événements susceptibles d'affecter les performances de la base de données.

Examiner votre charge de travail et l'utilisation des ressources

Si les performances de vos requêtes sont lentes, examinez les autres requêtes de votre charge de travail pour voir si elles affectent les performances de vos requêtes. Pour identifier les requêtes que vous devez optimiser, vous pouvez activer le mode Avancé de Database Insights pour Amazon RDS ou Amazon Aurora.

Si votre instance redémarre, votre instance de base de données risque de perdre les données mises en cache et de ralentir les performances des requêtes. Pour éviter ce problème de cache froid, configurez les paramètres suivants afin d'accélérer le préchauffage du pool de mémoires tampons après un redémarrage :

• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_dump_pct

Pour optimiser les performances des requêtes, il est recommandé de surveiller dans quelle mesure votre instance de base de données utilise le pool de mémoires tampons InnoDB. Pour plus d'informations, consultez la page Pool de mémoires tampons sur le site Web de MySQL. Pour surveiller l'état du pool de mémoires tampons InnoDB, examinez les compteurs de base de données de Performance Insights suivants :

• Pour connaître le nombre de demandes de lecture logiques, examinez le compteur Innodb_buffer_pool_read_requests.
• Pour connaître le nombre de lectures logiques qu'InnoDB ne peut pas effectuer à partir du pool de mémoires tampons et qu'il a dû lire directement depuis le disque, consultez Innodb_buffer_pool_reads.
• Utilisez Innodb_buffer_pool_hit_ratio pour le pourcentage de lectures qu'InnoDB peut effectuer à partir du pool de mémoires tampons.
• Consultez Innodb_buffer_pool_usage pour connaître le pourcentage du pool de mémoires tampons InnoDB contenant des pages de données.

Pour identifier les requêtes lentes, vous pouvez également activer l'option slow_query_log dans votre groupe de paramètres, puis publier les journaux sur CloudWatch Logs.

Optimiser les performances de vos requêtes

Pour optimiser les performances de vos requêtes, exécutez les commandes suivantes en fonction des besoins de votre plan d'exécution de requêtes. Pour plus d'informations, consultez la page Format de sortie EXPLAIN sur le site Web de MySQL.

Utiliser EXPLAIN pour optimiser vos requêtes

Pour consulter les détails concernant les performances de votre requête et les raisons pour lesquelles celle-ci peut être retardée, exécutez la commande EXPLAIN. Pour plus d’informations, consultez la page Optimisation de requêtes avec EXPLAIN sur le site Web de MySQL.

Pour déterminer si votre requête utilise un index, exécutez la requête EXPLAIN. Dans la sortie EXPLAIN, examinez les noms des tables, les clés utilisées et le nombre de lignes analysées par la requête. Pour plus d’informations, consultez la page Instruction GRANT sur le site Web de MySQL. Examinez le résultat, puis effectuez les actions suivantes :

• Si la sortie n'affiche pas les clés en cours d'utilisation, créez un index sur les colonnes utilisées dans la clause WHERE.
• Si la table utilise l'indexation requise, assurez-vous que les statistiques de la table sont à jour. Pour plus d'informations, consultez la page Table INFORMATION_SCHEMA STATISTICS sur le site Web de MySQL.

Utiliser ANALYZE TABLE pour mettre à jour les statistiques de vos requêtes

Si les statistiques de votre table ne sont pas à jour, les performances de la requête peuvent être médiocres. Pour mettre à jour les statistiques de vos requêtes, exécutez la commande ANALYZE TABLE. Pour plus d’informations, consultez la page Instruction ANALYZE TABLE sur le site Web de MySQL.

Utiliser EXPLAIN ANALYZE pour voir comment vos requêtes allouent du temps

Pour déterminer quelle partie de l'exécution de la requête est lente, exécutez la requête EXPLAIN ANALYZE pour voir comment MySQL alloue du temps à votre requête. Une fois la requête terminée, la requête EXPLAIN ANALYZE imprime le plan et ses mesures. Pour plus d'informations, consultez la page Obtention d'informations avec EXPLAIN ANALYZE sur le site Web de MySQL. Vous pouvez également utiliser SHOW PROFILE pour établir le profil de vos requêtes les plus lentes et trouver le statut auquel la session passe le plus de temps. Pour plus d’informations, consultez la page Instruction SHOW PROFILE sur le site Web de MySQL.

Utiliser SHOW FULL PROCESSLIST et la surveillance améliorée pour examiner les opérations

Exécutez la commande SHOW FULL PROCESSLIST pour consulter la liste des opérations effectuées sur le serveur de base de données. Vous pouvez également utiliser la surveillance améliorée pour examiner cette liste. Pour plus d’informations, consultez la page Instruction SHOW PROCESSLIST sur le site Web de MySQL.

Vérifier la longueur de la liste historique

Le système de transactions InnoDB gère le contrôle de simultanéité multiversion (MVCC). Si votre charge de travail nécessite plusieurs transactions ouvertes ou de longue durée, attendez-vous à une longue liste historique dans la base de données. Il est recommandé d'éviter les transactions ouvertes ou de longue durée sur la base de données. Pour plus d'informations, consultez la section La longueur de la liste historique d'InnoDB a augmenté de manière significative.

Si vous ne surveillez pas la taille de votre liste historique, vos performances peuvent diminuer au fil du temps. Une grande longueur de liste historique peut également entraîner une utilisation élevée des ressources, des performances de SELECT lentes et incohérentes ainsi qu’une augmentation du stockage.

Remarque : Les transactions de longue durée ne sont pas la seule cause des pics de longueur des listes historiques. Si les threads de purge ne peuvent pas suivre les modifications apportées à la base de données, la longueur de la liste historique demeure élevée. Dans les cas extrêmes, vous pouvez également rencontrer une panne de base de données.

La commande SHOW ENGINE INNODB STATUS affiche des informations sur le traitement des transactions, les événements d'attente et les blocages. Pour plus d’informations, consultez la page Instruction SHOW ENGINE sur le site Web de MySQL. Exécutez la requête SHOW ENGINE INNODB STATUS pour vérifier la longueur de votre liste historique :

SHOW ENGINE INNODB STATUS;

Exemple de sortie :

\------------ TRANSACTIONS ------------Trx id counter 26368570695  Purge done for
 trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Pour utiliser Performance Insights afin de vérifier la longueur de votre liste historique, procédez comme suit :

1. Ouvrez la console Amazon RDS.
2. Dans le volet de navigation, choisissez Performance Insights, puis sélectionnez la base de données pour laquelle vous souhaitez afficher les métriques.
3. Choisissez Métriques.
4. Sur la page Tableau de bord Métriques, choisissez Tableau de bord personnalisé.
5. Choisissez Ajouter un widget, puis sélectionnez la métrique Trx Rseg History Len.
6. Choisissez Ajouter un widget.

Remarque : Si les écritures en langage de manipulation des données (DML) entraînent une augmentation de la longueur de la liste historique, demandez à votre administrateur de base de données de mettre fin aux requêtes d'écriture.

Résoudre les requêtes bloquées

Si votre requête est exécutée pendant une période prolongée, il se peut qu'une autre requête la bloque. Dans MySQL 8.0, vous pouvez trouver les attentes de verrouillage dans le schéma de performance de la table data_lock_waits. Pour plus d’informations, consultez la page Utilisation des informations de transaction et de verrouillage InnoDB sur le site Web de MySQL. Exécutez la requête suivante pour identifier les transactions bloquantes :

SELECT
  r.trx\_id waiting\_trx\_id,    
  r.trx\_mysql\_thread\_id waiting\_thread,      
  r.trx\_query waiting\_query,    
  b.trx\_id blocking\_trx\_id,    
  b.trx\_mysql\_thread\_id blocking\_thread,    
  b.trx\_query blocking\_query    
FROM       performance\_schema.data\_lock\_waits w    
INNER JOIN information\_schema.innodb\_trx b    
  ON b.trx\_id = w.blocking\_engine\_transaction\_id    
INNER JOIN information\_schema.innodb\_trx r    
  ON r.trx\_id = w.requesting\_engine\_transaction\_id;

Informations connexes

Comment résoudre les problèmes liés à une instance RDS for MySQL ou MariaDB qui indique un état de stockage plein ?

Pourquoi une requête adressée à mon instance de base de données Amazon RDS for MySQL a-t-elle été bloquée alors qu'aucune autre session n'est active ?