Veritabanı Optimizasyonu: PostgreSQL'de Disk Spill ve work_mem

Veritabanı performans ayarlama için spill taban çizgisini çıkarın

Sıralama, Hash Join, Hash Aggregate ve DISTINCT işlemlerinde bellek yetersiz kalırsa PostgreSQL geçici dosya yazar. Bu durum çoğu zaman CPU grafiğinde değil, disk gecikmesinde ve sorgu süresindeki uzun kuyrukta görünür. İlk ölçüm için staging ortamında pg_stat_statements etkin olmalı; üretimde ise geçici dosyaları eşik üstünde loglamak, sorgu kimliğini uygulama isteğiyle eşleştirmeyi sağlar. 10 MB altındaki küçük spill'leri loglamamak için eşik belirlemek log hacmini kontrol eder.

-- postgresql.conf veya ALTER SYSTEM ile
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
log_temp_files = 10240
log_line_prefix = '%m [%p] db=%d user=%u app=%a '

-- Değişiklik sonrası gerekli ise kontrollü restart yapın
SELECT pg_reload_conf();

Taban çizgisini tek bir ortalama süreyle kurmayın. Aynı sorgu kimliği için çağrı sayısı, toplam süre, ortalama süre ve temp block sayaçlarını birlikte alın. temp_blks_written artarken shared_blks_read sabitse, darboğaz çoğunlukla tablo cache'inden değil ara işlem sonucu spill edilmesinden kaynaklanır. Bu ayrım, veritabanı eğitimi ve sql eğitimi kapsamında sık yapılan 'yavaş sorguya hemen indeks ekle' refleksinden daha güvenilir bir başlangıçtır.

SELECT queryid,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       temp_blks_read,
       temp_blks_written,
       left(query, 180) AS sample
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 20;

EXPLAIN ANALYZE ile Sort ve Hash spill mekanizmasını okuyun

Hedef sorguyu üretim parametreleriyle ve tercihen salt okunur bir replikada çalıştırın: EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE). Sort düğümündeki Sort Method: external merge Disk: ... çıktısı disk spill'ini doğrudan kanıtlar. Hash düğümündeki Batches değerinin 1'den büyük olması ise hash tablosunun tek bellekte sığmadığını gösterir. BUFFERS seçeneği olmadan yalnızca toplam süreye bakmak, disk yazımı ile soğuk cache okumasını ayırt edemez.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, VERBOSE)
SELECT customer_id, count(*) AS order_count
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 100;

Buradaki kritik incelik, work_mem değerinin sorgu başına tek seferlik bir tahsis olmamasıdır. Plan içinde iki Sort ve bir Hash düğümü varsa, paralel planın her worker'ı da kendi çalışma belleğini kullanabilir. Bu nedenle sunucu genelinde 256 MB gibi yüksek bir değer vermek, eşzamanlı 100 oturumda beklenmedik bellek baskısı ve OOM riskine dönüşür. Önce yalnızca teşhis oturumunda değer yükseltin, aynı parametrelerle önce-sonra planını ve Disk: veya Batches: alanlarını karşılaştırın.

BEGIN;
SET LOCAL work_mem = '128MB';
SET LOCAL max_parallel_workers_per_gather = 0;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT customer_id, count(*)
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY customer_id
ORDER BY count(*) DESC
LIMIT 100;
ROLLBACK;

Database indexleme ile sıralama maliyetini bellek artırmadan azaltın

Sorgu gerçekten az sayıda satır döndürüyorsa, bellek büyütmek yerine erişim yolunu değiştirin. Örneğin bir müşteri için son siparişleri getiren sorguda B-tree indeksinin kolon sırası filtre ve sıralama sırasını izlemelidir. (customer_id, created_at DESC) indeksi, önce eşitlik filtresini daraltır ve sonra yaprak sayfalarını zaten sıralı okur; ayrı bir Sort düğümü oluşmaz. INCLUDE ile yalnızca çıktı kolonlarını eklemek, indeks sırasını bozmadan index-only scan olasılığını yükseltir.

CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC)
INCLUDE (id, total_amount, status);

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_amount, status
FROM orders
WHERE customer_id = $1
ORDER BY created_at DESC
LIMIT 50;

Database indexleme her ORDER BY için çözüm değildir. WHERE created_at >= ... GROUP BY customer_id ORDER BY count(*) örneğinde nihai sıralama hesaplanmış aggregate değeri üzerindedir; ham tablo indeksi çoğu durumda bu sıralamayı sağlayamaz. Bu durumda sorguyu dar bir tarih aralığına çekmek, özet tablo veya materialized view kullanmak daha doğru olabilir. İndeks eklendikten sonra pg_stat_user_indexes.idx_scan değerini ve yazma maliyetini izleyin; kullanılmayan geniş INCLUDE indeksleri INSERT ve UPDATE sırasında ek WAL ve sayfa değişimi üretir.

SELECT schemaname, relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC;

SQL Server eğitimi bağlamında memory grant ve spill farkı

SQL Server tarafında aynı sınıf sorun, yürütme planındaki Hash Match veya Sort operatörünün TempDB'ye spill etmesi ve memory grant tahmininin yetersiz kalması olarak görünür. SQL Server eğitimi için önemli ayrım şudur: PostgreSQL'deki work_mem operatör ve worker başına üst sınırken, SQL Server sorgu başında bir memory grant ister. Actual Execution Plan içindeki spill uyarısı, ya da Extended Events ile sort_warning ve hash_warning yakalanarak ölçüm yapılabilir.

CREATE EVENT SESSION TrackSpills ON SERVER
ADD EVENT sqlserver.sort_warning,
ADD EVENT sqlserver.hash_warning
ADD TARGET package0.ring_buffer;
ALTER EVENT SESSION TrackSpills ON SERVER STATE = START;

SELECT requested_memory_kb, granted_memory_kb, used_memory_kb,
       ideal_memory_kb, query_cost
FROM sys.dm_exec_query_memory_grants;

Önce-sonra karşılaştırmasında yalnızca sorgu süresini değil, logical reads, TempDB yazımı, granted_memory_kb ve eşzamanlılık altında p95 gecikmesini kaydedin. Bir sorguya bellek grant'i büyütmek tekil testte spill'i kaldırabilir, fakat yoğun saatlerde diğer sorguların grant beklemesine neden olabilir. Plan değişikliği öncesinde Query Store'dan aynı query_id'nin en az iki yük dönemi için runtime istatistiklerini alın; parametrik veri dağılımı varsa küçük parametre ve büyük parametre örneklerini ayrı çalıştırın.

NoSQL eğitimi için disk kullanımı ve bellek sınırlarını ayırın

Nosql eğitimi sırasında MongoDB aggregation pipeline'larındaki bellek sınırları da benzer bir üretim arızası kaynağıdır. Büyük bir $sort veya $group aşamasında allowDiskUse: true geçici disk kullanımına izin verir, ancak bu seçeneği körlemesine açmak yavaş diskte kuyruk oluşturur. Önce explain('executionStats') ile totalDocsExamined, totalKeysExamined ve kazanan planı inceleyin; ardından eşleşmeyi pipeline'ın başına taşıyıp uygun bileşik indeksle aday belge sayısını azaltın.

db.orders.createIndex({ tenantId: 1, createdAt: -1 })

db.orders.explain('executionStats').aggregate([
  { $match: { tenantId: 't42', createdAt: { $gte: ISODate('2026-08-01') } } },
  { $sort: { createdAt: -1 } },
  { $limit: 100 },
  { $project: { _id: 1, createdAt: 1, total: 1 } }
], { allowDiskUse: false })

Bu yaklaşımın edge case'i shard edilmiş koleksiyonlardır: shard key ile uyumsuz bir sıralama, her shard üzerinde sonuç üretip mongos katmanında merge sort gerektirebilir. Bu nedenle uygulama telemetrisiyle sorgu süresini, MongoDB profiler ile docsExamined değerini ve disk kullanımını aynı zaman penceresinde karşılaştırın. SQL ve belge veritabanlarında ortak ilke bellek limitini yükseltmek değil, ara sonuç kümesinin neden büyüdüğünü plan kanıtıyla bulmaktır.

Sık Sorulan Sorular

Veritabanı optimizasyonu sırasında PostgreSQL disk spill nasıl tespit edilir?

Hedef sorguyu EXPLAIN (ANALYZE, BUFFERS, SETTINGS) ile çalıştırın. Sort düğümünde 'external merge Disk' veya Hash düğümünde 1'den büyük 'Batches' görürseniz spill vardır. Sunucu genelinde log_temp_files = 10240 ayarı da 10 MB üstü geçici dosyaları loglar.

Veritabanı performans ayarlama için work_mem ne kadar olmalı?

Sabit bir genel değer vermeyin. Önce SET LOCAL work_mem ile yalnızca hedef sorguyu deneyin, spill alanı ve p95 süreyi ölçün. Değerin plan düğümü ve paralel worker başına uygulanabildiğini hesaba katın; 3 bellek kullanan düğüm ve 4 worker, teorik olarak oturum başına 12 kat kullanım yaratabilir.

Database indexleme Sort işlemini her zaman kaldırır mı?

Hayır. İndeks ancak WHERE filtresi ve ORDER BY kolonlarının B-tree sırasıyla uyumlu olduğu durumlarda sıralı erişim sağlayabilir. ORDER BY count(*) gibi aggregate sonucu üzerinde ise ham tablo indeksi genellikle Sort'u kaldıramaz; özet tablo, materialized view veya daha dar giriş kümesi değerlendirilmelidir.

SQL Server eğitiminde TempDB spill hangi araçla izlenir?

Actual Execution Plan üzerindeki Sort ve Hash Match spill uyarılarını inceleyin. Sürekli gözlem için sqlserver.sort_warning ve sqlserver.hash_warning Extended Events olaylarını kaydedin; sys.dm_exec_query_memory_grants ile requested, granted ve used memory değerlerini aynı anda kontrol edin.