Das Symptom
Ein Bericht sortiert eine große Tabelle und braucht Minuten. Jemand liest, dass
PostgreSQL ab Werk nur vier Megabyte für Sortierungen vorsieht, setzt work_mem in
der postgresql.conf auf 512MB, lädt die Konfiguration neu, und der Bericht läuft
tatsächlich schneller. Zwei Tage später ist der Server zur Hauptgeschäftszeit nicht
mehr zu erreichen. Im Log stehen Out-of-memory-Fehler, der Kernel hat Prozesse
beendet, und der Speicherbedarf lag weit über dem, was 512MB mal der Zahl der
Verbindungen ergeben hätte.
Die Rechnung work_mem mal max_connections steht in vielen Köpfen und ist leider die Untergrenze. Der Bericht war nicht das Problem. Das Problem war, dass hundert Webverbindungen mit ihren kleinen Abfragen auf einmal dieselbe Erlaubnis hatten.
Die „Layer 8“-Wahrheit
work_mem ist kein Deckel pro Abfrage und erst recht keiner pro Verbindung. Die
Dokumentation beschreibt ihn als Grundwert für eine einzelne Operation: „Sets the base
maximum amount of memory to be used by a query operation (such as a sort or hash
table) before writing to temporary disk files.“ Ohne Einheit wird der Wert als
Kilobyte gelesen, die Voreinstellung sind vier Megabyte. Eine Abfrage mit ORDER BY,
DISTINCT und zwei Hash-Joins hat vier solche Operationen, und jede darf für sich bis
an die Grenze gehen.
Die Dokumentation sagt das nicht beiläufig, sondern als Warnung: Eine komplexe
Abfrage könne mehrere Sortier- und Hash-Operationen gleichzeitig ausführen, mehrere
Sitzungen könnten das gleichzeitig tun, und: „Therefore, the total memory used could
be many times the value of work_mem; it is necessary to keep this fact in mind when
choosing the value.“ Wer das Vielfache mit der Verbindungszahl verwechselt, hat einen
Faktor vergessen, und wer dann noch Hash-Tabellen übersieht, einen zweiten. Denn für
hash-basierte Operationen gilt nicht work_mem, sondern work_mem mal
hash_mem_multiplier, und dieser Faktor steht ab Werk auf 2,0. Aus 512MB werden
damit für einen Hash-Join ein Gigabyte, pro Operation, pro Sitzung.
Der Fehler liegt also nicht im Erhöhen, sondern im Ort des Erhöhens. Ein Wert in der
postgresql.conf gilt für jede Operation jeder Sitzung. Die Rolle der Webanwendung,
die tausend kurze Abfragen pro Minute stellt, hat mit 512MB nichts anzufangen, darf
sie aber beanspruchen, sobald einmal eine Abfrage ungünstig geplant wird. Der
Bericht, der die Erhöhung brauchte, läuft dagegen einmal am Tag in einer Sitzung.
Dort gehört der Wert hin: per SET LOCAL innerhalb der Transaktion oder per
ALTER ROLE an eine eigene Berichtsrolle. Die Grundlinie für alle bleibt klein.
Zwei Nachbarn im selben Kapitel machen die Sache besser steuerbar. Wenn vor allem
Hash-Joins und Aggregationen auf die Platte ausweichen, rät die Dokumentation, eher
hash_mem_multiplier zu erhöhen als work_mem, gerade dann, wenn ein höheres
work_mem Speicherdruck erzeugt; Werte von 2,0 bis 8,0 nennt sie für Systeme, auf
denen work_mem schon bei 40MB oder mehr steht. Und wer work_mem bewusst klein
lässt, verschiebt Last auf temporäre Dateien; temp_file_limit begrenzt deren Platz
pro Prozess und bricht die Transaktion ab, die ihn überschreitet, statt den
Datenträger zu füllen. Ab Werk steht dort -1, also keine Grenze.
Am Ende ist die Abwägung dieselbe wie bei jedem geteilten Vorrat: Nicht die größte
Abfrage bestimmt den sicheren Wert, sondern die Zahl derer, die ihn gleichzeitig
ausschöpfen dürfen. EXPLAIN (ANALYZE, BUFFERS) zeigt, ob eine Abfrage überhaupt auf
die Platte schreibt. Oft tut sie es, weil ein Index fehlt, und dann verschwindet die
Sortierung ganz, statt größer zu werden.