Author

Peter

Inhalt

1 Vacuum/Autovacuum

../../_images/kasten-mit-flaschen.jpg

« Übersicht: Manage PostgreSQL

Dieser Befehl oder Konfigurationsparameter gibt ungenutzten Speicherplatz für eine Wiederverwendung frei.

Konfiguriert wird er in postgresql.conf:

Vacuum verkleinert die Tabellen nicht automatisch. Dafür muss ein VACUUM FULL ausgeführt werden. Damit werden die Indices neu erstellt und das kann bei großen Tabellen etwas dauern.

autovacuum = on
track_counts = on

1.1 Die wichtigsten Parameter

Parameter

Anmerkung

autovacuum

autovacuumworkmem

autovacuummaxworkers

autovacuumnaptime

autovacuumanalyzescalefactor

autovacuumanalyzethreshold

autovacuumvacuumcostdelay

autovacuumvacuumcostlimit

autovacuumvacuumscalefactor

autovacuumvacuumthreshold

autovacuumfreezemaxage

autovacuummultixactfreezemaxage

logautovacuumminduration

vacuumcostpagedirty

vacuumcostpagehit

vacuumcostpagemiss

vacuumcostpagedirty

vacuumfreezeminage

vacuumfreezetableage

vacuummultixactfreezeminage

vacuummultixactfreezetableage

1.2 Konfiguration auf Tabellenebene

ALTER TABLE meine_tabelle SET (storage_parameter = value);

Parmaeter-Liste:

autovacuumenabled

autovacuumvacuumcostdelay

autovacuumvacuumcostlimit

autovacuumvacuumscalefactor

autovacuumvacuumthreshold

autovacuumfreezeminage

autovacuumfreezemaxage

autovacuumfreezetableage

autovacuummultixactfreezeminage

autovacuummultixactfreezemaxage

autovacuummultixactfreezetableage

autovacuumanalyzescalefactor

autovacuumanalyzethreshold

logautovacuumminduration

1.3 Optimierungen deaktivieren

Für große Tabellen ist es vielleicht hilfreich das AUTOVACCUM zu deaktivieren:

ALTER TABLE grosse_tabelle SET (autovacuum_enabled = off);

1.4 Behandlung von TOAST-Tabellen

TOAST = Abkürzung für oversize attribute storage technique

Damit werden Tabellen verwaltet, die das Limit eines Speicherblocks (Standard: 8Kbyte) überschreiten.

ALTER TABLE pgbench_accounts
SET ( toast.autovacuum_enabled = off);
SELECT n.nspname, c.relname,
                  pg_catalog.array_to_string(c.reloptions || array(
                  select 'toast.' ||
x from pg_catalog.unnest(tc.reloptions) x),', ')
                  as relopts
FROM pg_catalog.pg_class c
LEFT JOIN
pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
JOIN
pg_namespace n ON c.relnamespace = n.oid
WHERE c.relkind = 'r'
AND nspname NOT IN ('pg_catalog', 'information_schema');

1.5 Umschalten der Autovacuum-Parameter

Voraussetzung: autovacucum ist als include in der postgres.conf eingetragen:

autovacuum = on
autovacuum_max_workers = 3
include 'autovacuum.conf'

HINWEISE zu den Parametern:

  • autovacuummaxworkersgrößer 2 gesetzt werden

  • Zu hoch gesetzt, ist nicht unbedingt nützlich

  • vacuumcostdelaynicht zu hoch setzen.

  • maintenanceworkmemsollte über 1GB gesetzt werden.

  • Unter welcher PID läuft der VACUUM-Prozess – pgstatactivityview

autovacuum.conf.tag für die stark beschäftigte Datenbank

autovacuum_analyze_scale_factor = 0.1
autovacuum_analyze_threshold = 50
autovacuum_vacuum_cost_delay = 30
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_scale_factor = 0.2
autovacuum_vacuum_threshold = 50

autovacuum.conf.nacht für die etwas ruhigere Zeit

autovacuum_analyze_scale_factor = 0.05
autovacuum_analyze_threshold = 50
autovacuum_vacuum_cost_delay = 10
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_scale_factor = 0.1
autovacuum_vacuum_threshold = 50

Umschalten für die Nacht (entsprechend für den Tag), das kann durch einen Cron-Job automatisiert werden.

ln -sf autovacuum.conf.nacht autovacuum.conf
$ pg_ctl -D datadir reload

1.6 Defekte pages beseitigen

SET vacuum_freeze_table_age = 0;
VACUUM;

Siehe auch: pageinspect - ein Zusatzpaket zur Analyse

1.7 Ist VACUUM für eine Tabelle notwendig?

CREATE OR REPLACE VIEW av_needed AS
SELECT N.nspname, C.relname
, pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
, pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
, pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
, CASE WHEN pg_stat_get_tuples_updated(C.oid) > 0
       THEN pg_stat_get_tuples_hot_updated(C.oid)::real
      / pg_stat_get_tuples_updated(C.oid)
       END
  AS HOT_update_ratio
, pg_stat_get_live_tuples(C.oid) AS n_live_tup
, pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
, C.reltuples AS reltuples
, round( current_setting('autovacuum_vacuum_threshold')::integer
       + current_setting('autovacuum_vacuum_scale_factor')::numeric
       * C.reltuples)
  AS av_threshold
, date_trunc('minute',
    greatest(pg_stat_get_last_vacuum_time(C.oid),
         pg_stat_get_last_autovacuum_time(C.oid)))
  AS last_vacuum
, date_trunc('minute',
    greatest(pg_stat_get_last_analyze_time(C.oid),
         pg_stat_get_last_analyze_time(C.oid)))
  AS last_analyze
, pg_stat_get_dead_tuples(C.oid) >
  round( current_setting('autovacuum_vacuum_threshold')::integer
       + current_setting('autovacuum_vacuum_scale_factor')::numeric
       * C.reltuples)
  AS av_needed
, CASE WHEN reltuples > 0
       THEN round(100.0 * pg_stat_get_dead_tuples(C.oid) / reltuples)
       ELSE 0 END
  AS pct_dead
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.relkind IN ('r', 't')
  AND N.nspname NOT IN ('pg_catalog', 'information_schema')
  AND N.nspname NOT LIKE 'pg_toast%'
ORDER BY av_needed DESC, n_dead_tup DESC;

Danach die Abfrage für eine Tabelle:

SELECT *
FROM av_needed
WHERE nspname = 'public'
AND relname = 'pgbench_accounts';

1.9 Welche PID hat der VACCUM-Prozess?

SELECT pid, application_name, backend_type FROM pg_stat_activity;

1.10 Informationen zum Prozess

SELECT pid, application_name, backend_type FROM pg_stat_activity;

1.11 VACUUM für alle DB ausführen

vacuumdb --jobs=4 --all -U postgres