PostgreSQL-də əsas monitorinq komandaları — pg_stat_activity və pg_stat_user_tables

Baza monitorinqi pg_stat_activity və pg_stat_user_tables olmadan elə bil ki, insanın sağlamlığına təkcə temperaturuna baxmaq kimidir. Sadəcə ümumi mənzərəyə baxsan, problemi tapa bilməyəcəksən. Bu iki əsas PostgreSQL komandası sənə sadəcə baxmağa yox, həm də aktiv analiz etməyə kömək edəcək ki, bazanda nə baş verir.

pg_stat_activity nədir?

pg_stat_activity — PostgreSQL-in sistem view-sudur, hansı ki, bazaya olan bütün bağlantılar haqqında info verir. Sualına cavab tapırsan: kim bazaya qoşulub, hansı query-lər indi işləyir, hansı bağlantılar "boşda" qalıb. Bu sənin serverdə cari aktivliyi analiz etmək üçün əsas alətindir.

Gəlin pg_stat_activity-də olan əsas sahələrə baxaq. datname — client-in qoşulduğu bazanın adıdır, usename — qoşulan istifadəçinin adıdır. application_name — bağlantı üçün istifadə olunan app-ın adını göstərir, client_addr — serverə qoşulan client-in IP ünvanıdır. backend_start — client-in serverə nə vaxt qoşulduğunu göstərir, state — bağlantının cari vəziyyətini göstərir (active, idle, idle in transaction), query isə icra olunan və ya sonuncu icra olunmuş query-ni saxlayır.

Nümunə 1: bütün aktiv bağlantılara baxış

Aktiv bağlantıları görmək üçün bu query-ni işə sal:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

query sahəsinə fikir ver. Orda indi işləyən query-lər görünür. Əgər hansısa query çox uzun çəkirsə, deməli, nəsə düz getmir.

Nümunə 2: Transaction vəziyyətinin analizi

Bəzən bağlantılar idle in transaction vəziyyətində "ilişib" qalır. Bu o deməkdir ki, transaction başlayıb, amma bitməyib, bu da bloklanmalara səbəb ola bilər.

SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Bunu necə düzəltmək olar? Əgər "ilişib" qalmış transaction tapdınsa, onu bu komanda ilə bağlaya bilərsən:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Bəzi dev-lər bu işdə həddini aşır. Məsləhət görürəm ki, əvvəlcə komandadan soruş, proses "öldürmək" olar ya yox. Ups, yəni — bağlantını bağlamaq.

Cədvəllərin istifadəsinin monitorinqi: pg_stat_user_tables view-u

pg_stat_activity bağlantılara baxmağa imkan verirsə, pg_stat_user_tables cədvəllərin performansı barədə danışır. Onunla biləcəksən: cədvəllərdən nə qədər tez-tez oxunur və yazılır, hansı cədvəllər daha çox istifadə olunur və harada performans problemi ola bilər.

İndi isə pg_stat_user_tables-də əsas sahələrə baxaq. relname — cədvəlin adıdır, seq_scan — cədvəlin ardıcıl scan-larının sayı, idx_scan — index-lə scan-ların sayı. n_tup_ins — cədvələ insert olunan sətirlərin sayı, n_tup_upd — update olunan sətirlərin sayı, n_tup_del — silinən sətirlərin sayı.

Nümunə 1: index və ardıcıl scan-ların müqayisəsi

Əgər index çox az istifadə olunursa (idx_scan sıfıra yaxındırsa), çox güman ki, bu cədvələ olan query-ləri optimizasiya etmək olar.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Nəticə nümunəsi:

Əgər görürsən ki, orders cədvəli üçün ardıcıl scan-ların sayı çoxdur (seq_scan), index əlavə etməyi düşün. Təsəvvür elə ki, orders cədvəlində 3500 ardıcıl scan, cəmi 100 index scan var, amma employees cədvəlində 50 ardıcıl scan və 1000 index scan var — bu artıq optimizasiya üçün siqnaldır.

Nümunə 2: cədvəllərlə əməliyyatların sayının analizi

Cədvəllərdə nə qədər "canlı" data olduğunu görmək üçün insert, update və delete olunan sətirlərin sayına bax:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

Nə öyrənə bilərsən? Insert (n_tup_ins) və delete (n_tup_del) əməliyyatlarının sayı çox olan cədvəllər bazada "isti nöqtələr" ola bilər. Deməli, onların performansına xüsusi diqqət lazımdır.

Performans analizində komandaların praktik istifadəsi: pg_stat_activity və pg_stat_user_tables məlumatlarını birləşdiririk

Baza performansını analiz edəndə, iki mənbədən gələn məlumatı birləşdirə bilərsən. Əvvəlcə pg_stat_activity ilə uzun çəkən query-ləri tap, sonra bu query-lərin hansı cədvəllərdə işlədiyinə pg_stat_user_tables ilə bax. Əgər query-lər yüksək seq_scan olan cədvəllərdə uzun çəkirsə, query-ləri optimizasiya elə və ya index əlavə et.

Query nümunəsi:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';