Veritabanı Performans Ayarlama: Index, Profil ve Sorgu Optimizasyonları

Profiling ile darboğaz tespiti - veritabanı performans ayarlama

Adım 1: ölçümlenebilir bir temel (baseline) alın. PostgreSQL'de psql ile EXPLAIN (ANALYZE, BUFFERS) kullanın ve pg_stat_statements'ten sorgu bazlı toplam zamanı okuyun. Örnek komutlarla önceki durumu yakalayın ve testten sonra aynı komutları tekrar çalıştırarak karşılaştırın. Somut komutlar:

-- PostgreSQL: baseline ölçümü
psql -d mydb -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;"
-- Toplam süreleri pg_stat_statements'ten almak için
psql -d mydb -c "SELECT query, calls, total_time, mean_time FROM pg_stat_statements WHERE query LIKE '%FROM orders WHERE customer_id%';"

Neden bu ölçüm? EXPLAIN (ANALYZE, BUFFERS) ayrıntılı olarak CPU/timing ve buffer I/O sayılarını verir; pg_stat_statements ise sıcak sorguların toplam etkisini gösterir. Değişiklik sonrası 'total_time' ve 'shared_blks_read' gibi metriklerdeki azalma yüzdeyle ölçülür: önce = A, sonra = B, iyileşme = (A-B)/A*100.

SQL Server ortamı için benzer yaklaşım: Query Store'u veya sys.dm_exec_query_stats'i kullanarak bazelini alın, sonra değişiklik yapıp yeniden çalıştırın. Örnek T-SQL ile Query Store aktivasyonu ve temel sorgu istatistikleri alma:

-- SQL Server: Query Store açma
ALTER DATABASE [MyDb] SET QUERY_STORE = ON;
-- Özet istatistikler
SELECT qsqt.query_sql_text, rs.avg_duration, rs.count_executions
FROM sys.query_store_query_text qsqt
JOIN sys.query_store_query q ON qsqt.query_text_id = q.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

İndeks tasarımı, fillfactor ve rebuild stratejisi - database indexleme

İndeks stratejisinde amaç sadece ekleme yapmak değil; sayfa doluluğu ve page-split'leri kontrol ederek yazma maliyetini düşürmektir. SQL Server'da rebuild sırasında FILLFACTOR ayarıyla sayfa doluluğunu belirleyebilirsiniz. Örnek:

-- SQL Server: indeks rebuild ile fillfactor
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD WITH (FILLFACTOR = 80);
-- Alternatif reorganize (çevrim içi, daha az maliyetli)
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REORGANIZE;

PostgreSQL'de online reindex alternatifleri: CREATE INDEX CONCURRENTLY ve pg_repack. pg_repack, büyük tablolar için online repack/reindex sağlar. Kurulum ve kullanım örneği:

# sistemde pg_repack kurulu olmalı
pg_repack --table 'public.orders' --dbname=mydb --host=dbhost --username=repuser
# ya da PostgreSQL'de yeni indeks oluşturup eskiyi drop'lamak
psql -d mydb -c "CREATE INDEX CONCURRENTLY idx_orders_customer_created ON orders (customer_id, created_at DESC);"

Bloat ve fiziksel istatistikleri ölçün: PostgreSQL'de pgstattuple ile index ve table bloat'u ölçün, SQL Server'da sys.dm_db_index_physical_stats ile fragmentation ve page count inceleyin. Örnek PostgreSQL sorgusu:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT * FROM pgstattuple('public.orders');

Bu metrikler -dead_tuple_count, free_percent- ne kadar fiziksel verinin yeniden düzenlenmesi gerektiğini anlatır.

Sorgu planı ve istatistiklerin ayarlanması - veritabanı optimizasyonu

Plan kötüleşmelerinin çoğu hatalı satır tahmini (row estimate) kaynaklıdır. PostgreSQL'de bir sütun için istatistik hedefini yükseltip ANALYZE çalıştırmak sıklıkla doğru plan üretir. Örnek:

-- PostgreSQL: tek kolon için daha fazla histogram detayı
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 200;
ANALYZE VERBOSE orders;

Mekanizma - neden etkili? Artan statistics_target daha fazla histogram bin'i ve daha doğru selectivity tahmini sağlar; bu da join sırası, index vs seq scan kararlarını etkiler. Ancak maliyeti arttırır: ANALYZE süresi uzar ve daha fazla disk/CPU harcanır; bu yüzden yalnızca yüksek etkiye sahip kolonlar için kullanın.

SQL Server tarafında benzer olarak UPDATE STATISTICS ve DBCC SHOW_STATISTICS kullanın; plan sorunlarını izlemek için Extended Events veya Query Store'dan problematic planları yakalayın. Örnek:

-- SQL Server: tek tablo için istatistik güncelleme
UPDATE STATISTICS dbo.Orders (IX_Orders_CustomerId) WITH FULLSCAN;
-- Histogram kontrolü
DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_CustomerId');

NoSQL işyükleri için indeks ve veri lokalitesi - nosql eğitimi

MongoDB örneği: explain('executionStats') ile sorgunun gerçekten indeks kullandığını ve kaç dokümanın tarandığını görün. Komut:

// Mongo shell
db.orders.createIndex({customerId: 1, createdAt: -1});
db.orders.find({customerId: 123}).sort({createdAt: -1}).explain('executionStats');

executionStats içinde totalDocsExamined ve indexKeysExamined değerleri size gerçek maliyeti söyler.

NoSQL indeks seçiminde dikkat: çok sayıda küçük indeks yazma maliyetini yükseltir; tek bir compound indeksle hem filtre hem sıralamayı karşılayabiliyorsanız 'covered query' elde edersiniz. Ölçüm için mongostat ve mongotop kullanın:

# terminal
mongostat --host dbhost:27017 --rowcount 5
mongotop 1 5

Bu araçlar I/O, insert/update oranları ve sorgu temposunu gösterir.

Edge case: MongoDB'de yüksek cardinality olmayan (ör. boolean) alanlara indeks koymak genellikle zararlı olur; sharded cluster'da shard key seçimi yazma ve veri lokalitesi üzerinde doğrudan etkilidir. Test yöntemi: gerçek işyükünü simüle eden bir yük testi ile önce/sonra 'indexKeysExamined' ve p99 latency ölçün.

Sık Sorulan Sorular

veritabanı optimizasyonu için önce hangi metriklere bakmalıyım?

Önce latency ve throughput ölçün, sonra I/O ve CPU kaynaklarını ilişkilendirin. PostgreSQL için EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements toplam_time ve shared_blks_read; sistem seviyesi için iostat -x 1 ve vmstat 1. SQL Server için Query Store ve sys.dm_exec_query_stats; disk I/O için perfmon counters: LogicalDisk\Avg. Disk sec/Read. Baseline -> değişiklik -> yeniden ölçüm ile iyileşmeyi yüzde olarak raporlayın.

sql server eğitimi esnasında hangi profilleme araçlarını önceliklendirmeliyim?

Öncelik: Query Store (sorgu performans geçmişi ve plan karşılaştırması), Extended Events (hafif, granular olay yakalama) ve sys.dm_exec_* DMVs (anlık durum). Örnek XE session oluşturma:

CREATE EVENT SESSION [LongRunning] ON SERVER
ADD EVENT sqlserver.sql_statement_completed(WHERE duration > 1000000)
ADD TARGET package0.event_file(SET filename=N'longruns.xel');
ALTER EVENT SESSION [LongRunning] ON SERVER STATE = START;

nosql eğitimi kapsamında MongoDB'de index seçimi nasıl test edilir?

Gerçek sorgularla 'explain("executionStats")' çalıştırın ve indexKeysExamined ile totalDocsExamined'i karşılaştırın. Ayrıca mongostat ile write/read oranlarını gözlemleyin. Örnek akış: 1) mevcut sorguyu explain ile kaydet, 2) yeni index oluştur (db.collection.createIndex), 3) aynı sorguyu tekrar explain ile çalıştır, 4) totalKeysExamined düşmüş ve executionTime azalmışsa index uygundur.

database indexleme yaparken rebuild mi reorganize mi tercih etmeliyim?

SQL Server için fragmentation seviyesi belirleyici: avg_fragmentation_in_percent > 30 ise REBUILD, 5-30 arası ise REORGANIZE uygundur. Örnek sorgu ile karar verin:

SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('MyDb'), OBJECT_ID('dbo.Orders'), NULL, NULL, 'LIMITED');

PostgreSQL'de büyük tablolar için CREATE INDEX CONCURRENTLY veya pg_repack tercih edin.