MySQL Performans Tuning: Veritabanı Optimizasyonu

MySQL performansını optimize etmek, uygulama hızını doğrudan etkiler. Bu rehberde MySQL tuning tekniklerini öğreneceksiniz.

InnoDB Buffer Pool

En önemli MySQL ayarlarından biri buffer pool boyutudur:

# my.cnf veya my.ini
[mysqld]
# Dedicated DB server: RAM'in %70-80'i
innodb_buffer_pool_size = 12G

# Buffer pool instances (8GB+ için)
innodb_buffer_pool_instances = 8

# Log file size
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M

# Flush method
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1

Thread ve Connection Ayarları

[mysqld]
# Maximum connections
max_connections = 500

# Thread cache
thread_cache_size = 50

# Connection timeouts
wait_timeout = 600
interactive_timeout = 600

# Per-thread buffers
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

Slow Query Log

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# Analiz için
mysqldumpslow -s t /var/log/mysql/slow.log | head -20

# Veya pt-query-digest
pt-query-digest /var/log/mysql/slow.log

EXPLAIN Kullanımı

-- Sorgu planını göster
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- JSON formatında detaylı
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 1;

-- ANALYZE ile gerçek execution
EXPLAIN ANALYZE SELECT * FROM products WHERE category_id = 5;

# Dikkat edilecekler:
# - type: ALL (full table scan) kötü
# - type: ref, eq_ref, const iyi
# - rows: tahmini satır sayısı
# - Extra: Using filesort, Using temporary kötü

Index Stratejileri

-- Composite index (sıralama önemli)
CREATE INDEX idx_user_status_date ON orders (user_id, status, created_at);

-- Covering index
CREATE INDEX idx_covering ON users (email, name, status);

-- Partial index (MySQL 8.0+)
CREATE INDEX idx_active ON users (email) WHERE status = 'active';

-- Unused indexleri bul
SELECT
    table_name,
    index_name,
    stat_value
FROM mysql.innodb_index_stats
WHERE stat_name = 'n_diff_pfx01'
AND stat_value = 0;

Query Optimizasyonu

-- KÖTÜ: SELECT *
SELECT * FROM users WHERE id = 1;

-- İYİ: Sadece gerekli kolonlar
SELECT id, name, email FROM users WHERE id = 1;

-- KÖTÜ: OR kullanımı
SELECT * FROM users WHERE status = 'active' OR role = 'admin';

-- İYİ: UNION
SELECT * FROM users WHERE status = 'active'
UNION
SELECT * FROM users WHERE role = 'admin';

-- KÖTÜ: LIKE başında wildcard
SELECT * FROM users WHERE name LIKE '%john%';

-- İYİ: FULLTEXT index
ALTER TABLE users ADD FULLTEXT(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('john');

Table Maintenance

-- Tablo istatistiklerini güncelle
ANALYZE TABLE users;

-- Tabloyu optimize et
OPTIMIZE TABLE orders;

-- Fragmentation kontrolü
SELECT
    table_name,
    data_length,
    data_free,
    (data_free / data_length) * 100 AS fragmentation
FROM information_schema.tables
WHERE table_schema = 'mydb'
AND data_free > 0;

Monitoring

-- Global status
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

-- Buffer pool hit rate
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
    AS buffer_pool_hit_rate
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;

-- Hedef: %99+

Sonuç

MySQL performans optimizasyonu, sorgu analizi, index stratejisi ve sunucu yapılandırmasının kombinasyonudur. Düzenli monitoring ve slow query analizi ile performansı sürekli iyileştirin.