Comment vérifier les requêtes en cours d'exécution et diagnostiquer les problèmes de consommation des ressources pour mon instance de base de données Amazon RDS ou Aurora PostgreSQL ?

Résolution

Vérification des requêtes en cours d'exécution

Votre compte utilisateur doit disposer du rôle rds_superuser pour afficher tous les processus qui s'exécutent sur une instance de base de données RDS pour PostgreSQL ou Aurora PostgreSQL. Sinon, pg_stat_activity affiche uniquement les requêtes qui sont en cours d'exécution pour ses propres processus. Pour plus d'informations, consultez la documentation PostgreSQL relative au Récupérateur de statistiques.

1.    Connectez-vous à l'instance de base de données qui exécute PostgreSQL ou Aurora PostgreSQL.

2.    Exécutez la commande suivante :

SELECT * FROM pg_stat_activity ORDER BY pid;

Vous pouvez également modifier cette commande pour afficher la liste des requêtes en cours d'exécution classées selon la date à laquelle les connexions ont été établies :

SELECT * FROM pg_stat_activity ORDER BY backend_start;

Si la valeur de la colonne est vide, aucune transaction n'est ouverte dans cette session :

SELECT * FROM pg_stat_activity ORDER BY xact_start;

Vous pouvez également afficher la même liste de requêtes en cours d'exécution classées selon le moment où la dernière requête a été exécutée :

SELECT * FROM pg_stat_activity ORDER BY query_start;

Pour obtenir une vue agrégée des événements d'attente, le cas échéant, exécutez la commande suivante :

select state, wait_event, wait_event_type, count(*) from pg_stat_activity group by 1,2,3 order by wait_event;

Diagnostic de la consommation des ressources

En utilisant pg_stat_activity et en activant la surveillance améliorée, vous pouvez identifier la requête ou le processus qui consomme de grandes quantités de ressources système. Après avoir activé la surveillance améliorée, assurez-vous que le niveau de granularité défini est suffisant pour voir les informations dont vous avez besoin pour diagnostiquer le problème. Ensuite, vous pouvez vérifier pg_stat_activity pour voir les activités en cours dans votre base de données et les métriques de surveillance améliorée à ce moment-là.

1.    Identifiez la requête qui consomme des ressources en consultant la métrique de la liste des processus du système d'exploitation. Dans l'exemple suivant, le processus prend environ 95 % du temps du processeur sur l'instance de base de données RDS. L'ID de processus (pid) du processus est 14431 et le processus exécute une instruction SELECT. Vous pouvez également voir l'utilisation de la mémoire système en vérifiant la valeur MEM%.

2.    Connectez-vous à l'instance de base de données qui exécute PostgreSQL ou Aurora PostgreSQL.

3.    Identifiez l'activité actuelle de la session en exécutant la commande suivante :

SELECT * FROM pg_stat_activity WHERE pid = PID;

Remarque : remplacez PID par le pid que vous avez identifié à l'étape 1.

4.    Vérifiez le résultat de la commande :

datid            | 14008
datname          | postgres
pid              | 14431
usesysid         | 16394
usename          | master
application_name | psql
client_addr      | 27.0.3.145
client_hostname  |
client_port      | 52003
backend_start    | 2020-03-11 23:08:55.786031+00
xact_start       | 2020-03-11 23:12:16.960942+00
query_start      | 2020-03-11 23:12:16.960942+00
state_change     | 2020-03-11 23:12:16.960945+00
wait_event_type  |
wait_event       |
state            | active
backend_xid      |
backend_xmin     | 812
query            | SELECT COUNT(*) FROM columns c1, columns c2, columns c3, columns c4, columns c5;
backend_type     | client backend

Pour arrêter le processus qui exécute la requête, lancez la requête suivante à partir d'une autre session. Veillez à remplacer PID par le pid du processus que vous avez identifié à l'étape 3.

SELECT pg_terminate_backend(PID);

Important : avant de résilier les transactions, évaluez l'impact potentiel de chaque transaction sur l'état de votre base de données et de votre application.

Informations connexes

Comment résoudre les problèmes liés à l'utilisation élevée du processeur pour Amazon RDS ou Amazon Aurora PostgreSQL ?

Création de rôles

Surveillance améliorée

Documentation PostgreSQL relative à psql

Documentation PostgreSQL relative à pg_stat_activity