VACUUM FULL - Viel hilft viel?

VACUUM in PostgreSQL^® ist seit jeher mit Mythen und falschen Informationen behaftet. Besonders verbreitet ist offenbar die Einstellung, VACUUM FULL helfe vorbeugend. Das genaue Gegenteil ist häufig der Fall.

VACUUM – Der Staubsauger

Seit der Einführung von MVCC (Multi Version Concurrency Control) in PostgreSQL^® 6.5 im Jahr 1999 gibt es das Kommando VACUUM. Mit Hilfe dieses Kommandos wird der sogenannte Heap, also die Dateien, die die Tabellendaten enthalten, defragmentiert und nicht mehr belegter Speicherplatz freigegeben. Dies ist notwendig, da PostgreSQL^® Zeilen bei UPDATE oder DELETE nicht etwa physikalisch löscht, sondern eine neue Version der Zeile anlegt bzw. die Zeile einfach als gelöscht markiert. Die alte Version muss noch so lange beibehalten werden, wie es auch Transaktionen gibt, die diese Zeilenversion noch „sehen“ können. Ist eine Tabelle sehr stark durch UPDATE oder DELETE/INSERT frequentiert, und passiert VACUUM zu selten (beispielsweise weil Autovacuum nicht verwendet wird), so kann der sogenannte „tote“ Speicherplatz in einer Tabelle sehr stark anwachsen.

VACUUM FULL – Vorbeugende Reorganisation?

Viele Administratoren sind daher der Auffassung, dass es aus diesem Grund angebracht ist, dies im Vorfeld durch nächtliche VACUUM FULL Jobs dem Anwachsen der Tabelle vorzubeugen. Dies ist eine schlechte Strategie, aus mehreren Gründen:

1. VACUUM FULL benötigt im Gegensatz zu normalem VACUUM eine exklusive Tabellensperre, d.h. der Zugriff ist für alle nebenläufigen Transaktionen nicht möglich (auch reine Leseanfragen).
2. VACUUM FULL führt eine komplette physische Reorganisation der Tabelle durch, nicht jedoch der Indexe. Dies hat sich mit PostgreSQL^® 9.0 geändert. Eine exklusive Tabellensperre ist weiterhin notwendig.
3. Läuft eine Datenbank mit WAL-Archiving, so kommt es durch VACUUM FULL zu massiv erhöhtem Datenaufkommen im Transaktionslog. Dies kann Probleme mit dem Backuparchiv nach sich ziehen.
4. Man benötigt auf jeden Fall ein Wartungsfenster für exklusiven Zugriff der Tabellen.
5. Im Gegensatz zu normalen VACUUM ist VACUUM FULL daher nicht für den Einsatz in 24/7-Datenbanken geeignet.

Während sich die meisten Nachteile durch ein Wartungsfenster umschiffen lassen, sind die Nachteile durch sehr häufiges VACUUM FULL gravierender. Besonders PostgreSQL^®-Versionen bis einschließlich 8.4 sind davon betroffen. Um das zu verstehen, muss man sich die Funktionsweise des VACUUM FULL Kommandos in diesen Versionen ansehen:

1. VACUUM FULL untersucht die Tabelle sequentiell nach totem Speicherplatz. Hierzu werden die gefundenen toten Bereiche während VACUUM FULL in einem Array im Hauptspeicher gespeichert. Ist das Array voll (begrenzt durch maintenance_work_mem), so werden sichtbare (also aktive) Zeilen von unten her in die gefundenen toten Bereiche verlagert (sofern Platz hierfür ausreichend zur Verfügung steht).
2. Sind Indexe auf der Tabelle vorhanden, so müssen diese ebenfalls aktualisiert werden.
3. Ist das Array abgearbeitet, beginnt der Algorithmus wieder von vorne, solange, bis das Ende der Tabelle erreicht ist.
4. Anschließend wird die Tabelle physisch verkleinert.

Das Hauptproblem ist das Umsortieren der Zeilen in den freigewordenen Speicherplatz. Dies sorgt für massive I/O auf dem Speichersystem. Noch schwerwiegender ist jedoch die Tatsache, dass beim Umsortieren der Index ebenfalls aktualisiert werden muss. Passiert das sehr häufig, so kann es passieren, dass der Index selbst sehr stark fragmentiert. In diesem Fall wächst der Index selbst an, man spricht dann vom sogenannten Index Bloat. Daher kann es erforderlich sein, direkt nach dem VACUUM FULL ein REINDEX auf die Tabellen auszuführen, insbesondere wenn Tabellen sehr stark fragmentiert waren und viele Tupel umsortiert wurden. Dies alles sorgt bei sehr großen Tabellen auch für sehr lange Laufzeiten. Ab PostgreSQL^® 9.0 verhält sich VACUUM FULL wie das CLUSTER Kommando, d.h. die Tabelle wird sequentiell gelesen und parallel komplett neu aufgebaut. Dies hat den Vorteil, dass man nur die Zeilen liest, die aktiv sind und die „toten“ Zeilen außen vor lässt. Anschließend werden die Indexe neu erzeugt. Dies eliminiert viele Nachteile des alten Algorithmus, vermeidet jedoch nicht die Notwendigkeit exklusiver Tabellensperren. Ferner benötigt die Reorganisation der Tabelle im schlechtesten Falle nochmal soviel Speicherplatz, wie die aktuell zu bearbeitende Tabelle.

VACUUM und Autovacuum für tägliche oder sehr granulare Wartung

VACUUM bzw. Autovacuum sind für die tägliche oder dauerhafte Wartung von PostgreSQL^®-Datenbanken ausgelegt.

1. Wer sich eine sorgfältige VACUUM-Policy mit normalem VACUUM oder, noch besser, Autovacuum zurechtlegt, benötigt kein VACUUM FULL.
2. Autovacuum sollte auf jeden Fall in Betracht gezogen werden, muss jedoch an den Workload angepasst werden.
3. Ist dennoch mal eine Tabelle sehr stark aufgebläht, so kann mit aktuellen 8er PostgreSQL^®-Versionen mit CLUSTER die Tabelle häufiger deutlich schneller verkleinert werden, ohne das Problem der Indexfragmentierung. Da CLUSTER anhand eines Index die Tabelle reorganisiert, benötigt man mindestens einen Index. Ferner sollte unbedingt danach die Optimizerstatistiken mit ANALYZE aktualisiert werden.
4. Bis einschließlich PostgreSQL^® 8.3 ist es unbedingt notwendig, sich vor Inbetriebnahme die Parameter max_fsm_pages und max_fsm_relations anzuschauen. Die Werte dieser Parameter kann nur durch einen Neustart der Datenbank geändert werden und beeinflussen die Anzahl an erfassten fragmentierten Speicherplatz in Tabellen und Indexe sowie die Anzahl an Tabellen und Indexe die durch VACUUM erfasst werden können (VACUUM FULL benutzt die sogenannte Free Space Map nicht). Ab PostgreSQL^® 8.4 werden die FSM pro Tabelle automatisch angepasst.
5. Auch VACUUM kann unter günstigen Umständen eine Tabelle verkleinern. Wenn die Tabelle am Ende nur noch leere Blöcke enthält und aktuell keine Transaktion neue Zeilen in diese Bereiche einlagern möchte, dann kann auch normales VACUUM die Tabelle entsprechend eindampfen.

Warum dann überhaupt noch VACUUM FULL?

VACUUM FULL ist ein Kommando, das nicht für die tägliche Wartung ausgelegt ist. Ist das Kind einmal in den sprichwörtlichen Brunnen gefallen und eine Tabelle stark aufgebläht, so ist es je nach PostgreSQL^®-Version unausweichlich mit VACUUM FULL den Speicherplatz freizugeben. Bei älteren PostgreSQL^®-Versionen sollte sich der Administrator besonders bei sehr großen Speicherbedarf der Tabelle besser überlegen, auf das CLUSTER-Kommando auszuweichen. Möchte man dennoch VACUUM FULL benutzen, so sollte man bei älteren PostgreSQL^®-Versionen mit REINDEX ebenfalls die Indexe neu erzeugen. Weitere Infos zu diesem Thema finden sich im PostgreSQL^® Wiki.

Weitere Informationen

Alle Blog-Artikel zum Thema PostgreSQL^® werden auch als Kategorie PostgreSQL^® samt eigenem Feed angeboten. Wir helfen auch gerne mit Support und Services für PostgreSQL^®.