Yavaş sorğuların izlənməsi pg_stat_statements ilə

pg_stat_statements — PostgreSQL-də built-in extension-dur, hansı ki, bazada hansı sorğuların baş verdiyini və necə işlədiyini izləməyə imkan verir. Əslində, bu səssiz, amma diqqətli bir köməkçidir: hansı SQL-sorğular işlədilib, nə qədər vaxt aparıb, neçə dəfə çağırılıb və sistemi nə qədər yükləyib — hamısını qeyd edir.

Bəs bu nəyə lazımdır? Birincisi, problemli sorğuları tapmaq üçün. Bəzən baza bir dənə "pis adam"a görə yox, onlarla eyni ağır sorğuya görə yavaşlayır, hansılar ki, çox tez-tez işlədilir. İkincisi, statistika göstərir ki, hansı sorğular resursları — prosessor, yaddaş, disk — daha çox yeyir. Həm də başa düşə bilərsən ki, index-lər planladığın kimi işləyir ya yox: bəlkə hardasa ümumiyyətlə istifadə olunmur, hardasa isə əksinə, çatışmır.

pg_stat_statements sayəsində artıq təxmin etməyə ehtiyac yoxdur, real rəqəmləri görürsən — və onların əsasında nəticə çıxarıb optimallaşdırma edə bilərsən.

Yavaş sorğuları necə tapmaq olar?

İndi ən maraqlı hissə başlayır! pg_stat_statements cədvəlindən istifadə edərək, uzun müddət işləyən və ya serveri çox yükləyən sorğuları tapa bilərik.

Əsas ideya:

pg_stat_statements cədvəlində hər bir sətr bir sorğuya aid statistikadır. Sorğular mətninə görə (yəni, query sahəsi) qruplaşdırılır və hər biri üçün aşağıdakı metriklər hesablanır:

• total_time — sorğunun icra olunma müddətinin cəmi, millisekundla.
• calls — sorğunun neçə dəfə icra olunduğu.
• mean_time — sorğunun orta icra müddəti (total_time / calls).
• rows — sorğunun qaytardığı sətr sayı.

Sadə analiz nümunəsi

Gəlin, orta icra müddətinə görə ən yavaş sorğuları tapaq:

SELECT
    query,
    mean_time,
    calls,
    rows
FROM
    pg_stat_statements
ORDER BY
    mean_time DESC
LIMIT 5;

Bu sorğu ən uzun işləyən TOP-5 sorğunu göstərəcək. mean_time sahəsinə fikir ver: əgər orada millisekundla 500-1000-dən çox rəqəmlər varsa, deməli bu sorğuları optimallaşdırmaq lazımdır.

Yavaş sorğuların analizi nümunəsi

Gəlin bir nümunəyə baxaq:

Əvvəlki sorğunun nəticəsi belədir:

Nə görürük?

orders cədvəlinə sorğu: çox nadir işlədilir (cəmi 10 dəfə), amma hər dəfə 10 min sətr qaytarır. Yəqin ki, cədvəl çox böyükdür və sorğu index istifadə etmir.

products cədvəlinə sorğu: milyonlarla dəfə çağırılır, bəlkə də tətbiqdə dövrün içindədir. Hər dəfə cəmi 100 sətr qaytarır, amma tez-tez işlədiyinə görə bu sorğu da problem ola bilər.

customers cədvəlinə sorğu: tez işləyir (543 ms), amma çox tez-tez çağırılır.

Yavaş sorğuların optimallaşdırılması

Artıq problemli sorğuları tapdıqdan sonra, onların icra planına EXPLAIN ANALYZE ilə baxmaq lazımdır. Məsələn, orders cədvəlinə sorğu üçün:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'new';

Nə görə bilərik?

Seq Scan: əgər sorğu ardıcıl skanlama istifadə edirsə, index əlavə etmək lazımdır:

CREATE INDEX idx_orders_status ON orders (status);

Filtrasiya problemləri: əgər sorğu çoxlu sətr qaytarırsa, sorğunun öz mətninə bax. Ola bilər əlavə şərtlər əlavə etmək və ya nəticəni limitləmək lazımdır:

SELECT * FROM orders WHERE status = 'new' LIMIT 100;

İcra müddəti statistikası çıxarışı

Bəzən problemli sorğular o qədər də aydın olmur. Məsələn, tez-tez funksiyaları və ya subquery-ləri çağıran sorğular. Belə hallarda total_time sütununa baxmaq rahatdır:

SELECT
    query,
    total_time,
    calls,
    mean_time
FROM
    pg_stat_statements
ORDER BY
    total_time DESC
LIMIT 10;

Bu sorğu ümumi icra müddətinə görə ən "bahalı" sorğuları göstərəcək.

İndeksləşdirmənin optimallaşdırılması

Çox vaxt yavaş sorğular lazımi index-lərin olmaması ilə bağlıdır. pg_stat_statements istifadə et ki, hansı sorğuların index istifadə etmədiyini başa düşəsən. Əgər eyni filter-lə (məsələn, status sahəsinə görə) çoxlu yavaş sorğu görürsənsə, uyğun index əlavə et:

CREATE INDEX idx_orders_status ON orders (status);

Bundan sonra sorğunun performansını yenidən EXPLAIN ANALYZE ilə yoxla.

pg_stat_statements istifadə edərək, sorğuların performansını effektiv izləyə, "dar boğazları" tapa və verilənlər bazanın performansını yaxşılaşdıra bilərsən. Unutma, sorğuları nə qədər tez analiz etməyə başlasan, bütün sistemin işini optimallaşdırmaq bir o qədər asan olacaq.