Veritabanı Optimizasyonu: PostgreSQL'de WAL ve Checkpoint Analizi

Veritabanı optimizasyonu için WAL kaynaklı gecikmeyi ayırmak

Bir yazma gecikmesini checkpoint'e bağlamadan önce istemci gecikmesi, WAL flush süresi ve veri dosyası yazımını ayrı ölçün. PostgreSQL'de pg_stat_wal görünümünden WAL byte miktarını ve WAL yazma zamanını, işletim sistemi tarafında ise iostat -x 1 ile ilgili disklerin await, w_await ve %util değerlerini aynı zaman penceresinde kaydedin. Disk %util değeri tavana yakınken pg_stat_wal.wal_write_time artıyor ve uygulama p95 yazma gecikmesi aynı saniyelerde yükseliyorsa, sorun SQL planından çok WAL aygıtı doygunluğudur. Bu ayrım önemlidir: indeks eklemek veya sorguyu yeniden yazmak, fsync bekleyen bir commit'in süresini azaltmaz.

-- Ölçüme başlamadan önce sayaçları bir kez kaydedin.
SELECT now(), wal_records, wal_fpi, wal_bytes,
       wal_write, wal_sync, wal_write_time, wal_sync_time
FROM pg_stat_wal;

-- 60 saniye sonra aynı sorguyu çalıştırın ve farkı hesaplayın.
-- wal_bytes farki / 60 = saniye basina WAL üretimi

SELECT datname, xact_commit, xact_rollback,
       blks_read, blks_hit,
       temp_files, temp_bytes
FROM pg_stat_database
WHERE datname = current_database();

Sadece toplam sayaçlara bakmak yanıltıcı olabilir. Bir replikasyon bağlantısının geride kalması WAL aygıtını yavaşlatmaz, fakat WAL segmentlerinin silinmesini geciktirerek disk tüketimini büyütür. Bu nedenle pg_stat_replication içindeki sent_lsn, write_lsn, flush_lsn ve replay_lsn farklarını da byte cinsinden izleyin. Veritabanı eğitimi kapsamında sık atlanan ayrıntı şudur: commit yolu, synchronous_commit açık olduğunda WAL flush bekler; veri sayfalarının asıl dosyaya yazılması ise çoğunlukla checkpoint sürecinin işidir. İki gecikme biçiminin belirtileri ve çözümü farklıdır.

Checkpoint fırtınasını profil araçlarıyla yeniden üretmek

Üretim benzeri satır genişliği ve indeks sayısıyla izole bir ortamda pgbench çalıştırın. İlk denemede sabit istemci sayısı, süre ve seed kullanın; ikinci denemede yalnızca tek bir sunucu parametresini değiştirin. Linux üzerinde pgbench ile eş zamanlı iostat, pidstat -d ve PostgreSQL loglarını toplayın. Önce-sonra karşılaştırmasında yalnızca ortalama TPS'yi değil, pgbench'in latency average ve latency stddev değerlerini, ayrıca logdaki checkpoint tamamlanma sürelerini karşılaştırın.

# Test verisini bir kez olusturur
pgbench -i -s 100 appdb

# 15 dakika, 32 eszamanli istemci, her islem 10 kez tekrar edilir
pgbench -c 32 -j 8 -T 900 -P 10 appdb | tee before.txt

# Ayri terminallerde ayni anda
sudo iostat -x 1 nvme0n1 | tee iostat-before.txt
sudo pidstat -d 1 -p $(head -1 $PGDATA/postmaster.pid) | tee pidstat-before.txt

grep 'checkpoint complete' $PGDATA/log/*.log | tail -30

Checkpoint'in zorlanıp zorlanmadığını PostgreSQL logunda 'requested' veya 'segments added' ifadeleriyle doğrulayın. max_wal_size sınırına erken ulaşıldığında zamanlanmış checkpoint beklenmeden yeni checkpoint başlar ve çok sayıda kirli sayfa kısa sürede yazılır. Ayrıca wal_fpi oranını hesaplayın: wal_fpi / wal_records. Bu oran checkpoint sonrasında yükseliyorsa full-page write maliyeti baskındır. PostgreSQL, ilk değişiklikte sayfanın tam görüntüsünü WAL'a yazar; mekanizma, disk üzerinde yarım kalmış sayfa yazımından sonra kurtarmayı mümkün kılar. Bu yüzden yüksek wal_fpi, uygulamanın yaptığı mantıksal UPDATE sayısından daha fazla WAL üretilmesine yol açabilir.

Veritabanı performans ayarlama: checkpoint bütçesini kontrollü değiştirmek

Başlangıç ayarını, gözlenen WAL üretim hızısına göre hesaplayın. Örneğin sistem saatte 120 GB WAL üretiyorsa checkpoint_timeout 15 dakika iken yaklaşık 30 GB WAL bütçesi gerekir. max_wal_size bunun altındaysa zaman tabanlı değil boyut tabanlı checkpoint görmeniz beklenir. Önce ölçülen WAL hızının 1.5 ile 2 katı kadar pay bırakın, sonra en az iki tam yük penceresi test edin. max_wal_size değerini gereksizce büyütmek kurtarma süresini artırabilir; bu nedenle RTO hedefini ve disk boş alanını aynı değişiklik kaydında belirtin.

-- Uygulama trafiginin dusuk oldugu pencerede uygular.
ALTER SYSTEM SET checkpoint_timeout = '15min';
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET max_wal_size = '48GB';
ALTER SYSTEM SET min_wal_size = '8GB';
ALTER SYSTEM SET log_checkpoints = 'on';
SELECT pg_reload_conf();

SHOW checkpoint_timeout;
SHOW checkpoint_completion_target;
SHOW max_wal_size;

checkpoint_completion_target = 0.9, checkpoint yazımını zaman penceresinin daha büyük bölümüne yaymayı hedefler. Bu, toplam yazılan byte sayısını düşürmez; aynı sayfaların daha az ani I/O kuyruğu oluşturmasını sağlar. Karşılaştırma tablosunda p95 commit latency, en uzun checkpoint süresi, iostat w_await ve checkpoint sayısını birlikte tutun. Değişiklikten sonra TPS artarken recovery point objective kabul edilemez düzeyde büyüyorsa ayar başarılı sayılmaz. SSD üzerinde bile uzun kuyruklar WAL fsync isteklerinin veri dosyası yazımları arkasında beklemesine neden olabilir.

Database indexleme kararlarının WAL maliyetini hesaplamak

Database indexleme yalnızca SELECT maliyeti değildir. Her INSERT, UPDATE ve DELETE ilgili B-tree indekslerinde ek WAL kaydı, sayfa değişimi ve olası page split üretir. Özellikle güncellenen kolonları içeren geniş bileşik indeksler, checkpoint aralığında kirlenen sayfa sayısını artırır. Aday indeks silmeden önce en az bir iş döngüsü boyunca kullanım istatistiğini, indeks boyutunu ve sorgu planlarını alın. Birincil anahtar, unique constraint ve foreign key'i destekleyen indeksleri neredeyse hiç taranmıyor diye doğrudan silmek, yazma maliyetini düşürürken bütünlük denetimlerini pahalı hale getirebilir.

SELECT schemaname, relname, indexrelname,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;

-- Belirli bir yazma sorgusunun indeks etkisini plan ve I/O ile dogrulayın.
EXPLAIN (ANALYZE, BUFFERS, WAL)
UPDATE orders
SET status = 'paid', paid_at = clock_timestamp()
WHERE id = 420001;

EXPLAIN içindeki WAL records, WAL bytes ve full page images alanlarını değişiklik öncesi ve sonrası kaydedin. Bu metrikler, yalnızca test oturumunun ürettiği WAL'ı gösterdiği için toplu sayaçlara göre daha nedensel bir karşılaştırma sunar. Örneğin status kolonu çok sık değişiyorsa, status içeren bir indeksin kaldırılması veya kısmi indeksle yalnızca aktif kayıtları kapsaması anlamlı olabilir. Ancak PostgreSQL'de HOT update yalnızca indekslenmiş kolonlar değişmiyorsa oluşabilir; status'u indekse eklemek, önceden HOT olan güncellemeleri normal indeks güncellemesine dönüştürerek beklenmedik WAL artışı yaratabilir.

SQL eğitimi, SQL Server eğitimi ve NoSQL eğitimi için aynı ölçüm modeli

SQL eğitimi içinde öğrenilen transaction sınırları, WAL analizinin doğrudan parçasıdır: 10.000 satırı tek transaction ile yazmak commit flush sayısını azaltır, ancak kilit süresini, rollback maliyetini ve replikasyon gecikmesi riskini büyütür. Uygun batch boyutunu tahmin etmek için 100, 500, 1000 ve 5000 satırlık batch'lerde p95 süreyi, üretilen WAL byte miktarını ve replica lag değerini ölçün. PostgreSQL'de toplu yükleme için satır satır INSERT yerine COPY kullanmak, istemci-sunucu round trip sayısını somut olarak azaltır.

-- Tek tek INSERT yerine kontrollu batch ile COPY kullanin.
BEGIN;
COPY staging_events(event_id, account_id, payload, created_at)
FROM STDIN WITH (FORMAT csv);
-- istemci burada CSV satirlarini gonderir
\.
INSERT INTO events(event_id, account_id, payload, created_at)
SELECT event_id, account_id, payload, created_at
FROM staging_events
ON CONFLICT (event_id) DO NOTHING;
COMMIT;

SQL Server eğitimi alan ekipler aynı yaklaşımı sys.dm_io_virtual_file_stats ve sys.dm_db_log_stats ile uygular. Veri dosyası ve transaction log gecikmesini ayırmak için aşağıdaki DMV sorgusunu yük testi öncesi ve sonrası çalıştırın. NoSQL eğitimi tarafında da karşılık aynıdır: MongoDB'de writeConcern: majority ve journaling gecikmesini mongostat ile, WiredTiger cache evictions değerleriyle birlikte inceleyin. Veri modeli farklı olsa da kalıcı yazma yolu, kuyruk doygunluğu ve dayanıklılık semantiği ölçülmeden yapılan ayar değişikliği güvenilir değildir.

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.type_desc,
       vfs.num_of_writes,
       CASE WHEN vfs.num_of_writes = 0 THEN 0
            ELSE vfs.io_stall_write_ms / vfs.num_of_writes END AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id
 AND mf.file_id = vfs.file_id
WHERE mf.database_id = DB_ID();

Sık Sorulan Sorular

Veritabanı performans ayarlama sırasında checkpoint sorunu nasıl kanıtlanır?

Aynı yük penceresinde pg_stat_wal farkını, log_checkpoints çıktısını ve iostat -x 1 verisini kaydedin. max_wal_size nedeniyle başlayan checkpoint ile p95 commit gecikmesi ve disk w_await aynı anda yükseliyorsa hipotez güçlenir. Ayarı değiştirdikten sonra aynı pgbench komutuyla ikinci ölçümü alın; yalnızca ortalama TPS değil en uzun checkpoint ve p95 gecikmeyi de karşılaştırın.

Database indexleme WAL miktarını neden artırır?

Her ek indeks, yazılan veya silinen satır için ek indeks sayfası değişikliği ve WAL kaydı demektir. İndekslenmiş bir kolon değiştiğinde PostgreSQL HOT update kullanamaz. EXPLAIN (ANALYZE, BUFFERS, WAL) ile aynı UPDATE'i aday indeks öncesi ve sonrası çalıştırarak WAL bytes ve full page images alanlarını doğrudan karşılaştırın.

SQL eğitimi kapsamında PostgreSQL'de wal_compression ne zaman test edilmelidir?

pg_stat_wal içindeki wal_fpi / wal_records oranı yüksekse wal_compression adaydır. Önce staging ortamında ALTER SYSTEM SET wal_compression = 'on' uygulayın, pg_reload_conf ile yükleyin ve aynı yazma testinde WAL byte farkını ölçün. Disk I/O azalırken CPU tüketimi artabilir; bu yüzden pidstat -u ile PostgreSQL süreç CPU'sunu da önce-sonra kaydedin.

SQL Server eğitimi için transaction log gecikmesi hangi DMV ile incelenir?

sys.dm_io_virtual_file_stats ile LOG tipindeki dosyanın io_stall_write_ms / num_of_writes değerini ölçün. Veri dosyası ve log dosyasını type_desc üzerinden ayrı raporlayın. Yük testi süresince log dosyasındaki ortalama yazma gecikmesi yükseliyorsa, sorgu planını değiştirmeden önce log diski kuyruğunu, autogrowth olaylarını ve batch transaction boyutunu inceleyin.