Real vaxtda aktiv tranzaksiyaların izlənməsi pg_stat_activity ilə

pg_stat_activity əslində real vaxtda bir pəncərədir, hansı ki, bazada indi nə baş verdiyini anlamağa kömək edir. Əvvəlki leksiyada əsasları keçdik, indi isə bu güclü alətlə daha dərindən işləməyə baxaq.

pg_stat_activity-yə əsas sorğu nümunəsi:

SELECT * 
FROM pg_stat_activity;

Bu sorğu bütün aktiv bağlantıları və cari sorğuları göstərəcək. Əla! Amma məlumat çox olacaq və onları baxmaq üçün sonsuz vaxt sərf edə bilərik. Ona görə də ən vacib informasiyanı filtrləmək faydalıdır.

pg_stat_activity-də əsas sahələr

Gəlin, artıq bildiklərinizə əlavə olaraq sizə lazım olacaq əsas sahələrə baxaq. query_start sorğunun icrasına başlanma vaxtını göstərir, bu da uzun əməliyyatları tapmaq üçün kritikdir. pid bağlantı prosesinin identifikatorunu saxlayır — bu, əlaqəni idarə etmək (məsələn, sonlandırmaq) üçün lazımdır. state_change cari bağlantı vəziyyətinin nə vaxt qurulduğunu göstərir, bu da uzunmüddətli problemli vəziyyətləri analiz etmək üçün xüsusilə faydalıdır.

Aktiv proseslərin seçilməsi nümunəsi:

SELECT pid, usename, state, query, query_start 
FROM pg_stat_activity
WHERE state = 'aktiv';

Uzun sorğuları necə izləmək olar?

Təsəvvür et, sən database administratorusan və birdən serverdə yük göyə qalxdı. Nə etməli? Əvvəlcə hansı sorğunun bütün resursları yediyini tapmaq lazımdır. Belə "acgöz" sorğuları tapmaq üçün pg_stat_activity-dən istifadə edirik.

SELECT pid, usename, query, state, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'aktiv'
  AND (now() - query_start) > interval '10 saniyə';

Bu sorğu 10 saniyədən çox işləyən bütün sorğuları göstərəcək. Interval dəyərini öz tələblərinə uyğun dəyiş.

Problemli sorğuların sonlandırılması

Gəlin baxaq, necə artıq çoxdan işləyən və bazanın işinə mane olan sorğulardan qurtulmaq olar. Prosesin məcburi sonlandırılması üçün pg_terminate_backend() funksiyasından istifadə et.

Müəyyən PID-li prosesi sonlandırmaq nümunəsi:

SELECT pg_terminate_backend(12345);

Burada 12345 — pg_stat_activity-dəki pid sahəsindən prosesin identifikatorudur.

Vacibdir: Prosesin sonlandırılması düzgün başa çatmayan tranzaksiya üçün rollback yarada bilər, ona görə ehtiyatlı ol.

İndi isə, əgər avtomatik olaraq bütün "asılı qalmış" prosesləri, məsələn idle-tranzaksiyaları sonlandırmaq lazımdırsa, aşağıdakı PL/pgSQL-blokunu işlədə bilərsən. Artıq proqramlaşdırmanı öyrəndiyinə görə, loop (dövrə) anlayışı sənə tanışdır — bu, müəyyən şərt yerinə yetirilənə və ya verilənlər toplusunun işlənməsi bitənə qədər təkrarlanan konstruksiyadır:

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN 
        SELECT pid 
        FROM pg_stat_activity 
        WHERE state = 'idle in transaction' 
          AND (now() - state_change) > interval '5 dəqiqə'
    LOOP
        PERFORM pg_terminate_backend(r.pid);
    END LOOP;
END $$;

Bu dinamik həll sistemdə problemli tranzaksiyalardan təmizləmə aparmağa imkan verir. FOR dövrəsi sorğunun nəticəsində hər bir yazı üzrə keçir və tapılan hər PID üçün prosesin sonlandırılması əməliyyatını icra edir.

Tezliklə PL/pgSQL öyrənəcəyik, bir az səbr elə :P

Tranzaksiya vəziyyətinə görə filtrasiya

Bəzən sadəcə aktiv sorğunu tapmaq istəmirsən, həm də hansı bağlantıların xüsusi vəziyyətdə olduğunu bilmək istəyirsən, məsələn idle və ya idle in transaction. Bu, potensial problemləri kritik olmadan əvvəl tapmağa kömək edə bilər.

idle in transaction vəziyyətində olan tranzaksiyaları tapmaq üçün sorğu nümunəsi:

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

state_change sahəsi bu vəziyyətin nə vaxt qurulduğunu göstərir. Beləliklə, heç bir faydalı iş görməyən, amma database resurslarını bloklayan uzunmüddətli tranzaksiyaları tapa bilərsən.

Praktik tətbiq

Prod-da uzun sorğuların monitorinqi: müəyyən vaxt həddini aşan sorğuların müntəzəm monitorinqini qurub, bu barədə Slack, Telegram və ya istənilən digər bildiriş alətinə xəbər göndərə bilərsən. Bu, performans problemlərinə tez reaksiya verməyə imkan verəcək.

İnsident zamanı sorğuların analizi: server ləngiyəndə ilk növbədə pg_stat_activity-yə bax, səbəbi tap. Bu, performans problemlərinə reaksiya üçün standart protokolun olmalıdır.

Database xidməti: pg_stat_activity-nin müntəzəm analizi səmərəsiz sorğuları izləməyə və onları optimallaşdırmağa kömək edəcək (məsələn, indeks əlavə etmək və ya sorğunu yenidən yazmaq).

Monitorinq zamanı səhvlər yanlış filtrasiya və ya məlumatların düzgün şərh edilməməsi səbəbindən baş verə bilər. Məsələn, əgər aktiv vəziyyətinə görə filtrasiya edirsənsə, idle in transaction vəziyyətində olan sorğuları qaçıra bilərsən, halbuki onlar da resurs bloklanmasına səbəb ola bilər. Başqa bir səhv — prosesləri çox aqressiv sonlandırmaqdır, bu isə arzuolunmaz rollback-lərə və məlumat itkisinə gətirə bilər. Radikal addımlar atmadan əvvəl həmişə konteksti analiz et.

Əlavə monitorinq texnikaları

Daha inkişaf etmiş monitorinq üçün istifadəçilər, bazalar və ya sorğu tiplərinə görə statistika göstərən mürəkkəb sorğular yaza bilərsən. Məsələn, hər bir istifadəçinin sorğuları icra etməyə orta hesabla nə qədər vaxt sərf etdiyini və ya ən çox aktiv bağlantısı olan bazaları tapa bilərsən.

Həmçinin, log_min_duration_statement və log_statement konfiqurasiya parametrlərindən istifadə edərək uzun sorğuların avtomatik olaraq PostgreSQL log fayllarına yazılmasını qurmaq faydalıdır. Bu, performans problemlərini sonradan analiz etməyə və tətbiqlərin davranışında qanunauyğunluqları tapmağa kömək edəcək.