Surveillance des requêtes actives : Utiliser pg_stat_activity pour l'optimisation des performances

Lorsque votre base de données ralentit soudainement, vous devez savoir ce que PostgreSQL fait en ce moment. pg_stat_activity affiche les requêtes actives, les sessions inactives, les attentes de verrouillage et les transactions ouvertes, vous permettant de distinguer une requête lente d'une requête bloquée.

Utilisez-le pendant les incidents, mais conservez également quelques requêtes sauvegardées pour des vérifications de routine. Les exemples ci-dessous se concentrent sur les systèmes PostgreSQL où vous avez l'autorisation de lire l'activité des sessions que vous devez inspecter.

Comprendre la vue pg_stat_activity

pg_stat_activity est une vue système dynamique avec une ligne pour chaque processus serveur connecté au cluster de base de données. Cela inclut les backends clients, les workers en arrière-plan et les sessions inactives mais toujours connectées.

La surveillance de cette vue vous permet de voir exactement ce que la base de données fait maintenant, ce qui la rend inestimable pour déboguer les baisses de performance soudaines ou diagnostiquer les problèmes de contention trop transitoires pour être capturés efficacement par les fichiers journaux typiques.

Colonnes clés pour l'analyse des performances

Bien que pg_stat_activity contienne des dizaines de colonnes, les suivantes sont essentielles pour diagnostiquer les problèmes de performance :

Nom de colonne	Description	Pertinence pour l'optimisation
pid	ID du processus du backend.	Requis pour annuler ou terminer des sessions.
datname	Nom de la base de données à laquelle ce backend est connecté.	Aide à délimiter la surveillance dans les environnements multi-bases de données.
usename	Utilisateur qui a initié la connexion.	Identifie l'activité spécifique d'une application ou d'un utilisateur.
application_name	Nom de l'application qui se connecte (si défini par le client).	Excellent pour identifier les connexions provenant de microservices spécifiques.
state	Statut actuel de l'activité (par exemple, active, idle, idle in transaction).	Indicateur principal de ce que fait le backend.
query	La requête en cours, ou la dernière requête pour les sessions inactives. La visibilité peut être limitée par les privilèges et les paramètres.	Identifie l'instruction SQL impliquée.
query_start	Horodatage du début de l'exécution de la requête en cours.	Utilisé pour calculer la durée de la requête.
wait_event_type & wait_event	Détails sur ce que le processus attend (par exemple, acquisition de verrou, E/S).	Critique pour diagnostiquer la contention et le blocage.

Cas d'utilisation pratiques de la surveillance

La véritable puissance de pg_stat_activity réside dans le filtrage des données pour répondre à des questions spécifiques de performance.

Afficher toutes les requêtes actives

Pour voir uniquement les processus qui exécutent actuellement une instruction (pas inactifs), filtrez la vue par la colonne state.

-- Afficher toutes les requêtes en cours d'exécution
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Identifier les requêtes de longue durée

Identifier les requêtes qui s'exécutent depuis plus longtemps que prévu est souvent la première étape de l'optimisation des performances. Ces requêtes peuvent consommer des ressources, provoquer des pics d'E/S ou maintenir des verrous.

Pour identifier les requêtes qui s'exécutent depuis plus d'un seuil spécifique (par exemple, 5 secondes), utilisez la soustraction d'intervalles avec now() et query_start.

-- Trouver les requêtes qui s'exécutent depuis plus de 5 secondes
SELECT
    pid,
    usename,
    datname,
    state,
    (now() - query_start) AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
  AND (now() - query_start) > interval '5 seconds'
ORDER BY duration DESC;

Utilisez un seuil adapté à votre charge de travail. Une requête de caisse de cinq secondes peut être grave dans une application OLTP, tandis qu'une requête de rapport de cinq minutes peut être normale si elle s'exécute en dehors des heures de pointe.

Diagnostiquer les sessions inactives en transaction

Une connexion qui est idle in transaction a démarré une transaction mais ne l'a pas validée ou annulée. Elle attend que le client envoie la commande suivante. Ces sessions peuvent maintenir des verrous et garder les anciennes versions de lignes visibles, ce qui peut retarder le nettoyage par autovacuum et contribuer au gonflement des tables.

-- Trouver les sessions inactives mais qui maintiennent une transaction ouverte
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - xact_start AS txn_duration,
    query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY txn_duration DESC;

Si vous trouvez des sessions qui maintiennent des transactions ouvertes pendant des minutes ou des heures, vérifiez le chemin d'accès à l'application qui a ouvert la transaction. Les causes courantes incluent l'absence de gestion de rollback après une exception, une connexion renvoyée à un pool avant le nettoyage, ou une session d'administration interactive laissée ouverte.

Analyser la contention de verrouillage et le blocage

Lorsqu'une requête se bloque, elle attend souvent un verrou détenu par un autre processus. La vue pg_stat_activity, combinée à pg_locks, est cruciale pour diagnostiquer la contention.

Pour trouver les sessions qui attendent actuellement une ressource (un verrou, des E/S, etc.), regardez la colonne wait_event. Si une session est bloquée, son wait_event_type sera souvent Lock.

-- Identifier les processus actuellement bloqués par un verrou
SELECT
    a.pid,
    a.usename,
    a.query_start,
    a.query,
    a.wait_event,
    a.wait_event_type
FROM pg_stat_activity a
WHERE a.state = 'active'
  AND a.wait_event IS NOT NULL
ORDER BY a.query_start;

Pour une vue rapide "qui bloque qui", PostgreSQL fournit également pg_blocking_pids().

-- Afficher les sessions bloquées et les sessions qui les bloquent
SELECT
    blocked.pid AS blocked_pid,
    blocked.usename AS blocked_user,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.usename AS blocking_user,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE blocked.wait_event_type = 'Lock';

Si cela renvoie une requête web bloquée attendant une longue mise à jour administrative, annuler la requête administrative peut être plus sûr que de terminer la session applicative.

Gérer les sessions problématiques

Une fois qu'une requête ou session problématique est identifiée à l'aide de son ID de processus (pid), PostgreSQL fournit deux fonctions pour la gérer :

Annuler une requête avec pg_cancel_backend

Cette fonction tente d'arrêter gracieusement l'exécution d'une requête spécifique. La session elle-même reste connectée et disponible pour les requêtes futures.

-- Exemple : Annuler la requête en cours d'exécution sur le PID 12345
SELECT pg_cancel_backend(12345);

Terminer une session avec pg_terminate_backend

Cette fonction déconnecte de force le processus backend du serveur. Si la session était au milieu d'une transaction, PostgreSQL annulera automatiquement la transaction.

-- Exemple : Forcer la terminaison de la session avec le PID 54321
SELECT pg_terminate_backend(54321);

Essayez d'abord pg_cancel_backend lorsque la session exécute simplement une mauvaise requête. Utilisez pg_terminate_backend pour les sessions qui sont bloquées, abandonnées ou qui maintiennent une transaction ouverte qui ne peut pas être nettoyée normalement. L'annulation d'une grande transaction peut prendre du temps et ajouter une charge d'E/S, alors faites-le délibérément.

Meilleures pratiques pour la surveillance

Filtrer de manière agressive

Évitez SELECT * FROM pg_stat_activity comme habitude de production par défaut. La sortie est bruyante et le texte de la query peut exposer des valeurs sensibles si vos applications envoient des littéraux au lieu de paramètres de liaison. Sélectionnez les colonnes dont vous avez besoin et filtrez par state, datname, application_name ou durée.

Utiliser des outils pour la surveillance automatisée

Les vérifications manuelles sont utiles lors d'un incident, mais les tendances appartiennent à la surveillance. Suivez les sessions actives, les sessions en attente, les transactions longues et les compteurs idle in transaction dans votre tableau de bord PostgreSQL.

Configurer la journalisation des instructions

Combinez la surveillance en temps réel avec des données historiques. Configurez des paramètres comme log_min_duration_statement pour journaliser les requêtes qui dépassent un certain seuil, fournissant des données pour l'analyse même après la fin de l'exécution de la requête.

À retenir

Conservez trois vérifications sauvegardées : les requêtes actives triées par durée, les transactions inactives triées par âge de la transaction, et les sessions bloquées avec leurs bloqueurs. Lorsque PostgreSQL semble lent, ces vues vous indiquent s'il faut optimiser le SQL, corriger la gestion des transactions ou libérer une session bloquante.