Überwachung aktiver Abfragen: Verwendung von pg_stat_activity zur Leistungsoptimierung

Wenn Ihre Datenbank plötzlich langsamer wird, müssen Sie wissen, was PostgreSQL gerade tut. pg_stat_activity zeigt aktive Abfragen, Leerlaufsitzungen, Sperrwartezustände und offene Transaktionen an, sodass Sie eine langsame Abfrage von einer blockierten unterscheiden können.

Verwenden Sie es während Vorfällen, aber halten Sie auch einige gespeicherte Abfragen für Routineprüfungen bereit. Die folgenden Beispiele konzentrieren sich auf PostgreSQL-Systeme, bei denen Sie die Berechtigung haben, die Aktivität für die Sitzungen zu lesen, die Sie überprüfen müssen.

Die Ansicht pg_stat_activity verstehen

pg_stat_activity ist eine dynamische Systemansicht mit einer Zeile für jeden Serverprozess, der mit dem Datenbankcluster verbunden ist. Dazu gehören Client-Backends, Hintergrundarbeiter und Sitzungen, die im Leerlauf sind, aber noch verbunden sind.

Die Überwachung dieser Ansicht ermöglicht es Ihnen, genau zu sehen, was die Datenbank jetzt tut, was sie für das Debuggen plötzlicher Leistungseinbrüche oder die Diagnose von Konfliktsituationen, die für typische Protokolldateien zu flüchtig sind, unschätzbar macht.

Wichtige Spalten für die Leistungsanalyse

Obwohl pg_stat_activity Dutzende von Spalten enthält, sind die folgenden bei der Diagnose von Leistungsproblemen unerlässlich:

Spaltenname	Beschreibung	Relevanz für die Optimierung
pid	Prozess-ID des Backends.	Erforderlich zum Abbrechen oder Beenden von Sitzungen.
datname	Name der Datenbank, mit der dieses Backend verbunden ist.	Hilft bei der Eingrenzung der Überwachung in Umgebungen mit mehreren Datenbanken.
usename	Benutzer, der die Verbindung initiiert hat.	Identifiziert bestimmte Anwendungs- oder Benutzeraktivitäten.
application_name	Name der verbindenden Anwendung (falls vom Client festgelegt).	Hervorragend geeignet, um Verbindungen von bestimmten Microservices zu identifizieren.
state	Aktueller Aktivitätsstatus (z. B. active, idle, idle in transaction).	Kernindikator dafür, was das Backend tut.
query	Die aktuelle Abfrage oder die letzte Abfrage für Leerlaufsitzungen. Die Sichtbarkeit kann durch Berechtigungen und Einstellungen eingeschränkt sein.	Identifiziert die beteiligte SQL-Anweisung.
query_start	Zeitstempel, wann die aktuelle Abfrageausführung begann.	Wird verwendet, um die Abfragedauer zu berechnen.
wait_event_type & wait_event	Details darüber, worauf der Prozess wartet (z. B. Sperrerwerb, E/A).	Kritisch für die Diagnose von Konflikten und Blockierungen.

Praktische Anwendungsfälle für die Überwachung

Die wahre Stärke von pg_stat_activity liegt in der Filterung der Daten, um spezifische Leistungsfragen zu beantworten.

Alle aktiven Abfragen anzeigen

Um nur die Prozesse zu sehen, die derzeit eine Anweisung ausführen (nicht im Leerlauf), filtern Sie die Ansicht nach der Spalte state.

-- Alle derzeit ausgeführten Abfragen anzeigen
SELECT
    pid,
    usename,
    client_addr,
    application_name,
    now() - query_start AS duration,
    query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY duration DESC;

Langlaufende Abfragen identifizieren

Das Identifizieren von Abfragen, die länger als erwartet laufen, ist oft der erste Schritt bei der Leistungsoptimierung. Diese Abfragen können Ressourcen verbrauchen, E/A-Spitzen verursachen oder Sperren halten.

Um Abfragen zu identifizieren, die länger als einen bestimmten Schwellenwert (z. B. 5 Sekunden) laufen, verwenden Sie die Intervallsubtraktion mit now() und query_start.

-- Abfragen finden, die länger als 5 Sekunden laufen
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;

Verwenden Sie einen Schwellenwert, der zu Ihrer Arbeitslast passt. Eine fünfsekündige Checkout-Abfrage kann in einer OLTP-Anwendung schwerwiegend sein, während eine fünfminütige Berichtsabfrage normal sein kann, wenn sie außerhalb der Geschäftszeiten läuft.

Sitzungen im Leerlauf in Transaktion diagnostizieren

Eine Verbindung, die idle in transaction ist, hat eine Transaktion gestartet, aber nicht festgeschrieben oder zurückgesetzt. Sie wartet darauf, dass der Client den nächsten Befehl sendet. Diese Sitzungen können Sperren halten und alte Zeilenversionen sichtbar halten, was die Bereinigung durch Autovacuum verzögern und zu Tabellenblähungen beitragen kann.

-- Sitzungen finden, die im Leerlauf sind, aber eine offene Transaktion halten
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;

Wenn Sie Sitzungen finden, die Transaktionen minuten- oder stundenlang offen halten, überprüfen Sie den Anwendungspfad, der die Transaktion geöffnet hat. Häufige Ursachen sind fehlende Rollback-Behandlung nach einer Ausnahme, eine Verbindung, die vor der Bereinigung an einen Pool zurückgegeben wurde, oder eine interaktive Admin-Sitzung, die offen gelassen wurde.

Sperrkonflikte und Blockierungen analysieren

Wenn eine Abfrage hängt, wartet sie oft auf eine Sperre, die von einem anderen Prozess gehalten wird. Die Ansicht pg_stat_activity in Kombination mit pg_locks ist entscheidend für die Diagnose von Konflikten.

Um Sitzungen zu finden, die derzeit auf eine Ressource (eine Sperre, E/A usw.) warten, sehen Sie sich die Spalte wait_event an. Wenn eine Sitzung blockiert ist, ist ihr wait_event_type oft Lock.

-- Prozesse identifizieren, die derzeit durch eine Sperre blockiert werden
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;

Für eine schnelle Ansicht "wer blockiert wen" bietet PostgreSQL auch pg_blocking_pids().

-- Blockierte Sitzungen und die sie blockierenden Sitzungen anzeigen
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';

Wenn dies eine blockierte Webanfrage zurückgibt, die auf ein langes Admin-Update wartet, kann das Abbrechen der Admin-Abfrage sicherer sein als das Beenden der Anwendungssitzung.

Verwalten problematischer Sitzungen

Sobald eine problematische Abfrage oder Sitzung anhand ihrer Prozess-ID (pid) identifiziert wurde, bietet PostgreSQL zwei Funktionen zur Verwaltung:

Eine Abfrage mit pg_cancel_backend abbrechen

Diese Funktion versucht, die Ausführung einer bestimmten Abfrage ordnungsgemäß zu stoppen. Die Sitzung selbst bleibt verbunden und für zukünftige Abfragen verfügbar.

-- Beispiel: Die auf PID 12345 laufende Abfrage abbrechen
SELECT pg_cancel_backend(12345);

Eine Sitzung mit pg_terminate_backend beenden

Diese Funktion trennt den Backend-Prozess gewaltsam vom Server. Wenn sich die Sitzung mitten in einer Transaktion befand, führt PostgreSQL automatisch ein Rollback der Transaktion durch.

-- Beispiel: Die Sitzung mit PID 54321 gewaltsam beenden
SELECT pg_terminate_backend(54321);

Versuchen Sie zuerst pg_cancel_backend, wenn die Sitzung einfach eine schlechte Abfrage ausführt. Verwenden Sie pg_terminate_backend für Sitzungen, die feststecken, verlassen sind oder eine offene Transaktion halten, die nicht normal bereinigt werden kann. Das Zurücksetzen einer großen Transaktion kann Zeit in Anspruch nehmen und die E/A-Last erhöhen, also tun Sie dies bewusst.

Best Practices für die Überwachung

Aggressiv filtern

Vermeiden Sie SELECT * FROM pg_stat_activity als Ihre Standard-Produktionsgewohnheit. Die Ausgabe ist verrauscht, und der Abfragetext (query) kann sensible Werte preisgeben, wenn Ihre Anwendungen Literale anstelle von Bindeparametern senden. Wählen Sie die Spalten aus, die Sie benötigen, und filtern Sie nach state, datname, application_name oder Dauer.

Tools für die automatisierte Überwachung verwenden

Manuelle Überprüfungen sind während eines Vorfalls nützlich, aber Trends gehören in die Überwachung. Verfolgen Sie aktive Sitzungen, wartende Sitzungen, lange Transaktionen und die Anzahl der idle in transaction in Ihrem PostgreSQL-Dashboard.

Anweisungsprotokollierung konfigurieren

Kombinieren Sie Echtzeitüberwachung mit historischen Daten. Konfigurieren Sie Parameter wie log_min_duration_statement, um Abfragen zu protokollieren, die einen bestimmten Schwellenwert überschreiten, und so Daten für die Analyse bereitzustellen, auch nachdem die Abfrage ausgeführt wurde.

Fazit

Halten Sie drei gespeicherte Überprüfungen bereit: aktive Abfragen, geordnet nach Dauer, Leerlauftransaktionen, geordnet nach Transaktionsalter, und blockierte Sitzungen mit ihren Blockierern. Wenn PostgreSQL sich langsam anfühlt, sagen Ihnen diese Ansichten, ob Sie SQL optimieren, die Transaktionsbehandlung reparieren oder eine blockierende Sitzung bereinigen müssen.