PostgreSQL Performans Sorunlarını pg_stat_statements ile Analiz Etmek

yazar

Bir PostgreSQL sunucusu yavaşlamaya başladığında, ilk refleks genellikle “RAM ekleyelim” ya da “diski değiştirelim” oluyor. Oysa çoğu zaman asıl sorun çok daha spesifik bir yerde: kötü yazılmış ya da optimize edilmemiş bir SQL sorgusu. İşte bu noktada pg_stat_statements eklentisi hayat kurtarıcı oluyor. Bu yazıda bu eklentiyi nasıl kuracağınızı, nasıl yorumlayacağınızı ve gerçek dünya senaryolarında nasıl kullanacağınızı adım adım anlatacağım.

pg_stat_statements Nedir?

pg_stat_statements, PostgreSQL’in sunduğu bir contrib eklentisidir. Veritabanı sunucusunda çalışan her SQL sorgusunun istatistiklerini toplar ve bunları bir view üzerinden sorgulanabilir hale getirir. Kaç kez çalıştırıldı, toplam ne kadar sürdü, ortalama ne kadar sürdü, ne kadar disk I/O tüketti gibi bilgileri saklar.

Bu eklenti olmadan performans sorunlarını tespit etmek, karanlıkta el yordamıyla bir şey aramaya benzer. Eklentiyle birlikte hangi sorgunun sistemi boğduğunu dakikalar içinde bulabilirsiniz.

Kurulum ve Yapılandırma

Eklentiyi Etkinleştirme

pg_stat_statements PostgreSQL’in contrib paketinde gelir. Çoğu dağıtımda ayrıca yüklemeniz gerekir.

Debian/Ubuntu üzerinde:

sudo apt install postgresql-contrib

RHEL/CentOS/AlmaLinux üzerinde:

sudo dnf install postgresql15-contrib

Paketi yükledikten sonra postgresql.conf dosyasını düzenlemeniz gerekiyor. Bu dosya genellikle /etc/postgresql/15/main/postgresql.conf ya da /var/lib/pgsql/15/data/postgresql.conf yolunda bulunur.

sudo nano /etc/postgresql/15/main/postgresql.conf

Aşağıdaki satırları ekleyin ya da mevcut değerlerini güncelleyin:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

Bu parametrelerin anlamları şöyle:

  • shared_preload_libraries: PostgreSQL başlarken yüklenecek kütüphaneleri belirtir. pg_stat_statements buraya eklenmeden çalışmaz.
  • pg_stat_statements.max: Bellekte tutulacak maksimum benzersiz sorgu sayısı. 10000 çoğu sistem için yeterlidir.
  • pg_stat_statements.track: Hangi sorguların izleneceği. all seçeneği hem üst düzey hem de iç içe sorguları izler. top ise sadece doğrudan istemciden gelen sorguları izler.
  • pg_stat_statements.track_utility: VACUUM, ANALYZE gibi utility komutlarının da izlenip izlenmeyeceği.
  • pg_stat_statements.save: Sunucu yeniden başlatıldığında istatistiklerin kaydedilip kaydedilmeyeceği.

Yapılandırmayı uygulamak için PostgreSQL’i yeniden başlatmanız gerekiyor:

sudo systemctl restart postgresql

Eklentiyi Veritabanına Yükleme

Sunucuyu yeniden başlattıktan sonra, istatistikleri görmek istediğiniz veritabanında eklentiyi aktif etmeniz gerekiyor. Genellikle bunu postgres superuser’ı ile yaparsınız:

sudo -u postgres psql -d hedef_veritabanı -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"

Yüklenip yüklenmediğini doğrulamak için:

sudo -u postgres psql -c "SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';"

Eğer bir satır dönüyorsa eklenti başarıyla yüklenmiş demektir.

Temel Sorgular ile Analiz

En Yavaş Sorguları Bulma

Sisteme bağlanıp ilk bakışta en fazla zaman harcayan sorguları görmek için şu sorguyu kullanabilirsiniz:

SELECT
    round(total_exec_time::numeric, 2) AS toplam_sure_ms,
    calls AS cagri_sayisi,
    round(mean_exec_time::numeric, 2) AS ortalama_sure_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS yuzde,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Bu sorgu size toplam çalışma süresi en yüksek olan 20 sorguyu getirir. Dikkat: Toplam süre yüksek olan bir sorgu mutlaka kötü değildir. Yüksek calls sayısıyla gelen bir sorgu, her seferinde hızlı çalışıyor ancak çok sık çağrılıyor olabilir. Bu da optimize edilmesi gereken farklı bir problemdir.

Ortalama Süresi En Yüksek Sorgular

SELECT
    calls,
    round(total_exec_time::numeric, 2) AS toplam_ms,
    round(mean_exec_time::numeric, 2) AS ortalama_ms,
    round(stddev_exec_time::numeric, 2) AS sapma_ms,
    query
FROM pg_stat_statements
WHERE calls > 100
ORDER BY mean_exec_time DESC
LIMIT 20;

Burada calls > 100 filtresini koyuyoruz çünkü sadece bir kez çalışmış ve tesadüfen uzun sürmüş bir sorgu yanıltıcı olabilir. Gerçek anlamda sorunlu sorgular genellikle çok çağrılanlardır.

stddev_exec_time kolonuna dikkat edin. Standart sapma yüksekse bu sorgunun bazen hızlı bazen çok yavaş çalıştığı anlamına gelir. Bu durum genellikle lock contention, checkpoint zamanlaması veya veri büyümesinden kaynaklanır.

I/O Yoğun Sorgular

PostgreSQL 13 ve sonrasında pg_stat_statements disk I/O bilgilerini de tutuyor:

SELECT
    calls,
    round(total_exec_time::numeric, 2) AS toplam_ms,
    shared_blks_hit,
    shared_blks_read,
    round(
        (shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0)) * 100,
        2
    ) AS cache_hit_orani,
    query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY shared_blks_read DESC
LIMIT 20;

cache_hit_orani düşük olan sorgular (örneğin %80’in altı) diskten çok fazla veri okuyor demektir. Bu sorgular için index eklenmesi ya da work_mem artırılması çözüm olabilir.

Gerçek Dünya Senaryoları

Senaryo 1: Sabah Yavaşlama Şikayeti

Bir e-ticaret şirketinde çalışıyorsunuz. Her sabah saat 09:00-10:00 arasında uygulama yavaşlıyor. Kullanıcılar şikayet ediyor, DBA olarak masanıza geliyorlar. Önce pg_stat_statements istatistiklerini sıfırlayıp o zaman diliminde ne olduğuna bakıyorsunuz.

İstatistikleri sıfırlamak için:

sudo -u postgres psql -d eticaret_db -c "SELECT pg_stat_statements_reset();"

Saat 09:00’da sıfırladınız, 10:00’da tekrar baktınız. Şu sorguyu çalıştırdınız:

SELECT
    round(total_exec_time::numeric, 2) AS toplam_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS ort_ms,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sonuçlarda şunu gördünüz: Bir sipariş raporlama sorgusu 45 dakika içinde 3.800 kez çalışmış ve her biri ortalama 2.3 saniye sürmüş. Bu sorgu toplam sürenin %67’sini tek başına tüketiyor.

Sorguya baktığınızda şunu görüyorsunuz: WHERE created_at >= '2024-01-01' gibi bir koşul var ancak created_at kolonu üzerinde index yok. EXPLAIN ANALYZE ile doğruluyorsunuz ve sekans scan görüyorsunuz. Bir index ekliyorsunuz:

sudo -u postgres psql -d eticaret_db -c "CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders(created_at);"

CONCURRENTLY seçeneği production’da index oluştururken tabloyu kilitlemez. Ertesi sabah sorun ortadan kalkmış oluyor.

Senaryo 2: Belirli Bir Kullanıcının Sorguları

Büyük bir sistemde hangi kullanıcının probleme neden olduğunu bulmak istediğinizde:

SELECT
    pg_roles.rolname AS kullanici,
    count(*) AS sorgu_cesidi,
    round(sum(total_exec_time)::numeric, 2) AS toplam_ms,
    round(avg(mean_exec_time)::numeric, 2) AS genel_ort_ms
FROM pg_stat_statements
JOIN pg_roles ON pg_stat_statements.userid = pg_roles.oid
GROUP BY pg_roles.rolname
ORDER BY toplam_ms DESC;

Bu sorgu size hangi kullanıcının (uygulama kullanıcısı, ETL kullanıcısı, raporlama kullanıcısı gibi) sistemi ne kadar yüklediğini gösterir. Bir keresinde raporlama ekibinin saat 14:00’te çalıştırdığı büyük bir SELECT sorgusu tüm OLTP trafiğini sekteye uğratıyordu. Bu görünüm sayesinde tespit edip raporlama sorgularını statement_timeout ile sınırladık.

Senaryo 3: N+1 Sorgu Problemi

Bir ORM kullanan uygulama bazen N+1 problemi yaratır. Yani tek bir liste çekmek için yüzlerce sorgu çalışır. Bunu pg_stat_statements‘tan görmek mümkün:

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS ort_ms,
    round(total_exec_time::numeric, 2) AS toplam_ms,
    query
FROM pg_stat_statements
WHERE
    calls > 10000
    AND mean_exec_time < 10
ORDER BY calls DESC
LIMIT 10;

Bu sorgu çok hızlı ama çok sık çalışan sorguları getirir. Örneğin SELECT * FROM users WHERE id = $1 sorgusunun 50.000 kez çalıştığını gördüğünüzde, uygulamada kullanıcıları tek tek çeken bir döngü olduğunu anlıyorsunuz. Geliştirici ekiple paylaşıyorsunuz, JOIN ya da batch yükleme ile sorunu çözüyorlar.

İleri Düzey Analiz Teknikleri

Sorgu Normalizasyonu

pg_stat_statements benzer sorguları otomatik olarak normalize eder. Yani WHERE id = 1, WHERE id = 2, WHERE id = 3 gibi sorgular tek bir satırda WHERE id = $1 olarak gösterilir. Bu harika çünkü gerçek anlamda hangi sorgu kalıbının sorunlu olduğunu gösterir.

Ancak bazen farklı sorgular aynı hash altında toplanabilir. queryid kolonunu kullanarak spesifik bir sorguyu takip edebilirsiniz:

SELECT
    queryid,
    calls,
    round(total_exec_time::numeric, 2) AS toplam_ms,
    round(mean_exec_time::numeric, 2) AS ort_ms,
    rows,
    query
FROM pg_stat_statements
WHERE query ILIKE '%siparisler%'
ORDER BY total_exec_time DESC;

Periyodik Snapshot Alma

Anlık görüntü almak yerine zaman içinde değişimi izlemek için bir monitoring tablosu oluşturabilirsiniz:

sudo -u postgres psql -d monitoring_db << 'EOF'
CREATE TABLE IF NOT EXISTS pss_snapshots (
    snapshot_time TIMESTAMPTZ DEFAULT NOW(),
    queryid BIGINT,
    calls BIGINT,
    total_exec_time DOUBLE PRECISION,
    mean_exec_time DOUBLE PRECISION,
    rows BIGINT,
    query TEXT
);
EOF

Ardından bir cron job ile periyodik snapshot alabilirsiniz:

crontab -e
# Her 15 dakikada bir snapshot al
*/15 * * * * /usr/bin/psql -U postgres -d monitoring_db -c "INSERT INTO pss_snapshots (queryid, calls, total_exec_time, mean_exec_time, rows, query) SELECT queryid, calls, total_exec_time, mean_exec_time, rows, LEFT(query, 500) FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 100;" >> /var/log/pg_snapshot.log 2>&1

Bu yöntemle bir şikayet geldiğinde geçmişe dönük olarak hangi zaman diliminde hangi sorgunun patlama yaptığını görebilirsiniz.

Blocking Sorgularla Birlikte Analiz

pg_stat_statements tek başına tam resmi vermez. Yavaş sorgularla birlikte lock durumuna da bakmanız gerekir:

SELECT
    psa.pid,
    psa.state,
    psa.wait_event_type,
    psa.wait_event,
    round(pss.mean_exec_time::numeric, 2) AS ort_ms,
    psa.query
FROM pg_stat_activity psa
LEFT JOIN pg_stat_statements pss
    ON psa.query_id = pss.queryid
WHERE psa.state != 'idle'
ORDER BY pss.mean_exec_time DESC NULLS LAST;

Bu sorgu hem şu anda çalışan hem de geçmiş istatistikleri birleştirerek hangi aktif sorgunun tarihsel olarak yavaş olduğunu gösterir.

pg_stat_statements Bakımı

İstatistikleri Sıfırlama

Bir optimizasyon yaptıktan sonra etkisini ölçmek için istatistikleri sıfırlamanız gerekebilir. Tüm istatistikleri sıfırlamak yerine belirli bir sorgunun istatistiklerini de sıfırlayabilirsiniz (PostgreSQL 14+):

# Tüm istatistikleri sıfırla
sudo -u postgres psql -c "SELECT pg_stat_statements_reset();"

# Belirli bir kullanıcının istatistiklerini sıfırla (PostgreSQL 14+)
sudo -u postgres psql -c "SELECT pg_stat_statements_reset(userid) FROM pg_roles WHERE rolname = 'app_user';"

Bellek Kullanımını İzleme

pg_stat_statements.max değeri çok büyük tutulursa shared memory tüketimi artar. Mevcut durumu kontrol etmek için:

sudo -u postgres psql -c "
SELECT
    dealloc,
    stats_reset
FROM pg_stat_statements_info;
"

dealloc değeri sürekli artıyorsa pg_stat_statements.max değeriniz yetersiz demektir. Sorgular bellekten düşürülüyor ve önemli istatistikler kayboluyordur. Bu değeri artırmanız gerekir.

Pratik Bir Monitoring Script’i

Tüm bu sorguları bir araya getiren ve her gün otomatik rapor üreten basit bir script yazalım:

#!/bin/bash
# /usr/local/bin/pg_performance_report.sh

DB_NAME="uygulama_db"
DB_USER="postgres"
RAPOR_DOSYA="/var/log/postgresql/perf_rapor_$(date +%Y%m%d).txt"

echo "=== PostgreSQL Performans Raporu: $(date) ===" > $RAPOR_DOSYA
echo "" >> $RAPOR_DOSYA

echo "--- En Cok Zaman Harcayan 10 Sorgu ---" >> $RAPOR_DOSYA
psql -U $DB_USER -d $DB_NAME -c "
SELECT
    round(total_exec_time::numeric/1000, 2) AS toplam_sn,
    calls,
    round(mean_exec_time::numeric, 2) AS ort_ms,
    LEFT(query, 120) AS sorgu
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
" >> $RAPOR_DOSYA 2>&1

echo "" >> $RAPOR_DOSYA
echo "--- Cache Hit Orani Dusuk Sorgular ---" >> $RAPOR_DOSYA
psql -U $DB_USER -d $DB_NAME -c "
SELECT
    calls,
    shared_blks_read,
    round(
        (shared_blks_hit::numeric / nullif(shared_blks_hit + shared_blks_read, 0)) * 100, 2
    ) AS cache_hit,
    LEFT(query, 120) AS sorgu
FROM pg_stat_statements
WHERE calls > 100
    AND (shared_blks_hit + shared_blks_read) > 1000
ORDER BY cache_hit ASC
LIMIT 10;
" >> $RAPOR_DOSYA 2>&1

echo "Rapor olusturuldu: $RAPOR_DOSYA"

Bu script’i her gece çalıştırmak için cron’a ekleyebilirsiniz:

chmod +x /usr/local/bin/pg_performance_report.sh
echo "0 7 * * * postgres /usr/local/bin/pg_performance_report.sh" | sudo tee /etc/cron.d/pg_perf_report

Dikkat Edilmesi Gereken Noktalar

pg_stat_statements kullanırken birkaç önemli noktayı aklınızda tutun:

  • Overhead: Eklenti küçük de olsa bir overhead yaratır. Ancak production ortamlarında bu overhead genellikle kabul edilebilir düzeydedir ve kazancı çok daha fazladır.
  • Güvenlik: pg_stat_statements view’ı tüm kullanıcılara açıktır ancak superuser olmayan kullanıcılar sadece kendi sorgularının tam metnini görebilir. Diğer kullanıcıların sorgu metinleri şeklinde görünür.
  • Parametre sıfırlama: Bazı monitoring araçları periyodik olarak istatistikleri sıfırlayabilir. Bunu bilen biri eğer kasıtlı olarak sıfırlarsa geçmiş verileri kaybedersiniz. Bu yüzden snapshot tablosu tutmak önemlidir.
  • PostgreSQL versiyonu farkları: Kolon isimleri ve içerikleri PostgreSQL versiyonları arasında değişebilir. PostgreSQL 13’te total_time iken 14’te total_exec_time oldu. Script yazarken versiyona dikkat edin.
  • track_io_timing parametresi: I/O sürelerini de görmek istiyorsanız postgresql.conf‘da track_io_timing = on yapmanız gerekir. Bu ayar varsayılan olarak kapalıdır çünkü bazı platformlarda hafif bir overhead yaratabilir.

Sonuç

pg_stat_statements, PostgreSQL performans analizi için vazgeçilmez bir araçtır. Kurulumu basit, öğrenme eğrisi düşük, ancak sunduğu bilgi derinliği oldukça yüksek. Hangi sorgunun sisteminizi boğduğunu, hangi kullanıcının aşırı yük yarattığını, hangi sorguların disk yerine bellekten beslenmesi gerektiğini net olarak görebilirsiniz.

Benim kişisel tavsiyem: Bu eklentiyi kurmak için bir performans sorunu beklemeyin. Şu anda sisteminizde etkin değilse, bu yazıyı okuduktan hemen sonra kurun. Baseline istatistikler toplamaya başlayın, periyodik snapshot alın ve zaman içinde neyin değiştiğini gözlemleyin. Sorun patlak verdiğinde elinizdeki tarihsel verilerle çok daha hızlı müdahale edebilirsiniz.

Performans sorunları çoğunlukla birikerek gelir. Bugün 200ms olan bir sorgu, veri büyüdükçe 5 saniyeye çıkabilir. pg_stat_statements sizi bu gelişimden haberdar eder, siz de önlem almaya zamanınız olur. Sürpriz bir gece nöbeti tutmamak için iyi bir izleme altyapısı kurmak her zaman daha iyidir.

Yorum yapın