PostgreSQL veselības pārbaude (health check): DBA kontrolsaraksts ar vaicājumiem

PostgreSQL veselības pārbaude (health check) atbild uz vienu jautājumu: kas visticamāk apturēs šo datubāzi, un cik drīz? Lielākā daļa problēmu, ko atrodam produkcijā, bija redzamas statistikas skatos jau nedēļām iepriekš. Tos vienkārši neviens neskatījās. Šis kontrolsaraksts ir secība, kādā ejam cauri jaunai sistēmai, ar vaicājumiem, kas darbojas jebkurā mūsdienīgā PostgreSQL, un piezīmēm par to, ko uzskatīt par steidzamu.

1. Versija, darbības laiks un wraparound risks

SELECT version(), pg_postmaster_start_time(), now() - pg_postmaster_start_time() AS uptime;

-- transakciju ID vecums katrai datubāzei (piespiedu vacuum sākas pie autovacuum_freeze_max_age)
SELECT datname, age(datfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age') AS freeze_max_age
FROM pg_database ORDER BY 2 DESC;

• Versija. Pārbaudiet, vai galvenā versija vēl tiek atbalstīta un vai minor relīze ir aktuāla. Minor relīzes satur drošības un kļūdu labojumus, un tām nav vajadzīga datu izgāšana un atjaunošana, tikai jauni binārie faili un restarts. Tomēr izlasiet relīzes piezīmes, vai nav papildu soļu.
• XID vecums. Kad tabulas vecums sasniedz autovacuum_freeze_max_age (noklusējums 200 miljoni), PostgreSQL piespiedu kārtā palaiž anti-wraparound vacuum, pat ja autovacuum ir izslēgts. Vecums krietni virs šīs vērtības nozīmē, ka iesaldēšana netiek līdzi. Tas ir augstas prioritātes atradums; mūsu transakciju ID wraparound ceļvedī paskaidrots, kāpēc.
• Darbības laiks. Saskaņā ar dokumentāciju statistikas skaitītāji tiek atiestatīti pēc nekorektas izslēgšanas, avārijas atjaunošanas vai atjaunošanas no bāzes kopijas. Īss darbības laiks pēc avārijas nozīmē, ka zemāk redzamie skaitļi aptver tikai īsu periodu, tāpēc vērtējiet tos piesardzīgi.

2. Savienojumi un garas transakcijas

SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

SELECT pid, usename, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 10;

Skatieties uz divām lietām. Pirmkārt, cik sesiju ir stāvoklī idle in transaction. Otrkārt, vecākās atvērtās transakcijas vecumu. Transakcija, kas atvērta stundām, aiztur vacuum: rindas, kas kļuvušas mirušas pēc tās momentuzņēmuma, nevar izņemt, kamēr tā nav beigusies. Noslogotā datubāzē tas vien rada bloat un lēnākus vaicājumus. Atrodiet lietotni, kas to izraisa, un kā drošības tīklu iestatiet idle_in_transaction_session_timeout.

3. Replikācija, sloti un WAL arhivēšana

SELECT application_name, state, sync_state, replay_lag FROM pg_stat_replication;

SELECT slot_name, slot_type, active, wal_status, safe_wal_size
FROM pg_replication_slots;

SELECT archived_count, failed_count, last_failed_wal, last_failed_time
FROM pg_stat_archiver;

• Replikas nobīde. replay_lag skatā pg_stat_replication parāda, cik tālu laikā katra standby atpaliek. Salīdziniet to ar to, ko pieņem jūsu failover plāns.
• Replikācijas sloti ir biežākais pilna pg_wal cēlonis, ko redzam. Neaktīvs slots glabā WAL bezgalīgi, ja max_slot_wal_keep_size ir -1, kas ir noklusējums. Kolonna wal_status parāda, cik tuvu tas ir problēmām: reserved nozīmē max_wal_size robežās, extended nozīmē virs tās, bet faili joprojām tiek saglabāti, unreserved nozīmē, ka faili tiks dzēsti nākamajā kontrolpunktā, un lost nozīmē, ka slots vairs nav izmantojams. Katram neaktīvam slotam vajag īpašnieku, vai arī tas jādzēš.
• Arhivēšana. Augošs failed_count skatā pg_stat_archiver nozīmē, ka atjaunošana uz laika punktu (PITR) klusi nedarbojas un WAL krājas. Pārbaudiet last_failed_wal un servera žurnālu.

4. Datubāzes līmeņa skaitītāji

SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       temp_files, pg_size_pretty(temp_bytes) AS temp_size,
       deadlocks, checksum_failures, stats_reset
FROM pg_stat_database WHERE datname IS NOT NULL;

• Keša trāpījumu attiecība ir aptuvens signāls, nevis mērķis. OLTP tā parasti ir ļoti augsta. Pēkšņs kritums ir svarīgāks par absolūto vērtību.
• Pagaidu faili nozīmē, ka kārtošana un hash operācijas izlīst uz disku. Liels temp_bytes norāda uz konkrētiem vaicājumiem ar pārāk mazu work_mem vai uz vaicājumiem, kuriem nemaz nevajadzētu tik daudz kārtot.
• Deadlock skaitam jābūt tuvu nullei, un tas nedrīkst augt.
• checksum_failures jābūt nullei. Tas ir NULL, ja datu kontrolsummas ir izslēgtas. Jebkura vērtība virs nulles ir krātuves vai datu bojājuma incidents, nevis noskaņošanas uzdevums.

5. Tabulas un indeksi

-- mirušās rindas un autovacuum aktivitāte
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

-- lieli indeksi, kas nav izmantoti kopš pēdējās statistikas atiestatīšanas (bez unikālajiem)
SELECT s.relname, s.indexrelname, pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0 AND NOT i.indisunique
ORDER BY pg_relation_size(s.indexrelid) DESC LIMIT 10;

-- nederīgi indeksi, piemēram, pēc neizdevušās CREATE INDEX CONCURRENTLY
SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;

• Mirušās rindas. Lielām tabulām ar daudz mirušām rindām un vecu last_autovacuum vajag tabulas līmeņa autovacuum iestatījumus. Skatiet mūsu autovacuum noskaņošanas ceļvedi.
• Neizmantoti indeksi maksā rakstīšanas I/O, WAL apjomu un rezerves kopiju izmēru. idx_scan = 0 attiecas tikai uz laiku kopš pēdējās statistikas atiestatīšanas un tikai uz serveri, kuram vaicājat. Indekss var būt neizmantots primārajā serverī, bet vajadzīgs replikā. Pirms kaut ko dzēšat, pārbaudiet replikas un skatieties pilnu biznesa ciklu, piemēram, mēneša beigu darbus.
• Nederīgi indeksi (indisvalid = false) netiek izmantoti vaicājumos, bet joprojām tiek uzturēti katrā ierakstā. Parasti tos atstāj neizdevusies CREATE INDEX CONCURRENTLY; pārbūvējiet vai dzēsiet tos.

6. Kontrolpunkti un top vaicājumi

-- PostgreSQL 17+: kontrolpunkti pēc iemesla
SELECT num_timed, num_requested, num_done FROM pg_stat_checkpointer;
-- PostgreSQL 16 un vecākas: checkpoints_timed, checkpoints_req skatā pg_stat_bgwriter

-- vaicājumi ar lielāko kopējo laiku (paplašinājums pg_stat_statements; kolonnu nosaukumi kopš PostgreSQL 13)
SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
       left(query, 60)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Ja pieprasīto kontrolpunktu ir skaidri vairāk nekā plānoto, WAL piepilda max_wal_size, pirms beidzas checkpoint_timeout, un tas nozīmē vairāk I/O un vairāk pilnu lapu ierakstu. Skats pg_stat_checkpointer pastāv kopš PostgreSQL 17. Vecākās versijās tie paši skaitītāji ir skatā pg_stat_bgwriter. Vaicājumiem kārtojiet pg_stat_statements pēc kopējā, nevis vidējā laika: 5 ms vaicājums, kas izsaukts piecdesmit miljonus reižu dienā, parasti ir svarīgāks par vienu lēnu atskaiti.

7. Drošības pamati

SHOW password_encryption;      -- scram-sha-256 ir noklusējums kopš PostgreSQL 14
SHOW data_checksums;
SHOW ssl;
SELECT rolname FROM pg_roles WHERE rolsuper;
SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';

• Lomas, kuru paroles joprojām glabājas kā MD5 hash, jāpārceļ uz SCRAM, atiestatot to paroles pēc password_encryption = scram-sha-256 iestatīšanas.
• Turiet superlietotāju sarakstu īsu un pārliecinieties, ka lietotnes nepieslēdzas kā superlietotājs.
• Pārskatiet pg_hba.conf, meklējot trust ierakstus un platus adrešu diapazonus.
• data_checksums parāda, vai lapu kontrolsummas ir ieslēgtas. Kopš PostgreSQL 18 initdb tās ieslēdz pēc noklusējuma; klasteri, kas izveidoti ar vecākām versijām, bieži darbojas bez tām.

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

• Arī vaicājumiem ir cena. Viss iepriekš minētais lasa katalogu un statistikas skatus un ir lēts. Izvairieties no rīkiem, kas maksimālās slodzes laikā skenē vairāku TB tabulas, lai novērtētu bloat. Vispirms izmantojiet uz statistiku balstītus novērtējumus, un precīzus mērījumus (piemēram, pgstattuple) veiciet replikā vai ārpus slodzes laika.
• Rezervi līdz wraparound mēra laikā, nevis skaitļos. Ļoti lielai tabulai pilns anti-wraparound vacuum var ilgt daudzas stundas vai dienas. Izmēriet, cik ilgi pēdējais ilga šai tabulai, un salīdziniet to ar to, cik ātri aug XID vecums.
• Sloti un arhivēšana krīt apjoma dēļ. Liels klasteris ar intensīvu rakstīšanu var radīt desmitiem GB WAL stundā. Slota vai arhivēšanas kļūme var piepildīt disku dažās stundās, tāpēc brīdinājumi par wal_status un failed_count ir svarīgāki nekā mazā instancē.
• Neizmantoti indeksi lielās tabulās ir dārgi. 200 GB indekss bez nevienas skenēšanas ir tīra rakstīšanas pastiprināšana. Bet arī kļūdaini dzēsta indeksa pārbūve prasa stundas, tāpēc vispirms pārbaudiet katrā mezglā un pilnā ciklā.
• Testējiet atjaunošanu pilnā izmērā. Veselības pārbaudē jāiekļauj pēdējās veiksmīgās pilna izmēra rezerves kopijas atjaunošanas datums un ilgums. Šis skaitlis ir jūsu reālais atjaunošanas laiks.