Veritabanı Optimizasyonu: PostgreSQL Autovacuum ile Bloat Analizi

Veritabanı optimizasyonu için bloat'u ölçerek başlayın

İyi bir veritabanı eğitimi veya sql eğitimi, yüksek disk kullanımı ile gerçek bloat'u ayırmayı öğretmelidir. PostgreSQL'de pg_stat_user_tables içindeki n_dead_tup değeri tahmindir; autovacuum henüz analyze çalıştırmadıysa yanıltıcı olabilir. İlk envanteri son vacuum zamanları, canlı satır tahmini ve ölü satır tahmini ile çıkarın. n_dead_tup / n_live_tup oranı tek başına karar kriteri değildir: 20 milyon satırlı bir tabloda yüzde 2 ölü satır bile yüz binlerce gereksiz heap tuple ve ek disk okuması demektir.

SELECT
    relid::regclass AS tablo,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_live_tup + n_dead_tup > 100000
ORDER BY n_dead_tup DESC
LIMIT 20;

Tahmini doğrulamak için bakım penceresinde pgstattuple eklentisinin yaklaşık taramasını kullanın. pgstattuple_approx, tüm tabloyu satır satır taramak yerine sınırlı sayıda blok örneklediği için büyük production tablolarında daha güvenlidir. Buna rağmen disk I/O üreteceğinden, sonucu yoğun saatlerde onlarca tablo üzerinde paralel çalıştırmayın. Eklenti kurulumu çoğu kurulumda yüksek yetki gerektirir; uygulama rolüne CREATE EXTENSION ayrıcalığı vermek yerine DBA tarafından kontrollü kurulmalıdır.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    table_len / 1024 / 1024 AS table_mb,
    approx_tuple_count,
    round(dead_tuple_percent::numeric, 2) AS dead_tuple_pct,
    round(approx_free_percent::numeric, 2) AS free_space_pct
FROM pgstattuple_approx('public.orders');

Autovacuum'un temizleyemediği eski transaction'ları bulun

VACUUM, fiziksel olarak ölü görünen bir tuple'ı ancak hiçbir aktif snapshot onu göremiyorsa kaldırabilir. Uzun süren bir transaction'ın backend_xmin değeri eski kaldığında, PostgreSQL bu transaction'ın görünür sayabileceği tuple'ları silmez. Bu nedenle autovacuum logunda sık vacuum görmek, bloat'un gerçekten küçüldüğü anlamına gelmez. Aşağıdaki sorguda age(backend_xmin) değeri yüksek ve xact_age uzun oturumlar, investigation listesine alınmalıdır.

SELECT
    pid,
    usename,
    application_name,
    state,
    now() - xact_start AS xact_age,
    age(backend_xmin) AS xmin_age,
    left(query, 200) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC, xact_start ASC;

İkinci yaygın engel replication slot'tur. Tüketilmeyen logical replication slot'ları WAL birikimiyle bilinse de, slotun xmin veya catalog_xmin değeri eski kaldığında vacuum temizliğini de sınırlar. inactive olan slotu hemen silmek yerine önce abone tüketicisinin geri getirilebilirliğini doğrulayın; slot silinirse abonenin yeniden snapshot alması gerekebilir. Bu kontrol, bloat analizi sırasında sık atlanan production edge case'lerden biridir.

SELECT
    slot_name,
    slot_type,
    active,
    restart_lsn,
    xmin,
    catalog_xmin,
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) / 1024 / 1024 AS retained_wal_mb
FROM pg_replication_slots
ORDER BY retained_wal_mb DESC NULLS LAST;

Veritabanı performans ayarlama: tablo bazlı autovacuum eşikleri

Varsayılan vacuum tetikleme hesabı kabaca autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples şeklindedir. Örneğin 50 milyon satırlı, sürekli UPDATE alan bir orders tablosunda scale factor 0.2 ise vacuum yaklaşık 10 milyon ölü tuple oluşana kadar bekleyebilir. Bu gecikme heap sayfa sayısını, indeks pointer'larını ve sorguların buffer okumasını büyütür. Küçük ve sık değişen tablolar için cluster geneli ayarı değiştirmek yerine storage parameter'ları tablo seviyesinde ayarlayın.

ALTER TABLE public.orders SET (
    autovacuum_vacuum_threshold = 5000,
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_threshold = 2000,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit = 2000
);

SELECT
    c.relname,
    c.reloptions
FROM pg_class c
WHERE c.oid = 'public.orders'::regclass;

Bu değişiklikte cost_limit'i yükseltmenin mekanizması önemlidir: vacuum worker daha fazla maliyet bütçesiyle daha az uyur ve temizliği daha erken bitirir; karşılığında I/O yarışması artabilir. Bu yüzden önce tek tabloda uygulayın, disk gecikmesini işletim sistemi metriği veya izleme sisteminizdeki disk queue depth ile izleyin. INSERT ağırlıklı append-only tabloda güncelleme kaynaklı dead tuple az olabilir; sunucunuz destekliyorsa autovacuum_vacuum_insert_threshold ve autovacuum_vacuum_insert_scale_factor ayarları visibility map güncelliği için ayrıca değerlendirilmelidir.

Bu ayarlar database indexleme kararlarından bağımsız değildir. Vacuum, indekslerden artık geçersiz olan TID referanslarını temizleyebilir fakat btree dosyasını her zaman küçültmez. UPDATE ile değiştirilen indeksli kolonlar hem yeni indeks kaydı üretir hem de eski kaydı vacuum'a bırakır; bu nedenle yalnızca okunma istatistiği zayıf olan bir indeksin yazma maliyetini pg_stat_user_indexes ve pg_stat_all_indexes ile ayrı ölçmek gerekir.

Database indexleme sonrası bloat ve sorgu maliyetini doğrulayın

Önce-sonra karşılaştırması için aynı sorgu parametreleri, aynı eşzamanlılık seviyesi ve yeterince uzun ölçüm penceresi kullanın. Tek bir EXPLAIN çıktısı cache sıcaklığına göre yanıltıcı olabilir. Değişiklik öncesi ve sonrası 5 dakikalık aynı pgbench senaryosunu çalıştırın; işlem/saniye, ortalama latency ve hata oranını kaydedin. Uygulama sorgusunu workload.sql dosyasına koyarak ölçümün sentetik ama tekrar edilebilir olmasını sağlayın.

# Önce ve sonra aynı komutu çalıştırın.
pgbench   -h db.internal   -U benchmark_user   -d appdb   -c 32 -j 8 -T 300 -P 30   -f workload.sql   -r

Sorgu seviyesinde heap ve indeks erişimini ayırmak için gerçekçi bir müşteri kimliğiyle EXPLAIN (ANALYZE, BUFFERS) alın. Shared Hit Blocks artışı tek başına kötü değildir; kritik sinyal, aynı satır sayısı için yükselen shared read, temp read/write ve actual time değeridir. Vacuum sonrasında plan değiştiyse, pg_stat_statements içinden calls, mean_exec_time ve total_exec_time değerlerini aynı zaman pencerelerinde kıyaslayın; sadece en hızlı tek çalıştırmayı raporlamayın.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, status, created_at
FROM public.orders
WHERE customer_id = 481516
ORDER BY created_at DESC
LIMIT 50;

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    shared_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE query LIKE '%FROM public.orders%'
ORDER BY total_exec_time DESC
LIMIT 10;

İndeks bloat'u şüpheli ise pgstatindex ile avg_leaf_density ve leaf_fragmentation değerlerini kaydedin. Düşük leaf density, özellikle range scan yapan büyük btree indekslerinde daha fazla sayfa ziyareti anlamına gelir. Gerçek bir yeniden inşa gerekiyorsa REINDEX INDEX CONCURRENTLY komutu yazma erişimini tamamen kesmeden çalışabilir, ancak transaction block içinde çalıştırılamaz ve geçici olarak ek disk alanı ister. Önce tablo bloat'unu ve uzun transaction engellerini çözmeden reindex yapmak, kısa sürede aynı şişkinliğin tekrar oluşmasına yol açabilir.

SELECT *
FROM pgstatindex('public.orders_customer_created_idx');

REINDEX INDEX CONCURRENTLY public.orders_customer_created_idx;

SQL Server eğitimi ve nosql eğitimi bağlamında aynı hatayı yapmayın

Bir sql server eğitimi sırasında öğrenilen DMV'leri PostgreSQL'e, PostgreSQL autovacuum ayarlarını da SQL Server'a taşımayın. SQL Server tarafında sürüm zinciri ve ghost kayıt davranışını incelemek için sys.dm_db_index_physical_stats ile avg_fragmentation_in_percent, page_count ve ghost cleanup etkisini ayrı ölçün. PostgreSQL'deki n_dead_tup ile SQL Server fragmentation yüzdesi aynı metriği ifade etmez; ilki MVCC tuple temizliği, ikincisi çoğunlukla indeks sayfa düzeniyle ilgilidir.

SELECT
    OBJECT_NAME(ips.object_id) AS table_name,
    i.name AS index_name,
    ips.page_count,
    ips.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i
  ON i.object_id = ips.object_id
 AND i.index_id = ips.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

Bir nosql eğitimi bağlamında da MongoDB WiredTiger compaction veya LSM tabanlı motorlardaki compaction, PostgreSQL VACUUM ile eş tutulmamalıdır. Örneğin MongoDB'de db.collection.stats() ile storageSize ve totalIndexSize izlenebilir; compact operasyonu çalışma yükü ve replikasyon davranışı açısından ayrı planlanmalıdır. Veritabanı motorunun dosya alanını geri verme mekanizmasını doğrulamadan, yalnızca disk kullanım grafiğine bakarak bakım komutu çalıştırmak production riski yaratır.

Sık Sorulan Sorular

Veritabanı optimizasyonu için PostgreSQL bloat oranını hangi sorguyla ölçebilirim?

İlk sıralama için pg_stat_user_tables içindeki n_dead_tup, n_live_tup ve last_autovacuum alanlarını kullanın. Karar vermeden önce şüpheli tablolarda pgstattuple_approx('schema.table') çalıştırın; n_dead_tup istatistik tahmini iken pgstattuple_approx blok örneklemesiyle dead_tuple_percent üretir.

Database indexleme sonrasında PostgreSQL indeks bloat'u nasıl doğrulanır?

pgstattuple eklentisindeki pgstatindex('schema.index_name') fonksiyonundan avg_leaf_density ve leaf_fragmentation değerlerini değişiklik öncesi ve sonrası kaydedin. Reindex ancak sorgu buffer okumaları, indeks boyutu veya leaf density verisi sorunu destekliyorsa uygulanmalıdır; REINDEX INDEX CONCURRENTLY ek disk alanı ister ve transaction block içinde çalışmaz.

Veritabanı performans ayarlama için autovacuum scale factor nasıl seçilir?

Önce tablonun reltuples değerini ve günlük UPDATE/DELETE hacmini ölçün. Tetikleme eşiği threshold + scale_factor * reltuples olduğundan, 50 milyon satırlı yoğun güncellenen tabloda 0.2 yerine örneğin 0.02 ile başlayıp oluşan ölü tuple sayısını yaklaşık 1 milyon civarında sınırlayabilirsiniz. Değişikliği ALTER TABLE ... SET ile sadece hedef tabloda yapın ve disk gecikmesi ile pg_stat_progress_vacuum verisini izleyin.

SQL Server eğitimi alan biri PostgreSQL autovacuum yerine hangi SQL Server metriklerine bakmalı?

SQL Server'da sys.dm_db_index_physical_stats ile page_count ve avg_fragmentation_in_percent değerlerini, ayrıca iş yüküne göre version store ve ghost cleanup davranışını inceleyin. PostgreSQL n_dead_tup metriğini SQL Server indeks fragmentation yüzdesiyle doğrudan karşılaştırmayın; iki motor farklı satır görünürlüğü ve alan geri kazanım mekanizmaları kullanır.