Veritabanı Performans Ayarlama: Sorgu Seviyesinde İndeks ve Ölçüm Rehberi

Çalışma yükü analizi ile veritabanı optimizasyonu başlangıç ölçümleri

İlk adım doğru ölçüm: PostgreSQL için pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS) ve pg_buffercache; SQL Server için Query Store ve Extended Events kullanın. Örnek: PostgreSQL'de istatistikleri sıfırlayıp 5 dakika yük çalıştırıp sonra en ağır 10 sorguyu almak için

SELECT queryid, calls, total_time, mean_time, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Bu adım veritabanı eğitimi veya sql eğitimi bağlamında, hangi sorguların optimize edileceğini nesnel olarak belirler.

Profiling süreci için adımlar: (1) ölçümleri sıfırla (Postgres: pg_stat_statements_reset(); SQL Server: temiz Query Store snapshot), (2) standart iş yükünü çalıştır, (3) EXPLAIN ANALYZE ile planları al, (4) değişiklik uygula, (5) tekrar ölç. Örnek reset komutu:

-- PostgreSQL
SELECT pg_stat_statements_reset();
-- SQL Server (T-SQL)
ALTER DATABASE CURRENT SET QUERY_STORE CLEAR ALL; -- dikkat: production ortamda planlı zamanlama ile

Database indexleme: index kullanım heatmap'i ve canlı örnek

Sıcaklık haritası (heatmap) fikir verir: hangi index bloklarının sık kullanıldığını görmek için PostgreSQL'de pg_buffercache uzantısını kurun ve blok kullanımını toplayın. Kurulum ve örnek sorgu:

-- Postgres: enable extension (superuser)
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
-- Örnek: tablo başına cached page sayısı
SELECT c.relname, count(*) AS cached_pages
FROM pg_buffercache b
JOIN pg_class c ON b.relfilenode = pg_relation_filenode(c.oid)
WHERE c.relname = 'orders'
GROUP BY c.relname;

Bu sayede hangi tablolar/indexlerin bellekten ne kadar okunduğunu sayısal olarak görebilirsiniz.

Canlı sampling için küçük Python betiği: her 5 saniyede pg_stat_activity ve EXPLAIN ANALYZE planını çekip index kullanımını sayıp CSV'ye yazın. Bu, database indexleme stratejisini iş yüküne göre veriyle şekillendirmenizi sağlar. Örnek minimal betik:

import psycopg2, time, csv
conn = psycopg2.connect('host=127.0.0.1 dbname=app user=me')
cur = conn.cursor()
with open('plan_samples.csv','w',newline='') as f:
    w = csv.writer(f)
    w.writerow(['ts','query','total_time'])
    for _ in range(12):
        cur.execute("SELECT query FROM pg_stat_activity WHERE state='active' AND pid <> pg_backend_pid() LIMIT 1")
        q = cur.fetchone()
        if q:
            cur.execute('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' + q[0])
            plan = cur.fetchone()[0]
            total = plan[0]['Plan']['Actual Total Time'] if isinstance(plan, list) else 0
            w.writerow([time.time(), q[0][:200].replace('\n',' '), total])
        time.sleep(5)

Partial ve covering index ile sorgu hızlandırma: gerçek EXPLAIN karşılaştırması

Partial index ve INCLUDE sütunlu (covering) index birleşimi, IO ve heap-fetch azaltarak sorgu sürelerini düşürür. Örnek tablo ve sorgu ile önce/sonra ölçümü:

-- Örnek tablo ve veri hazırlığı
CREATE TABLE orders (id serial PRIMARY KEY, customer_id int, status text, created_at timestamptz, total numeric);
CREATE INDEX ON orders (customer_id);
-- Sorgu: aktif status olan ve created_at aralığında olan kayıtlar
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE status='paid' AND created_at > now() - interval '30 days' AND customer_id = 42;

-- Optimizasyon: partial index + covering
CREATE INDEX orders_paid_recent_idx ON orders (customer_id, created_at) INCLUDE (total) WHERE status = 'paid' AND created_at > now() - interval '90 days';
VACUUM ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS) SELECT id, total FROM orders WHERE status='paid' AND created_at > now() - interval '30 days' AND customer_id = 42;

Gerçek ölçüm yaklaşımı: EXPLAIN ANALYZE çıktısındaki 'Buffers: shared hit=X read=Y' değerlerini karşılaştırın; hedef shared_blks_read ve heap_fetches'in düşmesi.

Neden işe yarar - mekanizma: COVERING index ile index-only scan mümkünse heap fetch azalır; ama index-only scan için visibility map bitleri set olmalı. Bu yüzden değişiklikten sonra VACUUM (veya autovacuum) ile visibility map güncellenmesini sağlamak gereklidir. Ölçüm adımı:

-- Visibility map kontrolü
SELECT relname, heap_blks_read, heap_blks_hit FROM pg_statio_user_tables WHERE relname='orders';
-- vacuum sonrası tekrar ölçün
VACUUM VERBOSE orders;
SELECT * FROM pg_statio_user_tables WHERE relname='orders';

İndeks yeniden düzenleme, fillfactor ve plan stabilitesi (SQL Server ve PostgreSQL)

İndekste fragmentation, write amplification ve plan değişikliğine yol açar. SQL Server'da online rebuild, PostgreSQL'de REINDEX CONCURRENTLY veya pg_repack kullanın. SQL Server örneği:

ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);
-- Fragmentation ölçümü
SELECT index_id, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'LIMITED');

Bu komutlarla önce-sonra fragmentation ve CPU/IO etkisini ölçün.

PostgreSQL için örnek: fillfactor ayarlama ve pg_repack kullanımı ile online rebuild. Ölçüm için iostat ve EXPLAIN (ANALYZE, BUFFERS) kullanın. Komutlar:

-- Fillfactor değiştirme (yeni index oluşturma örneği)
CREATE INDEX CONCURRENTLY CONCURRENTLY_idx ON orders(customer_id) WITH (FILLFACTOR=70);
-- Alternatif: pg_repack (örnek işletim sistemi komutu, paket kurulu olmalı)
pg_repack --table=public.orders postgresql://user:pwd@127.0.0.1/appdb
-- IO ölçümü (Linux)
iostat -x 1 5

Ölçüm mantığı: rebuild öncesi iostat ve EXPLAIN ile IO/cpu profili alın, rebuild sonrası aynı işleri tekrarlayıp delta'yı raporlayın.

Plan stabilitesi için: SQL Server'da Query Store'dan 'force plan' kullanabilir, PostgreSQL'de plan değişikliğini tetikleyen parametreleri (random_page_cost, effective_cache_size) testle ve EXPLAIN ile doğrula. Örnek:

-- Postgres test: geçici olarak random_page_cost azalt ve plan değişimini kontrol et
SET LOCAL random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- geri döndürmek için transaction dışına çık

Köşe durumlar, sık yapılan hatalar ve operasyonel kontroller

Yaygın hata: gereksiz indeks oluşturmak. Ölçümle doğrulayın: yeni indeks sonrası idx_scan, idx_tup_read artışı beklenir; aksi halde indeksi kaldırın. Postgres'te indeksin kullanılıp kullanılmadığını görmek için:

SELECT indexrelid::regclass AS index, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes WHERE schemaname='public' AND tablename='orders';
-- 30 gün sonra idx_scan h'l' 0 ise indeksi düşürün

NoSQL bağlamı: nosql eğitimi kapsamında, benzer 'sıcak anahtar' analizini Redis/Elastic için slowlog veya hot shards ile yapın. Örneğin Redis için:

redis-cli SLOWLOG GET 128
-- sık çağrılan anahtarları görmek için latency top komutlarını kullanın

Böylece veritabanı mimarisinde doğru yerde index/partition/denormalize kararları alın.

Rollback ve test: değişiklikleri production'a uygulamadan önce staging üzerinde pgbench veya HammerDB ile yük testleri yapın ve EXPLAIN ANALYZE sonuçlarını, pg_stat_statements'teki toplam süreyi karşılaştırın. Örnek komut:

pgbench -c 20 -j 4 -T 60 -f myscript.sql postgres
-- sonuçları karşılaştırmak için sorgu sürelerini kolekte edin

Sık Sorulan Sorular

veritabanı optimizasyonu için hangi metrikleri önce almalıyım?

Önceliklerle başlayın: EXPLAIN (ANALYZE, BUFFERS) çıktısındaki toplam süre ve Buffers (hit/read), pg_stat_statements ile total_time ve calls, işletim sistemi seviyesinde iostat veya perf ile IO bekleme süreleri. PostgreSQL örneği:

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
SELECT queryid, calls, total_time FROM pg_stat_statements WHERE query LIKE '%your_query%';
iostat -x 1 3

Bu metrikler değişikliklerin etkisini sayısal olarak gösterir.

database indexleme yaparken partial index mi yoksa covering index mi tercih etmeli?

Sorgunun filtre koşulları ve dönen sütunları belirleyici. Eğer WHERE koşulu sabit bir duruma işaret ediyorsa partial index daha küçük ve daha düşük yazma maliyetlidir; eğer sorgu birkaç sütun için sık sık seleksiyon yapıp ek sütunları da döndürüyorsa INCLUDE ile covering index index-only scan'e izin vererek heap fetch'i azaltır. Kararı doğrulamak için EXPLAIN ANALYZE ve pg_stat_user_indexes idx_scan/idx_tup_read değerlerini karşılaştırın.

sql server eğitimi bağlamında indeks rebuild ve fillfactor uygulamasını nasıl ölçmeliyim?

SQL Server'da önce sys.dm_db_index_physical_stats ile fragmentation ölçün, sonra ALTER INDEX ... REBUILD WITH (ONLINE=ON, FILLFACTOR=X) uygulayın ve sys.dm_db_index_operational_stats ile IO/metrikleri takip edin. Ayrıca Query Store'dan sorgu süresi histogramını before/after karşılaştırın. Örnek sorgular:

SELECT avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.Orders'), NULL, NULL, 'LIMITED');
ALTER INDEX IX_Orders_CustomerId ON dbo.Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 80);
-- Query Store snapshot karşılaştırması GUI veya T-SQL ile yapılır