Pourquoi ma requête est-elle lente dans Amazon RDS for MySQL ?

Date de la dernière mise à jour : 27/10/2021

J'essaie de résoudre les problèmes de lenteur des requêtes dans Amazon Relational Database Service (Amazon RDS) for MySQL. Pourquoi cela se produit-il et comment puis-je améliorer les performances des requêtes ?

Brève description

Pour améliorer les performances des requêtes, prenez en compte les facteurs suivants :

  • Utilisation des ressources (telles que le processeur, la mémoire et le stockage)
  • Analyse de l'application
  • Réglage et surveillance des requêtes

Résolution

Utilisation des ressources (telles que le processeur, la mémoire et le stockage)

Pour comprendre la cause racine des problèmes de performances de la base de données, vérifiez toutes les ressources côté serveur utilisées par votre instance. Vous pouvez surveiller votre application et comparer le moment où les performances de la requête étaient normales par rapport au moment où l'exécution de la requête a commencé à prendre trop de temps.

Utilisez les métriques Amazon CloudWatch pour surveiller ces ressources sur une période qui prend en compte les jours où les performances étaient considérées comme normales. Vous pouvez également afficher les métriques de performances dans la console Amazon RDS pour surveiller les performances de la base de données.

Vous pouvez également vérifier le statut de votre instance pour identifier tout autre processus actif ou planifié susceptible d'avoir un impact sur les performances de la base de données. Dans la console Amazon RDS, vérifiez les événements qui se sont produits alors que votre base de données ne fonctionnait pas correctement.

Analyse de l'application

Pour analyser l'application qui contribue à la consommation des ressources, utilisez Performance Insights. Performance Insights fournit une analyse graphique de toutes vos requêtes et des attentes qui contribuent à augmenter la consommation des ressources.

Performance Insights utilise l'application comme métrique principale au lieu d'utiliser le nombre de vCPU pour une instance. Si votre application actuelle dépasse la limite de vCPU, votre serveur est surchargé. Si votre serveur est surchargé, vérifiez les requêtes qui contribuent à votre application et identifiez les moyens d'optimiser vos requêtes. Pensez ensuite à modifier votre classe d'instance.

Votre application Performance Insights peut également être décomposée en événements d'attente. Examinez les attentes les plus consommatrices de ressources en divisant la charge de base de données par le nombre d'événements d'attente. Les bandes de couleur plus intense du graphique de charge indiquent les types d'attente qui contribuent le plus à l'application. Pour plus d'informations, consultez Surveillance de la charge de base de données avec Performance Insights sur Amazon RDS.

Vous pouvez également utiliser le journal des requêtes lentes (activé dans votre groupe de paramètres personnalisé) pour identifier les requêtes lentes.

Vous pouvez ensuite utiliser les métriques Amazon CloudWatch pour vérifier si le volume de travail effectué sur votre instance a augmenté. Par exemple :

  • Connexions à la base de données – nombre de sessions clientes connectées à l'instance de base de données.
  • Débit de réception réseau (Mo/s) – taux de trafic réseau vers et depuis l'instance de base de données.
  • Débit d'écriture et de lecture – nombre moyen de mégaoctets lus ou écrits sur le disque par seconde.
  • Latence d'écriture et de lecture – durée moyenne d'une opération de lecture ou d'écriture, en milliseconde.
  • IOPS (lecture et écriture) – nombre moyen d'opérations de lecture ou d'écriture sur disque par seconde.
  • Espace de stockage disponible (Mo) – quantité d'espace disque actuellement non utilisée par l'instance de base de données.

Les métriques de latence indiquent le temps nécessaire pour terminer l'opération d'I/O en lecture ou en écriture sur disque. La corrélation entre les métriques de latence et les connexions de base de données ou les métriques de débit accrues peut indiquer que l'application est la cause de la lenteur de l'exécution des requêtes. Pour plus d'informations sur l'identification des facteurs d'utilisation, consultez Comment voir ce qui utilise le stockage dans une instance de base de données Amazon RDS qui exécute MySQL ?

Vous pouvez également utiliser la surveillance améliorée pour récupérer la liste des systèmes d'exploitation impliqués dans votre application et les métriques système sous-jacentes. Par défaut, l'intervalle de surveillance pour la surveillance améliorée est de 60 secondes. Il est recommandé de ramener cette valeur à 1 à 5 secondes afin d'obtenir des points de données plus précis.

Optimisation des requêtes

Une fois que la requête de longue durée a été identifiée à partir d'un journal des requêtes lentes ou de Performance Insights, réfléchissez aux moyens d'améliorer les performances de votre requête. Pour régler une requête, envisagez les approches suivantes :

  • Pour trouver les états où le plus de temps est passé, établissez le profil de vos requêtes les plus lentes. Pour plus d'informations, consultez Instruction SHOW PROFILE sur le site web de MySQL.
  • Exécutez la commande SHOW FULL PROCESSLIST en même temps que la surveillance améliorée. Cette combinaison vous permet de consulter la liste des opérations en cours d'exécution sur le serveur de base de données.
  • Utilisez la commande SHOW ENGINE INNODB STATUS pour obtenir des informations sur le traitement des transactions, les temps d'attente et les blocages.
  • Recherchez les requêtes bloquantes et résolvez le blocage. Pour plus d'informations, consultez Pourquoi une requête à mon instance de base de données Amazon RDS for MySQL a-t-elle été bloquée alors qu'il n'y a pas d'autre session active ?
  • Publiez les journaux MySQL sur Amazon CloudWatch. Les journaux sont renouvelés toutes les heures pour maintenir le seuil de 2 % d'espace de stockage alloué. Ils sont ensuite purgés s'ils datent de plus de deux semaines ou si leur taille combinée dépasse le seuil de 2 %.
  • Définissez une alarme Amazon CloudWatch afin de pouvoir surveiller l'utilisation de vos ressources et être averti en cas de dépassement des seuils.
  • Recherchez le plan d'exécution de la requête et vérifiez si celle-ci utilise les index appropriés. Vous pouvez optimiser votre requête à l'aide du plan EXPLAIN et consulter les détails sur la façon dont MySQL exécute la requête.
  • Maintenez les statistiques de votre requête à jour avec l'instruction de table ANALYZE. Les optimiseurs de requêtes peuvent parfois choisir des plans d'exécution médiocres en raison de statistiques obsolètes. Cela peut dégrader les performances d'une requête en raison d'estimations de cardinalité inexactes de la table et des index.
  • MySQL 8.0 utilise désormais une instruction EXPLAIN ANALYZE. L'instruction EXPLAIN ANALYZE est un outil de profilage de vos requêtes qui vous indique où MySQL passe du temps sur vos requêtes et pourquoi. Avec EXPLAIN ANALYZE, MySQL planifie, instrumente et exécute la requête tout en comptant les lignes et en mesurant le temps passé à différents points du plan d'exécution. Lorsque la requête est terminée, EXPLAIN ANALYZE affiche le plan et ses mesures à la place du résultat de la requête.
  • Dans MySQL version 8, sachez que les attentes de verrouillage sont répertoriées dans Performance Schema de la table data_lock_waits. Par exemple :
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;

Pour plus d'informations, reportez-vous à Utilisation des informations de transactions et de verrouillage d'InnoDB sur le site web de MySQL.