DOAG Datenbank Kolumne: "Wo ist mein AWR?" – Workload Reports mit PostgreSQL

Wer von Oracle kommt, vermisst in PostgreSQL oft den AWR-Report auf Knopfdruck. Dabei steckt in PostgreSQL deutlich mehr Performance-Instrumentierung, als der erste Blick vermuten lässt – inklusive Top-SQL, I/O-, WAL- und Wait-Event-Statistiken. Dieser Beitrag zeigt an einem reproduzierbaren Docker-Beispiel, wie man daraus Workload Reports gewinnt.

Einstieg

Wer jahrelang mit Oracle gearbeitet hat, kennt den Reflex: Die Anwendung ist langsam, also wird ein AWR-Report gezogen, die "Top SQL" gelesen, und meist ist der Übeltäter nach wenigen Minuten gefunden. Dass dafür das Diagnostics Pack lizenziert sein muss, nimmt man dabei als bedenkenlos hin.

Die gute Nachricht: PostgreSQL bringt die Rohdaten für genau diese Analyse von Haus aus mit – ohne Pack und ohne Lizenzfrage. Sie sind nur anders verpackt. Statt eines fertigen Reports gibt es auch hier Statistik-Views, aus denen man sich einen Report baut, oder man bezieht ihn automatisiert per Extension. Wie das funktioniert, zeigt dieser Beitrag Schritt für Schritt.

Das Setup: fünf Minuten, ein Container

Als Testumgebung genügt ein einzelner Docker-Container mit PostgreSQL 18. Wichtig sind nur wenige Parameter: pg_stat_statements muss vorgeladen werden, compute_query_id und track_io_timing sorgen für nutzbare Query-IDs und echte I/O-Zeiten, und log_checkpoints sowie log_lock_waits liefern Zusatzinformationen im Serverlog.

# docker-compose.yml (gekürzt)

services:

  pg:

    image: postgres:18

    command: >

      postgres -c shared_preload_libraries=pg_stat_statements

               -c compute_query_id=on -c track_io_timing=on

               -c log_checkpoints=on -c log_lock_waits=on

Für die Last nutzen wir pgbench, das jeder PostgreSQL-Installation beiliegt. Wir legen zehn Millionen Konten an (Scale 100, rund 1,5 GB) und starten zwei Lastprofile parallel: eine TPC-B-ähnliche Standardlast mit 14 Clients und zusätzlich zwei Clients, die eine absichtlich schlecht geschriebene Abfrage ausführen.

pgbench -i -s 100 bench                                      # 10 Mio. Konten, ca. 14 s

pgbench -c 14 -j 4 -T 180 bench &                       # TPC-B-artige Last

pgbench -n -c 2 -T 180 -f bad_query.sql bench      # 2 Clients mit "schlechter" Abfrage

Das Skript bad_query.sql (siehe unten) ist mit Absicht gemein: Es filtert die Tabelle mit zehn Millionen Zeilen nach einer nicht indizierten Spalte und sortiert das Ergebnis. Ein Report ist schließlich nur dann überzeugend, wenn er auch etwas findet.

\set bid random(1, 100 * :scale)

SELECT aid, abalance FROM pgbench_accounts WHERE bid = :bid ORDER BY abalance DESC, filler LIMIT 10;

Der Lauf über 180 Sekunden lieferte folgendes Bild: Die 14 Clients der Standardlast erreichten zusammen rund 2.400 Transaktionen pro Sekunde bei einer mittleren Latenz von nur 5,8 Millisekunden. Die beiden Clients mit der schlechten Abfrage kamen dagegen auf eine mittlere Antwortzeit von 465 Millisekunden – das Achtzigfache. Allein anhand der Durchsatzzahlen wirkt das System gesund; dass hier ein Problem schlummert, zeigen erst die Reports.
 

Welche Metriken zählen – und wo sie in PostgreSQL wohnen

Oracle-Administratoren finden die gewohnten AWR-Abschnitte in PostgreSQL an anderer Stelle wieder. Die folgende Tabelle ordnet die wichtigsten Bereiche einander zu.
 

Zwei Unterschiede bestimmen das Denkmodell. Erstens sind alle diese Zähler kumulativ seit dem letzten Reset. Ein "Report" ist daher immer die Differenz zweier Zeitpunkte – genau wie bei AWR, nur dass man die Snapshots selbst anlegen muss. Zweitens gibt es keine eingebaute Historie: Was niemand sammelt, ist nach einem Neustart oder Reset verschwunden.

Report Nr. 1: "AWR light" in wenigen Zeilen SQL

Die Idee ist einfach: Eine Funktion kopiert den aktuellen Stand der Statistik-Views in eigene Tabellen (awr.take_snapshot()), der Report bildet anschließend die Differenz zweier Snapshots. Hier das Load Profile:

SELECT round((e.xact_commit - b.xact_commit)

             / extract(epoch FROM (se.ts - sb.ts)))               AS commits_per_s,

       round(100.0 * (e.blks_hit - b.blks_hit)

             / nullif(e.blks_hit - b.blks_hit + e.blks_read - b.blks_read, 0), 2) AS buffer_hit_pct,

       pg_size_pretty(e.temp_bytes - b.temp_bytes)                AS temp_spill

FROM  awr.snap_db b JOIN awr.snap_db e ON true

JOIN  awr.snap sb ON sb.snap_id = b.snap_id

JOIN  awr.snap se ON se.snap_id = e.snap_id

WHERE b.snap_id = :b AND e.snap_id = :e;

 elapsed_s | commits_per_s | rollback_pct | buffer_hit_pct | deadlocks

-----------+---------------+--------------+----------------+---------

       189 |       2322 |          0.00 |         42.21 |        0

Auffällig ist die Cache-Hit-Rate von nur 42 Prozent. Für ein OLTP-System ist das ein Alarmzeichen – und für Oracle-Administratoren eine vertraute Warnlampe. Der Blick in die Top-SQL macht den Verursacher sofort sichtbar:

 calls     |  total_ms | mean_ms | pct     | query

-------------+-------------+---------------+----------+-------------------------------------------------------------

      775   | 359481   | 463.846   | 67.2   | SELECT aid, abalance FROM pgbench_accounts WHERE bid = $1 ORDER BY aba...

 433878 | 116338    |  0.268      | 21.7   | UPDATE pgbench_branches SET bbalance = ...

Gerade einmal 775 Aufrufe der nicht indizierten Abfrage verbrauchen 67 Prozent der gesamten Datenbankzeit und lasen laut pg_stat_statements rund 79 Millionen Blöcke. Die über 430.000 Aufrufe der eigentlichen Transaktionsstatements fallen dagegen kaum ins Gewicht. Ein Index auf bid – oder die Frage, warum eine OLTP-Anwendung so eine Abfrage überhaupt ausführt – ist der naheliegende Hebel.

Stolpersteine bei der Interpretation

• `shared_blks_read` bedeutet nicht "von der Platte gelesen". Gemeint ist nur: nicht im shared_buffers von PostgreSQL gefunden. Der Betriebssystem-Cache kann den Block trotzdem liefern. Bei Oracle mit Direct I/O sind physical reads dagegen meist echte I/Os. Deshalb sollte track_io_timing aktiviert sein und die read_time-Spalten ausgewertet werden.
• `pg_stat_io` unterscheidet nach Kontext. Der Seq Scan der schlechten Abfrage läuft im Kontext bulkread (Ringpuffer), nicht in normal. Wer nur normal betrachtet, übersieht über 99 Prozent des gelesenen Volumens: im Test 5,2 GB in normal gegenüber mehr als 600 GB in bulkread, davon der größere Teil durch parallele Worker.
• Der Beobachter taucht in den eigenen Statistiken auf. Die Snapshot-Aufrufe erschienen zunächst selbst in der Top-SQL. Ein Filter ist daher Pflicht.

Die ASH-Frage: Wer wartet worauf?

In pg_stat_activity zeigt jede aktive Sitzung ihren wait_event_type und wait_event. Einmalig abgefragt ist das nur eine Momentaufnahme; wiederholt man die Abfrage alle paar Sekunden (etwa mit \watch), entsteht ein ASH in Handarbeit. 40 Stichproben während des Lastlaufs ergaben:

64,4 % LWLock/WALWrite      <- viele kleine Commits, fsync-Latenz

13,0 % CPU

 7,7 % IO/WalSync

 4,4 % Lock/transactionid   <- Hot Row in pgbench_branches

Die Aussage "Wir warten auf das Schreiben des WAL beim Commit" kennt jeder Oracle-DBA als log file sync. Für eine echte Historie, wie sie ASH bietet, setzt man die Extension pg_wait_sampling ein: Sie sampelt die Wait Events im Hintergrund und hält sie vor.

Fertig statt selbstgebaut: pg_profile

Wer nicht selbst basteln möchte, greift zu pg_profile. Die Open-Source-Extension besteht aus reinem SQL und PL/pgSQL, braucht also kein C-Modul, und ist der naheliegendste AWR-Nachfolger im PostgreSQL-Umfeld. Sie baut auf pg_stat_statements und dblink auf, legt in regelmäßigen Abständen "Samples" an und erzeugt daraus HTML-Reports.

CREATE EXTENSION pg_stat_statements;

CREATE EXTENSION dblink;

CREATE EXTENSION pg_profile;

SELECT * FROM take_sample();          -- vor der Last

-- ... Last ...

SELECT * FROM take_sample();          -- nach der Last

\o report.html

SELECT get_report('local', 1, 2);     -- Differential-Report: get_diffreport(...)

Im Test mit pg_profile 4.16 auf PostgreSQL 18.4 gelang die Installation ohne jede Anpassung. Die schlechte Abfrage stand im Report auf Platz 1 nach I/O-Zeit – mit rund 95 Prozent der I/O-Zeit und 99 Prozent Read-Anteil. Praktisch ist außerdem get_diffreport: Es vergleicht zwei Zeiträume und beantwortet damit den Klassiker "Warum war es gestern schneller?". Zu beachten ist, dass die HTML-Datei ihre Tabellen per JavaScript im Browser aufbaut; sie lässt sich daher nicht in einem einfachen Texteditor oder Viewer lesen.

Reifere Alternativen im Überblick

pg_profile ist nicht das einzige Werkzeug. Je nach Anforderung – Log-Analyse, Dauerhistorie, Alerting oder Empfehlungen – bieten sich andere Lösungen an:

Fazit

PostgreSQL ist kein Sidequest. Load Profile, Top SQL, I/O, WAL, Checkpoints und Wait Events sind vorhanden – transparent, per SQL abfragbar und ohne Zusatzlizenz.

Was Oracle voraus hat, ist ADDM mit automatischen Empfehlungen und eine integrierte, automatisch verwaltete Historie. Was PostgreSQL voraus hat: Jede Zahl ist nur ein SELECT entfernt, und der Report gehört einem selbst. Für den Einstieg genügen drei Schritte: pg_stat_statements aktivieren, pg_profile installieren und zwei Samples ziehen. Danach lohnt es sich, den ersten PostgreSQL-Report neben den letzten AWR-Report zu legen und zu vergleichen, wie viel man darin wiedererkennt.

Raphael Salguero