PostgreSQL aufräumen - VACUUM

Die Bereinigung sogenannter dead tuples wird mit dem Kommando VACUUM veranlasst. VACUUM reorganisiert den Speicher nicht wirklich, sondern markiert die ungültig gewordenen Speicherbereiche, in denen dead tuples stehen, als frei zur Wiederverwendung. Diese Markierungen werden in die Free Space Map (FSM) eingetragen, ein Katalog, in dem die wiederverwendbaren Bereiche in den Speicherseiten aufgelistet sind. Nur vollständig leere Speicherseiten werden gelöscht. Diese Art von VACUUM wird auch als lazy vacuum bezeichnet. Der Vorteil ist, dass keine exklusiven Locks angefordert werden müssen und VACUUM im normalen Betrieb ausgeführt werden kann.

phpugs=# vacuum verbose anfrage;
...
INFO: "anfrage": removed 130085 row versions in 2381 pages
INFO: index "i_anenddate" now contains 39653 row versions in 593 pages
DETAIL: 130085 index row versions were removed.
169 index pages have been deleted, 0 are currently reusable.
...

Am Ende eines VACUUM VERBOSE wird der Status der Free Space Map ausgegeben:

INFO: free space map contains 2573 pages in 139 relations
DETAIL: A total of 4688 page slots are in use (including overhead).
4688 page slots are required to track all free space.
Current limits are: 153600 page slots, 1000 relations, using 965 kB.

Wiederverwendbare Speicherbereiche können nur durch VACUUM in die FSM eingetragen werden und nur Speicherbereiche, die in der FSM gelistet sind, können wiederverwendet werden.

Wenn danach Datensätze eingefügt oder aktualisiert werden, muss der Server den Speicher nicht durchsuchen, sondern kann anhand der FSM direkt einen freien Bereich auf einer Speicherseite ansteuern, um den Datensatz zu speichern. Wenn kein wiederverwendbarer Bereich gefunden wird, wird der Datensatz ans Ende der Tabelle angehängt.

Die Größe der FSM wird in der Konfigurationsdatei festgelegt. Anhand der Ausgabe von VACUUM VERBOSE kann man nachprüfen, ob die Werte ausreichend sind.

  • max_fsm_relations gibt die max. Anzahl der Tabellen an, die ihre freien Speicherbereiche in die FSM eintragen können. Falls die Anzahl der FSM-Pages zu klein ist, warnt Postgresql, z.B.:
    NOTICE: number of page slots needed (22608) exceeds max_fsm_pages (20000)
HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 22608.
  • max_fsm_pages gibt an, wieviel Speicherblöcke die FSM haben soll.

 VACUUM FULL

VACUUM FULL gibt den Speicher wieder an das Betriebssystem zurück, indem es Datensätze zwischen Speicherseiten verschiebt, um die Tabelle auf so wenige Blöcke wie möglich zu reduzieren. Allerdings fordert er während der Bearbeitung einen exklusiven Lock für die Tabelle an.

Wenn es mehr dead tuples gibt, als von der FSM erfasst werden können, ist ein VACUUM FULL nötig, weil diese Speicherbereiche nicht mehr von VACUUM freigegeben werden können. Eine Vergrößerung der FSM - max_fsm_pages behebt dieses Problem.

autovacuum

Seit PostgreSQL 8.1 gibt es einen Dienst, autovacuum, der die Bereinigung der Datenbanken automatisch ausführt und in der postgresql.conf aktiviert werden muss. Dieser Daemon

  • prüft periodisch alle Tabellen
  • markiert dead tuples
  • trägt wiederverwendbare Speicherbereiche in die FSM ein
  • führt das Kommando ANALYZE aus

Der autovacuum-Daemon ist (in neueren Versionen) standardmäßig eingeschaltet. Um ihn zu nutzen, muss in der postgresql.conf die folgenden Option aktiviert sein.

  • track_counts = on

autovacuum und eine ausreichende FSM machen VACUUM FULL, und damit exclusive Locks auf den Tabellen, unnötig. Es gibt viele Konfigurationsoptionen, mit denen autovacuum gesteuert werden kann, beispielsweise kann man ihn nach einem festen Zeitintervall (autovacuum_naptime = 1min) starten oder auch, indem man Schwellwerte für Tabellen angibt, beispielsweise 1000 aktualisierte oder gelöschte Datensätze: autovacuum_vacuum_threshold = 1000 oder autovacuum_analyze_threshold, mit dem ANALYZE für eine Tabelle ausgelöst wird, nachdem eine bestimmte Anzahl Datensätze eingefügt, aktualisiert oder gelöscht wurden.

Beispiel: In einer Installation mit ca. 60 Datenbanken und 450 Tabellen läuft autovacuum alle 2 Minuten:

Feb 22 06:12:51 server postgres[7758]: [1-1] 2007-02-22 06:12:51 CET
LOG: autovacuum: processing database "dbx"
...
Feb 22 06:14:53 server postgres[8050]: [1-1] 2007-02-22 06:14:53 CET
LOG: autovacuum: processing database "dby"