Veritabanı Query Profiling: Sistematik Optimizasyon Yolculuğu

Yavaş veritabanı query’leri, SaaS ürünlerinde altyapı maliyetlerinin kontrolden çıkmasının en yaygın nedenidir: 8 saniye süren ürün aramaları ve 45 saniyede yüklenen dashboard’lar semptomlar, kök neden değildir. Sunucu eklemek semptomları bastırırken faturayı büyütür. Önce profiling yapın, yalnızca profiling’in açıklayamadığı kısmı ölçekleyin. PostgreSQL tarafında bu pg_stat_statements ve EXPLAIN demek; MongoDB tarafında database profiler ve pipeline üzerinde explain planı demek.

Çok Kiracılı Bir SaaS’ta Semptomlar#

PostgreSQL (işlemsel veri için) ve MongoDB (analitik ve doküman depolaması için) kullanan çok kiracılı bir SaaS platformunu ele alalım. Mimari kağıt üzerinde sağlam görünür. Pratikte ise ürün arama query’leri yoğun trafikte 8 saniye sürer, analitik dashboard query’leri 45 saniye ve üzerinde yüklenir; özellik müşteri karşısında kullanılamaz hale gelir.

İki sayı da semptomdur. İsteğin yavaş olduğunu söylerler. Gecikmeyi hangi planın, hangi index’in ya da hangi collection scan’inin ürettiğini söylemezler; bu cevap yalnızca veritabanlarının kendisini enstrümante etmekten çıkar.

Başarısız MongoDB Migration’ı#

Yaygın bir hata kalıbı: MongoDB’nin tüm ölçeklendirme sorunlarını çözeceği varsayımıyla ana ürün kataloğunun PostgreSQL’den MongoDB’ye taşınması. NoSQL’in büyümeyi daha iyi kaldıracağı ve doküman depolamasının API response’larına doğal biçimde oturacağı gerekçesiyle alınan bu karar çoğunlukla birkaç ay sonra gözle görülür biçimde büyümüş bir AWS faturasıyla sonuçlanır.

SQL düşüncesini NoSQL dünyasına taşımak sorunun temelidir: uygulama, MongoDB aggregation pipeline’larında yapılması gereken karmaşık join’leri memory’de gerçekleştirir. Query performansı taşınmadan önceki halinden daha kötü hale gelir.

Veritabanı teknolojisi migration’ları kötü tasarlanmış query’leri sihirli bir şekilde düzeltmez. Önce erişim pattern’larını anlamak, sonra doğru aracı seçmek bu maliyetli rotadan kaçındırır.

Profiling Altyapısı#

Sonraki adım her iki motoru da enstrümante etmek: yavaş iş tahmin edilmek yerine kayda geçsin.

PostgreSQL Profiling Stack#

100ms’den fazla süren her şeyi yakalamak için detaylı query logging etkinleştirilir:

-- Kapsamlı query logging'i etkinleştir
ALTER SYSTEM SET log_min_duration_statement = 100;
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;
ALTER SYSTEM SET log_lock_waits = on;
SELECT pg_reload_conf();

pg_stat_statements ile gerçek tablo ortaya çıkar:

-- Kaynak yiyen query'leri bul
SELECT 
    substring(query, 1, 100) as query_start,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS cache_hit_percent
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 20;

PostgreSQL 12 ve öncesinde bu kolonların adı total_time ve mean_time; isim değişikliği pg_stat_statements 1.8 ile geldi.

Bu listeyi kullanışlı kılan şey, tek bir çağrının en kötü süresine göre değil toplam süreye göre sıralamaktır. En üstteki kayıt genelde tek başına masum görünen bir query’dir: birkaç yüz milisaniye, on binlerce çağrı ve neredeyse her çağrının diske gittiğini gösterecek kadar düşük bir cache hit oranı. Katalog örneğinde bu, biri kategoriye göre filtre uyguladığında 50 milyon satırlık products tablosunu baştan sona tarayan bir sequential scan’dir.

MongoDB Profiler Yapılandırması#

MongoDB için profiler 100ms üzerindeki operasyonları yakalar:

// 100ms'den yavaş operasyonları kaydet (level 1; level 2 her şeyi kaydeder)
db.setProfilingLevel(1, { slowms: 100 });

// Pattern'ları analiz et
db.system.profile.aggregate([
  { $match: { ns: "myapp.products" } },
  { $group: {
      _id: "$command.find",
      count: { $sum: 1 },
      avgDuration: { $avg: "$millis" },
      maxDuration: { $max: "$millis" },
      totalDuration: { $sum: "$millis" }
    }
  },
  { $sort: { totalDuration: -1 } },
  { $limit: 10 }
]);

Toplam süreye göre gruplamak genellikle diğer her şeyi gölgede bırakan tek bir pipeline’ı ortaya çıkarır. Sorumlu çoğunlukla uygulama seviyesindeki bir join’dir: servis dokümanları kendine çeker ve memory’sinde birleştirir, MongoDB’nin aggregation framework’ü ise boşta kalır.

Eksik Composite Index#

Yoğun alışveriş trafiğinde ürün arama performansı ciddi biçimde düşer; sonuçların yüklenmesi 8 saniye ve üzerine çıktığında kullanıcılar sepetlerini terk eder.

PostgreSQL logları kök nedeni gösterir:

-- Katil query (basitleştirilmiş)
SELECT p.*, c.name as category_name 
FROM products p 
JOIN categories c ON p.category_id = c.id 
WHERE p.active = true 
  AND p.category_id = $1 
  AND p.price BETWEEN $2 AND $3 
ORDER BY p.rating DESC, p.created_at DESC;

EXPLAIN planı, her arama için 50 milyon ürün üzerinde sequential scan yapıldığını gösterir. category_id ve price üzerinde ayrı index’ler vardır; bu filtre kombinasyonunun ihtiyaç duyduğu composite index yoktur.

CREATE INDEX CONCURRENTLY index’i write’ları bloklamadan kurar, dolayısıyla maintenance window gerekmez. Transaction bloğu içinde çalışmaz ve başarısız bir build geride düşürülmesi gereken invalid bir index bırakır:

CREATE INDEX CONCURRENTLY idx_products_category_price_rating 
ON products (category_id, price, rating DESC, created_at DESC) 
WHERE active = true;

Arama süresi saniyeler seviyesinden birkaç yüz milisaniyeye iner; çünkü planner 50 milyon satırı taramak yerine index’in yalnızca eşleşen dilimini okur. Sıralama planda kalır: price bir aralık predicate’i olduğu için index satırları rating’den önce price’a göre döndürür, PostgreSQL de geleni yine sıralar. Girdisi 50 milyon yerine birkaç bin satır olduğunda bu sıralama ucuzdur. Sıralamayı tamamen kaldırmak farklı bir kolon sırası ister, (category_id, rating DESC, created_at DESC, price); çünkü sıralama kolonlarından önce yalnızca eşitlik predicate’leri gelebilir. Bu sıra kategorideki tüm rating’leri okur ve price’ı satır satır filtreler, dolayısıyla asıl kazancını query bir LIMIT taşıdığında verir.

Bedeli write tarafındadır. Her ürün insert’ü veya update’i artık bir index daha maintain eder; partial index bile active bayrağı ya da indexlenen herhangi bir kolon değiştiğinde bu maliyeti öder. Index’i göndermeden önce kendi yükünde write latency farkını ölç; okuma kazancı ancak write bütçesi onu emiyorsa anlamlıdır.

Join’leri Aggregation Pipeline’a Taşımak#

PostgreSQL işin yalnızca yarısı. SQL tarafı düzeldikten sonra MongoDB tarafındaki analitik çoğu zaman yavaş kalır ve 30 saniye ve üzerinde yüklenen bir dashboard’ı kullanıcılar terk eder.

Sorun veriyi düşünme biçimindedir. Tipik hatalı Node.js yaklaşımı:

// Yanlış yol - uygulama seviyesinde join'ler
const users = await User.find({ active: true });
const userIds = users.map(u => u._id);
const orders = await Order.find({ userId: { $in: userIds } });
const analytics = users.map(user => {
  const userOrders = orders.filter(o => o.userId === user._id);
  const totalRevenue = userOrders.reduce((sum, o) => sum + o.total, 0);
  return {
    userId: user._id,
    totalRevenue,
    orderCount: userOrders.length,
    avgOrderValue: userOrders.length > 0 ? totalRevenue / userOrders.length : 0
  };
});

Bu yaklaşım milyonlarca dokümanı uygulama memory’sine yükler ve join’i JavaScript’te yapar; maliyet sonucun boyutuyla değil, collection’ların boyutuyla ölçeklenir.

Aynı işi aggregation pipeline’da çalıştırmak, işlemi verinin yanında tutar:

// Doğru yol - veritabanı seviyesinde aggregation
const analytics = await User.aggregate([
  { $match: { active: true } },
  { $lookup: {
      from: "orders",
      localField: "_id",
      foreignField: "userId",
      as: "orders"
    }
  },
  { $project: {
      userId: "$_id",
      totalRevenue: { $sum: "$orders.total" },
      orderCount: { $size: "$orders" },
      avgOrderValue: { 
        $cond: [
          { $gt: [{ $size: "$orders" }, 0] },
          { $divide: [{ $sum: "$orders.total" }, { $size: "$orders" }] },
          0
        ]
      }
    }
  }
]);

Yalnızca project edilen alanlar hat üzerinden geçer ve join index’lerin bulunduğu yerde yapılır. Önce foreign field’ı indexle: orders.userId üzerinde index yoksa $lookup her girdi dokümanı için scan’e düşer ve pipeline versiyonu, yerine geçtiği koddan daha yavaş olabilir. Stage başına 100MB memory limitini de gözet; allowDiskUse geniş bir $lookup’ın tamamen hata vermesini önler, bedeli diske taşmadır.

Performans İzleme Altyapısı#

Regresyonları kullanıcılara ulaşmadan yakalamak için kapsamlı bir monitoring sistemi kurulur:

interface DatabaseMetrics {
  postgresql: {
    activeConnections: number;
    queryDuration: PercentileMetrics;
    cacheHitRatio: number;
    indexUsage: IndexEfficiency[];
    lockWaitTimes: Duration[];
  };
  
  mongodb: {
    operationCounts: OperationType[];
    queryExecutionStats: ExecutionStats;
    indexEffectiveness: IndexMetrics[];
    shardingBalance: ShardDistribution;
  };
  
  infrastructure: {
    cpuUtilization: number;
    memoryUsage: MemoryMetrics;
    diskIOPS: IOMetrics;
    networkLatency: NetworkStats;
  };
}

// Özel query performance tracker
class QueryPerformanceTracker {
  private metrics = new Map<string, QueryMetrics>();
  
  async trackQuery(query: string, duration: number, database: 'postgres' | 'mongodb') {
    const querySignature = this.normalizeQuery(query);
    const existing = this.metrics.get(querySignature) || {
      count: 0,
      totalDuration: 0,
      maxDuration: 0,
      database
    };

    // Yeni örneği eklemeden önce baseline ile karşılaştır
    const previousMax = existing.maxDuration;

    existing.count++;
    existing.totalDuration += duration;
    existing.maxDuration = Math.max(previousMax, duration);

    this.metrics.set(querySignature, existing);

    // Regresyonda alert
    if (previousMax > 0 && duration > previousMax * 1.5) {
      await this.alertPerformanceRegression(querySignature, duration);
    }
  }
}

Değer imzadadır. Query metninden parametreleri normalize etmek aynı ifadeyi çağrılar arasında karşılaştırılabilir kılar; böylece pull request’te eklenen indexsiz bir varyant, loglara gömülmüş tek bir yavaş istek olarak değil, kayan bir dağılım olarak görünür. Kontrolü çalışan maksimuma değil saklanan bir baseline’a karşı yapmak da mekanizmanın kendi kendini susturmasını engeller.

Veritabanı Optimizasyonunun Ekonomisi#

Veritabanı faturalarının büyümesinin nedeni çoğunlukla query işidir. Sequential scan IOPS yakar ve buffer cache’ten işe yarar sayfaları atar, düşük cache hit oranı okumaları tekrar diske iter, ikisi birden instance sınıfını yukarı çeker.

Scan’i kaldırmak bu zinciri belirli bir sırayla tersine çevirir. Önce CPU ve IO düşer. Sadece query yükünü emmek için var olan read replica’lar kaldırılabilir hale gelir. Instance sınıfı ve Atlas katmanı en sonda iner; çünkü yeniden boyutlandırma yükü sürekli takip eden bir şey değil, bir maintenance aksiyonuna bağlı basamak fonksiyonudur. Query düzeltmesinden sonra herhangi bir şeyi küçültmeden önce en az bir tam haftalık trafik döngüsünü ölç; yoksa artık var olmayan bir yük profiline göre boyutlandırırsın.

PostgreSQL vs MongoDB: Doğru Aracı Seçme#

Her iki sistemin optimizasyonu, hangisini ne zaman kullanacağınıza dair net bir tablo çiziyor:

PostgreSQL kullan:

• ACID uyumluluğuna ihtiyacın varsa (finansal işlemler, envanter yönetimi)
• Karmaşık JOIN operasyonları query’lerinde yaygınsa
• Öngörülebilir performans karakteristiği istiyorsan
• Takımın NoSQL konseptlerinden çok SQL’e aşinaysa
• Data tutarlılığı eventual consistency’den daha önemliyse

MongoDB kullan:

• Built-in horizontal scaling’e ihtiyacın varsa
• Data modelin sık değişiyorsa (startup pivot’ları, hızlı iterasyon)
• Büyük veri setlerinde karmaşık aggregation’lar yapıyorsan
• Doküman tabanlı query’ler uygulama objelerinle match ediyorsa
• Kabul edilebilir write latency ile mükemmel read performansına ihtiyacın varsa

Cevap net değilse varsayılan PostgreSQL olsun. jsonb, çoğu ürün kataloğu için doküman yükünü yeterince iyi karşılar; bir collection’ı sonradan PostgreSQL’den çıkarmak, ilişkisel veriyi sonradan içeri taşımaktan daha küçük bir değişikliktir. Kararı erişim pattern’larına ve tutarlılık gereksinimlerine dayandır.

İşe Yarayan Profiling Araçları#

Veritabanı profiling araçlarını değerlendirmek, hangilerinin gerçek değer sunduğunu gösterir:

PostgreSQL Araçları#

Log analizi için pgBadger:

# Kapsamlı performans raporları oluştur
pgbadger -j 4 -f stderr /var/log/postgresql/postgresql-*.log \
  --prefix '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h' \
  -o /var/www/html/pgbadger.html

pgBadger PostgreSQL loglarını aksiyon alınabilir raporlara dönüştürür; ayrı ayrı hızlı ancak frekans nedeniyle toplam sürenin büyük bölümünü tüketen query’leri tespit eder.

Percona Monitoring and Management (PMM): PMM’in Query Analytics’i her query için planıyla birlikte zaman serisi tutar; gerçek bir regresyonu trafik değişiminden ayıran şey budur. Hem PostgreSQL hem MongoDB’yi kapsadığı için tek dashboard iki motorun sorularını da yanıtlar.

MongoDB Araçları#

MongoDB Compass: Compass explain çıktısını ağaç olarak gösterir; collection scan’i ya da memory’de sıralamayı ham JSON okumadan fark etmeyi kolaylaştırır. Bir pipeline’ı göndermeden önce orada kontrol etmek bir dakika sürer.

Özel Profiling Script’leri:

// Otomatik yavaş query detection
function analyzeSlowQueries() {
  return db.system.profile.aggregate([
    { $match: { millis: { $gt: 100 } } },
    { $group: {
        _id: {
          collection: "$ns",
          operation: "$op"
        },
        count: { $sum: 1 },
        avgDuration: { $avg: "$millis" },
        maxDuration: { $max: "$millis" }
      }
    },
    { $sort: { avgDuration: -1 } }
  ]);
}

Günlük çalıştığında bir önceki penceredeki en yavaş operasyonları raporlar. system.profile capped bir collection’dır: dolup başa sarmadan okunmayan kayıt kaybolur, o yüzden ham kayıtların yerinde kalacağına güvenmek yerine özeti dışarı aktar.

Yaygın Hatalar ve Kaçınma Yolları#

”Daha Fazla Index Ekle” Tuzağı#

Index’leri sihirli çözüm sanmak bu işin en yaygın yanlış gidiş biçimidir: query yavaşsa index ekle, hala yavaşsa bir tane daha. Çok geçmeden sıcak bir tablo bir düzine index taşır, her insert hepsini günceller ve write yolunun maliyeti okumadan kazanılanı aşar.

Her index read’leri hızlandırır ama write’ları yavaşlatır. Indexing stratejisini her tablonun read/write oranına göre tasarla ve write-heavy tablolarda seçici ol. pg_stat_user_indexes hiç scan edilmeyen index’leri gösterir; onlar saf maliyettir.

Production Data Sürprizi#

10.000 tekdüze test kaydında sorunsuz çalışan bir optimizasyon, dağılımı çarpık 50 milyon kayıtlık production hakkında pek bir şey söylemez.

Planner planı istatistiklerden seçer. Satırların çoğu tek bir kiracıdaysa ya da bir status kolonu %99 aynı değerdeyse satır tahminleri yanlış çıkar, planı da onunla birlikte yanlış olur; lokalde 10ms süren bir query production’da kat kat uzun sürebilir.

Optimizasyonları production ölçeğindeki veriyle, en azından benzer dağılım karakteristiğine sahip veriyle test et. Anonimleştirilmiş bir dump’ı geri yükleyip ANALYZE çalıştırmak, herhangi bir sentetik üreticiden daha yakın bir sonuç verir.

Yanlış Metrikler Odağı#

Tekrarlayan bir hata kalıbı: kullanıcı odaklı operasyonlar yerine veritabanı merkezli metrikler ölçüldüğünde yanlış query’ler optimize edilir. Haftalar gecelik batch process’lere gider, kullanıcının beklediği arama query’leri yavaş kalır.

Önce kullanıcı deneyimini doğrudan etkileyen operasyonlara odaklan. İç raporlama yavaş olabilir; kullanıcı odaklı operasyonlar olamaz.

Önerilen Yaklaşımlar#

Business Impact Measurement ile Başla Önce en yüksek business impact’li query’leri hedefle; en yavaş query her zaman en pahalıya mal olan değildir. Günde 10.000 kez hit alan query’de 1 saniyelik iyileşme, günde bir kez hit alan query’de 10 saniyelik iyileşmeden daha değerli.

Otomatik Performance Testing Implement Et CI/CD pipeline’ına otomatik performance regression testing kur. Performance sorunlarını müşteri şikayetlerinden sonra değil, code review sırasında yakala.

Takım Eğitimine Erken Yatırım Yap Optimizasyona başlamadan önce tüm geliştirme takımını veritabanı performans temellerinde eğit. Query yazan her developer EXPLAIN planları ve temel indexing stratejilerini anlamalı.

10x Data Growth için Plan Yap Optimizasyon stratejilerini mevcut data hacminin 10 katında çalışacak şekilde tasarla. Büyüme planlamazsan bugünün hızlı query’si yarının timeout’u olur.

Bu Yaklaşım Ne Zaman Geçerli#

Ölçeklemeden önce profiling yapmak, yük query’ye bağlıysa işe yarar: instance meşguldür ama zaman kimsenin okumadığı planlara gider. Kanıt tersini gösteriyorsa doğru hamle donanımı büyütmektir. En pahalı query’leri zaten index kullanan, cache hit oranı yüksek olan ve buna rağmen IOPS’u dolduran bir yük, EXPLAIN’in verebileceğini tüketmiştir; ona daha büyük bir instance gerekir. Index bakımının kendisinin darboğaza dönüştüğü write-heavy tablolar için de aynısı geçerlidir; orada sıradaki adım index düşürmek ya da tabloyu partition’lamaktır.

Başlangıç noktası her iki durumda da aynı: pg_stat_statements ve MongoDB profiler’ı aç, tek bir çağrının en kötü süresine göre değil toplam süreye göre sırala, başka hiçbir şeye dokunmadan önce ilk iki kaydı düzelt.

Kaynaklar#

• EXPLAIN Kullanımı - PostgreSQL Belgeleri (yeni sekmede açılır) - EXPLAIN çıktısını yorumlama ve sorgu planı optimizasyonu için resmi PostgreSQL kılavuzu
• EXPLAIN Komut Referansı - PostgreSQL (yeni sekmede açılır) - ANALYZE ve BUFFERS dahil PostgreSQL EXPLAIN komutu için tam sözdizimi ve seçenekler
• pg_stat_statements - PostgreSQL Belgeleri (yeni sekmede açılır) - Tüm SQL ifadelerinin planlama ve yürütme istatistiklerini izleyen PostgreSQL eklentisi
• auto_explain - PostgreSQL Belgeleri (yeni sekmede açılır) - Yavaş sorguların yürütme planlarını otomatik olarak kayıt altına alan PostgreSQL modülü
• Use The Index, Luke (yeni sekmede açılır) - PostgreSQL, MySQL, Oracle ve SQL Server’ı kapsayan SQL indeksleme ve performans ayarlaması üzerine ücretsiz e-kitap
• Database Profiler - MongoDB Kılavuzu (yeni sekmede açılır) - Profiling seviyeleri, slowms eşiği ve capped system.profile koleksiyonunu sorgulama
• Aggregation Pipeline - MongoDB Kılavuzu (yeni sekmede açılır) - Join, gruplama ve projeksiyonu veritabanı içinde çalıştırmak için stage referansı
• Query Performansını Analiz Etme - MongoDB Kılavuzu (yeni sekmede açılır) - Bir query veya pipeline aşamasının index kullanıp kullanmadığını explain çıktısından okuma
• pgBadger (yeni sekmede açılır) - log_min_duration_statement çıktısını sıralı query raporlarına dönüştüren PostgreSQL log analiz aracı