Comment puis-je résoudre un problème d’utilisation élevée du processeur pour Amazon RDS ou Amazon Aurora PostgreSQL ?

Brève description

Si vous constatez que votre charge utilise beaucoup le processeur, utilisez une combinaison des outils suivants pour en identifier la cause :

• Métriques Amazon CloudWatch
• Métriques de surveillance améliorée
• Métriques d’analyse des performances
• Vue et catalogues PostgreSQL natifs, tels que pg_stat_statements, pg_stat_activity et pg_stat_user_tables
• Connexions inactives dans la base de données
• Commande ANALYZE
• Paramètres de journalisation PostgreSQL pour enregistrer les requêtes de longue durée, l’autovacuum, les délais de verrouillage et les demandes de connexion et de déconnexion

Résolution

Métriques Amazon CloudWatch

Utilisez les métriques CloudWatch pour identifier les tendances du processeur sur de longues périodes. Comparez les graphiques WriteIOPs, ReadIOPs, ReadThroughput et WriteThroughput avec l’utilisation du processeur pour déterminer les moments où la charge de travail a fortement sollicité le processeur.

Une fois cette période identifiée, examinez les données de surveillance améliorée qui sont associées à votre instance de base de données. Vous pouvez configurer la surveillance améliorée pour collecter des données à des intervalles de 1, 5, 10, 15, 30 ou 60 secondes. Vous pouvez ainsi collecter des données à un niveau plus granulaire que CloudWatch.

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 la cause de l’utilisation élevée du processeur par une charge à un niveau granulaire. 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 consulter les données loadAverageMinute à des intervalles de 1, 5 et 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, il est possible que la limitation du processeur ne soit pas à l’origine de la latence de l’application. Lors du diagnostic de la cause de l’utilisation du processeur, vérifiez la charge moyenne pour éviter les faux positifs.

Par exemple, supposons que vous ayez une instance de base de données utilisant une classe d’instance db.m5.2xlarge de 3 000 IOPS provisionnées qui atteint la limite 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
Une	596,74

Utilisation du processeur

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

Remarque : dans le cadre de la surveillance améliorée, Nice% représente la quantité de processeur utilisée par votre charge de travail par rapport à la base de données.

Une fois que la surveillance améliorée est activée, vous pouvez également consulter la liste des processus du système d’exploitation associée à l’instance de base de données. La surveillance améliorée affiche un maximum de 100 processus et peut vous aider à identifier ceux qui ont le plus d’impact sur les performances. Vous pouvez combiner les résultats de la surveillance améliorée avec les résultats de pg_stat_activity pour vous aider à identifier l’utilisation des ressources par les requêtes.

Informations sur les performances

Utilisez l’analyse des performances d’Amazon RDS pour identifier la requête responsable de la charge de la base de données. Consultez l’onglet SQL qui correspond à une période donnée.

Vue et catalogues PostgreSQL natifs

Au niveau du moteur de base de données, vous pouvez utiliser pg_stat_activity et pg_stat_statements. Si le problème se produit en temps réel, utilisez pg_stat_activity ou pg_stat_statements pour regrouper les machines, les clients et les adresses IP qui envoient le plus de trafic. Utilisez ces données pour détecter des augmentations au fil du temps ou des augmentations liées aux serveurs d’applications. Vous pouvez également vérifier si un serveur d’applications présente des sessions bloquées ou des problèmes de verrouillage. Pour en savoir plus, consultez les sections pg_stat_activity et pg_stat_statements sur le site Web de PostgreSQL.

Pour activer pg_stat_statements, modifiez le groupe de paramètres personnalisés existant et définissez les valeurs suivantes :

• Ajouter pg_stat_statements à shared_preload_libraries
• track_activity_query_size = 4096
• pg_stat_statements.track = ALL
• pg_stat_statements.max = 10000

Choisissez Appliquer immédiatement, puis redémarrez l’instance de base de données. Exécutez ensuite une commande de ce type sur la base de données que vous souhaitez surveiller :

demo=> select current_database();current_database
------------------
demo
(1 row)     

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Remarque : la commande précédente installe l’extension dans la base de données de démonstration.

Une fois que pg_stat_statements est configuré, utilisez l’une des méthodes suivantes pour surveiller la sortie.

Pour répertorier les requêtes par durée totale (total_time) et savoir quelle requête reste le plus longtemps dans la base de données, exécutez l’une des requêtes suivantes :

PostgreSQL versions 12 et antérieures

SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versions 13 et ultérieures

SELECT total_plan_time+total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY 1 DESC LIMIT 10;

Pour répertorier les requêtes présentant un taux d’accès moindre au cache de mémoire tampon, exécutez l’une des requêtes suivantes :

PostgreSQL versions 12 et antérieures

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versions 13 et ultérieures

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY 3 DESC LIMIT 10;

Pour répertorier les requêtes par exécution afin de les échantillonner au fil du temps, exécutez la requête suivante :

PostgreSQL versions 12 et antérieures

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versions 13 et ultérieures

SELECT query,
calls,
(total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,
 rows/calls as avg_rows,
temp_blks_read/calls as avg_tmp_read,
 temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY 3 DESC LIMIT 10;

Connexions inactives dans la base de données

Les connexions inactives dans la base de données peuvent consommer des ressources de calcul, telles que la mémoire et le processeur. Si le processeur est beaucoup sollicité par votre instance, vérifiez que la base de données ne contient pas de connexions inactives. Pour en savoir plus, consultez la page Impact des connexions PostgreSQL inactives sur les performances. Pour détecter les connexions inactives, utilisez la surveillance améliorée pour consulter la liste des processus du système d’exploitation. Cette liste est toutefois limitée à un maximum de 100 processus.

Pour détecter les connexions inactives, exécutez les requêtes suivantes au niveau de la base de données.

Exécutez les requêtes suivantes pour afficher les sessions actives et inactives en cours :

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activity
WHERE query != '<IDLE>
'AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin'
ORDER BY query_start desc;

SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query
FROM pg_stat_activity
WHERE not pid=pg_backend_pid()
AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin';

Exécutez les requêtes suivantes pour obtenir le nombre de connexions par nom d’utilisateur et par nom d’application :

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;
    application_name    | count
------------------------+-------
 psql                   |     1
 PostgreSQL JDBC Driver |     1
                        |     5
(3 rows)

postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;
 usename  | count
----------+-------
 master   |     4
 user1    |     1
 rdsadmin |     2
(3 rows)

Une fois les connexions inactives identifiées, exécutez l’une des requêtes suivantes pour mettre fin à ces connexions :

psql=> SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'example-username'
   AND pid <> pg_backend_pid()
   AND state in ('idle');

-ou-

SELECT pg\_terminate\_backend (example-pid);

Si votre application génère trop de connexions, modifiez-la afin d’éviter d’utiliser la mémoire et les ressources du processeur pour gérer ces connexions. Pour ce faire, vous pouvez soit modifier l’application pour limiter le nombre de connexions, soit utiliser une solution de regroupement de connexions tel que PGBouncer. Vous pouvez également utiliser le Proxy Amazon RDS, un service géré qui vous permet de configurer un regroupement de connexions.

Commande ANALYZE

La commande ANALYZE collecte des statistiques sur le contenu des tables de la base de données et stocke les résultats dans le catalogue de système pg_statistic. Le planificateur de requêtes utilise ensuite ces statistiques pour déterminer les plans d’exécution les plus efficaces pour les requêtes. Si vous n’exécutez pas la commande ANALYZE fréquemment sur les tables de votre base de données, les requêtes peuvent consommer davantage de ressources de calcul. Les requêtes consomment plus de ressources en raison des statistiques obsolètes qui sont présentes dans le système pour les relations auxquelles vous accédez. Ces problèmes surviennent dans les conditions suivantes :

• La fonction autovacuum n’est pas fréquemment exécutée.
• La commande ANALYZE n’a pas été exécutée après une mise à niveau vers une version majeure.

La fonction autovacuum n’est pas fréquemment exécutée : la fonction autovacuum est un démon qui automatise l’exécution des commandes VACUUM et ANALYZE. Autovacuum détecte la présence de tables surchargées dans la base de données et récupère l’espace à des fins de réutilisation. Le démon autovacuum s’assure que les statistiques des tables sont régulièrement mises à jour en exécutant l’opération ANALYZE chaque fois que le seuil défini de tuples est dépassé. Le planificateur de requêtes peut ensuite utiliser le plan de requêtes le plus efficace en fonction des statistiques récentes. La non-exécution d’autovacuum peut entraîner la création de plans de requêtes sous-optimaux par le planificateur de requêtes et une augmentation de la consommation de ressources par les requêtes. Pour en savoir plus, consultez les ressources suivantes :

• Comprendre la fonction autovacuum dans les environnements Amazon RDS for PostgreSQL
• Étude de cas : le réglage de la fonction autovacuum dans Amazon RDS for PostgreSQL

Pour connaître la date de la dernière exécution des fonctions autovacuum et auto-analyze sur les tables, exécutez la requête suivante :

SELECT relname, last\_autovacuum, last\_autoanalyze FROM pg\_stat\_user\_tables;

La commande ANALYZE n’a pas été exécutée après une mise à niveau vers une version majeure : les bases de données PostgreSQL rencontrent généralement des problèmes de performances après toute mise à niveau majeure de la version du moteur. Ces problèmes s’expliquent souvent par le fait que l’opération ANALYZE n’a pas été effectuée après la mise à niveau pour actualiser la table pg_statistic. Exécutez l’opération ANALYZE pour chaque base de données de votre instance de base de données RDS for PostgreSQL. Les statistiques de l’optimiseur ne sont pas transférées lors d’une mise à niveau vers une version majeure. Par conséquent, pour éviter tous problèmes de performances dus à une utilisation accrue des ressources, vous devez régénérer toutes les statistiques.

Pour générer des statistiques pour toutes les tables standard de la base de données actuelle après une mise à niveau vers une version majeure, exécutez la commande suivante sans aucun paramètre :

ANALYZE VERBOSE

Paramètres de journalisation de PostgreSQL

Utilisez Amazon RDS for PostgreSQL pour activer la journalisation des requêtes. Consultez ensuite les journaux d’erreurs de PostgreSQL pour vérifier que vos paramètres log_min_duration_statement et log_statement sont définis sur des valeurs appropriées. Pour en savoir plus, consultez la page Error reporting and logging sur le site Web de PostgreSQL.

Réduction de l’utilisation du processeur

Une fois que vous avez identifié les requêtes à l’origine d’une utilisation élevée du processeur, appliquez les méthodes suivantes pour réduire davantage la consommation du processeur :

• Pour identifier des possibilités de réglage, utilisez les fonctions EXPLAIN et EXPLAIN ANALYZE pour connaître les mises en garde. Pour en savoir plus, consultez la page EXPLAIN sur le site Web de PostgreSQL.
• Si une requête est exécutée de manière répétée, utilisez des instructions préparées pour réduire la pression sur votre processeur. L’exécution répétée d’instructions préparées permet en effet de mettre en cache le plan de requête. Comme le plan est déjà mis en cache pour les exécutions suivantes, la durée de planification est considérablement réduite.

Informations connexes

Bonnes pratiques d’utilisation de PostgreSQL