- Author
Peter
Inhalt
1 Vacuum/Autovacuum¶

« Ü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