Comment résoudre les problèmes liés à l'utilisation intensive du processeur sur les instances Amazon RDS for MySQL ?

Date de la dernière mise à jour : 13/08/2021

Je constate une utilisation intensive du processeur sur mes instances de base de données Amazon Relational Database Service (Amazon RDS) for MySQL. Comment identifier et résoudre une utilisation intensive du processeur ?

Brève description

L'augmentation de l'utilisation du processeur peut être causée par plusieurs facteurs, tels que des charges de travail lourdes initiées par l'utilisateur, des requêtes simultanées multiples ou des transactions de longue durée.

Pour identifier la source de l'utilisation du processeur dans votre instance Amazon RDS for MySQL, passez en revue les approches suivantes :

  • Enhanced Monitoring (Surveillance améliorée)
  • Performance Insights (Analyse des performances)
  • Utilisation de requêtes qui identifie la cause de l'utilisation intensive du processeur lors d'une charge de travail
  • Journaux avec surveillance activée

Une fois la source identifiée, vous pouvez analyser et optimiser votre charge de travail afin de réduire l'utilisation du processeur.

Résolution

Utilisation d'Enhanced Monitoring

Dans la section Liste des processus du système d'exploitation (OS) d'Enhanced Monitoring, passez en revue les processus du système d'exploitation et les processus RDS. Vérifiez le pourcentage d'utilisation du processeur d'un processus mysqld ou Aurora. Ces métriques peuvent vous aider à déterminer si la hausse de l'utilisation du processeur est causée par des processus du système d'exploitation ou de RDS. Vous pouvez également utiliser ces métriques pour surveiller toute augmentation de l'utilisation du processeur causée par mysqld ou Aurora. Vous avez aussi la possibilité de voir la division de l'utilisation du processeur en examinant les métriques de cpuUtilization. Pour plus d'informations, consultez la section Suivi des métriques de système d'exploitation à l'aide d'Enhanced Monitoring.

Remarque : si vous activez la fonctionnalité Performance Schema, vous pouvez mapper l'ID de thread du système d'exploitation à l'ID de processus de votre base de données. Pour plus d'informations, consultez Pourquoi mon instance de base de données Amazon RDS utilise-t-elle l'espace d'échange alors que je dispose d'une quantité de mémoire suffisante ?

Utilisation de Performance Insights

Vous pouvez utiliser Performance Insights pour identifier les requêtes exactes qui s'exécutent sur l'instance et qui entraînent une utilisation intensive du processeur. Tout d'abord, activez Performance Insights pour MySQL. Vous pouvez ensuite utiliser Performance Insights pour optimiser votre charge de travail après avoir consulté votre administrateur de base de données (DBA).

Pour connaître les moteurs de bases de données que vous pouvez utiliser avec Performance Insights, consultez la section Surveillance avec Performance Insights sur Amazon RDS.

Utilisation de requêtes afin de détecter ce qui provoque l'utilisation intensive du processeur lors d'une charge de travail

Avant de pouvoir optimiser votre charge de travail, vous devez identifier la requête problématique. Vous pouvez exécuter les requêtes suivantes en cas de problème de consommation élevée du processeur afin d'identifier la cause première de cette surutilisation. Optimisez ensuite votre charge de travail afin de réduire l'utilisation du processeur.

La commande SHOW PROCESSLIST vous indique les threads en cours d'exécution sur votre instance MySQL. Parfois, le même ensemble d'instructions peut continuer à s'exécuter sans avoir été finalisé. Lorsque cela se produit, les instructions suivantes doivent attendre la fin de la première série d'instructions. Cela s'explique par le fait que le verrouillage au niveau des lignes InnoDB met peut-être à jour les mêmes lignes. Pour plus d'informations, consultez l'instruction SHOW PROCESSLIST sur le site Web de MySQL.

SHOW FULL PROCESSLIST;

Remarque : exécutez la requête SHOW PROCESSLIST en tant qu'utilisateur principal. Si vous n'êtes pas l'utilisateur principal, vous devez disposer des privilèges d'administrateur du serveur MySQL PROCESS pour voir tous les threads en cours d'exécution sur une instance MySQL. Sans les privilèges d'administrateur, SHOW PROCESSLIST affiche uniquement les threads associés au compte MySQL que vous utilisez.

La table INNODB_TRX fournit des informations sur toutes les transactions InnoDB en cours d'exécution qui ne sont pas des transactions en lecture seule.

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

La table INNODB_LOCKS fournit des informations sur les verrous demandés par une transaction InnoDB, mais non reçus.

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

La table INNODB_LOCK_WAITS fournit une ou plusieurs lignes pour chaque transaction InnoDB bloquée.

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Vous pouvez exécuter une requête semblable à celle-ci afin d'identifier les transactions en attente et celles qui bloquent les transactions en attente. Pour plus d'informations, reportez-vous à l'article Utilisation des informations de verrouillage et des transactions InnoDB sur le site Web de MySQL.

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       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_trx_id;

La requête SHOW ENGINE INNODB STATUS fournit des informations provenant du moniteur InnoDB standard sur l'état du moteur de stockage InnoDB. Pour plus d'informations, consultez l'instruction SHOW ENGINE sur le site Web de MySQL.

SHOW ENGINE INNODB STATUS;

SHOW GLOBAL SESSION STATUS fournit des informations sur l'état du serveur. Pour plus d'informations, consultez l'instruction SHOW STATUS sur le site Web de MySQL.

SHOW GLOBAL STATUS;

Remarque : ces requêtes ont été testées sur Aurora 2.x (MySQL 5.7), Aurora 1. x (MySQL 5.6) et MariaDB 10.x. En outre, la table INFORMATION_SCHEMA.INNODB_LOCKS n'est plus prise en charge à partir de MySQL 5.7.14 et supprimée dans MySQL 8.0. La table performance_schema.data_locks remplace la table INFORMATION_SCHEMA.INNODB_LOCKS. Pour plus d'informations, consultez la table data_locks sur le site Web de MySQL.

Analyse des journaux et activation de la surveillance

Lorsque vous analysez des journaux ou souhaitez activer la surveillance dans Amazon RDS for MySQL, envisagez les approches suivantes :

  • Analysez le journal de requêtes générales MySQL pour connaître l'activité de mysqld à un moment spécifique. Vous pouvez également afficher les requêtes en cours d'exécution sur votre instance à une heure spécifique, y compris des informations sur les connexions/déconnexions des clients. Pour plus d'informations, reportez-vous à l'article Journal de requêtes générales sur le site Web de MySQL.
    Remarque : lorsque vous activez le journal de requêtes générales sur de longues périodes, les journaux consomment du stockage et peuvent contribuer à la saturation des performances.
  • Analysez les journaux de requêtes lentes de MySQL afin d'y trouver les requêtes dont l'exécution prend plus de temps que le nombre de secondes défini pour long_query_time. Vous pouvez également examiner votre charge de travail et analyser vos requêtes pour améliorer les performances et la consommation de la mémoire. Pour plus d'informations, consultez la section Journal des requêtes lentes sur le site Web de MySQL. Conseil : lorsque vous utilisez le journal de requêtes lentes ou le journal de requêtes générales, définissez le paramètre log_output sur FILE (fichier).
  • Utilisez le plugin d'audit MariaDB pour auditer l'activité de la base de données. Par exemple, vous pouvez suivre les utilisateurs qui se connectent à la base de données ou les requêtes exécutées sur la base de données. Pour plus d'informations, consultez la section Prise en charge de MariaDB Audit Plugin.
  • Si vous utilisez Aurora pour MySQL, vous pouvez également faire appel à la fonction Advanced Auditing (audit avancé). L'audit peut vous permettre de mieux contrôler les types de requêtes que vous souhaitez journaliser. Il réduit les frais généraux de journalisation.
  • Utilisez le paramètre innodb_print_all_deadlocks pour vérifier les blocages et le verrouillage des ressources. Vous pouvez utiliser ce paramètre afin d'enregistrer des informations sur les blocages au niveau des transactions utilisateur InnoDB dans le journal des erreurs MySQL. Pour plus d'informations, consultez la section innodb_print_all_deadlocks sur le site Web de MySQL.

Analyse et optimisation de la charge de travail élevée du processeur

Une fois la requête responsable de l'utilisation élevée du processeur identifiée, optimisez votre charge de travail afin de réduire cette consommation.

Si vous voyez une requête qui n'est pas nécessaire à votre charge de travail, vous pouvez mettre fin à la session à l'aide de la commande suivante :

CALL mysql.rds_kill(processID);

Pour trouver le processID d'une requête, exécutez la commande SHOW FULL PROCESSLIST.

Si vous ne souhaitez pas arrêter la requête, optimisez-la à l'aide de la commande EXPLAIN. Cette commande montre les étapes individuelles impliquées dans l'exécution de la requête. Pour plus d'informations, consultez l'article Optimisation des requêtes avec EXPLAIN sur le site Web de MySQL.

Pour passer en revue les informations du profil, activez PROFILING. La commande PROFILING peut indiquer l'utilisation des ressources pour les instructions exécutées au cours de la section actuelle. Pour plus d'informations, consultez l'instruction SHOW PROFILE sur le site Web de MySQL.

Pour mettre à jour les statistiques des tables, utilisez ANALYZE TABLE. La commande ANALYZE TABLE peut aider l'optimiseur à choisir un plan d'exécution approprié. Pour plus d'informations, consultez l'instruction ANALYZE TABLE sur le site Web de MySQL.