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.
VACUUM
oder ANALYZE
ohne Grund ausPostgreSQL-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.
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 50autovacuum_vacuum_scale_factor
: Dies hat einen Standardwert von 0,2Zusammen 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:
pg_stat_user_tables.n_dead_tup > (pg_class.reltuples x autovacuum_vacuum_scale_factor) + autovacuum_vacuum_threshold
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.
Ä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 50autovacuum_analyze_scale_factor
: Dies hat einen Standardwert von 0,1Wie 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.
ALTER TABLE <table_name>
SET (autovacuum_analyze_threshold = <threshold rows>)
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:
individual thread’s cost_limit = autovacuum_vacuum_cost_limit / autovacuum_max_workers
Die Arbeitskosten eines Autovacuum-Threads werden anhand von drei Parametern berechnet:
vacuum_cost_page_hit
: Dies hat einen Standardwert von 1vacuum_cost_page_miss
: Dies hat einen Standardwert von 10vacuum_cost_page_dirty
: Dies hat einen Standardwert von 20Was diese Parameter bedeuten, ist Folgendes:
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.
ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_limit = <large_value>)
ALTER TABLE <table_name> SET (autovacuum_vacuum_cost_delay = <lower_cost_delay>)
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.
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:
Kontaktieren Sie uns gerne über das
Kontaktformular und vereinbaren ein unverbindliches
Beratungsgespräch mit unseren Berater:innen zur
Bedarfsevaluierung. Gemeinsam optimieren wir Ihre
Umgebung und steigern Ihre Performance!
Wir freuen uns auf Ihre Kontaktaufnahme!