Monitoring — pg_stat_activity, pg_stat_statements, slow queries

~20 daq · PostgreSQL · Xavfsizlik va Boshqaruv

Ilova sekinlashdi. Nima bo'lyapti? Qaysi so'rovlar sekin? Qancha ulanish bor? Kim nima qilyapti? PostgreSQL ning ichki ko'rinish (view) lari bu savollarga javob beradi.

pg_stat_activity — faol ulanishlar

SELECT pid, usename, state, query, now() - query_start AS davomiylik
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY davomiylik DESC;

pid   | usename  | state  | query                        | davomiylik
──────   ──────────   ──────   ──────────────────────────────   ───────────
12345  api_user   active  SELECT * FROM buyurtmalar ...   00:02:15
23456  api_user   idle in SELECT * FROM foydalanuvchi...  00:00:05

Ulanishlar soni

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

Uzoq ishlayotgan so'rov ni to'xtatish

-- Sekin so'rov
SELECT pg_cancel_backend(pid);    -- so'rovni bekor qilish (yumshoq)
SELECT pg_terminate_backend(pid); -- ulanishni uzish (qattiq)

pg_stat_statements — so'rovlar statistikasi

Qaysi so'rovlar eng ko'p vaqt olayotganini ko'rish uchun extension:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'

-- Eng sekin so'rovlar
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS jami_ms,
  round(mean_exec_time::numeric, 2)  AS o_rtacha_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

Slow query log

postgresql.conf da:

log_min_duration_statement = 1000   -- 1 soniyadan sekin so'rovlarni log qilish
log_destination = 'csvlog'
logging_collector = on

Log faylidan sekin so'rovlarni ko'rish:

grep "duration:" /var/log/postgresql/postgresql-*.csv | sort -t: -k2 -rn | head -20

Jadval statistikasi

-- Jadvallar holati
SELECT
  relname,
  seq_scan,       -- ketma-ket skan soni (index ishlatilmagan)
  idx_scan,       -- index skan soni
  n_live_tup,     -- faol qatorlar
  n_dead_tup      -- o'lik qatorlar (VACUUM kerak)
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

seq_scan ko'p bo'lsa — index kerak.

Index ishlatilmayotganlar

SELECT
  indexrelname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS hajm
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%'
ORDER BY pg_relation_size(indexrelid) DESC;

Hech ishlatilmagan katta indexlar — o'chirish mumkin.

Database hajmi

-- Database hajmi
SELECT pg_size_pretty(pg_database_size('myapp'));

-- Jadvallar hajmi
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(oid)) AS jami_hajm,
  pg_size_pretty(pg_relation_size(oid))        AS jadval_hajm
FROM pg_class
WHERE relkind = 'r'
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 10;

Xulosa

-- Faol so'rovlar
SELECT pid, state, query, now()-query_start FROM pg_stat_activity WHERE state != 'idle';

-- Sekin so'rovlar
SELECT query, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

-- VACUUM kerak jadvallar
SELECT relname, n_dead_tup FROM pg_stat_user_tables WHERE n_dead_tup > 10000;

-- Ishlatilmayotgan indexlar
SELECT indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0;

-- Hajmlar
SELECT pg_size_pretty(pg_database_size('myapp'));

Keyingi dars

58-dars: Extensions nima — CREATE EXTENSION, mashhur extensionlar