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

Dernière mise à jour : 11-02-2022

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)
  • Requêtes qui détectent la cause de l'utilisation du processeur dans la 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. Confirmez 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 les métriques de surveillance du système d'exploitation avec surveillance améliorée.

Remarque : si vous activez 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 les Informations de Performance 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. Veillez à consulter votre administrateur de bases de données.

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

Utilisation de requêtes pour détecter la cause de l'utilisation du CPU dans la 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 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'administration 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.

Pour MySQL 5.7 ou antérieur :

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

Pour MySQL 8.0 :

SELECT * FROM performance_schema.data_lock;

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

Pour MySQL 5.7 ou antérieur :

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

Pour MySQL 8.0 :

SELECT * FROM performance_schema.data_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.

Pour MySQL 5.7 ou antérieur :

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;

Pour MySQL 8.0 :

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;

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 instruction SHOW ENGINE sur le site web de MySQL.

SHOW ENGINE INNODB STATUS;

La requête SHOW GLOBAL SESSION STATUS fournit des informations sur l'état du serveur. Pour plus d'informations, consultez 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 les journaux ou que vous souhaitez activer la surveillance dans Amazon RDS for MySQL, considérez 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 contre la base de données. Pour plus d'informations, consultez Prise en charge de MariaDB Audit Plugin.
  • Si vous utilisez Aurora pour MySQL, vous pouvez également utiliser 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 terminer la requête, optimisez-la à l'aide de la commande EXPLAIN. Cette commande montre les étapes individuelles impliquées dans l'exécution d'une requête. Pour plus d'informations, consultez l'article Optimisation des requêtes avec EXPLAIN sur le site web de MySQL.

Pour consulter les détails 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 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 exécuter la requête. Pour plus d'informations, consultez instruction ANALYZE TABLE sur le site web de MySQL.