PostgreSQL: VACUUM und ANALYZE Best Practice-Tipps

VACUUM und ANALYZE sind die beiden wichtigsten Wartungsmethoden für PostgreSQL-Datenbanken.

Ein VACUUM wird verwendet, um den von „toten” Tupeln in einer Tabelle belegten Platz wiederherzustellen. Ein totes Tupel entsteht, wenn ein Datensatz entweder gelöscht oder aktualisiert wird. PostgreSQL entfernt die alte Zeile nicht physisch aus der Tabelle, sondern setzt hier einen „Marker“, damit Abfragen diese Zeile nicht zurückgeben. Wenn ein Vacuum-Prozess ausgeführt wird, wird der von diesen toten Tupeln belegte Platz als wiederverwendbar markiert.

Bei dem ANALYZE-Vorgang wird der Inhalt der Tabellen einer Datenbank analysiert und es werden Statistiken über die Verteilung der Werte in jeder Spalte jeder Tabelle gesammelt. Die PostgreSQL-Abfrage-Engine verwendet diese Statistiken, um den besten Abfrageplan zu finden. Wenn Zeilen in eine Datenbank eingefügt, gelöscht und aktualisiert werden, ändert sich auch die Spaltenstatistik. ANALYZE – entweder manuell oder automatisch von PostgreSQL nach einem Autovacuum ausgeführt – stellt sicher, dass die Statistiken auf dem neuesten Stand sind.

Obwohl sie relativ einfach klingen, sind VACUUM und ANALYZE hinter den Kulissen zwei komplexe Prozesse und wir möchten Ihnen in diesem Blogbeitrag einige Best Practices für VACUUM und ANALYZE vorstellen.

Tipp 1: Führen Sie kein manuelles VACUUM oder ANALYZE ohne Grund aus

PostgreSQL-Vacuuming minimiert Tabellen-Bloats (unsere sog. “toten” Tupel) und verhindert eine Fragmentierung der Transaktions-ID. AUTOVACUUM stellt den von toten Tupeln belegten Speicherplatz nicht wieder her. Dazu müssen Sie einen VACUUM FULL-Befehl ausführen. VACUUM FULL hat jedoch Auswirkungen auf die Leistung. Die Zieltabelle ist während des Vorgangs gesperrt, wodurch Lesevorgänge in der Tabelle verhindert werden. Der Prozess erstellt außerdem auch eine vollständige Kopie der Tabelle, für deren Ausführung zusätzlicher Speicherplatz erforderlich ist. Sie sollten VACUUM FULL nur dann ausführen, wenn ein sehr großer Anteil an Bloats vorliegt und die Abfragen stark leiden. Weiterhin sollten Sie dafür Zeiträume mit der niedrigsten Datenbankaktivität verwenden.

Es wird auch empfohlen, nicht zu oft manuelle Vacuums in der gesamten Datenbank auszuführen. Wenn die Zieldatenbank bereits durch den Autovacuumprozess optimal defragmentiert werden konnte, entfernt ein manuelles Vacuum möglicherweise keine toten Tupel, sondern verursacht unnötige Lese-/Schreibvorgänge oder verbraucht CPU-Zeit. Falls erforderlich, sollten manuelle Vacuums nur tabellenweise ausgeführt werden.

Autovacuum hält auch die Datenverteilungsstatistiken einer Tabelle auf dem neuesten Stand, ohne sie neu zu erstellen. Bei manueller Ausführung erstellt der Befehl ANALYZE diese Statistiken tatsächlich neu, anstatt sie zu aktualisieren. Auch hier kann die Neuerstellung von Statistiken, wenn sie bereits durch ein reguläres Autovacuum optimal aktualisiert wurden, unnötigen Druck auf die Systemressourcen verursachen.

Die Zeit, in der Sie ANALYZE manuell ausführen müssen, liegt unmittelbar nach dem Import von Daten in die Zieltabelle. Eine große Anzahl neuer Zeilen in einer vorhandenen Tabelle verzerrt die Verteilung der Spaltendaten erheblich. Die neuen Zeilen führen dazu, dass vorhandene Spaltenstatistiken nicht mehr aktuell sind. Wenn das Abfrageoptimierungsprogramm solche Statistiken verwendet, kann die Abfrageleistung sehr langsam sein. In diesen Fällen ist es besser, den Befehl ANALYZE unmittelbar nach dem Laden der Daten auszuführen, um die Statistik vollständig neu zu erstellen, als darauf zu warten, dass das Autovacuum aktiviert wird.

Tipp 2: Feineinstellung des Autovacuum-Schwellenwerts

Es ist wichtig, das Autovacuum zu überprüfen oder anzupassen und die Konfigurationsparameter in der Datei postgresql.conf oder in einzelnen Tabelleneigenschaften zu analysieren, um ein Gleichgewicht zwischen Autovacuum und Leistungsgewinn herzustellen.

PostgreSQL verwendet zwei Konfigurationsparameter, um zu entscheiden, wann ein Autovacuum gestartet werden soll:

• autovacuum_vacuum_threshold : Dies hat einen Standardwert von 50
• autovacuum_vacuum_scale_factor : Dies hat einen Standardwert von 0,2

Zusammen weisen diese Parameter PostgreSQL an, ein Autovacuum zu starten, wenn die Anzahl der toten Zeilen in einer Tabelle die Anzahl der Zeilen in dieser Tabelle multipliziert mit dem Skalierungsfaktor plus dem Vacuum-Schwellenwert überschreitet. Mit anderen Worten, PostgreSQL startet das Autovauum für eine Tabelle, wenn:

Für kleine bis mittelgroße Tabellen kann dies ausreichend sein. Bei einer Tabelle mit 10.000 Zeilen muss die Anzahl der toten Zeilen über 2.050 ((10.000 x 0,2) + 50) liegen, bevor ein Autovacuum gestartet wird.

Die Standardwerte funktionieren möglicherweise nicht für alle Tabellen. Bei den Standardwerten muss eine Tabelle mit 1 Million Zeilen beispielsweise mehr als 200.050 tote Zeilen enthalten, bevor ein Autovacuum gestartet wird ((1000.000 x 0,2) + 50). Dies kann längere Lücken zwischen Autovacuums, zunehmend lange Autovacuum-Zeiten und schlimmer noch bedeuten, dass Autovacuum überhaupt nicht ausgeführt wird, wenn aktive Transaktionen auf dem Tabellen es sperren.

Daher sollte das Ziel darin bestehen, diese Schwellenwerte auf optimale Werte einzustellen, damit das Autovacuum in regelmäßigen Abständen stattfinden kann und nicht lange dauert, während die Anzahl der toten Zeilen relativ niedrig gehalten wird.

Ein Ansatz besteht darin, den einen oder den anderen Parameter zu verwenden. Wenn wir also z.B. autovacuum_vacuum_scale_factor auf 0 und stattdessen autovacuum_vacuum_threshold auf beispielsweise 5.000 setzen, wird eine Tabelle automatisch defragmentiert, wenn die Anzahl der toten Zeilen mehr als 5.000 beträgt.

Tipp 3: Optimieren Sie den Schwellenwert für die automatische Analyse

Ähnlich wie beim Autovacuum verwendet die Autoanalyse auch zwei Parameter, die entscheiden, wann das Autovacuum auch eine Autoanalyse auslöst:

• autovacuum_analyze_threshold : Dies hat einen Standardwert von 50
• autovacuum_analyze_scale_factor : Dies hat einen Standardwert von 0,1

Wie bei Autovacuum kann der Parameter autovacuum_analyze_threshold auf einen Wert gesetzt werden, der die Anzahl der eingefügten, gelöschten oder aktualisierten Tupel in einer Tabelle vor Beginn einer automatischen Analyse festlegt. Wir empfehlen, diesen Parameter für große Tabellen und Tabellen mit hohen Transaktionen separat festzulegen. Die Tabellenkonfiguration überschreibt die postgresql.conf-Werte.

Das folgende Codefragment zeigt die SQL-Syntax zum Ändern der Einstellung autovacuum_analyze_threshold für eine Tabelle.

Tipp 4: Optimieren Sie die Autovacuum-Worker

Ein weiterer Parameter, der von Datenbankadministratoren häufig übersehen wird, ist autovacuum_max_workers mit dem Standardwert 3. Autovacuum ist kein einzelner Prozess, sondern eine Anzahl einzelner Vacuum-Threads, die parallel ausgeführt werden. Der Grund für die Angabe mehrerer Worker besteht darin, sicherzustellen, dass das Vacuuming großer Tabellen das Vacuuming kleinerer Tabellen nicht verhindert. Der Parameter autovacuum_max_workers weist PostgreSQL an, die Anzahl der Autovacuum-Worker-Threads für die Bereinigung zu erhöhen.

Es ist üblich, die Anzahl der maximalen Arbeitsthreads zu erhöhen, in der Hoffnung, dass dies das Autovacuum beschleunigt. Dies funktioniert nicht, da alle Threads dasselbe autovacuum_vacuum_cost_limit verwenden, das einen Standardwert von 200 hat. Jedem Autovacuum-Thread wird mithilfe der folgenden Formel ein Kostenlimit zugewiesen:

Die Arbeitskosten eines Autovacuum-Threads werden anhand von drei Parametern berechnet:

• vacuum_cost_page_hit : Dies hat einen Standardwert von 1
• vacuum_cost_page_miss : Dies hat einen Standardwert von 10
• vacuum_cost_page_dirty : Dies hat einen Standardwert von 20

Was diese Parameter bedeuten, ist Folgendes:

• Wenn ein Vacuum-Thread die Datenseite findet, die er im gemeinsam genutzten Puffer bereinigen soll, betragen die Kosten 1.
• Befindet sich die Datenseite nicht im gemeinsam genutzten Puffer, sondern im Betriebssystem-Cache, betragen die Kosten 10.
• Wenn die Seite als “dirty” markiert werden muss, weil der Vacuum-Thread tote Zeilen löschen musste, betragen die Kosten 20.

Eine erhöhte Anzahl von Worker-Threads senkt das Kostenlimit für jeden Thread. Da jedem Thread eine niedrigere Kostengrenze zugewiesen wird, wird er häufiger in den Ruhezustand versetzt, da die Kostenschwelle leicht erreicht wird, was letztendlich dazu führt, dass der gesamte Vacuum-Prozess langsam abläuft. Wir empfehlen, das autovacuum_vacuum_cost_limit auf einen höheren Wert wie beispielsweise 2000 zu erhöhen und dann die maximale Anzahl an Worker-Threads anzupassen.

Besser ist es, diese Parameter nur bei Bedarf für einzelne Tabellen abzustimmen. Wenn beispielsweise das Autovacuum einer großen Transaktionstabelle zu lange dauert, kann die Tabelle vorübergehend so konfiguriert werden, dass sie ihre eigene Vacuumkostengrenze und Kostenverzögerungen verwendet. Das Kostenlimit und die Verzögerung überschreiben die in postgresql.conf festgelegten systemweiten Werte.

Das folgende Codefragment zeigt, wie einzelne Tabellen konfiguriert werden.

Durch die Verwendung des ersten Parameters wird sichergestellt, dass der der Tabelle zugewiesene Autovacuum-Thread mehr Arbeit leistet bevor er in den Ruhezustand versetzt wird. Wenn Sie autovacuum_vacuum_cost_delay verringern, wird auch die Zeit verringert, die der Thread im Ruhezustand verbringt.

Fazit

Wie Sie sehen können, ist das Ändern der Konfigurationsparameter für VACUUM und ANALYZE unkompliziert, muss jedoch zuerst sorgfältig beobachtet werden. Jede Datenbank unterscheidet sich in Bezug auf Größe, Verkehrsmuster und Transaktionsrate. Wir empfehlen Ihnen zunächst genügend Informationen über Ihre Datenbank zu sammeln, bevor Sie die Parameter ändern oder ein manuelles Vacuum-/Analysesystem einführen. Solche Informationen könnten sein:

• Anzahl der Zeilen in jeder Tabelle
• Anzahl der toten Tupel in jeder Tabelle
• Die Zeit des letzten Vacuums für jede Tabelle
• Der Zeitpunkt der letzten Analyse für jede Tabelle
• Die Rate des Einfügens / Aktualisierens / Löschens von Daten in jeder Tabelle
• Die Zeit, die das Autovacuum für jede Tabelle benötigt
• Warnungen vor nicht defragmentierten Tabellen
• Aktuelle Performance der Abfragen und der Tabellen, auf die Sie zugreifen
• Performance der gleichen Abfragen nach einem manuellen Vacuum/Analyse