Monitoraggio delle Query Attive: Utilizzo di pg_stat_activity per l'Ottimizzazione delle Prestazioni

Quando il tuo database rallenta improvvisamente, devi sapere cosa sta facendo PostgreSQL in questo momento. pg_stat_activity mostra query attive, sessioni inattive, attese di lock e transazioni aperte, permettendoti di distinguere una query lenta da una bloccata.

Usalo durante gli incidenti, ma tieni anche alcune query salvate per controlli di routine. Gli esempi seguenti si concentrano su sistemi PostgreSQL in cui hai il permesso di leggere l'attività per le sessioni che devi ispezionare.

Comprendere la Vista pg_stat_activity

pg_stat_activity è una vista di sistema dinamica con una riga per ogni processo server connesso al cluster del database. Include backend client, worker in background e sessioni inattive ma ancora connesse.

Monitorare questa vista ti permette di vedere esattamente cosa sta facendo il database in questo momento, rendendola preziosa per il debug di cali improvvisi di prestazioni o per diagnosticare problemi di contesa troppo transitori per essere catturati efficacemente dai normali file di log.

Colonne Chiave per l'Analisi delle Prestazioni

Mentre pg_stat_activity contiene decine di colonne, le seguenti sono essenziali per diagnosticare problemi di prestazioni:

Nome Colonna	Descrizione	Rilevanza per l'Ottimizzazione
pid	ID del processo del backend.	Necessario per cancellare o terminare le sessioni.
datname	Nome del database a cui questo backend è connesso.	Aiuta a circoscrivere il monitoraggio in ambienti multi-database.
usename	Utente che ha avviato la connessione.	Identifica attività specifiche di applicazioni o utenti.
application_name	Nome dell'applicazione che si connette (se impostato dal client).	Ottimo per identificare connessioni da microservizi specifici.
state	Stato corrente dell'attività (es. active, idle, idle in transaction).	Indicatore principale di cosa sta facendo il backend.
query	La query corrente, o l'ultima query per sessioni inattive. La visibilità può essere limitata da privilegi e impostazioni.	Identifica l'istruzione SQL coinvolta.
query_start	Timestamp di quando è iniziata l'esecuzione della query corrente.	Usato per calcolare la durata della query.
wait_event_type e wait_event	Dettagli su cosa sta aspettando il processo (es. acquisizione lock, I/O).	Critico per diagnosticare contesa e blocchi.

Casi d'Uso Pratici per il Monitoraggio

Il vero potere di pg_stat_activity deriva dal filtrare i dati per rispondere a domande specifiche sulle prestazioni.

Visualizzare Tutte le Query Attive

Per vedere solo i processi che stanno attualmente eseguendo un'istruzione (non inattivi), filtra la vista per la colonna state.

-- Visualizza tutte le query attualmente in esecuzione
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Identificare Query di Lunga Durata

Identificare le query che sono in esecuzione da più tempo del previsto è spesso il primo passo nell'ottimizzazione delle prestazioni. Queste query possono consumare risorse, causare picchi di I/O o mantenere lock.

Per identificare le query in esecuzione da più di una soglia specifica (es. 5 secondi), usa la sottrazione di intervalli con now() e query_start.

-- Trova le query in esecuzione da più di 5 secondi
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;

Usa una soglia adatta al tuo carico di lavoro. Una query di checkout di cinque secondi può essere grave in un'app OLTP, mentre una query di reportistica di cinque minuti può essere normale se eseguita fuori orario.

Diagnosticare Sessioni Inattive in Transazione

Una connessione che è idle in transaction ha avviato una transazione ma non l'ha ancora committata o rollbackata. Sta aspettando che il client invii il comando successivo. Queste sessioni possono mantenere lock e tenere visibili vecchie versioni di righe, ritardando la pulizia da parte di autovacuum e contribuendo al gonfiore delle tabelle.

-- Trova le sessioni inattive che mantengono una transazione aperta
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;

Se trovi sessioni che mantengono transazioni aperte per minuti o ore, controlla il percorso dell'applicazione che ha aperto la transazione. Le cause comuni includono la mancata gestione del rollback dopo un'eccezione, una connessione restituita a un pool prima della pulizia, o una sessione amministrativa interattiva lasciata aperta.

Analizzare la Contesa di Lock e i Blocchi

Quando una query si blocca, spesso è in attesa di un lock detenuto da un altro processo. La vista pg_stat_activity, combinata con pg_locks, è cruciale per diagnosticare la contesa.

Per trovare le sessioni che stanno attualmente aspettando una risorsa (un lock, I/O, ecc.), guarda la colonna wait_event. Se una sessione è bloccata, il suo wait_event_type sarà spesso Lock.

-- Identifica i processi attualmente bloccati da un lock
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;

Per una rapida vista "chi blocca chi", PostgreSQL fornisce anche pg_blocking_pids().

-- Mostra le sessioni bloccate e le sessioni che le bloccano
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';

Se questo restituisce una richiesta web bloccata in attesa di un lungo aggiornamento amministrativo, cancellare la query amministrativa può essere più sicuro che terminare la sessione dell'applicazione.

Gestione delle Sessioni Problematiche

Una volta identificata una query o sessione problematica tramite il suo ID di processo (pid), PostgreSQL fornisce due funzioni per gestirla:

Cancellare una Query con pg_cancel_backend

Questa funzione tenta di fermare gentilmente l'esecuzione di una query specifica. La sessione stessa rimane connessa e disponibile per query future.

-- Esempio: Cancella la query in esecuzione sul PID 12345
SELECT pg_cancel_backend(12345);

Terminare una Sessione con pg_terminate_backend

Questa funzione disconnette forzatamente il processo backend dal server. Se la sessione era nel mezzo di una transazione, PostgreSQL eseguirà automaticamente il rollback della transazione.

-- Esempio: Termina forzatamente la sessione con PID 54321
SELECT pg_terminate_backend(54321);

Prova prima pg_cancel_backend quando la sessione sta semplicemente eseguendo una query errata. Usa pg_terminate_backend per sessioni bloccate, abbandonate o che mantengono una transazione aperta che non può essere pulita normalmente. Il rollback di una transazione grande può richiedere tempo e aggiungere carico I/O, quindi fallo deliberatamente.

Best Practice per il Monitoraggio

Filtra Aggressivamente

Evita SELECT * FROM pg_stat_activity come abitudine predefinita in produzione. L'output è rumoroso e il testo della query potrebbe esporre valori sensibili se le tue applicazioni inviano letterali invece di parametri bind. Seleziona le colonne di cui hai bisogno e filtra per state, datname, application_name o durata.

Usa Strumenti per il Monitoraggio Automatico

I controlli manuali sono utili durante un incidente, ma le tendenze appartengono al monitoraggio. Tieni traccia delle sessioni attive, delle sessioni in attesa, delle transazioni lunghe e dei conteggi di idle in transaction nel tuo dashboard PostgreSQL.

Configura la Registrazione delle Istruzioni

Combina il monitoraggio in tempo reale con i dati storici. Configura parametri come log_min_duration_statement per registrare le query che superano una certa soglia, fornendo dati per l'analisi anche dopo che la query ha terminato l'esecuzione.

Conclusioni

Tieni tre controlli salvati: query attive ordinate per durata, transazioni inattive ordinate per età della transazione e sessioni bloccate con i loro bloccanti. Quando PostgreSQL sembra lento, queste viste ti dicono se ottimizzare le query, correggere la gestione delle transazioni o liberare una sessione bloccante.