Veritabanı Performans Ayarlama: Parametre Duyarlı Sorgu Planları

SQL Eğitimi İçin İlk Adım: Parametre Dağılımını Ölçmek

Parametre duyarlı plan problemi, sorgunun metninden çok parametrenin seçiciliğinden doğar. Örneğin orders tablosunda tenant_id=42 için 20 satır, tenant_id=1 için 8 milyon satır varsa aynı WHERE tenant_id = $1 filtresi iki farklı erişim yoluna ihtiyaç duyabilir. Veritabanı eğitimi kapsamında ilk yapılacak iş, ortalama süre yerine varyasyonu ölçmektir. PostgreSQL'de pg_stat_statements eklentisini shared_preload_libraries üzerinden yükleyip sorgu kimliğine göre mean_exec_time ile stddev_exec_time değerlerini karşılaştırın. Yüksek standart sapma, aynı normalize sorgunun farklı parametrelerle farklı maliyet profili ürettiğini gösterir.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT queryid,
       calls,
       mean_exec_time,
       stddev_exec_time,
       rows,
       shared_blks_hit,
       shared_blks_read
FROM pg_stat_statements
WHERE query ILIKE '%from orders%'
ORDER BY stddev_exec_time DESC
LIMIT 20;

Önce-sonra kıyasını rastgele uygulama trafiğiyle değil, iki temsilci parametre sınıfıyla yapın: küçük tenant ve büyük tenant. pgbench ile her iki sınıf için ayrı senaryo çalıştırılabilir. Her koşuda aynı eşzamanlılık, aynı süre ve sıcak veya soğuk önbellek durumu kullanılmalıdır; aksi halde shared buffer hit oranındaki değişim plan değişikliğini maskeleyebilir. p95 gecikmesini pgbench çıktısından, fiziksel I/O farkını ise EXPLAIN (ANALYZE, BUFFERS) çıktısındaki shared read bloklarından kaydedin.

# tenant_small.sql
SELECT id, total_cents
FROM orders
WHERE tenant_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 100;

pgbench -n -c 20 -j 4 -T 180 -f tenant_small.sql appdb
pgbench -n -c 20 -j 4 -T 180 -f tenant_large.sql appdb

PostgreSQL'de Generic Plan ve Custom Plan Kararını Sınamak

PostgreSQL prepared statement kullanırken plan_cache_mode=auto altında önce custom planlar üretir, sonra generic planın tahmini maliyetini önceki custom plan maliyetleriyle karşılaştırır. Generic plan parametre değerini bilmeden üretildiği için tenant dağılımı çok bozuksa küçük tenant için index scan yerine pahalı bir bitmap veya sequential scan seçebilir. Bu davranışı tahmin etmek yerine aynı prepared statement'i force_generic_plan ve force_custom_plan ile çalıştırarak buffer ve süre farkını ölçün.

PREPARE open_orders(integer) AS
SELECT id, total_cents
FROM orders
WHERE tenant_id = $1
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 100;

BEGIN;
SET LOCAL plan_cache_mode = force_generic_plan;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) EXECUTE open_orders(42);
COMMIT;

BEGIN;
SET LOCAL plan_cache_mode = force_custom_plan;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) EXECUTE open_orders(42);
COMMIT;

force_custom_plan ayarını sunucu geneline açmak doğru varsayılan değildir. Her yürütmede yeniden planlama CPU maliyeti getirir ve çok kısa sorgularda planlama süresi yürütme süresini geçebilir. Daha güvenli yaklaşım, ölçüm sonrası yalnızca sorunlu işlem için SET LOCAL kullanmak veya sorgu biçimini iki seçicilik sınıfına ayırmaktır. PgBouncer transaction pooling kullanılıyorsa oturum seviyesindeki SET ve PREPARE durumunun işlem sonunda korunacağı varsayılmamalıdır; bu modda SET LOCAL ile açık transaction kullanın ya da uygulama sürücüsünün prepared statement davranışını pooler yapılandırmasıyla birlikte test edin.

SQL Server Eğitimi: Parameter Sniffing ve Query Store İncelemesi

SQL Server'da saklı yordam ilk derlendiğinde kullanılan parametre, plan seçiminde sniff edilir. Az satırlı bir CustomerId ile derlenen nested loops planı, milyonlarca satırı olan müşteri için tekrar kullanılırsa lookup sayısı katlanabilir. SQL Server eğitimi sırasında bu problemi yalnızca süreyle değil logical reads ile doğrulayın: SET STATISTICS IO, TIME ON komutu tablo başına mantıksal okuma sayısını verir; gerçek yürütme planı ise tahmini ve gerçekleşen satır farkını gösterir.

SET STATISTICS IO, TIME ON;
EXEC dbo.GetOpenOrders @CustomerId = 42;
EXEC dbo.GetOpenOrders @CustomerId = 900001;
SET STATISTICS IO, TIME OFF;

SELECT q.query_id,
       p.plan_id,
       rs.count_executions,
       rs.avg_duration,
       rs.avg_logical_io_reads
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p
  ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
  ON rs.plan_id = p.plan_id
ORDER BY rs.avg_logical_io_reads DESC;

Düzeltme seçimi ölçüme bağlıdır. OPTION (RECOMPILE), her çağrıda güncel parametre ile plan üretir ve özellikle seyrek çalışan ağır raporlarda mantıklıdır; ancak yüksek QPS olan bir endpoint'te derleme CPU'sunu yükseltebilir. OPTION (OPTIMIZE FOR UNKNOWN) ise histogramdaki belirli bir değeri değil genel yoğunluğu temel alır ve uç değerler için kötüleşebilir. Parametre duyarlı plan özelliğini destekleyen SQL Server kurulumlarında Query Store üzerinden birden fazla plan varyantı oluşup oluşmadığını inceleyin; plan forcing uygulamadan önce her varyantın logical reads ve duration değerlerini ayrı değerlendirin. Tek bir planı zorlamak, bugünün büyük müşterisini düzeltirken yarının küçük müşteri trafiğini bozabilir.

Database Indexleme ile Plan Hassasiyetini Sınırlamak

Database indexleme sadece bir indeksi eklemek değildir; indeks anahtar sırası sorgunun filtre, sıralama ve limit davranışına göre seçilmelidir. tenant_id eşitlik filtresi, status sabit filtresi ve created_at DESC sıralaması olan açık sipariş ekranı için partial index, hem generic hem custom planın tarayacağı alanı küçültür. INCLUDE sütunları B-tree arama koşuluna katılmaz, fakat görünürlük haritası elverdiğinde index-only scan için id ve total_cents değerlerini heap erişimi olmadan döndürebilir.

CREATE INDEX CONCURRENTLY idx_orders_open_tenant_created
ON orders (tenant_id, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'open';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents
FROM orders
WHERE tenant_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 100;

Veritabanı optimizasyonu ölçümünde indeksi eklemeden önce ve sonra aynı parametre setiyle shared_blks_read, Heap Fetches ve execution time değerlerini tabloya koyun. PostgreSQL'de CREATE INDEX CONCURRENTLY bir transaction block içinde çalıştırılamaz; migration aracı tüm migration'ı otomatik transaction içine alıyorsa bu komut başarısız olur. Ayrıca index-only scan görünse bile Heap Fetches yüksek olabilir: autovacuum henüz sayfaları all-visible işaretlemediyse PostgreSQL görünürlük kontrolü için heap'e gider. Bu nedenle sadece plan düğümüne değil EXPLAIN çıktısındaki Heap Fetches sayısına bakın.

NoSQL Eğitimi: MongoDB Sorgu Şekli ve Plan Önbelleği

NoSQL eğitimi içinde aynı ilke MongoDB için de geçerlidir: plan seçimi sorgu şekline, filtreye ve indeks adaylarına bağlıdır. tenantId alanı çok dengesiz dağılıyorsa küçük bir tenant için uygun olan plan büyük tenant'ta çok fazla belge ve indeks anahtarı inceleyebilir. MongoDB'de executionStats ile nReturned, totalKeysExamined ve totalDocsExamined oranlarını kaydedin. Hedef, 100 sonuç döndüren sorgunun yüz binlerce anahtar taramadığını doğrulamaktır.

db.orders.createIndex(
  { tenantId: 1, status: 1, createdAt: -1 },
  { name: "tenant_status_created" }
)

db.orders.explain("executionStats").find(
  { tenantId: 42, status: "open" },
  { _id: 1, totalCents: 1 }
).sort({ createdAt: -1 }).hint("tenant_status_created")

hint() üretimde kalıcı çözüm olarak değil, indeks adayının etkisini izole eden deney aracı olarak kullanılmalıdır. Zorlanan indeks sonradan kaldırılırsa sorgu hata verir ve veri dağılımı değiştiğinde daha iyi plan seçimini engeller. Önce hintsiz explain çıktısını alın, ardından hint ile totalKeysExamined ve totalDocsExamined farkını karşılaştırın. Bu karşılaştırma, ilişkisel sistemlerdeki veritabanı performans ayarlama sürecinin NoSQL tarafındaki karşılığıdır: planı tahmin etmek yerine gerçek tarama hacmini ölçmek.

Sık Sorulan Sorular

Veritabanı performans ayarlama sırasında generic plan sorunu nasıl tespit edilir?

PostgreSQL'de aynı prepared statement'i force_generic_plan ve force_custom_plan altında EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ile çalıştırın. Büyük fark varsa actual time, shared_blks_read ve satır sayısı sapmasını kaydedin. pg_stat_statements içindeki stddev_exec_time değeri de parametreye bağlı süre dalgalanmasını ortaya çıkarır.

SQL Server eğitiminde parameter sniffing için OPTION RECOMPILE ne zaman kullanılmalı?

Sorgu seyrek çalışıyor, parametre dağılımı çok bozuk ve yanlış planın logical reads maliyeti yüksekse OPTION (RECOMPILE) ölçülebilir bir seçenektir. Önce SET STATISTICS IO, TIME ON ile mevcut ve recompile davranışındaki okuma ile CPU değerlerini karşılaştırın. Yüksek çağrı hacminde her çağrının derleme maliyetini Query Store runtime istatistikleriyle ayrıca kontrol edin.

Database indexleme sonrası PostgreSQL index-only scan neden heap okuması yapar?

Index-only scan, satır görünürlüğünü visibility map üzerinden doğrular. İlgili heap sayfası all-visible değilse PostgreSQL Heap Fetches üretir. EXPLAIN (ANALYZE, BUFFERS) çıktısındaki Heap Fetches değerini izleyin; yüksekse autovacuum çalışma geçmişini ve güncelleme yoğunluğunu pg_stat_user_tables üzerinden inceleyin.

NoSQL eğitimi için MongoDB indeksinin doğru olduğunu nasıl ölçerim?

db.collection.explain('executionStats').find(...) çağrısında nReturned, totalKeysExamined ve totalDocsExamined alanlarını alın. Aynı sorguyu önerilen indeksle geçici hint kullanarak tekrar çalıştırın. Dönüş sayısına göre anahtar ve belge tarama sayısı belirgin biçimde düşmüyorsa indeks anahtar sırası veya sorgu filtresi erişim desenine uygun değildir.