Database Performance Tuning & Profiling

# postgresql.conf  
# 32 GB RAM'li dedicated sunucu örneği  
shared_buffers = 8GB  
effective_cache_size = 24GB   # OS cache dahil toplam beklenti

-- Global ayar (ihtiyatlı)  
-- postgresql.conf  
work_mem = 64MB  
  
-- Ağır rapor sorgusu için session bazlı yükseltme  
SET work_mem = '256MB';  
EXPLAIN (ANALYZE, BUFFERS)  
SELECT customer_id, SUM(amount)  
FROM orders  
GROUP BY customer_id  
ORDER BY SUM(amount) DESC;

-- Index bloat tahmini (pgstattuple extension gerekir)  
CREATE EXTENSION IF NOT EXISTS pgstattuple;  
  
SELECT  
  schemaname || '.' || relname AS table,  
  indexrelname AS index,  
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,  
  round(100 * (pgstatindex(indexrelid)).leaf_fragmentation, 1) AS bloat_pct  
FROM pg_stat_user_indexes  
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')  
ORDER BY pg_relation_size(indexrelid) DESC  
LIMIT 20;  
  
-- Kesintisiz rebuild  
REINDEX INDEX CONCURRENTLY idx_orders_created_at;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  
  
-- En pahalı 10 sorgu (toplam süre)  
SELECT  
  round(total_exec_time::numeric, 1) AS total_ms,  
  calls,  
  round(mean_exec_time::numeric, 2) AS mean_ms,  
  round((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 1) AS pct,  
  left(query, 80) AS query  
FROM pg_stat_statements  
ORDER BY total_exec_time DESC  
LIMIT 10;

# my.cnf — 64 GB RAM dedicated MySQL sunucu  
[mysqld]  
innodb_buffer_pool_size = 48G  
innodb_buffer_pool_instances = 8   # her instance ~6 GB  
innodb_buffer_pool_chunk_size = 128M

-- Buffer pool hit ratio kontrolü  
SELECT  
  ROUND(  
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100, 2  
  ) AS hit_ratio_pct  
FROM  
  (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads  
   FROM performance_schema.global_status  
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS a,  
  (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests  
   FROM performance_schema.global_status  
   WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS b;  
-- Hedef: >= 99.0

# my.cnf  
[mysqld]  
slow_query_log = 1  
slow_query_log_file = /var/log/mysql/slow.log  
long_query_time = 0.5  
log_queries_not_using_indexes = 1  
log_slow_admin_statements = 1

# En pahalı sorguları özetle (Percona Toolkit)  
pt-query-digest /var/log/mysql/slow.log \  
  --limit 10 \  
  --order-by Query_time:sum

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)  
SELECT o.id, c.name, SUM(oi.qty * oi.price) AS total  
FROM orders o  
JOIN customers c ON c.id = o.customer_id  
JOIN order_items oi ON oi.order_id = o.id  
WHERE o.created_at >= NOW() - INTERVAL '30 days'  
GROUP BY o.id, c.name  
ORDER BY total DESC  
LIMIT 50;  
  
-- Dikkat edilecek satırlar:  
-- Seq Scan on orders  → index eksik olabilir  
-- Buffers: shared read=8421  → disk'ten okundu, cache miss  
-- Rows Removed by Filter: 1.2M  → WHERE selektivitesi düşük

-- Yavaş: tabloya geri dönmek zorunda  
-- EXPLAIN: type=ref, Extra=Using where  
SELECT email, created_at  
FROM users  
WHERE status = 'active'  
ORDER BY created_at DESC  
LIMIT 20;  
  
-- Covering index  
CREATE INDEX idx_users_status_created_email  
  ON users (status, created_at DESC, email);  
  
-- Artık: Extra=Using index  → tabloya hiç dokunulmaz

# postgresql.conf — global  
autovacuum_max_workers = 4  
autovacuum_vacuum_scale_factor = 0.05  
autovacuum_analyze_scale_factor = 0.02  
autovacuum_work_mem = 1GB

-- Tablo bazlı override (en yoğun tablo)  
ALTER TABLE orders SET (  
  autovacuum_vacuum_scale_factor = 0.02,  
  autovacuum_vacuum_threshold = 1000,  
  autovacuum_analyze_scale_factor = 0.01  
);  
  
-- Wraparound riski izleme  
SELECT  
  datname,  
  age(datfrozenxid) AS xid_age,  
  round(age(datfrozenxid)::numeric / 2000000000 * 100, 1) AS wraparound_pct  
FROM pg_database  
ORDER BY xid_age DESC;  
-- wraparound_pct > 70 → kritik alarm

-- ❌ N+1 (uygulama sözde-kodu)  
-- orders = SELECT * FROM orders LIMIT 100  
-- for o in orders:  
--     customer = SELECT * FROM customers WHERE id = o.customer_id  
  
-- ✅ Tek sorgu — JOIN  
SELECT o.id, o.total, c.name, c.email  
FROM orders o  
JOIN customers c ON c.id = o.customer_id  
ORDER BY o.created_at DESC  
LIMIT 100;  
  
-- ✅ Toplu fetch — ANY / IN  
SELECT id, name, email  
FROM customers  
WHERE id = ANY(ARRAY[1, 5, 12, 44, 89]);  -- PostgreSQL  
  
SELECT id, name, email  
FROM customers  
WHERE id IN (1, 5, 12, 44, 89);            -- MySQL

SELECT  
  schemaname || '.' || relname AS table,  
  seq_scan,  
  idx_scan,  
  n_live_tup AS rows,  
  round(  
    CASE WHEN (seq_scan + idx_scan) = 0 THEN 0  
    ELSE 100.0 * idx_scan / (seq_scan + idx_scan) END, 1  
  ) AS idx_usage_pct,  
  pg_size_pretty(pg_relation_size(relid)) AS size  
FROM pg_stat_user_tables  
WHERE n_live_tup > 10000  
  AND seq_scan > 1000  
ORDER BY seq_scan DESC  
LIMIT 20;

EXPLAIN FORMAT=JSON  
SELECT o.id, SUM(oi.qty * oi.unit_price) AS total  
FROM orders o  
JOIN order_items oi ON oi.order_id = o.id  
WHERE o.status = 'shipped'  
  AND o.created_at >= '2026-01-01'  
GROUP BY o.id  
ORDER BY total DESC  
LIMIT 20\G  
  
-- Odaklanılacak alanlar:  
-- cost_info.query_cost  
-- rows_examined_per_scan  
-- used_key_parts  
-- using_filesort / using_temporary_table

-- ❌ Tüm satırları indeksle (şişman)  
CREATE INDEX idx_orders_status ON orders (status, created_at);  
  
-- ✅ Sadece açık siparişler  
CREATE INDEX idx_orders_open_created  
  ON orders (created_at DESC)  
  WHERE status = 'open';  
  
-- Soft-delete pattern  
CREATE INDEX idx_users_active_email  
  ON users (email)  
  WHERE deleted_at IS NULL;  
  
-- Sorgu partial index'i kullanır:  
SELECT * FROM orders  
WHERE status = 'open'  
  AND created_at >= NOW() - INTERVAL '7 days';

-- Kullanılmayan index'ler (MySQL 5.7+ / 8.0 sys schema)  
SELECT * FROM sys.schema_unused_indexes  
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');  
  
-- Redundant index'ler  
SELECT * FROM sys.schema_redundant_indexes  
WHERE table_schema = 'app_production';  
  
-- Güvenli kaldırma (önce staging'de dene)  
-- ALTER TABLE orders DROP INDEX idx_orders_old_status;

# pgbouncer.ini  
[databases]  
app_db = host=127.0.0.1 port=5432 dbname=app_db  
  
[pgbouncer]  
listen_addr = 0.0.0.0  
listen_port = 6432  
auth_type = scram-sha-256  
auth_file = /etc/pgbouncer/userlist.txt  
pool_mode = transaction  
max_client_conn = 1000  
default_pool_size = 40  
min_pool_size = 10  
reserve_pool_size = 10  
server_reset_query = DISCARD ALL  
ignore_startup_parameters = extra_float_digits

-- PostgreSQL tarafı: gerçek bağlantı tavanı  
-- postgresql.conf  
max_connections = 100   -- pool arkasında düşük tut

# my.cnf  
[mysqld]  
# Full durability (varsayılan, önerilen)  
innodb_flush_log_at_trx_commit = 1  
sync_binlog = 1  
  
# Yüksek yazma, 1 sn kayıp tolere edilebilir  
# innodb_flush_log_at_trx_commit = 2  
# sync_binlog = 1

-- Runtime kontrol  
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

-- Zaman serisi olay tablosu  
CREATE TABLE events (  
  id          bigserial,  
  created_at  timestamptz NOT NULL DEFAULT now(),  
  payload     jsonb  
) PARTITION BY RANGE (created_at);  
  
-- BRIN: pages_per_range ayarı trade-off  
CREATE INDEX idx_events_created_brin  
  ON events USING BRIN (created_at)  
  WITH (pages_per_range = 32);  
  
-- Karşılaştırma  
SELECT  
  indexrelname,  
  pg_size_pretty(pg_relation_size(indexrelid)) AS size  
FROM pg_stat_user_indexes  
WHERE relname = 'events';

-- ❌ Yavaş  
INSERT INTO events (user_id, event_type) VALUES (1, 'click');  
INSERT INTO events (user_id, event_type) VALUES (2, 'view');  
-- ... 10.000 kez  
  
-- ✅ Multi-value batch  
INSERT INTO events (user_id, event_type, created_at) VALUES  
  (1, 'click', NOW()),  
  (2, 'view',  NOW()),  
  (3, 'click', NOW());  
  -- 500–1000 satır/statement ideal  
  
-- ✅ Dosyadan bulk  
LOAD DATA LOCAL INFILE '/tmp/events.csv'  
INTO TABLE events  
FIELDS TERMINATED BY ',' ENCLOSED BY '"'  
LINES TERMINATED BY '\n'  
(user_id, event_type, @created)  
SET created_at = STR_TO_DATE(@created, '%Y-%m-%d %H:%i:%s');

# mysql client ile  
mysql --local-infile=1 -e "LOAD DATA LOCAL INFILE ..."

-- Blocker → blocked ağacı  
SELECT  
  blocked.pid AS blocked_pid,  
  blocked.usename AS blocked_user,  
  left(blocked.query, 60) AS blocked_query,  
  blocking.pid AS blocking_pid,  
  blocking.usename AS blocking_user,  
  left(blocking.query, 60) AS blocking_query,  
  blocked.wait_event_type,  
  blocked.wait_event,  
  now() - blocked.query_start AS blocked_duration  
FROM pg_stat_activity blocked  
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted  
JOIN pg_locks bk ON bk.locktype = bl.locktype  
  AND bk.database IS NOT DISTINCT FROM bl.database  
  AND bk.relation IS NOT DISTINCT FROM bl.relation  
  AND bk.page IS NOT DISTINCT FROM bl.page  
  AND bk.tuple IS NOT DISTINCT FROM bl.tuple  
  AND bk.virtualxid IS NOT DISTINCT FROM bl.virtualxid  
  AND bk.transactionid IS NOT DISTINCT FROM bl.transactionid  
  AND bk.classid IS NOT DISTINCT FROM bl.classid  
  AND bk.objid IS NOT DISTINCT FROM bl.objid  
  AND bk.objsubid IS NOT DISTINCT FROM bl.objsubid  
  AND bk.granted  
JOIN pg_stat_activity blocking ON blocking.pid = bk.pid  
WHERE blocked.pid <> blocking.pid;  
  
-- Nazik iptal / sert sonlandırma  
-- SELECT pg_cancel_backend(12345);  
-- SELECT pg_terminate_backend(12345);

-- Mevcut isolation  
SELECT @@transaction_isolation;  
  
-- Session bazlı (önerilen ilk adım)  
SET SESSION transaction_isolation = 'READ COMMITTED';  
  
-- Global (my.cnf kalıcı)  
-- [mysqld]  
-- transaction_isolation = READ-COMMITTED  
  
-- Son deadlock'u incele  
SHOW ENGINE INNODB STATUS\G  
-- "LATEST DETECTED DEADLOCK" bölümüne bak

# my.cnf — ek gözlem  
[mysqld]  
innodb_print_all_deadlocks = 1  
innodb_deadlock_detect = ON

# postgresql.conf — NVMe production örneği  
random_page_cost = 1.1  
seq_page_cost = 1.0  
effective_io_concurrency = 400  
maintenance_io_concurrency = 400  
  
# Tablespace bazlı override da mümkün:  
# ALTER TABLESPACE fast_ssd SET (random_page_cost = 1.1);

-- ❌  
SELECT * FROM products WHERE category_id = 42;  
  
-- ✅  
SELECT id, name, price, slug  
FROM products  
WHERE category_id = 42  
ORDER BY name  
LIMIT 50 OFFSET 0;  
  
-- PostgreSQL: geniş TOAST kolonunu bilinçli hariç tut  
SELECT id, title, created_at  
FROM articles  
WHERE status = 'published';  
-- body / html_content çekilmedi → TOAST I/O yok

-- TempDB Dosyaları Eşit Boyutlandırma ve Otomatik Büyüme (MB bazlı)
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 8GB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\MSSQL\DATA\tempdev2.ndf', SIZE = 8GB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'D:\MSSQL\DATA\tempdev3.ndf', SIZE = 8GB, FILEGROWTH = 512MB);
ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = 'D:\MSSQL\DATA\tempdev4.ndf', SIZE = 8GB, FILEGROWTH = 512MB);