PostgreSQL^® 9.6 Feature: pg_stat_activity - wait_event

Mit PostgreSQL^® 9.6 wird der bekannte View pg_stat_activity um zwei Spalten erweitert: wait_event und wait_event_type. Die alte Spalte waiting fällt dafür weg.

Hintergrund

PostgreSQL^® unterscheidet intern zwischen zwei Lock-Typen: Heavyweight-Locks und Lightweight-Locks (LWLocks). Je nach zu erwartender Dauer des Locks kommt entweder die eine oder die andere Variante zum Einsatz.

Zu Heavyweight-Locks zählen Locks, die sich auf Datenbankobjekte beziehen. Sie werden verwendet, um auf SQL-Ebene gleichzeitige, konfligierende Aktionen gegeneinander abzusichern. So darf beispielsweise eine Tabelle erst gelöscht werden, sobald es keine Transaktionen mehr gibt, die diese verwenden, oder zwei Transaktionen, die ein UPDATE der gleichen Zeile versuchen, müssen auf einander warten. Diese Locks können im View pg_locks eingesehen werden. In Verbindung mit dem View pg_stat_activity kann so herausgefunden werden, wer auf wen wartet. Eine Beispielquery befindet sich im PostgreSQL^®-Wiki.

Lightweight-Locks hingegen werden für kurze Locking-Operationen verwendet. Dies beinhaltet zum Beispiel das Füllen der WAL-Buffer (WALBufMappingLock) oder auch das Warten auf das Herausschreiben selbiger (WALWriteLock). Ein weiteres Beispiel ist der Zugriff auf Pages innerhalb des Shared Memory Segments (buffer_content), wie er beim Lesen oder Schreiben von Daten auftritt. Problematisch ist nun, dass Lightweight-Locks, im Gegensatz zu Heavyweight-Locks, nicht in pg_locks oder einem anderen System-View erfasst sind. Um bei Performanceanalysen zu erkennen, auf welchen LWLock gerade gewartet wurde, musste bis dato zu externen Tools Tools wie perf gegriffen werden.

Funktion

Zusätzlich zu den bereits bekannten Statusinformationen wie pid, query_start oder auch query, halten mit PostgreSQL^® 9.6 zwei neue Spalten Einzug in pg_stat_activity. Die Spalten wait_event und wait_event_type enthalten detaillierte Locking Informationen. Sie ersetzt zeitgleich die Spalte waiting, die früher je nach Status lediglich TRUE oder FALSE beinhaltete und damit signalisierte, dass auf einen Heavyweight-Lock gewartet wurde.

Die neue Spalte gibt wait_event nun auch Aufschluss darüber ob und auf welche Art von LWLocks gewartet wird. Muss ein Backend auf einen Lock warten, enthält die Spalte wait_event den Namen des Events, andernfalls NULL.

Beispiel

Zur Veranschaulichung des neuen Features bemühen wir das PostgreSQL^®-eigene Benchmark-Tool pgbench.

Zunächst erstellen wir uns eine neue Testdatenbank und befüllen diese mit Testdaten.

$ createdb pgbench
$ pgbench -i -s 50 pgbench

Nachdem unsere Datenbank erstellt und gefüllt ist, starten wir pgbench mit 20 gleichzeitigen Verbindungen in 2 Threads:

$ pgbench -c 20 -j 2 -T 300 pgbench 

Schauen wir uns zeitgleich den View pg_stat_activity an, so stellen wir fest, dass viele der Backends auf Locks vom Typ LWLockNamed warten, einen WALWriteLock. Sie warten also darauf, dass die geschriebenen WAL-Records auf die Festplatte geschrieben werden:

psql=# select pid, wait_event, wait_event_type, state, query from pg_stat_activity WHERE wait_event is not NULL;
  pid  |  wait_event   | wait_event_type | state  |                                 query                                  
-------+---------------+-----------------+--------+------------------------------------------------------------------------
 25632 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -3359 WHERE bid = 4;
 25633 | WALWriteLock  | LWLockNamed     | active | END;
 25635 | WALWriteLock  | LWLockNamed     | active | END;
 25636 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + 1807 WHERE bid = 1;
 25638 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -603 WHERE bid = 12;
 25639 | transactionid | Lock            | active | UPDATE pgbench_tellers SET tbalance = tbalance + 2794 WHERE tid = 169;
 25640 | WALWriteLock  | LWLockNamed     | active | END;
 25642 | WALWriteLock  | LWLockNamed     | active | END;
 25643 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -4985 WHERE bid = 5;
 25644 | WALWriteLock  | LWLockNamed     | active | END;
 25645 | WALWriteLock  | LWLockNamed     | active | END;
 25646 | WALWriteLock  | LWLockNamed     | active | END;
 25648 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + 2700 WHERE bid = 1;
 25649 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -456 WHERE bid = 5;
 25650 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -617 WHERE bid = 17;
 25651 | WALWriteLock  | LWLockNamed     | active | END;
(16 rows)

Ändern wir nun beispielsweise die Einstellung synchronous_commit auf off, so stellen wir fest, dass genau diese WALWriteLocks nicht mehr Auftreten. Die Backends warten nicht mehr darauf, dass ihre Daten auf die Festplatte geschrieben werden:

psql=# SELECT pid, wait_event, wait_event_type, state, query from pg_stat_activity WHERE wait_event is not NULL;
  pid  |  wait_event   | wait_event_type | state  |                                  query                                  
-------+---------------+-----------------+--------+-------------------------------------------------------------------------
 26201 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -1065 WHERE bid = 7;
 26203 | transactionid | Lock            | active | UPDATE pgbench_tellers SET tbalance = tbalance + -3052 WHERE tid = 173;
 26204 | transactionid | Lock            | active | UPDATE pgbench_branches SET bbalance = bbalance + -2375 WHERE bid = 5;
(3 rows)

Fazit

Das oben gezeigte Beispiel veranschaulicht, wie detailliert nun Einsicht in das Locking der Datenbank genommen werden kann. Besonders für Analysen der Datenbankperformance bietet dies entscheidende Vorteile gegenüber den bisherigen Möglichkeiten.

Details

Detailliertere Informationen zu den verwendeten Lock-Typen, deren Bedeutung und Verwendung hat die PostgreSQL^®-Dokumentation. Wem dies nicht ausreicht, dem bietet die Datei src/backend/storage/lmgr/README im Sourcecode weitere Informationen über Locking in PostgreSQL^®.

Bei Fragen steht Ihnen außerdem unser PostgreSQL^® Competence Center zu Verfügung.

PostgreSQL^® 9.6 befindet sich aktuell in der Beta Phase. Ein Release- Datum steht aktuell noch nicht fest. Wer die neue Version bereits im Vorfeld testen möchte, findet den aktuellen Tarball unter www.postgresql.org. Für Debian und Ubuntu stehen auf apt.postgresql.org bereits vorgefertigte Pakete bereit.