İndekslərin və cədvəllərin istifadəsi statistikası toplanması

Təsəvvür elə, sənin database-in böyük bir anbar kimidir. İndekslər — bu kataloqlar və siyahıdır, hansı ki, lazım olanı tez tapmağa kömək edir. Cədvəllər isə — elə rəflərdəki mallardır. Əgər indeks pis istifadə olunursa, bu o deməkdir ki, kataloq uzaq bir küncdə tozlanır və heç kim ona baxmır. Əgər cədvəl aktiv istifadə olunursa, amma strukturu pisdirsə və ya artıq məlumatlar varsa, bu bizim anbarı (database-i) yükləyir və işini ləngidir.

Analizin əsas məqsədləri:

1. İndekslərin istifadəsinin effektivliyinin qiymətləndirilməsi. Məsələn, bahalı indeksin boş-boşuna yatır? At getsin onu!
2. Oxuma və yazma əməliyyatlarının tezliyinin müəyyənləşdirilməsi. Bu, hansı cədvəllərin aktiv istifadə olunduğunu başa düşməyə kömək edir.
3. Sorguların optimizasiyası. Statistika göstərir ki, harada indeks əlavə edib və ya dəyişib, məlumatların işlənməsini sürətləndirmək olar.

pg_stat_user_indexes və pg_stat_user_tables view-ları

PostgreSQL-də statistika toplamaq üçün iki çox faydalı view var: pg_stat_user_indexes və pg_stat_user_tables. Gəlin, bunlara bir az yaxından baxaq.

pg_stat_user_indexes: indekslər necə istifadə olunur?

Əsas sahələr:

• relname — indeksin aid olduğu cədvəlin adı.
• indexrelname — indeksin adı.
• idx_scan — indeksin neçə dəfə axtarış üçün istifadə olunduğu.
• idx_tup_read — indeks vasitəsilə oxunan sətrlərin sayı.
• idx_tup_fetch — həqiqətən qaytarılan sətrlərin sayı (filtrlərdən sonra).

Sorgu nümunəsi:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Burada biz:

• indeksin çağırılma sayına (idx_scan) görə sıralayırıq ki, hansı indekslərin daha populyar olduğunu görək.
• əgər indeks demək olar ki, istifadə olunmur (idx_scan = 0), düşün: bəlkə, ümumiyyətlə lazım deyil?

Praktik tətbiq:

Tutaq ki, yeni app versiyası çıxarırsan və yeni indeks əlavə etmisən. pg_stat_user_indexes ilə yoxlaya bilərsən ki, sənin sorgun həqiqətən yeni indeksi istifadə edir, yoxsa PostgreSQL hələ də köhnə yolu seçir və sənin optimizasiya şah əsərini görmür.

pg_stat_user_tables: cədvəllər üzrə məlumatlara baxış

Əsas sahələr:

• relname — cədvəlin adı.
• seq_scan — cədvəlin ardıcıl skan edilməsi sayı (indekssiz).
• seq_tup_read — ardıcıl skan zamanı cədvəldən qaytarılan sətrlərin sayı.
• idx_scan — cədvəl üçün indeksli skan sayı.
• n_tup_ins — əlavə olunan sətrlərin sayı
• n_tup_upd — yenilənən sətrlərin sayı.
• n_tup_del — silinən sətrlərin sayı.

Sorgu nümunəsi:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

Burada nə görürük?

• Çox ardıcıl skan olunan cədvəllər (seq_scan) indeks əlavə etməyə ehtiyac olduğunu göstərir.
• Əlavə, yeniləmə və silmə əməliyyatlarının sayı cədvəldə məlumatların nə qədər tez-tez dəyişdiyini göstərir.

Praktik tətbiq: Sən users cədvəli ilə işləyirsən, burada app-in bütün istifadəçilərinin məlumatları saxlanılır. pg_stat_user_tables ilə görürsən ki, bu cədvəldə ardıcıl skanlar (seq_scan) çoxdur. Bu, siqnaldır: ən çox istifadə olunan sütunlara indeks yaratmaq vaxtıdır ki, sorgular daha sürətli işləsin.

Nümunə: real database-də indeks və cədvəl analizi

Tutaq ki, səndə orders (sifarişlər) və products (məhsullar) cədvəlləri olan database var. İstəyirsən başa düşəsən ki, cədvəllər və indekslər nə qədər effektiv istifadə olunur.

İndekslərin analizi:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Görürsən ki, orders_customer_id_idx indeksi 50 min dəfə çağırılıb, amma orders_date_idx cəmi 5 dəfə. Ola bilsin, orders_date_idx artıq lazım deyil.

Cədvəllərin analizi:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

products cədvəli daim ardıcıl skan olunur. Bu, işarədir: məhsullar kataloqunda indekslər çatışmır.

Tipik səhvlər və onlardan necə qaçmaq olar

Yeni başlayanlar üçün klassik tələ — statistikaya fikir verməməkdir. Məsələn, yeni indeks əlavə etmisən və düşünürsən: «İndi sorgular uçacaq», amma PostgreSQL onu istifadə etmir, çünki statistika avtomatik yenilənməyib. Cədvəllərdə ciddi dəyişikliklərdən sonra statistikaları əl ilə ANALYZE komandası ilə yeniləməyi unutma.

Başqa bir tipik səhv — indeksləri fanatik şəkildə əlavə etməkdir. Yadda saxla, hər indeks diskdə yer tutur və əlavə, yeniləmə, silmə əməliyyatlarını ləngidir. pg_stat_user_indexes statistikası ilə yoxla ki, indeks həqiqətən istifadə olunur, yoxsa sadəcə yer tutur.

Biliklərin praktik faydası: harada lazım olacaq?

Real development-də: əgər database ləngiyirsə, birinci növbədə cədvəllər və indekslərlə bağlı problemləri axtaracaqsan.

Müsahibədə: indeks optimizasiyası sualları — SQL-interview-ların klassikasıdır. pg_stat_user_indexes-i izah edə bilirsən? Deməli, artıq yarı yoldasan.

Database administrasiyasında: monitoring — hər gün DBA-nın rutinasıdır. Cədvəl və indeks statistikası olmadan heç nəyi yaxşılaşdıra bilməzsən.