PostgreSQL Server müllt sich mit der Zeit zu

[Karsten72]

Hallo Community,
habt ihr auch schon Mal folgende Erfahrung gemacht:
Auf einer Linux-Maschine läuft ein PostgreSQL - Server. Mit der Zeit wird der für den PostgreSQL - Server benötigte HD- Speicher natürlich größer.
Jetzt macht man einen Dump der DB, setzt das System neu auf, spielt den Dump zurück und der benötigte HD- Speicher für den PostgreSQL - Server ist signifikant kleiner.
Vielen Dank im Voraus.

[castorp]

Klingt danach, als würde bei Dir autovacuum nicht funktionieren. Meistens sind lang laufende Transaktion (Verbindungen im Status “idle in transaction”) der Grund dafür. Die wiederum werden meistens durch schlecht geschriebene Anwendungen verursacht. Dem kann man z.B. durch eine niedrigen Wert für idle_in_transaction_session_timeout begegnen (z.B. 15min)

Ein eher seltener Grund sind Anwendungen die einfach so sehr die Datenbank belasten, dass autovacuum nicht mehr hinterherkommt. Wir reden hier aber über dauerhafte Lasten von mehreren hundert oder sogar tausend Transaktionen pro Sekunde ohne Unterbrechung. In solchen Fällen hilft es typischerweise autovacuum für die betroffenen Tabellen aggressiver zu machen (geringere Schwellenwerte, höhere Grenzwerte für die maximale Arbeit pro Durchlauf usw.)

Du kannst ja mal prüfen ob ein vacuum full den Speicher entsprechend verringert (Achtung: das blockiert jeden Zugriff auf die Tabelle). Wenn ja, dann ist es Zeit entweder die autovacuum Konfiguration anzupassen, oder die Anwendung zu prüfen ob die richtig arbeitet.

[akretschmer]

siehe @castorp , klingt als würde Autovacuum nicht oder nicht richtig laufen.Hast Du es evtl. in der Config ausgeschaltet?

Folgende Abfrage gibt Dir einen Überblich, die Spalte ‘expect_av’ sollte überwiegende leer sein, dead_tup keine sehr hohen Werte haben:

WITH tmp AS (
  SELECT
    psut.relname,
    coalesce((select option_value::numeric from pg_options_to_table(pg_class.reloptions) where option_name = 'autovacuum_vacuum_scale_factor'),current_setting('autovacuum_vacuum_scale_factor')::numeric) as vacuum_scale_factor,
    coalesce((select option_value::int from pg_options_to_table(pg_class.reloptions) where option_name = 'autovacuum_vacuum_threshold'),current_setting('autovacuum_vacuum_threshold')::int) as vacuum_threshold,
    to_char(psut.last_vacuum, 'YYYY-MM-DD HH24:MI') as last_vacuum,
    to_char(psut.last_autovacuum, 'YYYY-MM-DD HH24:MI') as last_autovacuum,
    pg_class.reltuples AS n_tup,
    psut.n_dead_tup AS dead_tup
  FROM pg_stat_user_tables psut
    JOIN pg_class on psut.relid = pg_class.oid
)

SELECT 
  relname,
  vacuum_scale_factor,
  vacuum_threshold,
  last_vacuum,
  last_autovacuum,
  to_char(n_tup, '9G999G999G999') AS n_tup,
  to_char(dead_tup, '9G999G999G999') AS dead_tup,
  to_char(vacuum_threshold + vacuum_scale_factor * n_tup::bigint,'9G999G999G999') AS av_threshold,
  CASE
    WHEN vacuum_threshold + vacuum_scale_factor * n_tup < dead_tup
      THEN '*'
      ELSE ''
    END AS expect_av
FROM
  tmp
ORDER BY
  tmp.dead_tup DESC;

[Karsten72]

Vielen Dank für die wertvollen Hinweise. Ich werde dies umsetzen.