Author

Peter

Inhalt

1 Vacuum/Autovacuum

1 Vacuum/Autovacuum ¶

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
autovacuum_work_mem
autovacuum_max_workers
autovacuum_naptime
autovacuum_analyze_scale_factor
autovacuum_analyze_threshold
autovacuum_vacuum_cost_delay
autovacuum_vacuum_cost_limit
autovacuum_vacuum_scale_factor
autovacuum_vacuum_threshold
autovacuum_freeze_max_age
autovacuum_multixact_freeze_max_age
log_autovacuum_min_duration
vacuum_cost_page_dirty
vacuum_cost_page_hit
vacuum_cost_page_miss
vacuum_cost_page_dirty
vacuum_freeze_min_age
vacuum_freeze_table_age
vacuum_multixact_freeze_min_age
vacuum_multixact_freeze_table_age

1.2 Konfiguration auf Tabellenebene ¶

ALTER TABLE meine_tabelle SET (storage_parameter = value);

Parmaeter-Liste:

autovacuum_enabled

autovacuum_vacuum_cost_delay

autovacuum_vacuum_cost_limit

autovacuum_vacuum_scale_factor

autovacuum_vacuum_threshold

autovacuum_freeze_min_age

autovacuum_freeze_max_age

autovacuum_freeze_table_age

autovacuum_multixact_freeze_min_age

autovacuum_multixact_freeze_max_age

autovacuum_multixact_freeze_table_age

autovacuum_analyze_scale_factor

autovacuum_analyze_threshold

log_autovacuum_min_duration

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:

autovacuum_max_workersgrößer 2 gesetzt werden
Zu hoch gesetzt, ist nicht unbedingt nützlich
vacuum_cost_delaynicht zu hoch setzen.
maintenance_work_memsollte über 1GB gesetzt werden.
Unter welcher PID läuft der VACUUM-Prozess – pg_stat_activityview

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.8 Informationen über die VACCUM-Prozesse ¶

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