Auto Vacuum funktioniert nicht richtig

Hallo zusammen,

seit längerem haben wir auf einem OpenSuse 10.2 eine PostgreSQL 8.2.4 im Testeinsatz und haben folgende Beobachtung gemacht:
In dieser Datenbank gibt es eine Tabelle mit ca. 40.000 Einträgen (Größe, ca. 5MB) in der alle 15 Minuten über eine Programm ein Update mit den neuesten statistischen Werten gemacht wird. Es werden praktisch keine Inserts gemacht, sondern fast nur Updates.

Wir haben Autovacuum eingestellt und folgende Parameter in der config-Datei:

#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------

autovacuum = on                # enable autovacuum subprocess?
                    # 'on' requires stats_start_collector
                    # and stats_row_level to also be on
#autovacuum_naptime = 1min        # time between autovacuum runs
#autovacuum_vacuum_threshold = 500    # min # of tuple updates before
                    # vacuum
#autovacuum_analyze_threshold = 250    # min # of tuple updates before 
                    # analyze
#autovacuum_vacuum_scale_factor = 0.2    # fraction of rel size before 
                    # vacuum
#autovacuum_analyze_scale_factor = 0.1    # fraction of rel size before 
                    # analyze
#autovacuum_freeze_max_age = 200000000    # maximum XID age before forced vacuum
                    # (change requires restart)
#autovacuum_vacuum_cost_delay = -1    # default vacuum cost delay for 
                    # autovacuum, -1 means use 
                    # vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1    # default vacuum cost limit for 
                    # autovacuum, -1 means use
                    # vacuum_cost_limit

Nach einigen Tagen haben wir aber festgestellt, dass die Datei der Tabelle 450 MB hatte und mit jedem Update 5 MB größer wurde.
Um zu sehen ob Vacuum überhaupt geht haben wir dann ein Vacuum-Full gemacht und dann hatte die Tabelle nur noch 5 MB. Einige Updates später hatte sie wieder 25 MB. Was mich irritiert, da (wie aus den Paramtern zu ersehen ist) das Autovacuum jede Minute anspringen sollte und damit die Tabelle gar nicht so groß anwachsen sollte.

Jetzt die Fragen:

  • Habe wir generell in der Konfig irgendetwas falsch eingestellt ?
  • Kann es sein, dass ein Prozess, das Autovacuum stört oder unterbricht ?
  • Kann ich irgendwo, außer in der Tabelle pg_stat_all_tables sehen, ob das Autovacuum angesprungen ist ?
  • ist die Zeit von einer Minute für das Autovacuum zu kurz ? Sollte man hier evtl mehr einstellen ? (Damit sich der Prozess nicht selbst blockiert, wenn er länger als 1 Minuten benötigen sollte ?)

Kann uns jemand sonst noch irgendwelche Tipps zu autovacuum geben ?

Vielen Dank im Vorraus für Antworten

Setze mal maintenance_work_mem höher, ich rate mal wild, daß der Wert zu gering ist.

Ansonsten mal ein ‘vacuum analyse verbose’ auf diese spezielle Tabelle und schauen, was das sagt.

Andreas

Als erstes: das # vor dem Parameter sollte verschwinden, sonst ist der Parameter nicht gesetzt :wink:

Eine ‘naptime’ von einer Minute bedeutet, das autovacuum eine Minute wartet, bevor die nächste Datenbank bearbeitet wird. Bei jedem Vacuum Lauf wird immer genau eine Datenbank berücksichtigt. Dieser Parameter ist dafür gedacht, das zwischen zwei VACUUM Läufen die Datenbank etwas “entspannen” zu lassen.

Im Logfile sollte stehen, welche Tabellen bearbeitet wurden. Ist dort irgendetwas zu sehen?

HI,

im Logfile ist nichts zu finden, dass das Autovacuum lief.

Hier die Einstellungen zum Thema Speicher usw:

#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 256MB            # min 128kB or max_connections*16kB
                    # (change requires restart)
#temp_buffers = 8MB            # min 800kB
#max_prepared_transactions = 5        # can be 0 or more
                    # (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 1MB                # min 64kB
#maintenance_work_mem = 16MB        # min 1MB
#max_stack_depth = 2MB            # min 100kB

# - Free Space Map -

max_fsm_pages = 204800        # min max_fsm_relations*16, 6 bytes each
                    # (change requires restart)
#max_fsm_relations = 1000        # min 100, ~70 bytes each
                    # (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000        # min 25
                    # (change requires restart)
#shared_preload_libraries = ''        # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0            # 0-1000 milliseconds
#vacuum_cost_page_hit = 1        # 0-10000 credits
#vacuum_cost_page_miss = 10        # 0-10000 credits
#vacuum_cost_page_dirty = 20        # 0-10000 credits
#vacuum_cost_limit = 200        # 0-10000 credits

# - Background writer -

#bgwriter_delay = 200ms            # 10-10000ms between rounds
#bgwriter_lru_percent = 1.0        # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5        # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333        # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5        # 0-1000 buffers max written/round

Noch mal zum besseren Verständnis:
Wenn ein Vacuum-Lauf immer nur eine Datenbank bearbeitet, wie ist dann die Reihenfolge, wie die Datenbank bearbeitet werden? Geht das immer Reihum (Round Robin) oder ist das noch von anderen Faktoren wie Last etc. abhängig.

Vielen Dank

Das müsste in etwa so aussehen:

LOG: autovacuum: processing database “test”



Noch mal zum besseren Verständnis:
Wenn ein Vacuum-Lauf immer nur eine Datenbank bearbeitet, wie ist dann die Reihenfolge, wie die Datenbank bearbeitet werden? Geht das immer Reihum (Round Robin) oder ist das noch von anderen Faktoren wie Last etc. abhängig.

Round Robin. Wenn in einer Datenbank nichts los ist, ist das Autovacuum halt schneller fertig. Dann wird $naptime gewartet, danach kommt die nächste Datenbank dran.

Hallo,

im Logfile ist leider nichts zu finden. Wir haben aber folgende Entdeckung gemacht:
Das Programm, das die eine Tabelle ständig updated hat immer ein exclusive Lock auf der Datenbank. (Sieht man im Statusfenster im PG-Admin) Kann es sein, dass damit das Auto-Vacuum behindert wird, bzw. gar dieses nicht erst anläuft ?


Danke noch mal für die Hilfe

Ja, das dürfte die Ursache sein.

Andreas

Anlaufen wird das VACUUM schon … nur kann es diese Tabelle nicht bearbeiten.

Gegenfrage: was bringt mir eine Datenbank, wenn die Tabelle ständig gelockt ist? Irgendwie ist das Verhalten der Applikation nicht ganz logisch. Noch dazu schreibst du in deinem ersten Beitrag, das es alle 15 Minuten Einträge gibt. Dazwischen muss es doch keinen exklusiven Lock geben, oder?

Hi zusammen,

Sorry, ich habe mich vermutlich zu ungenau ausgedrückt:

Die Tabelle wird nicht absichtlich exclusiv gelockt. (wir entwicklen hier mit Gupta Team Developer, falls den irgendjemand kennt.). Der Lock kam dadurch zustande, dass im Login-Dialog ein Testconnect gemacht und dieser beim Erfolg gleich wieder disconnected wurde, allerdings ohne ein Commit zu machen. (Habe ich inzwischen geändert, jetzt wird vor jedem Disconnect automatisch ein Commit gemacht)
Es wurde wurde also keinerlei Aktion mit dieser Connection durchgeführt, noch viel weniger irgendetwas mit der angesprochenen Tabelle.
Alle anderen Programme konnten problemlos arbeiten und auch auf diese Tabelle zugreifen und ändern, wie gesagt nur der Statusdialog im PG-Admin hat den Lock noch angzeigt. Alles andere lief normal.

Gruss Jürgen

Tabelle exklusiv gelockt, aber andere Programme konnten damit arbeiten?

I’m lost …

Anscheinend schreib ich wirklich zu ungenau…

In der Statusanzeige steht nur ein Datensatz:
PID: 4711
Datenbank:
Relation:
Benutzer: User
TX: 7504982
Modus: ExclusivLock
Gewährt: Ja
Start:
Abfrage: in Transaction

Dort steht weder ein Tabelle noch eine Datenbank.
Wie gesagt, was ich mache, um zu testen, ob der Benutzer existiert oder nicht, bzw. ob sein Password stimmt ist folgendes:

If Connect() …
; ok, er kann sich connecten
Disconnect()

Ich mache nichts mit irgendeiner Tabelle. In der neuen Version sieht das jetzt so aus:

If Connect() …
; ok, er kann sich connecten
Commit()
Disconnect()

Und damit ist auch dieser seltsame Lock weg.

Sorry für die Ungenauigkeiten

Gut … funktioniert jetzt Auto Vacuum?

Wenn nicht, es taucht nichts im Log auf, oder?
Wie sehen die Configoptionen in postgresql.conf aus?

Ob das Autovacuum jetzt geht weiss ich noch nicht, da der Server nicht hier bei uns steht, sondern bei unserem Kunden, werde ich bis Donnerstag erfahren.

Die Config-Optionen habe ich schon in meinem ersten Post eingestellt.

Grüße

Hallo!
leider hast Du noch nicht die komplette conf gepostet.
Damit autovacuum funktioniert müssen (zumindest in PG 8.1) auch die Parameter
stats_start_collector
und
stats_row_level
auf on stehen!
Suche im log nach “misconfiguration”.

Gruß, Robert

HI

vielen Dank noch mal für die Antworten. Inzwischen scheint das Autovacuum zu funktionieren. (Die Parameter stats_start_collector und stats_row_level waren schon gesetzt).
Ich nehme an hier war wirklich der Lock auf der Datenbank wie oben beschrieben schuld.
Anscheind hat die Laufzeitumgebung die SQL-Connection aus Performancegründen nicht komplett freigegeben. Gupta hat hier immer das Problem, dass die erste Connection sehr lange dauert, während dann die weiteren recht schnell gehen und deswegen anscheinend, wenn schon mal eine Connection da ist, diese intern auch gehalten wird.

Noch mal danke für die Hilfe.

(Wie sich die Indices jetzt entwicklen (siehe anderen Tread in allgemein) werden ich im Auge behalten und berichten)