PostgreSQL "canceling statement due to statement timeout": cēloņi un risinājumi

ERROR: canceling statement due to statement timeout ir PostgreSQL ziņojums, ka vaicājums izpildījās ilgāk, nekā atļauj statement_timeout, un tika atcelts. Datubāzē nekas nav salūzis. Limits nostrādāja, kā paredzēts. Noderīgie jautājumi ir: kurš limits nostrādāja, kas to iestatīja un kāpēc vaicājumam vajadzēja vairāk laika nekā agrāk.

Vispirms izlasiet precīzu ziņojumu

Vaicājumus atceļ vairāki dažādi mehānismi, un to ziņojumi no pirmā acu uzmetiena izskatās līdzīgi. Ja tos atšķir, nav jāmin:

ERROR:  canceling statement due to statement timeout   -- SQLSTATE 57014
ERROR:  canceling statement due to lock timeout        -- SQLSTATE 55P03
ERROR:  canceling statement due to user request        -- SQLSTATE 57014
FATAL:  terminating connection due to transaction timeout   -- SQLSTATE 25P04

• Statement timeout. Vaicājums izpildījās ilgāk par statement_timeout. Laiku mēra no brīža, kad komanda nonāk serverī, līdz tās pabeigšanai, tāpēc arī gaidīšana uz bloķējumiem tiek ieskaitīta.
• Lock timeout. lock_timeout ierobežo, cik ilgi drīkst gaidīt viena bloķējuma iegūšana. Tas nostrādā tikai gaidot bloķējumu, nekad laikā, kad vaicājums dara reālu darbu.
• User request. Kāds vai kaut kas nosūtīja atcelšanu: pg_cancel_backend(), Ctrl+C psql vai klienta puses vaicājuma taimauts. Daudzi draiveri savu vaicājuma taimautu realizē, nosūtot atcelšanas pieprasījumu, un tad serveris žurnālā raksta "user request", nevis "statement timeout".
• Transaction timeout. Kopš PostgreSQL 17 transaction_timeout ierobežo visu transakciju un pārtrauc sesiju, nevis atceļ vienu vaicājumu.

Lietotnēm svarīgi ir SQLSTATE kodi: statement timeout un lietotāja atcelšanai ir kopīgs kods 57014 (query_canceled), bet lock timeout ir 55P03 (lock_not_available). Ja atkārtošanas loģika skatās tikai uz vienu kodu, tā var lock timeout uztvert kā fatālu kļūdu vai otrādi.

Hot standby serverī ir ceturtais līdzīgais ziņojums, "canceling statement due to conflict with recovery". Ar jūsu iestatītajiem taimautiem tam nav nekāda sakara. Par to rakstījām rakstā canceling statement due to conflict with recovery.

Atrodiet, no kurienes nāk taimauts

Noklusējuma statement_timeout ir 0, kas nozīmē, ka limita nav. Tātad, ja redzat šo kļūdu, kāds vērtību ir iestatījis. Tā var nākt no postgresql.conf, ALTER DATABASE ... SET, ALTER ROLE ... SET, SET sesijā, savienojuma virknes vai PGOPTIONS, savienojumu pūla sākuma vaicājuma vai ORM. Neminiet, pajautājiet datubāzei:

-- kādu vērtību redz šī sesija un no kurienes tā nāk?
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('statement_timeout', 'lock_timeout', 'transaction_timeout');

-- lomu un datubāzu līmeņa iestatījumi (psql saīsne: \drds)
SELECT r.rolname, d.datname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_roles r ON r.oid = s.setrole
LEFT JOIN pg_database d ON d.oid = s.setdatabase;

Kolonna source rāda, vai vērtība nāk no konfigurācijas faila, datubāzes, lietotāja vai sesijas. Atcerieties, ka lomas un datubāzes iestatījumi attiecas tikai uz jaunām sesijām. Ilgi dzīvojošs pūls patur veco vērtību, līdz no jauna pieslēdzas. Ja lomas un datubāzes iestatījumi konfliktē, ALTER ROLE dokumentācija nosaka secību: lomas iestatījums konkrētā datubāzē ir svarīgāks par lomas iestatījumu, kas savukārt ir svarīgāks par datubāzes iestatījumu.

Vienu PostgreSQL dokumentācijas brīdinājumu vērts atkārtot: statement_timeout iestatīšana postgresql.conf nav ieteicama, jo tā ietekmē visas sesijas, arī apkopes un rezerves kopiju darbus.

Lēns vaicājums vai gaidīšana uz bloķējumu?

Tā kā gaidīšana uz bloķējumiem tiek ieskaitīta statement_timeout, pilnīgi ātrs UPDATE var sasniegt taimautu, ja tas stāv aiz citas transakcijas. Paskatieties, kas izpildās un ko tas gaida:

SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS running_for,
       pg_blocking_pids(pid) AS blocked_by,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY query_start
LIMIT 20;

• wait_event_type = 'Lock' un netukšs blocked_by: tā ir bloķēšanas problēma. Atrodiet bloķējošo PID. Ļoti bieži tā ir sesija stāvoklī idle in transaction, kas tur rindu bloķējumus, kamēr lietotne dara ko citu.
• Bloķējuma gaidīšanas nav, vaicājums vienkārši strādā: tā ir plāna vai datu apjoma problēma. Palaidiet EXPLAIN šim vaicājumam. EXPLAIN (ANALYZE, BUFFERS) lietojiet tikai tad, ja varat atļauties vaicājumu izpildīt, jo ANALYZE to tiešām izpilda. Ja tas maina datus, palaidiet to BEGIN ... ROLLBACK iekšienē.

Lai tos pamanītu laika gaitā, pārliecinieties, ka log_min_error_statement ir noklusējuma vērtībā ERROR vai zemāk, tad atceltā vaicājuma teksts nonāk žurnālā. Pievienojiet log_min_duration_statement, lai redzētu vaicājumus, kas tuvojas limitam, un log_lock_waits = on, lai žurnālā rakstītu bloķējumu gaidīšanu, kas ilgāka par deadlock_timeout (noklusējumā 1s).

Risinājumi tādā secībā, kādā mēs tos izmēģinām

1. Labojiet vaicājumu, nevis limitu. Vaicājumam, kas agrāk aizņēma 2 sekundes, bet tagad 40, parasti ir iemesls: pēc shēmas izmaiņām pazudis indekss, novecojusi statistika, plāna maiņa, tabulai augot, vai tabulas uzpūšanās. Taimauta palielināšana to tikai noslēpj līdz nākamajam incidentam.
2. Novērsiet bloķētāju. Ja vaicājums gaidīja bloķējumu, labojiet transakciju, kas to tur. Saīsiniet transakcijas lietotnē un pievienojiet lomai idle_in_transaction_session_timeout, lai pamestas sesijas neturētu bloķējumus mūžīgi.
3. Iestatiet taimautus katrai slodzei, nevis globāli. Tīmekļa pieprasījumiem, atskaitēm un pakešdarbiem vajag dažādus limitus. Piesaistiet tos lomām un palieliniet vienai transakcijai ar SET LOCAL, kas transakcijas beigās atgriežas iepriekšējā vērtībā (piemērs zemāk).
4. Turiet lock_timeout zemāku par statement_timeout. Dokumentācija norāda, ka lock_timeout, kas vienāds ar vai lielāks par nenulles statement_timeout, ir bezjēdzīgs, jo statement timeout vienmēr nostrādās pirmais. Zems lock timeout liek bloķējumu gaidīšanai ātri beigties ar savu skaidru ziņojumu.
5. Apstrādājiet kļūdu lietotnē. Atcelts vaicājums pārtrauc pašreizējo transakciju. Atritiniet to un, ja tas ir jēgpilni, atkārtojiet visu transakciju, bet neatkārtojiet uzreiz ciešā ciklā, ja datubāze jau ir pārslogota.

-- tīmekļa trafiks: īss pavadiņš
ALTER ROLE app_web IN DATABASE shop SET statement_timeout = '15s';
ALTER ROLE app_web IN DATABASE shop SET lock_timeout = '3s';

-- atskaišu loma: ilgāk, bet joprojām ar robežu
ALTER ROLE reporting SET statement_timeout = '20min';

-- viens smags darbs, kam tiešām vajag vairāk laika
BEGIN;
SET LOCAL statement_timeout = '3h';
-- ... ilgais vaicājums ...
COMMIT;   -- pēc COMMIT vai ROLLBACK atgriežas sesijas vērtība

Taimauti lielās datubāzēs

Datubāzēs ar simtiem gigabaitu vai terabaitiem taimauti praksē uzvedas citādi:

• Vaicājumi palēninās, tabulām augot. Limits, kas bija ērts pie 100 GB, pie 2 TB var sākt nostrādāt bez jebkādām koda izmaiņām. Sekojiet ilguma tendencēm ar log_min_duration_statement vai pg_stat_statements, lai redzētu vaicājumus, kas tuvojas limitam, pirms to pamana lietotāji.
• Apkopei jāiet ar citu lomu. Lielās tabulās CREATE INDEX, VACUUM, pg_dump un datu aizpildīšana var ilgt stundām. Ja tie manto tīmekļa lomas 15 sekunžu taimautu, tie izgāžas vēlu un darbs aiziet zudumā. Dodiet tiem atsevišķu lomu vai SET LOCAL izņēmumu.
• Atcelts CREATE INDEX CONCURRENTLY atstāj atliekas. Dokumentācijā teikts, ka neizdevusies vienlaicīga būvēšana atstāj INVALID indeksu. Vaicājumi to neizmanto, bet tas joprojām palēnina rakstīšanu. Lielā tabulā pārbaudiet \d, vai nav INVALID, tad izdzēsiet un būvējiet no jauna vai izmantojiet REINDEX INDEX CONCURRENTLY.
• Shēmas izmaiņām vajag lock_timeout. ALTER TABLE, kas gaida ekskluzīvu bloķējumu aiz ilgas atskaites, var likt citiem vaicājumiem šai tabulai sastāties rindā aiz tā. Īss lock_timeout ar atkārtošanu neļauj noslogotai tabulai apstāties:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
-- ERROR: canceling statement due to lock timeout  -> wait, retry

• Ilgas transakcijas kaitē vairāk nekā ilgi vaicājumi. Lielā, noslogotā datubāzē viena transakcija, kas atvērta stundām, neļauj vacuum noņemt mirušās rindas visā šajā datubāzē, un uzpūšanās var uzkrāties ātri. Kopš PostgreSQL 17 transaction_timeout lietotņu lomām var noteikt augšējo robežu.

Kad vaicājums jāaptur pašam

pg_cancel_backend(pid) atceļ pašreizējo vaicājumu un saglabā sesiju. pg_terminate_backend(pid) pārtrauc visu sesiju. Vispirms mēģiniet atcelt. Atcelts rakstīšanas vaicājums tiek atritināts un tā izmaiņas atmestas, bet jau ierakstītās mirušās rindu versijas paliek tabulā, līdz tās noņem vacuum, tāpēc liela atcelta datu aizpildīšana var atstāt manāmu uzpūšanos.