PostgreSQL autovacuum pielāgošana: kā apturēt tabulu uzpūšanos

PostgreSQL autovacuum pielāgošana ir viens no tiem uzdevumiem, par kuriem neviens nedomā, līdz 20 GB tabula pārvēršas 200 GB tabulā, indeksu skenēšana bez redzama iemesla palēninās vai žurnālā parādās brīdinājums, ka datubāze "must be vacuumed within N transactions". Noklusējuma vērtības ir apzināti piesardzīgas: tās der mazām un vidējām tabulām, bet klusi neizdodas lielām tabulām ar daudz rakstīšanas. Šajā rakstā skaidrojam, kā autovacuum izlemj, kad darboties, kuri iestatījumi patiešām ir svarīgi un kā to pielāgot, nekaitējot produkcijai.

Kāpēc PostgreSQL vispār vajag VACUUM

PostgreSQL īsteno MVCC, saglabājot vecās rindu versijas. UPDATE ieraksta jaunu rindas versiju, bet DELETE veco tikai atzīmē kā dzēstu; neviens no tiem to uzreiz neizņem, jo citām transakcijām to joprojām var vajadzēt redzēt. Šīs mirušās rindu versijas (dead tuples) paliek tabulā un tās indeksos, līdz VACUUM atzīmē vietu kā atkārtoti izmantojamu. PostgreSQL dokumentācijā minēti četri vacuum uzdevumi:

• atbrīvot mirušo rindu aizņemto vietu, lai to varētu izmantot jaunas rindas;
• atjaunināt plānotāja statistiku (ANALYZE daļa);
• atjaunināt redzamības karti (visibility map), kas ļauj veikt index-only skenēšanu;
• sasaldēt vecās rindas, lai pasargātu no transakciju ID pārpildes (wraparound).

Ja vacuum darbojas pārāk reti, mirušās rindas uzkrājas ātrāk, nekā vieta tiek atkārtoti izmantota. Tabula un tās indeksi aug (uzpūšanās jeb bloat), tiem pašiem datiem jānolasa vairāk lapu, kešatmiņa kļūst mazāk efektīva un vaicājumi pakāpeniski palēninās — tāpēc uzpūšanās bieži vispirms izpaužas kā "pēdējos mēnešos datubāze kļuvusi lēnāka".

Kā autovacuum izlemj, kad tīrīt tabulu

Autovacuum palaidējs katrā datubāzē pamostas ik pēc autovacuum_naptime (noklusējums 1 minūte) un palaiž līdz autovacuum_max_workers darbiniekiem (noklusējums 3). Tabula tiek tīrīta, kad mirušo rindu skaits pārsniedz slieksni:

vacuum threshold = autovacuum_vacuum_threshold
                 + autovacuum_vacuum_scale_factor * reltuples

Ar noklusējuma vērtībām — bāze 50 rindas un scale factor 0,2 — tabula tiek tīrīta, kad aptuveni 20% tās rindu ir kļuvušas mirušas. 10 000 rindu tabulai tas ir 2050 rindas, un tas ir labi. 500 miljonu rindu tabulai tas ir ap 100 miljoniem mirušo rindu, pirms autovacuum vispār sāk darbu, un tad darbiniekam viss uzkrājums jāapstrādā vienā piegājienā. Tieši šis viens scale factor ir lielākās daļas mūsu redzēto uzpūšanās problēmu cēlonis.

Ir vēl divi palaišanas nosacījumi. Kopš PostgreSQL 13 vacuum var izraisīt arī tikai ievietošanas (autovacuum_vacuum_insert_threshold, noklusējums 1000, un autovacuum_vacuum_insert_scale_factor, noklusējums 0,2), kas uztur tikai papildināmās tabulas sasaldētas un to redzamības karti aktuālu. Savukārt jebkura tabula, kuras vecākais nesasaldētais transakcijas ID ir vecāks par autovacuum_freeze_max_age (noklusējums 200 miljoni transakciju), saņem pret-wraparound vacuum neatkarīgi no sliekšņiem — pat ja autovacuum ir izslēgts.

1. solis: atrodiet tabulas, ar kurām autovacuum netiek galā

Sāciet ar statistikas skatiem, nevis minējumiem:

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Tabulas ar miljoniem mirušo rindu, augstu mirušo rindu īpatsvaru vai last_autovacuum pirms vairākām dienām ir jūsu kandidāti. Precīzam konkrētas tabulas attēlam paplašinājums pgstattuple precīzi parāda dzīvo, mirušo un brīvo vietu (tas nolasa visu tabulu, tāpēc lielām tabulām palaidiet to ārpus slodzes laika). Ieslēdziet arī log_autovacuum_min_duration (kopš PostgreSQL 15 noklusējums ir 10 minūtes; pielāgošanas laikā noderīgāks ir, piemēram, 1s), lai žurnālā redzētu, cik ilgi katrs palaidiens ilga un cik daudz tas izņēma.

Mirušajām rindām un vecākās transakcijas vecumam pastāvīgi jābūt jūsu monitoringa paneļos; par tiem rakstām sadaļā datubāzu rādītāji, kas paredz incidentus.

2. solis: pielāgojiet lielās tabulas atsevišķi

Globālā scale factor samazināšana ietekmē visas tabulas. Tīrāka pieeja ir iestatīt glabāšanas parametrus tām dažām lielajām, noslogotajām tabulām, kurām tas vajadzīgs:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold    = 10000,
  autovacuum_analyze_scale_factor = 0.02
);

Tagad tabula tiek tīrīta, kad mainījies ap 1% rindu, nevis 20%, tāpēc katrs palaidiens ir mazāks, ātrāks un mazāk traucējošs. Dažas komandas iet tālāk un iestata scale factor uz 0 ar fiksētu slieksni (piemēram, 100 000 mirušo rindu), kas padara vacuum biežumu paredzamu neatkarīgi no tabulas izmēra. To pašu dariet ar ANALYZE: novecojusi statistika lielā tabulā ir klasisks pēkšņu plānu izmaiņu cēlonis.

3. solis: ļaujiet autovacuum strādāt pietiekami ātri

Autovacuum tiek apzināti ierobežots. Darbinieki uzkrāj "izmaksas" par nolasītajām un mainītajām lapām, un, sasniedzot cost limitu, tie aizmieg uz autovacuum_vacuum_cost_delay (noklusējums 2 ms). Limits pēc noklusējuma ir vacuum_cost_limit = 200, un tas tiek dalīts starp visiem strādājošajiem darbiniekiem — pievienojot darbiniekus, nepalielinot limitu, katrs no tiem vienkārši strādā lēnāk.

• Palieliniet autovacuum_vacuum_cost_limit (piemēram, līdz 1000–2000) serveros ar SSD/NVMe krātuvi. Noklusējums izvēlēts daudz lēnākiem diskiem.
• Pievienojiet darbiniekus tikai tad, ja vienlaikus vacuum vajag daudzām tabulām. Vairāk darbinieku palīdz ar plašumu, nevis ar vienu milzīgu tabulu.
• Dodiet vacuum atmiņu. maintenance_work_mem (vai autovacuum_work_mem) ierobežo, cik mirušo rindu atsauču darbinieks var turēt, pirms tam atkal jāskenē indeksi.
• Mainiet vienu lietu vienlaikus un sekojiet I/O latentumam: vacuum konkurē ar jūsu vaicājumiem par diska joslas platumu.

PostgreSQL 18 jaunais iestatījums autovacuum_worker_slots (tiek piemērots servera startā) rezervē darbinieku vietas, un pēc tam autovacuum_max_workers var mainīt darbības laikā līdz šim skaitam bez restartēšanas — ērti incidenta laikā. 18. versija arī pievieno total_autovacuum_time skatam pg_stat_all_tables un izvēles cost-delay atskaites ar track_cost_delay_timing, kas parāda, cik daudz laika vacuum pavada, guļot ierobežojuma dēļ (PostgreSQL 18 laidiena piezīmes).

4. solis: novērsiet to, kas bloķē vacuum

Pielāgošana nepalīdz, ja vacuum nav atļauts izņemt mirušās rindas. Rindu var izņemt tikai tad, kad neviena transakcija to vairs nevar redzēt, tāpēc viss, kas aiztur vecāko redzamo momentuzņēmumu (xmin horizontu), bloķē tīrīšanu visā datubāzē:

1. Ilgstošas vai idle-in-transaction sesijas. Pārbaudiet pg_stat_activity, meklējot vecu xact_start un stāvokli idle in transaction; iestatiet idle_in_transaction_session_timeout, lai aizmirsta sesija nevarētu stundām aizturēt horizontu.
2. Pamesti replikācijas sloti. Slots, kura patērētāja vairs nav, saglabā savu xmin skatā pg_replication_slots un aiztur WAL. Dzēsiet slotus, kurus neviens neizmanto.
3. Replikas ar hot_standby_feedback = on, kurās darbojas ilgi atskaišu vaicājumi — tās aiztur vacuum primārajā serverī.
4. Aizmirstas sagatavotās transakcijas skatā pg_prepared_xacts.

Ja žurnāls rāda, ka autovacuum darbojas bieži, bet n_dead_tup gandrīz nemainās, gandrīz vienmēr iemesls ir kāds no šiem. Par lēniem vaicājumiem, ko izraisa uzpūšanās, rakstām mūsu PostgreSQL lēno vaicājumu optimizācijas kontrolsarakstā.

Transakciju ID pārpilde: kļūme, līdz kurai nedrīkst nonākt

Transakciju ID ir 32 bitu, tāpēc katra tabula jātīra (jāsasaldē) vismaz reizi aptuveni divos miljardos transakciju. PostgreSQL sevi aizsargā: kad datubāzei līdz pārpildei atlikuši 40 miljoni transakciju, tā sāk rakstīt brīdinājumus žurnālā, bet, kad atlikuši mazāk nekā trīs miljoni, tā atsakās piešķirt jaunus transakciju ID — līdz vacuum pabeigšanai darbojas tikai lasīšanas transakcijas. Uzraugiet vecumu tieši:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Vērtības, kas turpina augt krietni virs autovacuum_freeze_max_age, nozīmē, ka pret-wraparound vacuum netiek pabeigti — parasti iepriekš minēto bloķētāju vai pārāk stingra ierobežojuma dēļ. Iestatiet brīdinājumus krietni pirms PostgreSQL brīdinājumiem; atkopšanās no tikai lasīšanas stāvokļa vairāku terabaitu datubāzē var ilgt stundas.

Ko darīt ar tabulām, kas jau ir uzpūstas?

Parasts VACUUM padara vietu atkārtoti izmantojamu, bet failus samazina reti. VACUUM FULL pārraksta tabulu un atdod vietu operētājsistēmai, taču visu darbības laiku tur ACCESS EXCLUSIVE slēdzeni un prasa vietu pilnai kopijai — noslogotai tabulai tas nozīmē dīkstāvi. Ierastā pieeja produkcijā ir tiešsaistes pārbūve ar paplašinājumu, piemēram, pg_repack, bet indeksiem — REINDEX CONCURRENTLY. Pārbūvējiet vienreiz un pēc tam izlabojiet autovacuum iestatījumus, lai tabula atkal neuzpūstos.