Najpierw: pg_stat_statements
Bez tego punkt 2 nie ruszy. Rozszerzenie nie jest domyślnie włączone.
W postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
Wymaga restartu — pg_reload_conf() nie wystarczy. Potem w bazie:
CREATE EXTENSION pg_stat_statements;
Trzyma 5000 unikalnych zapytań (pg_stat_statements.max). Wyzerowanie liczników:
SELECT pg_stat_statements_reset();
1. Aktywne zapytania
SELECT
a.pid,
a.datname,
a.usename,
a.application_name,
a.client_addr,
a.state,
a.wait_event_type,
a.wait_event,
now() - a.xact_start AS xact_age,
now() - a.query_start AS query_age,
now() - a.state_change AS state_age,
pg_blocking_pids(a.pid) AS blocked_by,
a.query
FROM pg_stat_activity a
WHERE a.pid <> pg_backend_pid()
AND a.backend_type = 'client backend'
AND a.state IS DISTINCT FROM 'idle'
ORDER BY COALESCE(a.xact_start, a.query_start, a.backend_start) ASC;
pg_blocking_pids mówi, kto kogo blokuje — pusta tablica znaczy, że ten backend na nic nie
czeka.
state IS DISTINCT FROM 'idle' wycina bezczynne połączenia, ale zostawia
idle in transaction. I to zwykle one są problemem.
Ubicie:
-- Anuluje zapytanie, połączenie zostaje
SELECT pg_cancel_backend(12345);
-- Zrywa połączenie
SELECT pg_terminate_backend(12345);
Najpierw cancel.
2. Wolne zapytania
SELECT
s.queryid,
d.datname,
s.calls,
round((s.total_exec_time / 1000.0)::numeric, 1) AS total_sec,
round(s.mean_exec_time::numeric, 2) AS mean_ms,
round(s.max_exec_time::numeric, 2) AS max_ms,
round(s.stddev_exec_time::numeric, 2) AS stddev_ms,
round((100.0 * s.total_exec_time / NULLIF(sum(s.total_exec_time) OVER (), 0))::numeric, 1) AS pct_of_total_time,
s.rows,
round(s.rows::numeric / NULLIF(s.calls, 0), 1) AS rows_per_call,
round((100.0 * s.shared_blks_hit / NULLIF(s.shared_blks_hit + s.shared_blks_read, 0))::numeric, 1) AS cache_hit_percent,
pg_size_pretty(s.shared_blks_read * current_setting('block_size')::bigint) AS disk_read,
pg_size_pretty(s.temp_blks_read * current_setting('block_size')::bigint) AS temp_read,
pg_size_pretty(s.temp_blks_written * current_setting('block_size')::bigint) AS temp_written,
pg_size_pretty(s.wal_bytes::bigint) AS wal_generated,
round(s.shared_blk_read_time::numeric, 1) AS io_read_ms,
round(s.shared_blk_write_time::numeric, 1) AS io_write_ms,
s.query
FROM pg_stat_statements s
LEFT JOIN pg_database d ON d.oid = s.dbid
WHERE d.datname = current_database()
ORDER BY s.total_exec_time DESC
LIMIT 50;
Patrz na pct_of_total_time, nie na mean_ms. Zapytanie po 5 ms puszczone milion razy
waży więcej niż jedno trzysekundowe.
temp_written powyżej zera = sortuje na dysku, podnieś work_mem. Niski cache_hit_percent =
czyta z dysku zamiast z cache. Wysoki stddev_ms = plan sypie się przy niektórych parametrach.
3. Indeksy
SELECT
n.nspname AS schema_name,
t.relname AS table_name,
i.relname AS index_name,
am.amname AS index_type,
ix.indisunique AS is_unique,
ix.indisprimary AS is_primary_key,
(ix.indisvalid AND ix.indisready AND ix.indislive) AS is_valid,
ix.indnkeyatts AS key_columns,
ix.indnatts - ix.indnkeyatts AS included_columns,
ix.indpred IS NOT NULL AS is_partial,
NULLIF(t.reltuples, -1)::bigint AS row_count_estimate,
round(pg_relation_size(i.oid) / 1024.0 / 1024, 2) AS index_size_mb,
COALESCE(sui.idx_scan, 0) AS num_of_scans,
sui.last_idx_scan,
COALESCE(sui.idx_tup_read, 0) AS index_entries_read,
COALESCE(sui.idx_tup_fetch, 0) AS heap_rows_fetched,
COALESCE(sut.n_tup_ins, 0) AS table_inserts,
COALESCE(sut.n_tup_upd, 0) AS table_updates,
COALESCE(sut.n_tup_hot_upd, 0) AS hot_updates,
GREATEST(COALESCE(sut.n_tup_upd, 0) - COALESCE(sut.n_tup_hot_upd, 0), 0) AS non_hot_updates,
COALESCE(sut.n_tup_del, 0) AS table_deletes,
sd.stats_reset AS counters_since,
pg_get_expr(ix.indpred, ix.indrelid) AS predicate,
pg_get_indexdef(i.oid) AS index_definition
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class t ON t.oid = ix.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_am am ON am.oid = i.relam
LEFT JOIN pg_stat_user_indexes sui ON sui.indexrelid = i.oid
LEFT JOIN pg_stat_user_tables sut ON sut.relid = t.oid
CROSS JOIN (
SELECT stats_reset
FROM pg_stat_database
WHERE datname = current_database()
) sd
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND t.relkind IN ('r', 'm', 'p')
ORDER BY pg_relation_size(i.oid) DESC;
num_of_scans = 0 to kandydat do wywalenia — ale najpierw zerknij na counters_since. Ta
kolumna jest tu właśnie po to, żeby nie skasować indeksu na podstawie licznika wyzerowanego
wczoraj.
is_valid = false to niedokończone CREATE INDEX CONCURRENTLY. Taki indeks nie jest używany,
a i tak kosztuje przy każdym zapisie — skasować i zbudować od nowa.
non_hot_updates to update’y, które musiały ruszyć indeksy. Dużo = wysoki koszt utrzymania.
last_idx_scan wymaga PG 16.
4. Martwe krotki i nieaktualne statystyki
SELECT
s.schemaname,
s.relname,
s.n_live_tup,
s.n_dead_tup,
round(100.0 * s.n_dead_tup / NULLIF(s.n_live_tup + s.n_dead_tup, 0), 1) AS dead_percent,
s.n_mod_since_analyze,
s.n_ins_since_vacuum,
age(c.relfrozenxid) AS xid_age,
s.last_vacuum,
s.last_autovacuum,
s.last_analyze,
s.last_autoanalyze,
pg_size_pretty(pg_table_size(s.relid)) AS table_size,
c.reloptions
FROM pg_stat_user_tables s
JOIN pg_class c ON c.oid = s.relid
WHERE s.n_dead_tup > 1000
OR s.n_mod_since_analyze > 1000
ORDER BY dead_percent DESC NULLS LAST;
Wysoki dead_percent = autovacuum nie nadąża i tabela puchnie.
Wysoki n_mod_since_analyze = planner liczy na nieaktualnych statystykach i dobiera złe plany.
Zwykle boli bardziej niż samo bloat.
xid_age dobijający do 200 mln (autovacuum_freeze_max_age) = zbliża się wraparound.
5. Rozmiary
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
NULLIF(c.reltuples, -1)::bigint AS row_count_estimate,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
pg_size_pretty(pg_table_size(c.oid)) AS table_size,
pg_size_pretty(pg_relation_size(c.oid)) AS main_fork_size,
pg_size_pretty(pg_indexes_size(c.oid)) AS indexes_size,
pg_size_pretty(
CASE
WHEN c.reltoastrelid = 0 THEN 0
ELSE pg_total_relation_size(c.reltoastrelid)
END
) AS toast_size,
round(100.0 * pg_total_relation_size(c.oid) / NULLIF(pg_database_size(current_database()), 0), 2) AS pct_of_database
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
ORDER BY pg_total_relation_size(c.oid) DESC;
pg_total_relation_size = tabela + indeksy + TOAST. pg_table_size to samo bez indeksów,
pg_relation_size sam główny fork.
row_count_estimate to szacunek z ostatniego ANALYZE, nie COUNT(*). -1 znaczy „nigdy
nie analizowana”.
Całe bazy na serwerze:
SELECT
datname,
pg_size_pretty(pg_database_size(datname)) AS database_size
FROM pg_database
WHERE NOT datistemplate
ORDER BY pg_database_size(datname) DESC;