PostgreSQL "out of shared memory": kā salabot max_locks_per_transaction

Šī ir viena no mulsinošākajām PostgreSQL kļūdām. Serverim ir daudz RAM, shared_buffers ir kārtībā, un tomēr vaicājums beidzas ar "out of shared memory" un mājienu palielināt max_locks_per_transaction. Kļūda nav par atmiņu kopumā. Tā nozīmē, ka fiksēta izmēra tabula, kurā PostgreSQL uzskaita slēdzenes, ir pilna. Kad to zināt, labojums parasti ir ātrs, un īstais cēlonis parasti ir viens no četriem.

ERROR:  out of shared memory
HINT:  You might need to increase "max_locks_per_transaction".

Kas ir slēdžu tabula

Katra tabula, indekss, sekvence vai cits objekts, ko transakcija skar, saņem slēdzeni, un lielākā daļa šo slēdzeņu tiek turētas līdz transakcijas beigām. PostgreSQL tās glabā koplietotā slēdžu tabulā, kuras izmērs tiek noteikts servera startā. Dokumentācijā teikts, ka tajā ir vieta max_locks_per_transaction objektiem uz katru servera procesu vai sagatavoto transakciju. Noklusējums ir 64.

Praksē svarīgas trīs detaļas:

• Tas ir kopīgs fonds, nevis sesijas kvota. Dokumentācijā teikts, ka iestatījums ierobežo vidējo objektu slēdzeņu skaitu uz transakciju; viena transakcija var bloķēt daudz vairāk, ja vien viss ietilpst. Tāpēc viena sesija ar 20 000 slēdzenēm var likt pilnīgi nevainīgam vaicājumam citā sesijā beigties ar šo kļūdu.
• Tiek skaitīti objekti, nevis rindas. Rindu slēdzenes šeit netiek glabātas, un to skaits nav ierobežots. Miljona rindu bloķēšana vienā tabulā šo kļūdu neizraisīs. Desmit tūkstošu tabulu skaršana izraisīs.
• Izmēra formula. Pirmkodā tabulā ir max_locks_per_transaction × (MaxBackends + max_prepared_transactions) ieraksti, kur MaxBackends ir max_connections plus autovacuum darbinieki, fona darbinieki un WAL sūtītāji. Ar noklusējuma iestatījumiem tas ir ap desmit tūkstošiem vietu visam serverim, un tas ir mazāk, nekā daudzi gaida.

Dažas slēdzenes koplietoto tabulu neizmanto vispār. Vājās relāciju slēdzenes (tādas, kādas ņem parasti SELECT, INSERT, UPDATE un DELETE) var nonākt nelielā katra procesa fast-path masīvā. PostgreSQL 17 un vecākās versijās šim masīvam bija 16 vietas. PostgreSQL 18 uzlaboja slēgšanu vaicājumiem, kas skar daudzas relācijas: fast-path vietu skaits tagad mērogojas ar max_locks_per_transaction, un ar noklusējuma iestatījumu tās ir 64 uz procesu. Tas padara problēmu 18. versijā mazāk ticamu, bet nenovērš to: kad fast-path vietas ir izlietotas, nākamās slēdzenes nonāk koplietotajā tabulā.

1. solis: atrodiet, kas tur slēdzenes

Kamēr problēma notiek, paskatieties pg_locks, sagrupējot pēc procesa:

SELECT l.pid,
       count(*)                               AS locks,
       count(*) FILTER (WHERE NOT l.fastpath) AS in_shared_table,
       a.state,
       now() - a.xact_start                   AS xact_age,
       left(a.query, 80)                      AS query
FROM pg_locks l
LEFT JOIN pg_stat_activity a USING (pid)
GROUP BY l.pid, a.state, a.xact_start, a.query
ORDER BY locks DESC
LIMIT 10;

Kolonna fastpath parāda, vai slēdzene iegūta caur fast-path; koplietotajā tabulā tiek skaitītas tās, kur vērtība ir false. Parasti šī saraksta augšgalā nav nekā īpaša: desmitiem slēdzeņu uz sesiju. Incidenta laikā parasti redzama viena vai divas sesijas ar tūkstošiem. Pārbaudiet, kas tā par sesiju un cik veca ir tās transakcija.

Lai redzētu, kāda veida objektus tā ir bloķējusi:

SELECT l.locktype, c.relkind, l.mode, count(*)
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE l.pid = 12345            -- pid no iepriekšējā vaicājuma
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

Garš relkind = 'r' un 'i' saraksts zem viena vecāka nosaukuma norāda uz particijām un to indeksiem. AccessShareLock režīma slēdzenes uz visu datubāzē norāda uz dump vai skriptu, kas iet cauri visam katalogam.

Salīdziniet kopējo izmantojumu ar ietilpību:

-- aptuvenais koplietotās slēdžu tabulas izmērs (īstais ir nedaudz lielāks,
-- jo vietas saņem arī fona procesi)
SELECT current_setting('max_locks_per_transaction')::int
     * (current_setting('max_connections')::int
        + current_setting('max_prepared_transactions')::int) AS approx_slots,
       (SELECT count(*) FROM pg_locks WHERE NOT fastpath)    AS used_now;

2. solis: apturiet kaitējumu

Ja vainīgā sesija ir skaidra un to ir droši apturēt, atceliet tās vaicājumu un sesiju pārtrauciet tikai tad, ja atcelšana nepalīdz:

SELECT pg_cancel_backend(12345);     -- atcelt pašreizējo vaicājumu
SELECT pg_terminate_backend(12345);  -- beigt sesiju, ja ar atcelšanu nepietiek

Tās slēdzenes tiek atbrīvotas, kad beidzas tās transakcija, un pārējā sistēma atkopjas uzreiz. Ja šī sesija ir migrācija vai rezerves kopija, kas jums tiešām vajadzīga, nepārtrauciet to akli. Tā vietā plānojiet restartēšanu ar lielāku limitu (4. solis) vai sadaliet darbu (3. solis).

Biežākie cēloņi

1. Particionētas tabulas ar daudzām particijām. Vaicājums uz vecāktabulu, kas nevar atmest particijas, bloķē katru particiju un tās indeksus līdz transakcijas beigām. Particionēšanas dokumentācija piemin mazāk acīmredzamu slazdu: particijas, kas atmestas vaicājuma plāna inicializācijas laikā, joprojām tiek bloķētas izpildes sākumā. Plānotājs hierarhijas ar "līdz dažiem tūkstošiem particiju" apstrādā diezgan labi, raksta dokumentācijā, ja tipiski vaicājumi var atmest visas, izņemot dažas. Ikdienas particijas, kas glabātas piecus gadus, katra ar četriem indeksiem, jau ir vairāk nekā 9000 objektu, aptuveni visas noklusējuma slēdžu tabulas lielumā.
2. pg_dump datubāzei ar daudzām tabulām. pg_dump darba sākumā iegūst koplietotas slēdzenes uz tabulām, ko tas izgāž (saskaņā ar pg_dump dokumentāciju opcija --lock-wait-timeout pastāv tieši šim solim), un patur tās, kamēr dump darbojas; sadaļā par paralēlo dump paskaidrots, ka vadošais process tur šīs koplietotās slēdzenes, lai neviens nevarētu dzēst tabulas dump vidū. Tūkstošiem tabulu vai particiju nozīmē tūkstošiem slēdzeņu vienai sesijai visa dump laikā. Ja paļaujaties uz loģiskajiem dump, mūsu raksts par rezerves kopiju un atjaunošanas testēšanu skaidro, kāpēc ir vērts pārbaudīt, vai tie joprojām pabeidzas.
3. Lielas migrācijas vienā transakcijā. Skripts, kas vienā BEGIN ... COMMIT izveido, maina vai dzēš simtiem tabulu vai particiju, tur katru no šīm slēdzenēm līdz commit. Tāpat dara atjaunošana, kas visu izpilda vienā transakcijā.
4. Ilgas transakcijas, kas skar daudz. Atskaišu sesija, kas no rīta atvēra transakciju un kopš tā laika vaicājusi simtiem tabulu, tur visas šīs slēdzenes. Mūsu ceļvedis par idle in transaction sesijām parāda, kā tās atrast un ierobežot.

3. solis: novērsiet cēloni

• Particijas: pārliecinieties, ka biežie vaicājumi filtrē pēc particiju atslēgas, lai plānotājs varētu atmest particijas, un pārbaudiet to ar EXPLAIN. Atvienojiet vai dzēsiet particijas, kuras vairs nevaicājat. Ja dizainam vajag desmitiem tūkstošu particiju, pārskatiet to; dokumentācija skaidri brīdina, ka pārāk daudz particiju palielina plānošanas laiku un atmiņas patēriņu.
• Migrācijas: apstipriniet pa daļām, piemēram, vienu transakciju uz particiju vai tabulu grupu, nevis vienu milzīgu transakciju.
• Dump: ļoti lielām shēmām apsveriet shēmu vai tabulu grupu izgāšanu atsevišķi vai fiziskās rezerves kopijas, kas nebloķē katru tabulu.
• Ilgas transakcijas: iestatiet idle_in_transaction_session_timeout un turiet atskaišu sesijas autocommit režīmā vai īsās transakcijās.

4. solis: palieliniet limitu pareizā secībā

max_locks_per_transaction palielināšana ir pilnvērtīgs risinājums, nevis tikai apiešana, ja jums tiešām ir daudz particiju vai tabulu. Tā stājas spēkā tikai servera startā:

ALTER SYSTEM SET max_locks_per_transaction = 256;
-- stājas spēkā tikai pēc restartēšanas

Koplietotā atmiņa slēdžu tabulai aug proporcionāli, tāpēc vērtība nav jāpadara milzīga. Noklusējuma divkāršošana vai četrkāršošana ir saprātīgs pirmais solis; pēc tam pārbaudiet pg_locks smagāko darbu laikā.

Monitorings, lai to pamanītu laikus

Pievienojiet monitoringam SELECT count(*) FROM pg_locks WHERE NOT fastpath un iestatiet brīdinājumu pie daļas no iepriekš aprēķinātās ietilpības. Sekojiet arī lielākajam slēdzeņu skaitam vienam procesam. Lēns pieaugums parasti seko particiju skaitam; pēkšņs lēciens parasti sakrīt ar nakts dump vai izlaidumu.