Comment puis-je résoudre les problèmes liés à l’utilisation élevée du processeur sur mon instance Amazon RDS for MySQL ou Amazon Aurora MySQL ?

Brève description

L’augmentation de l’utilisation du processeur peut être due à plusieurs facteurs : volumineuses charges de travail lancées par l’utilisateur, multiples requêtes simultanées, ou transactions de longue durée.

Pour identifier la source de l’utilisation du processeur dans votre instance Amazon RDS for MySQL, explorez les approches suivantes :

• Surveillance améliorée
• Performance Insights
• Requêtes détectant la cause d’utilisation de processeurs 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 de la surveillance améliorée

La surveillance améliorée fournit une vue au niveau du système d’exploitation. Cette vue peut vous aider à identifier à un niveau granulaire la cause de la charge élevée du processeur. Par exemple, vous pouvez consulter la charge moyenne, la distribution du processeur (system% ou nice%) et la liste des processus du système d’exploitation.

Grâce à la surveillance améliorée, vous pouvez contrôler les données loadAverageMinute à des intervalles de 1, de 5 et de 15 minutes. Une charge moyenne supérieure au nombre de processeurs virtuels indique que l’instance est soumise à une charge importante. Si la charge moyenne est inférieure au nombre de processeurs virtuels pour la classe d’instance de base de données, c’est que la limitation du processeur n’est pas forcément la cause de la latence d’application. Lors du diagnostic de la cause d’utilisation du processeur, vérifiez la charge moyenne pour éviter les faux positifs.

Par exemple, si vous avez une instance de base de données qui utilise une classe d’instance db.m5.2xlarge avec 3 000 IOPS provisionnées atteignant la limite du processeur, vous pouvez consulter les exemples de métriques suivants pour identifier la cause première de l’utilisation élevée du processeur. Dans l’exemple suivant, huit processeurs virtuels sont associés à la classe d’instance. Pour la même charge moyenne, un dépassement de 170 indique que la machine est soumise à une charge importante pendant la période mesurée :

Charge moyenne en minutes

Quinze	170,25
Cinq	391,31
Un	596,74

Utilisation du processeur

Utilisateur (%)	0,71
Système (%)	4,9
Nice (%)	93,92
Total (%)	99,97

Remarque : Amazon RDS donne à votre charge de travail une priorité supérieure aux autres tâches exécutées sur l’instance de base de données. Pour hiérarchiser ces tâches, les tâches liées à la charge de travail ont une valeur Nice supérieure. Par conséquent, dans Surveillance améliorée, Nice% représente la part du processeur utilisée par votre charge de travail vis-à-vis de la base de données.

Une fois Surveillance améliorée activée, vous pouvez également contrôler la liste des processus du système d’exploitation associée à l’instance de base de données. Surveillance améliorée indique un maximum de 100 processus. Cela peut vous aider à identifier les processus ayant le plus d’impact sur les performances selon l’utilisation du processeur et de la mémoire.

Dans la section Liste des processus du système d’exploitation de Surveillance améliorée, examinez les processus du système d’exploitation et les processus de RDS. Vérifiez le pourcentage d’utilisation du processeur d’un processus mysqld ou Aurora. Ces métriques peuvent vous aider à confirmer si l’augmentation de l’utilisation du processeur est due au système d’exploitation ou aux processus de RDS. Vous pouvez également utiliser ces métriques pour surveiller toute augmentation de l’utilisation du processeur due à mysqld ou à Aurora. Vous pouvez également voir la répartition de l’utilisation du processeur en examinant les métriques relatives à cpuUtilization. Pour plus d’informations, consultez Surveillance des métriques du processeur avec Surveillance améliorée.

Remarque : si vous activez Schéma des performances, vous pouvez mapper l’identifiant de thread du système d’exploitation à celui du 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 de la mémoire d’échange quand je dispose de suffisamment de mémoire ?

Utilisation de Performance Insights

Vous pouvez utiliser Performance Insights pour identifier les requêtes exactes qui s’exécutent sur l’instance en provoquant une utilisation élevée du processeur. Tout d’abord, activez Performance Insights for MySQL. Vous pouvez ensuite utiliser Performance Insights pour optimiser votre charge de travail. Pensez à consulter votre administrateur de base de données.

Pour voir les moteurs de base 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 processeur dans la charge de travail

Avant de pouvoir optimiser votre charge de travail, vous devez identifier la requête à problème. Vous pouvez exécuter les requêtes suivantes en cas de problème de processeurs surchargés, afin d’identifier la cause première de l’utilisation du processeur. Optimisez ensuite votre charge de travail pour réduire l’utilisation du processeur.

La commande SHOW PROCESSLIST affiche les threads en cours d’exécution sur votre instance de MySQL. Parfois, un même l’exécution d’ensemble d’instructions peut se poursuivre sans prendre fin. Dans ce cas, les instructions suivantes doivent attendre la fin de la première série d’instructions. Cela est dû au fait que le verrouillage au niveau des lignes d’InnoDB peut mettre à 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 du système. Si vous n’êtes pas l’utilisateur principal du système, vous devez disposer des privilèges d’administration de serveur MySQL PROCESS pour voir tous les threads exécutés sur une instance de MySQL. Sans privilèges d’administrateur, SHOW PROCESSLIST n’affiche que les threads associés au compte MySQL que vous êtes en train d’utiliser.

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 qu’une transaction InnoDB a demandés, mais pas reçus.

Pour MySQL 5.7 ou version antérieure :

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

Pour MySQL 8.0 :

SELECT * FROM performance_schema.data_locks;

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

Pour MySQL 5.7 ou version antérieure :

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 similaire à la suivante pour voir les transactions en attente et les transactions qui bloquent les transactions en attente. Pour plus d’informations, consultez Using InnoDB transaction and locking information sur le site Web de MySQL.

Pour MySQL 5.7 ou version antérieure :

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

SHOW ENGINE INNODB STATUS;

L’instruction 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), sur Aurora 1.x (MySQL 5.6), est sur MariaDB 10.x. En outre, la table INFORMATION_SCHEMA.INNODB_LOCKS n’est plus prise en charge depuis MySQL 5.7.14 et a été supprimée de MySQL 8.0. La table performance_schema.data_locks remplace la table INFORMATION_SCHEMA.INNODB_LOCKS. Pour plus d’informations, consultez The data_locks table sur le site Web de MySQL.

Analyse de journaux et activation de la surveillance

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

• Analysez le Journal de requête général de MySQL pour voir ce que fait mysqld à un moment précis. Vous pouvez également visualiser les requêtes en cours d’exécution sur votre instance à un moment précis, notamment des informations sur les heures de connexion ou de déconnexion de clients. Pour plus d’informations, consultez The General Query Log sur le site Web de MySQL.
  Remarque : lorsque vous activez le General Query Log pendant de longues périodes, les journaux consomment de l’espace de stockage et peuvent alourdir les performances.
• Analysez les MySQL Slow Query Logs pour trouver les requêtes dont l’exécution prend plus de temps que les secondes que vous avez définies pour long_query_time. Vous pouvez également examiner votre charge de travail et analyser vos requêtes pour améliorer les performances et la charge de mémoire. Pour plus d’informations, consultez The Slow Query Log) sur le site Web de MySQL. Conseil : lorsque vous utilisez Slow Query Log ou General Query Log, définissez le paramètre log_output pour FILE.
• Utilisez le plug-in d’audit MariaDB pour consulter l’activité des bases 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 vis-à-vis de la base de données. Pour plus d’informations, consultez Prise en charge du plug-in d’audit MariaDB.
• Si vous utilisez Aurora pour MySQL, vous pouvez également utiliser Audit avancé. L’audit peut vous permettre de mieux contrôler les types de requêtes que vous souhaitez journaliser. Cela permet de réduire les frais 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 pour enregistrer des informations sur les blocages des transactions utilisateur d’InnoDB dans le journal d’erreurs de MySQL. Pour plus d’informations, consultez innodb_print_all_deadlocks sur le site Web de MySQL.

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

Après avoir identifié la requête qui augmente l’utilisation du processeur, optimisez votre charge de travail pour réduire son utilisation.

Si vous voyez une requête superflue pour votre charge de travail, vous pouvez mettre fin à la connexion à l’aide de la commande suivante :

CALL mysql.rds_kill(processID);

Pour trouver l’identifiant de processus 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. La commande EXPLAIN indique les différentes étapes de l’exécution d’une requête. Pour plus d’informations, consultez Optimizing Queries with EXPLAIN sur le site Web de MySQL.

Pour consulter les détails de profil, activez PROFILING. La commande PROFILING peut indiquer l’utilisation des ressources pour les instructions s’exécutant pendant la session en cours. Pour plus d’informations, consultez l’instruction SHOW PROFILE sur le site Web de MySQL.

Pour mettre à jour les statistiques d’une table, utilisez ANALYZE TABLE. La commande ANALYZE TABLE peut aider l’optimiseur à choisir un plan approprié pour exécuter la requête. Pour plus d’informations, consultez l’instruction ANALYZE TABLE sur le site Web de MySQL.

---

Informations connexes

Amazon RDS for MySQL

Amazon RDS for MariaDB

Comment puis-je activer et surveiller les journaux d’une instance de base de données Amazon RDS MySQL ?

Optimisation d’Amazon RDS for MySQL à l’aide de Performance Insights