pg_stat_statements: kā atrast vaicājumus, kas patiešām noslogo PostgreSQL

pg_stat_statements ir PostgreSQL paplašinājums, kas uzkrāj plānošanas un izpildes statistiku par katru atšķirīgo SQL izteikumu, ko izpilda serveris. Kad datubāze ir lēna un neviens nezina kāpēc, tā ir pirmā vieta, kur skatāmies, jo tas atbild uz jautājumu, uz ko lēno vaicājumu žurnāls neatbild: kuri izteikumi kopā patērē visvairāk datubāzes laika.

Vaicājums, kas reizi naktī ilgst 40 sekundes, žurnālā izskatās biedējoši. Vaicājums, kas ilgst 3 ms, bet izpildās 2000 reižu sekundē, izmaksā daudz vairāk. pg_stat_statements grupē izteikumus pēc to normalizētās formas (konstantes kļūst par $1, $2), tāpēc šie 2000 izsaukumi sekundē parādās kā viena rinda ar reālām summām. Oficiālajā dokumentācijā aprakstīta katra kolonna. Zemāk ir tas, kā mēs to lietojam praksē.

pg_stat_statements ieslēgšana

Modulim vajadzīga koplietojamā atmiņa, tāpēc tam jābūt norādītam shared_preload_libraries, un tā pievienošanai vai noņemšanai nepieciešams servera restarts. Plānojiet šo restartu apkopes logā. Pats skats tiek izveidots katrā datubāzē ar CREATE EXTENSION.

# postgresql.conf (nepieciešams restarts)
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = auto          # noklusējums; nedrīkst būt off
pg_stat_statements.max = 10000   # noklusējums 5000, tikai servera startā

-- pēc tam katrā datubāzē, kur vajadzīgs skats
CREATE EXTENSION pg_stat_statements;

Lai modulis strādātu, jāaprēķina vaicājumu identifikatori. Noklusējuma compute_query_id = auto to ieslēdz automātiski, kad pg_stat_statements ir ielādēts, tāpēc pārbaudiet to tikai tad, ja kāds to iestatījis uz off. Pārvaldītajos servisos, piemēram, RDS vai Cloud SQL, tos pašus iestatījumus maina caur parametru vai karogu konfigurāciju, nevis postgresql.conf.

Smagākie vaicājumi pēc kopējā laika

Šo vaicājumu palaižam vispirms. Tas sarindo izteikumus pēc kopējā izpildes laika un parāda katra daļu no kopsummas:

SELECT queryid,
       calls,
       round(total_exec_time::numeric)    AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows / nullif(calls, 0)            AS rows_per_call,
       round((100 * total_exec_time
              / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
       left(query, 80)                    AS query
FROM pg_stat_statements
WHERE toplevel
ORDER BY total_exec_time DESC
LIMIT 20;

Kolonnu nosaukumi ir svarīgi. Kopš PostgreSQL 13 izpildes laiks ir total_exec_time un mean_exec_time; PostgreSQL 12 un vecākās versijās tie bija total_time un mean_time, tāpēc daudzi nokopēti skripti neizdodas. Laiki ir milisekundēs. Filtrs toplevel (PostgreSQL 14+) atstāj lietotnes izsauktos izteikumus un paslēpj funkciju iekšienē izpildītos, kurus uzskaita tikai tad, ja pg_stat_statements.track = all; noklusējums ir top.

Kā lasīt skaitļus

• Liels kopējais laiks, mazs vidējais, milzīgs calls. Parasti N+1 modelis lietotnē vai trūkstoša kešatmiņa. Vaicājuma paātrināšana palīdz nedaudz; retāka izsaukšana palīdz ļoti.
• Liels vidējais laiks, maz izsaukumu. Klasisks lēns vaicājums. Paņemiet tā tekstu, palaidiet EXPLAIN (ANALYZE, BUFFERS) uz reālistiskas kopijas un izejiet cauri plānam kā mūsu PostgreSQL lēno vaicājumu optimizācijas kontrolsarakstā.
• Liels rows_per_call. Lietotne ielādē daudz vairāk rindu, nekā parāda. Bieži īstais risinājums ir lapošana vai trūkstošs filtrs.
• Vidējais slēpj lēcienus. Skatieties arī max_exec_time un stddev_exec_time. Izteikums ar 5 ms vidējo un 30 s maksimumu ir bloķēšanas vai plāna problēma, nevis CPU problēma.
• Skaitās tikai veiksmīgas izpildes. Dokumentācijā norādīts, ka statistika tiek atjaunināta veiksmīgām operācijām, tāpēc vaicājums, kas regulāri sasniedz statement_timeout, var izskatīties lētāks, nekā ir. Pārbaudiet atcelšanas žurnālos.

Sarindošana pēc I/O, pagaidu failiem un WAL

Laiks nav vienīgās izmaksas. Datubāzē, kas ir lielāka par RAM, izteikumi, kas nolasa visvairāk bloku ārpus shared buffers, parasti ir tie, kas izspiež visu pārējo no kešatmiņas:

SELECT queryid,
       calls,
       shared_blks_read,
       round(100.0 * shared_blks_hit
             / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       temp_blks_written,
       pg_size_pretty(wal_bytes)          AS wal,
       left(query, 60)                    AS query
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

Sakārtojiet to pašu vaicājumu pēc temp_blks_written, lai atrastu kārtošanas un hash operācijas, kas izlīst uz diska, vai pēc wal_bytes, lai atrastu rakstīšanu, kas rada replikācijas aizkavi un WAL arhīva apjomu. Kolonna shared_blk_read_time (pirms PostgreSQL 17 blk_read_time) tiek aizpildīta tikai tad, ja ieslēgts track_io_timing. Pēc noklusējuma tas ir izslēgts, jo laika mērīšanas izsaukumi dažās platformās var būt dārgi; dokumentācija iesaka vispirms izmērīt ar pg_test_timing.

Momentuzņēmumi ir labāki par atiestatīšanu

Skatā ir kumulatīvi skaitītāji kopš katra ieraksta izveides. Lai redzētu, kas notika no 14:00 līdz 14:15, saglabājiet momentuzņēmumus un atņemiet. Funkcijai nododot false, vaicājumu teksti netiek atgriezti, tāpēc bieži momentuzņēmumi paliek lēti:

-- momentuzņēmums bez vaicājumu tekstiem (lēti), piem., ik 5-15 minūtes
INSERT INTO pgss_snap
SELECT now(), userid, dbid, toplevel, queryid, stats_since,
       calls, total_exec_time, rows, shared_blks_read,
       temp_blks_written, wal_bytes
FROM pg_stat_statements(false);

-- cik pilna ir hash tabula un vai ieraksti tiek izmesti?
SELECT dealloc, stats_reset FROM pg_stat_statements_info;

Savienojiet momentuzņēmumus pēc userid, dbid, toplevel, queryid. Kopš PostgreSQL 17 katram ierakstam ir stats_since; ja tas starp diviem momentuzņēmumiem mainījies, ieraksts tika izmests un izveidots no jauna, un starpībai nav jēgas. Šīs starpības ir arī izejmateriāls brīdinājumiem par datubāzes metrikām, kas paredz incidentus.

Slazdi, par kuriem vērts zināt

• Ieraksti tiek izmesti. pg_stat_statements.max noklusējums ir 5000. Kad parādās vairāk atšķirīgu izteikumu, retāk izpildītie tiek izmesti un pg_stat_statements_info.dealloc pieaug. Ja dealloc nepārtraukti aug, palieliniet max (vajadzīgs restarts) vai atrodiet, kas ģenerē tik daudz unikālu izteikumu.
• Vaicājuma teksts var pazust. Teksti glabājas ārējā failā, nevis koplietojamā atmiņā. Ja šis fails kļūst pārāk liels, modulis var izmest visus tekstus, un kolonna query kļūst NULL, kamēr skaitītāji paliek.
• Tiesības. Tikai superlietotāji un lomas ar pg_read_all_stats redz citu lietotāju izteikumu tekstu un queryid. Monitoringa lietotājs bez tās joprojām redz skaitītājus, bet ne to, kuram SQL tie pieder.
• Plānošanas laiks pēc noklusējuma izslēgts. pg_stat_statements.track_planning aizpilda *_plan_time kolonnas, bet dokumentācija brīdina, ka tas var radīt manāmu veiktspējas kritumu. Ieslēdziet to izmeklēšanai, nevis pastāvīgi aiz ieraduma.
• PostgreSQL 18 grupē vairāk. Saskaņā ar PostgreSQL 18 laidiena piezīmēm konstanšu sarakstiem, piemēram, IN (1, 2, 3), vaicājuma ID ņem vērā tikai pirmo un pēdējo konstanti, SET vērtības tiek parametrizētas, un vaicājumi uz vienāda nosaukuma tabulām dažādās shēmās tiek grupēti kopā. Tas samazina troksni, bet pēc jaunināšanas skaitļi pa shēmām saplūst.

Kas mainās lielās, noslogotās datubāzēs

• Izteikumu mainība ir svarīgāka par izmēru. Ģenerēts SQL ar mainīgiem kolonnu sarakstiem, ORM, kas ieliek vērtības tekstā, un shēma-katram-klientam arhitektūra var radīt daudz vairāk nekā 5000 atšķirīgu izteikumu. Pastāvīga izmešana nozīmē, ka jūsu saraksts ir nepilnīgs tieši tad, kad tas vajadzīgs. Sekojiet dealloc un pielāgojiet max slodzei; līdz ar to pieaug koplietojamās atmiņas patēriņš.
• Katrai instancei ir sava statistika. Replikām un primārajam ir atsevišķi skaitītāji. Ja lasīšanas trafiks iet uz replikām, vaicājiet arī tās, citādi lasīšanas ietilpīgie izteikumi primārajā vienkārši neparādīsies.
• Glabājiet momentuzņēmumus ārpus mērāmās datubāzes. Pilna skata ar tekstiem nolasīšana ik pēc dažām sekundēm serverī ar desmitiem tūkstošu ierakstu pati par sevi ir slodze. Nolasiet skaitītājus ar pg_stat_statements(false), tekstus ielādējiet tikai jauniem queryid un vēsturi glabājiet monitoringa sistēmā.
• Sasaistiet datus ar queryid. Kad vaicājumu identifikatori tiek aprēķināti, pg_stat_activity.query_id un %Q aizstājējs log_line_prefix izmanto to pašu identifikatoru, tāpēc izteikumu, ko redzat žurnālos vai kas bloķē tieši tagad, var sasaistīt ar tā ilgtermiņa summām.
• Pārbaudiet labojumus uz pilna izmēra datiem. Plāns, kas izskatās labi uz mazas kopijas, terabaitu mērogā var pilnībā mainīties. Pārbaudiet indeksu vai vaicājumu izmaiņas uz produkcijas izmēra datiem un pēc tam salīdziniet starpības pg_stat_statements pirms un pēc.