PostgreSQL'de Autovacuum ve Tablo Şişmesi: Ölü Satırlar, Temizliği Engelleyen İşlemler ve Tablo Bazlı Ayar

PostgreSQL'de bir satırı güncellediğinizde ya da sildiğinizde eski sürüm diskten hemen kalkmaz. MVCC gereği o satırı hâlâ görebilecek açık işlemler olabilir, bu yüzden eski sürüm "ölü satır" olarak sayfada kalır. Bu ölü satırları temizleyip yerini yeniden kullanılabilir hâle getiren iş VACUUM'dur ve çoğu kurulumda bunu arka planda autovacuum yapar. Sorun, autovacuum'un varsayılan ayarlarının küçük ve orta tablolara göre seçilmiş olmasıdır. Büyük, sık güncellenen bir tabloda temizlik geride kalır, tablo ve indeksler şişer, sorgular aynı veriyi okumak için giderek daha fazla sayfa tarar. Bu yazıda şişmenin neden oluştuğunu, nasıl ölçüleceğini ve hangi ayarın hangi durumda işe yaradığını anlatıyoruz.

Autovacuum bir tabloya ne zaman uğrar

Autovacuum bir tabloyu, ölü satır sayısı şu eşiği aştığında temizlemeye alır:

autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * satır_sayısı

Varsayılanlar 50 ve 0.2'dir. Yani tablonun yaklaşık beşte biri ölü satır olmadan temizlik başlamaz. Bin satırlık bir tabloda bu 250 satırdır ve hiçbir şey hissedilmez. Yüz milyon satırlık bir tabloda ise yirmi milyon ölü satır birikmesi gerekir. Bu tablo her dakika güncelleniyorsa temizlik seyrek, uzun ve ağır koşar.

İkinci sınır hızdır. Autovacuum, üretim yükünü ezmemek için maliyet tabanlı bir fren kullanır: belli bir iş birimi biriktirdiğinde kısa bir süre uyur. autovacuum_vacuum_cost_limit doğrudan ayarlanmazsa vacuum_cost_limit değerini (varsayılan 200) kullanır ve bu bütçe aynı anda çalışan tüm autovacuum işçileri arasında paylaştırılır. autovacuum_max_workers değerini artırmak bu yüzden tek başına temizliği hızlandırmaz, aynı bütçeyi daha fazla işçiye böler.

Önce ölçün

Şişmeyi tahminle değil, istatistikle konuşun. İlk bakılacak yer pg_stat_user_tables görünümüdür:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / greatest(n_live_tup, 1), 1) AS dead_pct,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Bu sayılar tahmindir, ama hangi tablonun geride kaldığını göstermeye yeter. last_autovacuum alanı çok eskiyse ya da hiç dolmamışsa ve ölü satır oranı yüksekse o tablo ayrı ayar istiyor demektir.

Gerçek doluluk oranını görmek için pgstattuple eklentisi vardır. pgstattuple('tablo_adi') tabloyu baştan sona okuyup ölü satır ve boş alan yüzdesini döner. Büyük tabloda bu tam tarama demektir; yoğun saatte çalıştırmayın ya da daha ucuz olan pgstattuple_approx sürümünü kullanın.

Autovacuum'un ne yaptığını görmek için log_autovacuum_min_duration ayarını birkaç saniyeye çekin. Böylece uzun süren her temizlik koşusu, kaç satır sildiği ve kaçını silemediği ile birlikte log'a düşer.

En sık sebep: temizliği engelleyen eski işlem

Ayarları kurcalamadan önce şu soruyu sorun: autovacuum çalışıyor ama satırları silemiyor olabilir mi? VACUUM, veritabanındaki en eski açık anlık görüntüden sonra ölmüş satırları silemez. Saatlerdir açık duran tek bir işlem, bütün kümede temizliği o noktada dondurur. Log'da ya da VACUUM VERBOSE çıktısında "dead but not yet removable" diye görünen satırlar tam olarak budur.

Bu ufku geride tutan dört tipik şey vardır:

-- Uzun süren ya da "idle in transaction" bekleyen oturumlar
SELECT pid, state, xact_start, age(backend_xmin) AS xmin_age, left(query, 60)
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 10;

-- Tüketicisi kaybolmuş replikasyon slotları
SELECT slot_name, active, xmin, catalog_xmin
FROM pg_replication_slots;

-- Unutulmuş hazırlanmış işlemler (iki aşamalı commit)
SELECT gid, prepared, owner FROM pg_prepared_xacts;

Dördüncüsü hot_standby_feedback açık bir replikadır: replikada koşan uzun bir rapor sorgusu birincildeki temizliği de bekletir.

En yaygın kaynak uygulama tarafıdır. İşlem açıp ağ çağrısı bekleyen, hata yolunda commit ya da rollback yapmadan bağlantıyı havuza geri bırakan kod, oturumu "idle in transaction" durumunda bırakır. idle_in_transaction_session_timeout bu oturumları belli bir süre sonra kapatır. Bunu veritabanı genelinde makul bir değere ayarlayın ve uzun işlem gerektiren toplu işler için oturum düzeyinde gevşetin. Ayarı kapalı bırakmak, bir gün bir hata yolunun bütün kümenin temizliğini durdurmasına izin vermektir.

Büyük tablolar için tablo bazlı ayar

Küme genelindeki ölçek çarpanını düşürmek bütün küçük tablolarda gereksiz iş üretir. Daha doğru yol, sorunu yaşayan tabloya kendi eşiğini vermektir:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 10000,
  autovacuum_analyze_scale_factor = 0.02
);

Burada temizlik, tablonun yüzde birine ek olarak on bin ölü satırda tetiklenir. Analyze eşiğini de düşürmek önemlidir, çünkü hızla büyüyen bir tabloda eski istatistik planlayıcının yanlış plan seçmesine yol açar.

Hız tarafında, disk ve CPU payınız varsa autovacuum_vacuum_cost_limit değerini küme genelinde yükseltmek genellikle işçi sayısını artırmaktan daha etkilidir. Değişikliği yaptıktan sonra log'daki süreleri ve disk gecikmesini birlikte izleyin; amaç temizliğin yükün gerisinde kalmaması, üretim sorgularıyla yarışması değil.

PostgreSQL 13 ile gelen autovacuum_vacuum_insert_threshold ve autovacuum_vacuum_insert_scale_factor ayarlarını da bilin. Yalnızca ekleme alan tablolar eskiden ölü satır üretmediği için uzun süre temizlenmezdi; bu, görünürlük haritasının güncellenmemesi ve yalnız-indeks taramalarının yavaşlaması demekti. Artık ekleme sayısı da temizliği tetikleyebiliyor.

Şişmeyi doğuştan azaltmak: HOT güncellemeler

Bir güncelleme hiçbir indeksli sütuna dokunmuyorsa ve yeni sürüm aynı sayfaya sığıyorsa PostgreSQL bunu HOT (heap-only tuple) güncelleme olarak yapar. İndekslere yeni girdi yazılmaz ve eski sürüm sayfa içinde kendiliğinden budanabilir. pg_stat_user_tables içinde n_tup_hot_upd ile n_tup_upd oranına bakın. Oran düşükse iki sebep vardır: ya sık güncellenen bir sütun indekslidir (örneğin "son görülme zamanı" üzerinde gereksiz bir indeks), ya da sayfalarda yer kalmıyordur. İkincisi için tablonun fillfactor değerini düşürmek, örneğin 90'a çekmek, her sayfada güncellemeler için boşluk bırakır. Bu yalnızca bundan sonra yazılan sayfalara uygulanır; mevcut veri için tablonun yeniden yazılması gerekir.

Şişmiş tabloyu geri almak

Normal VACUUM yeri yeniden kullanıma açar ama çoğu durumda diski işletim sistemine geri vermez. Tablo bir kez yüz gigabayta şiştiyse, temizlik sonrasında da o boyutta kalır. Küçültmek için seçenekler şunlar:

VACUUM FULL tabloyu baştan yazar, ama işlem boyunca tabloda ACCESS EXCLUSIVE kilit tutar; okuma bile bekler. Ayrıca yeni kopya için tablo boyutu kadar boş disk ister. Bakım penceresi olan küçük tablolar dışında üretimde kullanmayın.

pg_repack eklentisi aynı işi tabloyu çevrimiçi tutarak yapar: kopyayı arka planda üretir, aradaki değişiklikleri tetikleyiciyle taşır ve yalnızca başta ve sonda kısa kilit alır. Tabloda birincil anahtar ya da boş olamayan benzersiz bir indeks olmalıdır. Yine de tablo boyutu kadar ek disk ister ve yoğun yazma altında son aşamadaki kilit beklemeye takılabilir; sakin bir saatte çalıştırın.

Yalnızca indeksler şişmişse tabloya dokunmanız gerekmez. REINDEX INDEX CONCURRENTLY (PostgreSQL 12 ve sonrası) indeksi yazmaları kilitlemeden yeniden kurar.

Kuyruk ve olay tabloları için ayrı tasarım

Satırların eklenip kısa süre sonra silindiği tablolar (iş kuyrukları, giden kutusu tabloları, oturum kayıtları) VACUUM'un en kötü olduğu iş yüküdür. Burada ayar yapmak yerine tasarımı değiştirmek daha iyi sonuç verir. Veriyi zamana göre bölümleyip eski bölümü DROP ya da DETACH ile atmak, milyonlarca DELETE yapmaktan hem hızlıdır hem de hiç ölü satır üretmez.

Unutulmaması gereken: işlem numarası taşması

PostgreSQL işlem numaraları 32 bittir ve dairesel kullanılır. Eski satırların "dondurulması" da VACUUM'un işidir. Bir tablonun yaşı autovacuum_freeze_max_age değerini (varsayılan 200 milyon) aştığında autovacuum, autovacuum kapalı olsa bile, taşmayı önleme amaçlı bir temizlik başlatır. Bu koşu normal autovacuum gibi kilit çakışmasında kendini iptal etmez ve büyük tabloda saatler sürebilir. Eşiğe hiç yaklaşılmazsa veritabanı sonunda yeni işlem kabul etmeyi reddeder. Aşağıdaki sorguyu izlemeye ekleyin ve yaş 200 milyonun çok üzerine çıkıyorsa yukarıdaki "eski işlem" kontrolüne dönün:

SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 2 DESC;

Ne yapmamalı

Autovacuum'u bir tabloda ya da kümede kapatmayın. Kısa vadede yük azalır, sonra şişme ve taşma koruması çok daha ağır biçimde geri gelir. Zamanlanmış VACUUM FULL görevi kurmayın; bu her gece bir kesinti planlamaktır. Küçük tablolar için tablo bazlı agresif ayar da yazmayın, varsayılanlar onlar için doğrudur. Ayar yapmadan önce her zaman engelleyen işlem var mı diye bakın: çoğu şişme vakası yanlış eşikten değil, kapanmayan bir işlemden çıkar.