PostgreSQL İzleme: pg_stat_activity ve pg_stat_statements

İki ayrı sistem: anlık durum ve birikimli istatistik

Kılavuz izleme görünümlerini iki gruba ayırıyor. Dinamik görünümler (pg_stat_activity, pg_stat_replication, pg_stat_wal_receiver, pg_stat_subscription, pg_stat_ssl ve pg_stat_progress_* ailesi) şu anda olan biteni gösterir. Birikimli istatistik görünümleri (pg_stat_database, pg_stat_user_tables, pg_stat_archiver, pg_stat_io gibi) ise son sıfırlamadan bu yana toplanan sayaçları tutar. Birikimli istatistikler paylaşımlı bellekte toplanır ve sunucu düzgün kapatıldığında pg_stat klasörüne kaydedilir. Ani kapanma, çökme, temel yedekten başlama veya PITR sonrasında ise bütün sayaçlar sıfırlanır.

İki ayrıntı yanlış yorumları önler. Birincisi, her süreç istatistiklerini boşta kalmadan hemen önce ve en sık saniyede bir paylaşımlı belleğe aktarır; dolayısıyla devam eden bir sorgunun etkisi sayaçlarda henüz görünmez. İkincisi, bir transaction içinde istatistik görünümünü ilk kez okuduğunuzda değerler önbelleğe alınır ve transaction bitene kadar aynı kalır. Değişimi izlemek istiyorsanız her ölçümü ayrı bir transaction'da yapın.

Toplanan bilgiler parametrelerle denetlenir: track_activities çalışan komutun izlenmesini, track_counts tablo ve indeks erişim sayaçlarını, track_io_timing blok okuma ve yazma sürelerini, track_functions kullanıcı fonksiyonlarının izlenmesini açar. Sıradan kullanıcılar başkalarının etkinliğini gizleyemesin diye bu parametreleri SET ile yalnızca süper kullanıcılar değiştirebilir.

pg_stat_activity ile oturumları görmek

Senaryomuzu üç ayrı psql oturumuyla kurduk; her birine PGAPPNAME ortam değişkeniyle bir uygulama adı verdik ki listede ayırt edilsinler. fatura_servisi bir transaction açıp stok tablosundaki 1 numaralı satırı güncelledi ve commit etmeden bekledi. stok_guncelle aynı satırı güncellemeye çalıştı. rapor ise pg_sleep(60) içeren uzun bir sorgu çalıştırdı. Dördüncü bir oturumdan baktığımızda:

SELECT pid, application_name AS uygulama, state, wait_event_type, wait_event,
       now() - xact_start AS tx_suresi, left(query, 40) AS sorgu
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND pid <> pg_backend_pid()
ORDER BY application_name;

  pid  |    uygulama    |        state        | wait_event_type |  wait_event   |    tx_suresi    |                  sorgu                   
-------+----------------+---------------------+-----------------+---------------+-----------------+------------------------------------------
 44712 | fatura_servisi | idle in transaction | Client          | ClientRead    | 00:00:05.108095 | UPDATE stok SET adet = adet - 1 WHERE ur
 30780 | rapor          | active              | Timeout         | PgSleep       | 00:00:03.055185 | SELECT pg_sleep(60), count(*) FROM satis
 28372 | stok_guncelle  | active              | Lock            | transactionid | 00:00:03.069792 | UPDATE stok SET adet = 0 WHERE urun_id =
(3 rows)

Bu üç satır, sık karşılaşılan üç durumu temsil ediyor:

• idle in transaction: oturum açık bir transaction içinde ama şu an sorgu çalıştırmıyor, istemciden komut bekliyor (ClientRead). query sütunu çalışan değil, son çalıştırılan sorguyu gösterir. Bu oturum UPDATE'in aldığı satır kilidini tutmaya devam ediyor.
• active + Timeout/PgSleep: sorgu çalışıyor ve bilinçli olarak bekliyor. Gerçek bir uzun raporda burada genellikle G/Ç veya CPU ile ilgili bekleme olayları görürsünüz.
• active + Lock/transactionid: sorgu çalışıyor ama başka bir transaction'ın bitmesini bekliyor. Kılavuz state ile wait_event sütunlarının bağımsız olduğunu vurguluyor: active durumdaki bir sorgunun bekleme olayı doluysa, sorgu sistemde bir yerde engelleniyor demektir.

Önemli sütunlar: backend_start bağlantının, xact_start mevcut transaction'ın, query_start mevcut (veya son) sorgunun başladığı zaman; state_change durumun son değiştiği an. Uzun sorguları bulmak için now() - query_start, uzun açık transaction'ları bulmak için now() - xact_start kullanılır. query metni varsayılan olarak 1024 baytta kesilir; sınırı track_activity_query_size belirler. Yetki tarafında, sıradan kullanıcılar başka rollerin oturumlarını görür ama sorgu metni gibi sütunlar boş gelir; tamamını görmek için süper kullanıcı veya pg_read_all_stats rolü gerekir.

Görünüm yalnızca istemci bağlantılarını değil, bütün sunucu süreçlerini listeler. Test kümemizde backend_type sütununa göre grupladığımızda şu dağılımı gördük:

         backend_type         | count 
------------------------------+-------
 archiver                     |     1
 autovacuum launcher          |     1
 background writer            |     1
 checkpointer                 |     1
 client backend               |     4
 io worker                    |     3
 logical replication launcher |     1
 walsummarizer                |     1
 walwriter                    |     1
(9 rows)

Üç io worker süreci, PostgreSQL: Release 18 notlarında anlatılan ve PostgreSQL 18 ile gelen asenkron G/Ç altyapısına aittir; io_method varsayılanı worker, io_workers varsayılanı 3'tür. archiver ve walsummarizer ise bu kümede WAL arşivleme ve summarize_wal açık olduğu için çalışıyor (WAL arşivleme ve PITR). Sorgularınızda backend_type = 'client backend' filtresi, yalnızca uygulama bağlantılarına bakmanızı sağlar.

Kim kimi engelliyor? pg_blocking_pids()

SELECT pid, application_name, pg_blocking_pids(pid) AS engelleyen
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

  pid  | application_name | engelleyen 
-------+------------------+------------
 28372 | stok_guncelle    | {44712}
(1 row)

pg_blocking_pids(), verilen sürecin kilidini almasını engelleyen süreçlerin dizisini döndürür. Burada stok_guncelle oturumunu engelleyen, 44712 numaralı fatura_servisi oturumu. Kilit türleri, pg_locks görünümü ve deadlock'lar kilitler ve deadlock yazısında ayrıntılı anlatılıyor; burada izleme ve müdahale tarafına odaklanıyoruz.

pg_cancel_backend ve pg_terminate_backend

İki fonksiyon iki farklı sinyal gönderir. pg_cancel_backend(pid) SIGINT ile yalnızca o anda çalışan sorguyu iptal eder; bağlantı açık kalır, istemci hata alır ve yoluna devam edebilir. pg_terminate_backend(pid) SIGTERM ile oturumu tamamen sonlandırır; açık transaction geri alınır ve tuttuğu kilitler serbest kalır. İkinci parametre olarak milisaniye cinsinden bir zaman aşımı verirseniz fonksiyon sürecin gerçekten sonlanmasını bekler; vermezseniz yalnızca sinyalin gönderildiğini bildirir.

SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE application_name = 'rapor';
-- sonuç: t
SELECT pg_terminate_backend(pid, 5000) FROM pg_stat_activity WHERE application_name = 'fatura_servisi';
-- sonuç: t

Sonuçlar oturumların kendi taraflarında şöyle göründü. rapor oturumu ERROR: canceling statement due to user request hatasını aldı. Sunucu günlüğüne fatura_servisi için FATAL: terminating connection due to administrator command yazıldı. Engelleyen oturum kapanınca bekleyen stok_guncelle hemen tamamlandı ve UPDATE 1 döndürdü. Tabloda Çay'ın adedi 0 oldu: fatura_servisi'nin commit edilmemiş değişikliği geri alındığı için yalnızca ikinci güncelleme kaldı.

Yetki kuralları şöyle: bir rol, üyesi olduğu rolün oturumlarını iptal edebilir veya sonlandırabilir; başkalarınınkiler için pg_signal_backend rolünün yetkileri gerekir. Süper kullanıcı oturumlarına yalnızca süper kullanıcılar dokunabilir. Önce pg_cancel_backend deneyin; işe yaramazsa veya oturum idle in transaction durumundaysa (iptal edilecek çalışan sorgu yoktur) pg_terminate_backend kullanın.

pg_stat_database: veritabanı düzeyinde sayaçlar

SELECT datname, numbackends, xact_commit, xact_rollback, deadlocks,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS onbellek_yuzde,
       sessions_killed
FROM pg_stat_database WHERE datname = 'ornek_izleme';

   datname    | numbackends | xact_commit | xact_rollback | deadlocks | onbellek_yuzde | sessions_killed 
--------------+-------------+-------------+---------------+-----------+----------------+-----------------
 ornek_izleme |           1 |          36 |             2 |         0 |          86.67 |               1
(1 row)

numbackends bu görünümde anlık değeri gösteren tek sütundur; geri kalanların hepsi son sıfırlamadan bu yana birikmiş toplamlardır. xact_rollback değerindeki 2, senaryodaki iki geri almayla (iptal edilen rapor sorgusu ve sonlandırılan oturumun açık transaction'ı) örtüşüyor. sessions_killed yönetici müdahalesiyle sonlandırılan oturum sayısını gösteriyor; bizim pg_terminate_backend çağrımız burada 1 olarak göründü. blks_hit, okunmak istenen bloğun PostgreSQL'in paylaşımlı tamponunda bulunduğu durumları sayar; işletim sistemi önbelleğinden gelen okumalar blks_read'e yazılır. Diğer faydalı sütunlar temp_files ve temp_bytes (sıralama ve hash için diske taşan veri), deadlocks, conflicts (yalnızca bekleme sunucularında) ve checksum_failures'dır. PostgreSQL: Release 18 notlarına göre PostgreSQL 18 ile bu görünüme paralel işçi etkinliğini gösteren parallel_workers_to_launch ve parallel_workers_launched sütunları eklendi.

Tablo düzeyinde benzer bilgiler pg_stat_user_tables (sıralı ve indeks taramaları, canlı ve ölü satır sayıları, son VACUUM ve ANALYZE zamanları) ve pg_stat_user_indexes görünümlerindedir. Ölü satırlar ve autovacuum için VACUUM ve autovacuum, replikasyon görünümleri için streaming replication yazısına bakın.

pg_stat_statements kurulumu

pg_stat_statements, PostgreSQL ile birlikte gelen bir eklentidir (contrib modülü) ve sunucuda çalışan bütün SQL komutlarının planlama ve yürütme istatistiklerini toplar. Ek paylaşımlı bellek gerektirdiği için sunucu başlarken yüklenmesi gerekir; bu da yeniden başlatma demektir. Ayrıca sorgu kimliği (query id) hesaplaması açık olmalıdır; compute_query_id varsayılan auto değerindeyken eklenti yüklendiğinde bu kendiliğinden olur. Biz ayarı ALTER SYSTEM ile yazdık:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET log_min_duration_statement = '200ms';

pg_ctl -D ana -l ana.log -w restart

SHOW shared_preload_libraries;  -- sonuç: pg_stat_statements
SHOW compute_query_id;          -- sonuç: auto
CREATE EXTENSION pg_stat_statements;

shared_preload_libraries bir liste olduğu için, başka kütüphaneler zaten yüklüyse onları da yazmanız gerekir; aksi halde ALTER SYSTEM listeyi tamamen değiştirir. Eklenti yüklendiğinde bütün veritabanlarındaki sorguları izler, ama görünüm ve fonksiyonlar yalnızca CREATE EXTENSION çalıştırdığınız veritabanında kullanılabilir hale gelir. CREATE EXTENSION, bir eklentinin nesnelerini (görünüm, fonksiyon, tip) mevcut veritabanına yükler; \dx komutu yüklü eklentileri listeler. Test kümemizde sürüm 1.12 olarak göründü.

Başlıca ayarlar: pg_stat_statements.max izlenecek en fazla farklı ifade sayısıdır (varsayılan 5000; aşılınca en az çalışanlar atılır ve pg_stat_statements_info.dealloc artar). pg_stat_statements.track varsayılan top ile yalnızca istemcinin doğrudan gönderdiği ifadeleri, all ile fonksiyon içindekileri de sayar. track_planning planlama süresini de ölçer ama kılavuz bunun fark edilir bir performans maliyeti olabileceğini belirtiyor; varsayılanı off'tur.

En pahalı sorguları bulmak

İstatistikleri pg_stat_statements_reset() ile sıfırladıktan sonra birkaç sorgu çalıştırdık: aynı toplam sorgusunu iki farklı ürün için üç kez, birkaç tekil satır sorgusu, bir UPDATE ve bir JOIN. Ardından toplam süreye göre sıraladık:

SELECT query, calls, round(total_exec_time::numeric, 2) AS toplam_ms,
       round(mean_exec_time::numeric, 2) AS ortalama_ms, rows
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
  AND query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 3;

Çıktının ilk iki kaydı (genişletilmiş görünümle, \x on):

-[ RECORD 1 ]---------------------------------------------------------------
query       | SELECT sum(adet) FROM satis WHERE urun_id = $1
calls       | 3
toplam_ms   | 103.41
ortalama_ms | 34.47
rows        | 3
-[ RECORD 2 ]---------------------------------------------------------------
query       | SELECT count(*) FROM satis s JOIN stok k USING (urun_id) WHERE s.adet > $1
calls       | 1
toplam_ms   | 33.11
ortalama_ms | 33.11
rows        | 1

İlk kayıt, eklentinin en değerli özelliğini gösteriyor: urun_id = 1 ve urun_id = 2 ile çalıştırılan sorgular, sabit değerleri $1 ile değiştirilerek tek bir kayıtta birleşti ve calls 3 oldu. Böylece binlerce farklı parametreyle çalışan tek bir yavaş sorgu kalıbını kolayca görürsünüz. Süre sütunları milisaniye cinsindendir. Hangi sütuna göre sıraladığınız sorunun türüne bağlıdır:

Kılavuz queryid değerinin sorgu metninden daha güvenilir bir kimlik olduğunu, ama kararlılığının sınırlı olduğunu da not ediyor: değer iç nesne kimliklerine bağlı olduğundan, örneğin bir tablo silinip yeniden oluşturulunca aynı metin farklı bir kimlik alabilir. Fiziksel replikasyondaki sunucularda aynı sorgunun kimliği aynı olur, logical replication'da bu garanti edilmez. PostgreSQL: Release 18 notlarına göre PostgreSQL 18'de sabit listelerinin kimlik hesabında yalnızca ilk ve son sabitin dikkate alınması ve aynı tablo adını kullanan sorguların gruplanması gibi değişiklikler yapıldı; ayrıca CREATE TABLE AS ve DECLARE sorguları izlenmeye başlandı.

Yavaş sorgu günlüğü: log_min_duration_statement

pg_stat_statements toplamları gösterir; tek tek yavaş çalıştırmaları ve hangi parametrelerle çalıştıklarını görmek için günlük gerekir. log_min_duration_statement, belirtilen süreden uzun süren her tamamlanmış ifadeyi süresiyle birlikte günlüğe yazar. Varsayılan -1 (kapalı), 0 ise bütün ifadeleri yazar. Biz 200 ms yaptık ve sunucu günlüğünde kilit beklerken uzun süren güncelleme şöyle göründü:

LOG:  duration: 16193.828 ms  statement: UPDATE stok SET adet = 0 WHERE urun_id = 1

Bu satır önemli bir ayrımı gösteriyor: ölçülen süre, sorgunun kilit beklediği zamanı da içerir. UPDATE'in kendisi hızlıdır, ama 16 saniye boyunca başka bir transaction'ı bekledi. Yavaş sorgu günlüğünde gördüğünüz her şey planlama sorunu değildir; kilit beklemelerini ayırt etmek için log_lock_waits = on ayarı, deadlock_timeout süresinden uzun kilit beklemelerini ayrıca günlüğe yazar. log_line_prefix ile günlük satırına zaman, süreç kimliği, kullanıcı ve veritabanı gibi bilgileri ekleyebilirsiniz. Değeri çok düşük tutmak yoğun sunucularda günlük hacmini hızla büyütür; bu yüzden sınırı iş yükünüze göre seçin.

Diğer kullanışlı görünümler

Sık yapılan hatalar

• state = 'idle in transaction' olan oturumları zararsız sanmak; kilit tutarlar ve VACUUM'un eski satırları temizlemesini engelleyebilirler.
• query sütununu her zaman çalışan sorgu sanmak; active dışındaki durumlarda son çalıştırılan sorgudur.
• Bir transaction içinde istatistik görünümünü tekrar tekrar okuyup değerlerin değişmesini beklemek; değerler transaction sonuna kadar önbellekte kalır.
• shared_preload_libraries ayarlandıktan sonra yeniden başlatmayı veya CREATE EXTENSION adımını unutmak; görünüm bulunamaz.
• ALTER SYSTEM SET shared_preload_libraries ile mevcut listeyi yanlışlıkla silmek.
• Sayaçların sıfırlandığını hesaba katmamak; ani kapanma ve kurtarma sonrası birikimli istatistikler sıfırdan başlar, stats_reset sütununa bakın.

Sık Sorulan Sorular

Uzun süren sorguları nasıl listelerim?

SELECT pid, now() - query_start AS sure, query FROM pg_stat_activity WHERE state = 'active' AND backend_type = 'client backend' ORDER BY sure DESC; çalışan sorguları süreye göre sıralar. Açık kalmış transaction'lar için aynı sorguda xact_start kullanın.

pg_cancel_backend ile pg_terminate_backend arasındaki fark nedir?

İlki yalnızca çalışan sorguyu iptal eder, bağlantı açık kalır. İkincisi bağlantıyı kapatır, açık transaction geri alınır ve kilitler bırakılır.

pg_stat_statements performansı etkiler mi?

Eklenti ek paylaşımlı bellek kullanır ve her ifade için sayaç günceller. Kılavuz özellikle track_planning açıkken aynı yapıdaki sorguları çok sayıda eşzamanlı bağlantı çalıştırdığında belirgin bir maliyet olabileceğini söylüyor; bu yüzden o ayar varsayılan olarak kapalıdır.

İstatistikleri nasıl sıfırlarım?

pg_stat_statements_reset() yalnızca eklentinin istatistiklerini sıfırlar ve karşılaştırmalı ölçümden önce güvenle kullanılabilir. pg_stat_reset() ise mevcut veritabanının birikimli sayaçlarını sıfırlar; kılavuz bunun autovacuum'un ne zaman VACUUM veya ANALYZE çalıştıracağına karar verirken kullandığı sayaçları da sıfırladığını, bunun tablo şişmesine ya da eski planlayıcı istatistiklerine yol açabileceğini uyarıyor ve sonrasında veritabanı genelinde ANALYZE öneriyor.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: PostgreSQL öğrenme rehberi · EXPLAIN ANALYZE · Kilitler ve deadlock · VACUUM ve autovacuum · postgresql.conf · Performans optimizasyonu