Optimierung der postgresql.conf-Parameter für optimale Lese- und Schreibleistung

PostgreSQL läuft normalerweise akzeptabel mit den Standardeinstellungen, aber „akzeptabel“ kann sich in langsame Lesevorgänge, unregelmäßige Schreibvorgänge oder zufällig aussehende Latenzzeiten verwandeln, sobald echter Datenverkehr eintrifft. Die Datei postgresql.conf legt das grundlegende Ressourcenbudget fest: wie viel Speicher PostgreSQL für den gemeinsamen Cache verwenden kann, wie viel jede Abfrageoperation verwenden kann, bevor sie auf die Festplatte ausgelagert wird, wie aggressiv Checkpoints schmutzige Seiten schreiben und welche Hinweise der Planer über die darunterliegende Maschine erhält.

Der Fehler, den ich am häufigsten sehe, ist, die PostgreSQL-Optimierung wie eine Liste magischer Zahlen zu behandeln. Jemand kopiert shared_buffers = 25% des RAM, setzt work_mem auf einen großen Wert, verdoppelt max_connections und hofft, dass die Datenbank schneller wird. Manchmal funktioniert es. Manchmal beginnt sie während eines Berichtsjobs zu swappen oder stößt während Checkpoints an ihre Grenzen.

Der sicherere Weg ist, anhand von Symptomen zu optimieren. Sind Lesevorgänge langsam, weil der Arbeitssatz nicht zwischengespeichert ist? Lagern Berichte Sortierungen auf die Festplatte aus? Häufen sich Schreibvorgänge während Checkpoints? Konkurrieren zu viele Anwendungsverbindungen um Speicher? Diese Anleitung führt durch die Parameter, die normalerweise zuerst wichtig sind, mit Beispielen, die Sie anpassen können, anstatt sie blind zu kopieren.

Grundlegende Speicherparameter verstehen

Effizientes Speichermanagement ist für Hochleistungs-Datenbanksysteme von größter Bedeutung. PostgreSQL nutzt verschiedene Speicherbereiche, von denen zwei der wichtigsten shared_buffers für das Caching häufig abgerufener Daten und work_mem für interne Abfrageoperationen sind.

shared_buffers

shared_buffers ist wohl einer der wichtigsten Speicherparameter, die es zu optimieren gilt. Er definiert die Menge an dediziertem Speicher, die PostgreSQL zum Caching von Datenblöcken verwendet. Diese Blöcke umfassen Tabellendaten, Indexdaten und Systemkataloge. Wenn eine Abfrage Daten anfordert, überprüft PostgreSQL zuerst shared_buffers. Wenn die Daten dort gefunden werden (ein Cache-Treffer), werden sie viel schneller abgerufen, als wenn sie von der Festplatte gelesen werden müssten.

Auswirkungen auf die Leistung

• Leseleistung: Ein größerer shared_buffers-Wert erhöht die Wahrscheinlichkeit von Cache-Treffern und reduziert die Festplatten-E/A bei leseintensiven Arbeitslasten erheblich. Dies führt zu schnelleren Abfrageantworten.
• Schreibleistung: shared_buffers enthält auch „schmutzige“ Seiten (Datenblöcke, die geändert, aber noch nicht auf die Festplatte geschrieben wurden). Ein größerer Puffer kann mehr Schreibvorgänge aufnehmen, sodass das System sie zu weniger, dafür größeren Schreibvorgängen auf die Festplatte bündeln kann, was den Schreibdurchsatz verbessert. Wenn er jedoch zu groß ist, kann dies zu längeren Checkpoint-Zeiten und erhöhten E/A-Spitzen während Checkpoints führen.

Optimierungsrichtlinien

• Ausgangspunkt: Eine gängige Empfehlung ist, shared_buffers auf 25% Ihres gesamten physischen RAMs zu setzen. Beispielsweise wäre auf einem Server mit 16 GB RAM shared_buffers 4 GB.
• Größere RAM-Systeme: Auf Servern mit 64 GB+ RAM könnte die Zuweisung von 25% übertrieben sein. PostgreSQL verlässt sich auch auf den Dateisystem-Cache des Betriebssystems. Ab einem bestimmten Punkt kann eine Erhöhung von shared_buffers abnehmende Renditen bringen, da der OS-Cache einen Großteil des restlichen Cachings effektiv übernehmen kann. In solchen Fällen könnten 15-20% ausreichen, um mehr RAM für den OS-Cache oder work_mem freizugeben.
• Überwachung: Behalten Sie die Cache-Trefferquote in pg_stat_database im Auge, aber behandeln Sie einen Prozentsatz nicht als Beweis dafür, dass alles gesund ist. Eine hohe Trefferquote kann einige sehr teure Abfragen verbergen, und eine niedrigere Quote kann für Batch-Jobs, die große Tabellen einmal scannen, normal sein. Überwachen Sie auch das Checkpoint-Verhalten und die Festplattenlatenz.

Beispielkonfiguration

Um shared_buffers in postgresql.conf auf 4 GB zu setzen:

shared_buffers = 4GB

Tipp: Nach einer Änderung von shared_buffers müssen Sie den PostgreSQL-Dienst neu starten, damit die Änderungen wirksam werden.

Eine praktische Überprüfung nach der Änderung:

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

Wenn Sie shared_buffers erhöht haben und die Anwendung immer noch auf Festplattenlesevorgänge wartet, liegt das Problem möglicherweise an der Abfrageform, fehlenden Indizes, Tabellenblähungen oder einem Arbeitssatz, der größer als der Speicher ist. Mehr Cache ist kein Ersatz für einen besseren Ausführungsplan.

work_mem

work_mem gibt die maximale Speichermenge an, die von einer Abfrageoperation (wie einer Sortierung oder Hash-Tabelle) verwendet werden soll, bevor temporäre Daten auf die Festplatte geschrieben werden. Dieser Speicher wird pro Sitzung, pro Operation zugewiesen. Wenn eine komplexe Abfrage mehrere Sortier- oder Hash-Operationen umfasst, könnte sie work_mem innerhalb einer einzigen Sitzung mehrfach verbrauchen.

Auswirkungen auf die Leistung

• Komplexe Abfragen: work_mem hat erhebliche Auswirkungen auf Abfragen mit ORDER BY, GROUP BY, DISTINCT, Hash-Joins und Materialisierung. Wenn eine Sortier- oder Hash-Operation das work_mem-Limit überschreitet, lagert PostgreSQL die überschüssigen Daten in temporäre Festplattendateien aus, was zu einer viel langsameren Ausführung führt.
• Parallelität: Da work_mem pro Operation und pro Sitzung zugewiesen wird, kann ein hoher globaler work_mem-Wert in Kombination mit vielen gleichzeitigen komplexen Abfragen schnell den verfügbaren RAM erschöpfen, was zu Swapping und schwerwiegenden Leistungseinbußen führt.

Optimierungsrichtlinien

• Übermäßige globale Werte vermeiden: Setzen Sie work_mem nicht blind auf einen sehr großen Wert global. Berücksichtigen Sie stattdessen die typische Parallelität Ihrer Anwendung und den Speicherbedarf Ihrer ressourcenintensivsten Abfragen.
• Überwachung von Festplattenauslagerungen: Verwenden Sie EXPLAIN ANALYZE bei problematischen Abfragen. Achten Sie auf Zeilen wie Sort Method: external merge Disk: NkB oder HashAggregate batches: N (disk), die darauf hinweisen, dass work_mem nicht ausreichte und Daten auf die Festplatte ausgelagert wurden.
• Gezielte Optimierung: Erwägen Sie für bestimmte langlaufende Berichte oder Batch-Jobs, work_mem auf Sitzungsebene vor der Ausführung der Abfrage zu setzen, anstatt global. Dies ermöglicht eine höhere Speichernutzung für diese spezifische Abfrage, ohne andere gleichzeitige Sitzungen zu beeinträchtigen.

Beispielkonfiguration

Um work_mem global in postgresql.conf auf 16 MB zu setzen:

work_mem = 16MB

Um work_mem für eine bestimmte Sitzung zu setzen (z. B. in psql oder einer Anwendungsverbindung):

SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY some_column;

Warnung: Seien Sie vorsichtig, wenn Sie work_mem erhöhen. Wenn 100 gleichzeitige Abfragen jeweils 1 GB work_mem benötigen, sind das 100 GB RAM! Testen Sie Änderungen immer in einer Staging-Umgebung und überwachen Sie die Speichernutzung Ihres Systems.

Ein realistischerer Weg, work_mem zu verwenden, ist, den globalen Wert bescheiden zu halten und ihn nur für bekannte Berichtssitzungen zu erhöhen:

BEGIN;
SET LOCAL work_mem = '256MB';

SELECT customer_id, sum(total_amount)
FROM orders
WHERE created_at >= current_date - interval '90 days'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;

COMMIT;

Dieses Muster ist sicherer, als den globalen Wert für jede Webanfrage zu erhöhen. Eine Webanwendung mit vielen kurzen Abfragen benötigt eine vorhersagbare Speichernutzung. Ein nächtlicher Bericht kann sich ein größeres Budget pro Abfrage leisten.

Verwaltung der Schreibleistung und Haltbarkeit mit Checkpoints

Checkpoints sind ein kritischer Mechanismus in PostgreSQL, um die Datenhaltbarkeit zu gewährleisten und das Transaktionslog (WAL - Write-Ahead Log) zu verwalten. Sie synchronisieren regelmäßig geänderte Datenblöcke aus shared_buffers auf die Festplatte und markieren den Punkt, bis zu dem alle vorherigen Änderungen auf den permanenten Speicher geschrieben wurden.

checkpoint_timeout

checkpoint_timeout definiert die maximale Zeit zwischen automatischen WAL-Checkpoints. Checkpoints treten auch auf, wenn die Menge der seit dem letzten Checkpoint erzeugten WAL-Segmente max_wal_size überschreitet.

Auswirkungen auf die Leistung

• Häufige Checkpoints (kurzes checkpoint_timeout): Führt zu häufigeren E/A-Spitzen, wenn schmutzige Seiten auf die Festplatte geschrieben werden. Während dies die Wiederherstellungszeit nach einem Absturz verkürzt (weniger WAL zum Wiederholen), kann es die Leistung der aktiven Arbeitslast aufgrund konzentrierter Schreibaktivitäten negativ beeinflussen.
• Seltene Checkpoints (langes checkpoint_timeout): Reduziert die Häufigkeit von E/A-Spitzen, was zu einer gleichmäßigeren Leistung während des normalen Betriebs führt. Es bedeutet jedoch, dass im Falle eines Absturzes möglicherweise mehr Daten aus dem WAL wiederholt werden müssen, was zu längeren Datenbank-Wiederherstellungszeiten führt. Es erfordert auch eine größere max_wal_size, um die angesammelten WAL-Segmente zu speichern.

Optimierungsrichtlinien

• Gleichgewicht: Das Ziel ist, ein Gleichgewicht zwischen gleichmäßiger laufender Leistung und akzeptabler Wiederherstellungszeit zu finden. Viele Produktionssysteme beginnen bei etwa 5-15 Minuten und passen dann basierend auf dem WAL-Volumen und den Wiederherstellungszielen an.
• Zusammenspiel mit max_wal_size: Diese beiden Parameter arbeiten zusammen. Wenn checkpoint_timeout lang ist, aber max_wal_size zu klein ist, werden Checkpoints häufiger durch max_wal_size als durch checkpoint_timeout ausgelöst. Passen Sie max_wal_size so an, dass sie groß genug ist, damit checkpoint_timeout der primäre Auslöser sein kann.
• Überwachung: Verwenden Sie pg_stat_bgwriter, um die Zähler checkpoints_timed und checkpoints_req zu beobachten. checkpoints_timed sollte deutlich höher sein als checkpoints_req (Checkpoints, die aufgrund von WAL-Größenbeschränkungen angefordert wurden), wenn Ihr checkpoint_timeout der primäre Auslöser ist.

Beispielkonfiguration

Um checkpoint_timeout in postgresql.conf auf 10 Minuten zu setzen:

checkpoint_timeout = 10min
# Erwägen Sie auch, max_wal_size entsprechend anzupassen
max_wal_size = 4GB # Beispiel, je nach Arbeitslast anpassen

Best Practice: Zielen Sie darauf ab, dass Checkpoints hauptsächlich durch checkpoint_timeout und nicht durch max_wal_size ausgelöst werden. Dies sorgt für vorhersagbarere E/A-Muster. Wenn max_wal_size häufig Checkpoints auslöst, erhöhen Sie seinen Wert.

Überprüfen Sie das Muster mit:

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

Wenn checkpoints_req schnell ansteigt, setzt PostgreSQL Checkpoints, weil das WAL über max_wal_size hinausgewachsen ist, nicht weil der Timer abgelaufen ist. Dies zeigt sich oft als Schübe von Schreib-E/A. Eine Erhöhung von max_wal_size kann die Arbeitslast glätten, aber auch die Wiederherstellungszeit nach einem Absturz erhöhen, da möglicherweise mehr WAL wiederholt werden muss.

Planer- und WAL-Einstellungen, die eine Überprüfung wert sind

Drei Einstellungen befinden sich oft neben den großen Speicher- und Checkpoint-Parametern.

effective_cache_size ist kein Speicher, den PostgreSQL zuweist. Es ist eine Planerschätzung, wie viel Cache wahrscheinlich über die PostgreSQL-Shared-Buffers und den Betriebssystem-Dateisystem-Cache hinweg verfügbar ist. Wenn es zu niedrig eingestellt ist, vermeidet der Planer möglicherweise Index-Scans, da er annimmt, dass Lesevorgänge teuer sind. Auf einem dedizierten Datenbankserver ist ein großer Anteil des RAMs ein üblicher Ausgangspunkt, aber der richtige Wert hängt davon ab, was sonst noch auf dem Host läuft.

effective_cache_size = 12GB

maintenance_work_mem betrifft Wartungsoperationen wie CREATE INDEX, ALTER TABLE ADD FOREIGN KEY und VACUUM. Es wird nicht für normale Abfragesortierungen auf die gleiche Weise wie work_mem verwendet. Wenn Indexerstellungen während Wartungsfenstern quälend langsam sind, kann eine Erhöhung dieses Werts für die Sitzung helfen:

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

wal_buffers steuert den Speicher, der für WAL-Einträge verwendet wird, bevor sie ausgeschrieben werden. Der Standardwert ist normalerweise in Ordnung, da PostgreSQL ihn automatisch dimensionieren kann, aber schreibintensive Arbeitslasten mit großen Transaktionen können davon profitieren, zu überprüfen, ob WAL-Schreibvorgänge ein Engpass sind, bevor sie geändert werden. Optimieren Sie es nicht nur, weil es in einer Checkliste erscheint.

Unterschiedliche Ausgangspunkte für verschiedene Arbeitslasten

Für eine OLTP-Webanwendung liegt die Priorität auf stabiler Latenz bei Parallelität. Halten Sie work_mem konservativ, verwenden Sie einen Verbindungspooler anstatt Tausende direkter Verbindungen zuzulassen, und achten Sie auf Sperrwartezeiten und schlechte Pläne, bevor Sie shared_buffers die Schuld geben. Ein typisches Problem sieht so aus: Ein Release fügt eine Dashboard-Abfrage mit ORDER BY created_at DESC über Millionen von Zeilen hinzu, die Abfrage wird auf die Festplatte ausgelagert, und plötzlich ist jede Anfrage langsamer, weil die Datenbank temporäre Datei-E/A durchführt. Die Lösung kann ein Index oder eine schmalere Abfrage sein, nicht ein größeres globales work_mem.

Für eine Analyse- oder Berichtsdatenbank sind große Sortierungen und Hash-Aggregate normal. Sie können work_mem für Berichtsrollen erhöhen, maintenance_work_mem für Bulk-Index-Arbeiten erhöhen und längere Abfragen akzeptieren. Das Risiko ist die Parallelität. Zehn Analysten, die gleichzeitig speicherintensive Berichte ausführen, können weit mehr Speicher verbrauchen, als ein erfolgreicher Testabfrage vermuten ließ.

Für ein schreibintensives System sind Checkpoints und WAL wichtiger. Wenn die Anwendung periodische Schreibaussetzer hat, überprüfen Sie, ob sie mit Checkpoints zusammenfallen. Achten Sie auch auf Speicherlatenz, WAL-Festplattensättigung, Autovacuum-Aktivität und ob lange Transaktionen die Bereinigung verhindern. Eine Erhöhung von checkpoint_timeout allein wird eine Festplatte, die mit dem durchschnittlichen Schreibvolumen nicht Schritt halten kann, nicht reparieren.

Ein einfacher Optimierungs-Workflow

Beginnen Sie mit der Aufzeichnung der aktuellen Konfiguration:

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'checkpoint_timeout',
    'max_wal_size',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

Erfassen Sie dann Symptome, bevor Sie etwas ändern. Speichern Sie einen oder zwei langsame Abfragepläne mit EXPLAIN (ANALYZE, BUFFERS). Überprüfen Sie die Protokollierung temporärer Dateien, wenn Sie Auslagerungen vermuten:

log_temp_files = 0

Diese Einstellung protokolliert jede temporäre Datei, verwenden Sie sie also vorsichtig auf einem ausgelasteten System oder setzen Sie einen Schwellenwert wie 64MB. Wenn Sie viele große temporäre Dateien von derselben Abfrageform sehen, optimieren Sie die Abfrage, fügen Sie einen Index hinzu oder erhöhen Sie work_mem für diese Arbeitslast.

Ändern Sie immer nur eine Sache auf einmal. Einige Einstellungen erfordern einen Neustart, andere nur ein Neuladen, und einige können pro Sitzung gesetzt werden. PostgreSQL sagt Ihnen, was was ist:

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout', 'max_wal_size');

postmaster-Kontext bedeutet Neustart. sighup bedeutet Neuladen. user bedeutet, dass Änderungen auf Sitzungsebene möglich sind.

Allgemeine Optimierungstipps und Best Practices

• Iterative Optimierung: Beginnen Sie mit kleinen, inkrementellen Änderungen. Ändern Sie jeweils einen Parameter, beobachten Sie die Auswirkungen und passen Sie ihn dann bei Bedarf weiter an. Optimierung ist keine einmalige Aufgabe, sondern ein fortlaufender Prozess.
• Alles überwachen: Nutzen Sie die integrierten Statistikansichten von PostgreSQL (pg_stat_database, pg_stat_bgwriter, pg_stat_activity), Überwachungstools auf Betriebssystemebene (z. B. iostat, vmstat, top) und externe Überwachungslösungen, um Daten zu CPU, Speicher, Festplatten-E/A und Abfrageleistung zu sammeln.
• Verstehen Sie Ihre Arbeitslast: Ist Ihre Anwendung leseintensiv oder schreibintensiv? Führt sie komplexe Analyseabfragen oder einfache Transaktionsoperationen durch? Passen Sie Ihre Konfiguration an die spezifischen Merkmale Ihrer Arbeitslast an.
• Berücksichtigen Sie andere Parameter: Obwohl shared_buffers, work_mem und checkpoint_timeout entscheidend sind, können viele andere Parameter die Leistung beeinflussen. Beispielsweise werden effective_cache_size (Hinweise für den Abfrageplaner über den verfügbaren OS-Cache) und wal_buffers (Speicher für WAL-Einträge vor dem Leeren) oft zusammen mit diesen optimiert.
• Verwenden Sie EXPLAIN ANALYZE: Dieses unschätzbare Tool hilft Ihnen zu verstehen, wie PostgreSQL eine Abfrage ausführt, identifiziert Engpässe und kann aufdecken, ob work_mem nicht ausreicht.

Die beste PostgreSQL-Optimierungsarbeit ist auf eine gute Weise langweilig: messen, eine Einstellung ändern, erneut messen und einen Rollback-Pfad bereithalten. shared_buffers, work_mem und Checkpoint-Einstellungen können einen echten Unterschied machen, aber sie arbeiten mit Abfrageplänen, Indizes, Autovacuum, Verbindungszahlen und Speicher zusammen. Wenn diese Teile ungesund sind, wird die Konfiguration allein die Datenbank nicht retten.