autovacuum für einzelne Tabellen abstellen funktioniert nicht in Postgres 9.0

Hallo,

weil auto_vacuum auf unseren Datenbanken (Postgres 9.0 unter Windows 64 Bit) viel zu lange braucht wollte Ich dies gezielt für bestimmte (hier sehr viele) Tabellen deaktiveren, in die ausschließlich Datensätze (eine begrenzte Zeit lang) hinzugefügt werden. Nach kurzer Recherche bin Ich auf folgendes Kommando dazu gestossen:

alter table table_name set (autovacuum_enabled=false);

Nachdem das keine Wirkung zeigte und immer noch autovacuums mit dem Kommentar “(to prevent wraparound)” ausgeführt wurden bin Ich auf einen Bug-Report gestoßen, der einen mit 0 besetzten Wert von autovacuum_freeze_max_age dafür verantwortlich macht. Es wurde dort empfohlen, diesen auf -1 für “deaktiviert” zu setzen. Das funktioniert aber nicht, da das alter table hier keine negativen Werte akzeptiert. Ich habe diesen dann auf den maximal möglichen Wert eingestellt:

alter table table_name set (autovacuum_freeze_max_age=2000000000);

Zur sicheren Aktivierung habe Ich den Server wieder neu gestartet, es bleibt aber beim alten Verhalten, autovacuum führt weiterhin den oben beschriebenen vacuum auf diesen Tabellen aus.

Hat hier schonmal jemand postgres (9.0) erfolgreich das autovacuum auf bestimmten Tabellen abgewöhnt und kann kurz erklären, was man dazu tun muss?

Ähm … ich möchte dir ganz dringend empfehlen, keine Einstellungen zu verändern deren Auswirkung du nicht kennst! Das kann in diesem Fall zu Datenverlusten führen.

Lies dir bitte diese Seite hier komplett durch:

PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: Routine Vacuuming

Und speziell den Abschnitt zu XID Wraparound.

Danke für die Anmerkung, diesen Abschnitt der Dokumentation kenne Ich.

Kannst Du bei der eigentlichen Frage dieses Threads helfen, wie unterbindet man sicher autovacuums für Tabellen?

Gar nicht. Das Vacuum “to prevent wraparund” ist notwendig, sonst verlierst du Daten.
Wenn du rechtzeitig (z. B. nachts) ein Vacuum auf der Tabelle laufen lässt, muss Autovacuum nicht anspringen. Andernfalls wird Autovacuum ein Vacuum für diese Tabelle starten, egal was du versuchst.

Wir liegen bei unseren Datenbanken und den darauf laufenden Anwendungen um Größenordnungen unterhalb der kritischen Zahl von 2 Billionen Transaktionen pro Tabelle. Ich beobachte hier auch, daß das Vacuum immer wieder durchgeführt wird, auch auf Datenbanken/Tabellen an denen seit Monaten keinerlei Änderungen mehr vorgenommen wurden. Da scheint die Erkennung, wann das überhaupt nötig ist (in der 9.0) nicht richtig zu funktionieren. Das vergößert die Last und die Turn-Around-Zeit für den Vacuum auf dem Server unnötigerweise.

Wir brauchen also immer noch einen Weg, damit nicht mehr veränderte und sehr selten gelesene Tabellen nicht mehr vakuumiert werden, ohne das autovacuum global auszuschalten. Gibt es dafür einen Ansatz? Das kann ja jetzt kein gänzlich neues Problem sein.

Die Anzahl Transaktionen ist nicht pro Tabelle sondern pro Cluster grübel oder Datenbank? Eines von beiden.
Oh, und PostgreSQL fängt nicht erst kurz vor den 2 Billionen mit dem VACUUM an, sondern viel früher. Sonst wäre es kurz darauf bereits zu spät und die Datenbank müsste sich herunterfahren.

Das ist kein gänzlich neues Problem, da hast du Recht. Die Lösung ist halt, das man das Vacuum manuell startet, und zwar rechtzeitig.

Auch auf Datenbank-Level wird diese Zahl hier nicht annähernd erreicht. Auf vielen Datenbanken hier sind seit dem letzten vacuum auch keine Transaktionen durchgeführt worden, es wird aber trotzdem ein vacuum (to prevent…) ausgeführt. Hat hier jemamd ähnliche Erfahrungen gemacht? Gegen ein vacuum, das vom Grad der Nutzung, hier Anzahl der Transaktionen, abhängt, ist ja nichts einzuwenden. Nur sollten die Intervalle dann im Bezug zur Nutzung auch plausibel sein, und das vacuum sollte nicht immer wieder ohne Änderung anspringen.

Hast du dir mal die Parameter zum Vacuum angesehen, z.B.

autovacuum_vacuum_threshold    # min number of row updates before vacuum
autovacuum_analyze_threshold    # min number of row updates before analyze
autovacuum_vacuum_scale_factor    # fraction of table size before vacuum
autovacuum_analyze_scale_factor    # fraction of table size before analyze

Mit denen u.v.a. sollte das Vacuum eigentlich ziemlich genau parametrisierbar sein.

autovacuum_vacuum_threshold und autovacuum_analyze_threshold enthalten
beide Werte > 0. Trotzdem werden auch inaktive Datenbanken bei jedem Lauf
komplett vakuumiert, es vergeht auch bei jeder Tabelle Zeit die geschätzt
proportional zur Datenmenge in den Tabellen ist. Könnt Ihr bitte einmal
eure Installationen auf diesen Aspekt hin beobachten? Ihr habt ja sicher
auch die eine oder andere Tabelle oder vielleicht sogar ganze Datenbanke,
die seit längerem nicht mehr geändert wurden.