Veritabanı optimizasyonu: OLTP index stratejileri, write amplification ve ölçüm

OLTP için index seçimi ve write amplification ölçümü

OLTP tablolarında her ek indeks yazma maliyetini arttırır; bunu somut olarak ölçmek için PostgreSQL'de LSN farkını kullanın: önce LSN alıp workload çalıştırıp sonra tekrar ölçün. Komutlar:

SELECT pg_current_wal_lsn() AS lsn_before;
-- iş yükünü çalıştır (ör. pgbench)
SELECT pg_current_wal_lsn() AS lsn_after;
SELECT pg_wal_lsn_diff(lsn_before, lsn_after) AS wal_bytes;

Bu ölçüm size indeks eklemenin WAL üzerinden yazma amplifikasyonunu bayt bazında verir; neden: her ek indeks için ek B-tree güncellemesi WAL'a yazılır, dolayısıyla wal_bytes doğrudan yazma maliyetini yansıtır.

İndeksleri azaltma ya da kapsamlı (covering) indekslerle sorgu cevap süresini düşürürken write amplification'ı kontrol edin. Örnek: PostgreSQL'de sık kullanılan sorguya yönelik covering indeks oluşturmak için INCLUDE kullanabilirsiniz:

CREATE INDEX ix_orders_user_status ON orders (user_id, created_at) INCLUDE (status);
EXPLAIN (ANALYZE, BUFFERS) SELECT status FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;

Neden: INCLUDE ile sık okunan sütunları index'te tutarak heap erişimlerini engellersiniz; maliyet mekanizması: okuma maliyeti düşerken index boyutu artar, bu da WAL ve page-splitting üzerinde etkili olur.

Deneysel adım: aynı workload'u 'önce' indeksi varken, 'sonra' indeksi silip yeniden ölçün. Örnek pgbench komutu ve ölçüm akışı:

pgbench -c 20 -j 4 -T 60 -f myscript.sql mydb
-- ölçüm öncesi LSN
SELECT pg_current_wal_lsn();
-- ölçüm sonrası LSN ve wal farkı
SELECT pg_wal_lsn_diff(lsn_before, lsn_after);

Bu before/after karşılaştırması ile indeksin yazma maliyetine etkisini yüzdelik olarak hesaplayabilirsiniz: (wal_after - wal_before) / wal_before * 100.

Sorgu profilleme: EXPLAIN, pg_stat_statements ve sistem metrikleri

Sorgu performansını anlamak için EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) kullanın ve kritik sorguları pg_stat_statements ile sıralayın. Kurulum ve örnek sorgular:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...;

Neden: pg_stat_statements toplam CPU ve I/O tüketimini gösterir; EXPLAIN ANALYZE ile planın gerçek yürütme istatistiklerini elde edersiniz, mekanizma: planner tahmini ile gerçek istatistiklerin farkı size selectivity/estimation bug'larını işaret eder.

Sistem seviyesi profil için iostat/htop veya perf kullanın; örnek Linux komutları:

sudo apt-get install sysstat
iostat -dx 1 10
sudo perf record -a -g -- sleep 30
sudo perf report

Bu sayede disk I/O ve kernel/user CPU hot spot'larını görebilirsiniz; neden: sık indeks güncellemeleri I/O beklemelerini artırır, perf ile fonksiyon seviyesinde CPU kullanım nedenini görebilirsiniz.

Before/after karşılaştırması için metrik koleksiyonunu otomatikleştirin: Prometheus node_exporter ile metrik toplayın ve sorgu değişimlerinden önce/sonra snapshot alın. Örnek PromQL:

increase(pg_stat_database_xact_commit{job='pg'}[1m])
rate(node_disk_io_time_seconds_total[5m])

Neden: zaman serisi karşılaştırması anomali tespiti yapmanızı sağlar; tek seferlik ölçümlerde gürültü olabilir, dolayısıyla 95th percentile veya rate bazlı karşılaştırma tercih edin.

İndeks maliyeti: SQL Server araçları, FILLFACTOR ve operasyonel istatistikler

SQL Server'da indeksin yazma maliyetini ölçmek için sys.dm_db_index_operational_stats ve sys.dm_db_index_usage_stats kullanın:

SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL);
SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID() AND object_id = OBJECT_ID('dbo.Orders');

Bu DMV'ler page splits, leaf-insertlar ve update/delete istatistiklerini verir; neden: page split sayısı doğrudan disk yazma ve log üretimini artırır.

FILLFACTOR ile page-splitting'i azaltarak write amplification'ı kontrol edebilirsiniz; SQL Server örneği:

ALTER INDEX IX_Orders_UserId ON dbo.Orders REBUILD WITH (FILLFACTOR = 70);
-- veya yeni indeks
CREATE INDEX IX_Orders_UserId ON dbo.Orders (UserId) WITH (FILLFACTOR = 80);
-- ölçüm: page splits before/after
SELECT page_split_count = sample_page_split_count FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL);

Neden: daha düşük fillfactor boşluk bırakarak page splitleri azaltır; mekanizma: eklenen boş alan yeni satırın mevcut sayfaya yerleşmesini sağlar, fakat tampon/pagedb boyutu ve cache etkilerini de göz önünde bulundurmanız gerekir.

Edge case: FILLFACTOR çok düşük ayarlanırsa cache verimliliği düşer ve okuma maliyeti artar. Bu yüzden A/B testi yapın: before/after için cache hit ratio, page split sayısı ve transaction log büyümesini karşılaştırın. Örnek metrik sorgusu:

SELECT record_id, io_stall_read_ms, io_stall_write_ms FROM sys.dm_io_virtual_file_stats(NULL, NULL);
-- transaction log büyümesi izleme: sys.fn_dblog veya backup log lsn karşılaştırması

NoSQL için secondary index stratejileri: MongoDB örneği ve maliyet analizi

MongoDB'de secondary-index yazma maliyetini ölçmek için mongostat/mongotop ve explain('executionStats') kullanın; indeks oluştururken partial index ile yazma yükünü azaltabilirsiniz:

db.orders.createIndex({userId: 1, createdAt: -1}, {partialFilterExpression: {status: 'active'}})
db.orders.find({userId:123}).explain('executionStats')

Neden: partialFilterExpression ile sadece belirli belgeler için indeks oluşturulur; mekanizma: daha az index entry = daha az write amplification ve daha küçük index cache.

Ayrıca MongoDB'de background index oluşturma ve rolling index deployment yöntemini kullanın: ilk olarak replica set üzerinde secondary'de index oluşturup sonra primary'ya failover ile taşıma yapın. Komut örneği:

rs.stepDown()
-- secondary üzerinde index oluştur
db.orders.createIndex({userId:1}, {background:true})
-- tekrar primary'yi geri al

Bu prosedür downtime'ı azaltır, ancak replika gecikmesini ve oplog büyümesini izleyin (mongostat output ve rs.printReplicationInfo()).

Edge case: çok kısa TTL'li belgeler ile index oluşturmamak genellikle daha iyidir; çünkü index entry kısa süre sonra silinir ve index churn artar. Ölçüm adımı: mongostat ile insert/delete rate'i ve index miss ratio'yu karşılaştırın.

Pratik vaka: before/after profilleme akışı ve otomatik test adımları

Adım adım A/B test akışı (PostgreSQL örneği): 1) baseline LSN ve pg_stat_statements snapshot; 2) pgbench ile yük çalıştır; 3) LSN farkı, EXPLAIN ANALYZE ve iostat/perf toplama; 4) indeks değişikliği (CREATE/DROP veya ALTER INDEX); 5) aynı ölçümleri tekrar alıp yüzdelik değişimi hesaplayın. Komut örneği akış:

-- Baseline
SELECT pg_current_wal_lsn() AS lsn_before;
SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
pgbench -c 20 -j 4 -T 60 mydb > /tmp/pgbench_before.out
-- Değişiklik: örn. indeks ekle
CREATE INDEX ix_temp ON orders (user_id) INCLUDE (status);
-- Test tekrarı
pgbench -c 20 -j 4 -T 60 mydb > /tmp/pgbench_after.out
SELECT pg_current_wal_lsn() AS lsn_after;
SELECT pg_wal_lsn_diff(lsn_before, lsn_after) AS wal_bytes;

Bu akış size hem sistemsel (WAL, I/O) hem de sorgu bazlı (pg_stat_statements, EXPLAIN) before/after kıyaslamasını verir.

Otomasyon önerisi: ölçümleri toplayıp sonuçları JSON olarak saklayın ve yüzde değişimini hesaplayacak bir script yazın. Minimal örnek Python betiği:

import subprocess, json
# çalıştır: psql -c "SELECT pg_current_wal_lsn()"
# parse et ve karşılaştır

Neden: elle hesaplamalar hata yapmaya açıktır; script ile test koşullarını tekrarlanabilir hale getirin ve CI pipeline'ına entegre edin (ör. Jenkins/GitHub Actions).

Best practice: indeks değişikliklerini production'da doğrudan uygulamak yerine staging üzerinde aynı veri yüzdesiyle (sampling) test edin; örnek sampling: PostgreSQL'de tabloyu CLUSTER/pg_repack yerine partitioning ile deneyin veya SQL Server'da filtered index ile önce küçük partition'da test edin. Ölçümlerde latency p99, p95 ve throughput'u birlikte raporlayın; sadece avg latency yanıltıcıdır.

Sık Sorulan Sorular

veritabanı optimizasyonu yaparken write amplification nasıl ölçülür?

PostgreSQL için pratik yöntem LSN farkını kullanmaktır: testten önce SELECT pg_current_wal_lsn() ile lsn_before alın, yükü çalıştırın (ör. pgbench), sonra lsn_after alın ve SELECT pg_wal_lsn_diff(lsn_before, lsn_after) ile WAL baytını hesaplayın. SQL Server'da ise transaction log büyümesini sys.fn_dblog veya log dosya boyutundaki değişimle izleyin; ayrıca sys.dm_db_index_operational_stats ile page split sayısını karşılaştırın.

database indexleme yaparken sql server eğitimi kapsamında hangi DMV'leri kullanmalıyım?

SQL Server için öncelikli DMV'ler: sys.dm_db_index_usage_stats (kullanım), sys.dm_db_index_operational_stats (page splits, leaf insert), sys.dm_io_virtual_file_stats (I/O beklemeleri). Örnek: SELECT * FROM sys.dm_db_index_operational_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL) ile page_split_count ve leaf_insert_count kontrol edin; bu sayede indeksin yazma maliyetini nicel ölçersiniz.

nosql eğitimi bağlamında MongoDB'de secondary index'in maliyetini nasıl azaltırım?

MongoDB'de maliyeti azaltmak için partial index, sparse index ve TTL kullanın; örnek: db.orders.createIndex({userId:1}, {partialFilterExpression: {status:'active'}}). Ayrıca index oluşturmayı replica set üzerinde secondary'de background:true ile yapıp failover kullanarak production etkisini azaltabilirsiniz. Etkiyi mongostat ve explain('executionStats') ile ölçün.

sql eğitimi almış biri için indeks değişikliğinin performansa etkisini hangi metriklerle raporlamalıyım?

Raporlama için mutlaka: WAL/log büyümesi (bayt), disk I/O (iostat read/write throughput), page split sayısı (DMV/pg stat), sorgu p95/p99 latencileri, throughput (tps) ve cache hit ratio. Bu metrikleri before/after karşılaştırarak yüzde değişimlerini sunun; örnek hesaplama: (wal_after - wal_before)/wal_before*100.