InnoDB Buffer Pool Yetersizliği: MySQL Bellek Optimizasyonu

Gecen hafta bir müşterinin production sunucusunda klasik bir MySQL krizi yaşandı. Gece 2’de telefon, “site çok yavaş, neredeyse erişilemiyor” şikayeti. Bağlandım, birkaç sorgu çalıştırdım ve sorun hemen gözüme çarptı: InnoDB buffer pool boyutu 128MB olarak ayarlanmış, veritabanı ise 8GB veri barındırıyor. Sistem her sorgu için diske gidip geliyor, disk I/O tavana vurmuş, uygulama kilitlenmiş durumda. Bu yazıda bu senaryoyu ve benzerlerini adım adım ele alacağız.

InnoDB Buffer Pool Nedir ve Neden Bu Kadar Önemli?

InnoDB buffer pool, MySQL’in en kritik bellek bileşenidir. Tablolardan ve index’lerden okunan veri sayfalarını bellekte önbelleğe alır. Bir sorgu çalıştığında MySQL önce buffer pool’a bakar, veri oradaysa diskten okumaya gerek kalmaz. Buna cache hit denir. Eğer veri bellekte yoksa diske gidilir, bu da disk I/O anlamına gelir ve yavaşlığın temel kaynağıdır.

Düşük bir buffer pool boyutu şu anlama gelir: MySQL sürekli disk okuyup yazıyor, belleğe sığmayan veriler için tekrar tekrar I/O operasyonu yapıyor. Modern NVMe diskler bile bu durumda RAM’e kıyasla onlarca kat yavaş kalır. Dolayısıyla buffer pool optimizasyonu, MySQL performansının belki de en kritik tek değişkenidir.

Mevcut Durumu Teşhis Etmek

Önce ne durumda olduğumuzu anlamak lazım. Sistemin nabzını tutmadan ilaç yazmaya kalkmak doktor hatası olur.

Buffer Pool Durumunu Kontrol Etmek

MySQL’e bağlanıp şu sorguyu çalıştırın:

mysql -u root -p -e "SHOW ENGINE INNODB STATUSG" | grep -A 20 "BUFFER POOL AND MEMORY"

Daha okunabilir bir çıktı için:

mysql -u root -p << 'EOF'
SELECT
  FORMAT(pool_size * 16384 / 1024 / 1024, 2) AS 'Buffer Pool MB',
  FORMAT(free_buffers * 16384 / 1024 / 1024, 2) AS 'Free MB',
  FORMAT(database_pages * 16384 / 1024 / 1024, 2) AS 'Used MB',
  FORMAT(pages_made_dirty * 16384 / 1024 / 1024, 2) AS 'Dirty MB',
  hit_rate AS 'Hit Rate'
FROM information_schema.INNODB_BUFFER_POOL_STATS;
EOF

Hit Rate Hesaplamak

Buffer pool verimliliğinin en kritik göstergesi hit rate‘dir. Şu sorgu size bunu verir:

mysql -u root -p << 'EOF'
SELECT
  (1 - (
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') /
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_rate_percent;
EOF

Bu değer %99’un altındaysa ciddi bir sorun var demektir. %95’in altıysa acil müdahale gereklidir. Bahsettiğim müşteri senaryosunda bu değer %71 çıkmıştı, yani her 3 okumadan 1’i diske gidiyordu.

Global Status ile Genel Bakış

mysql -u root -p -e "
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Innodb_pages_read';
SHOW GLOBAL STATUS LIKE 'Innodb_pages_written';
"

Bu çıktıda dikkat edeceğiniz değerler:

• Innodb_buffer_pool_reads: Fiziksel diskten yapılan okuma sayısı, düşük olmalı
• Innodb_buffer_pool_read_requests: Toplam okuma isteği sayısı
• Innodb_buffer_pool_wait_free: Boş sayfa bekleyen işlem sayısı, sıfır olmalı
• Innodb_buffer_pool_pages_flushed: Diske yazılan sayfa sayısı

Sistem Belleğini ve MySQL Konfigürasyonunu Görmek

# Toplam sistem belleği
free -h

# Mevcut MySQL buffer pool ayarı
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool%';"

# MySQL'in ne kadar bellek kullandığını gör
ps aux | grep mysql | awk '{print $6/1024 " MB"}'

Ne Kadar Buffer Pool Yeterli?

Klasik kural şudur: Sunucunun toplam RAM’inin %70-80’ini buffer pool’a vermek. Ama bu her zaman doğru değil. Biraz daha düşünmek lazım.

Hangi faktörlere bakmalısınız:

• Veritabanı boyutu: Eğer toplam veri miktarı 10GB ve sunucunuzda 64GB RAM varsa, tüm veriyi belleğe alabilirsiniz. Bu ideal senaryodur.
• Çalışma seti: Tüm veriyi değil, sık erişilen veriyi bellekte tutmak yeterlidir. Genellikle aktif verinin %25-30’u working set’i oluşturur.
• Diğer uygulamalar: Aynı sunucuda uygulama sunucusu, Redis gibi bileşenler varsa bellek paylaşımını hesaba katın.
• İşletim sistemi: Kernel ve OS işlemleri için minimum 1-2GB bırakın.

Pratik bir hesaplama şöyle yapılabilir:

# Toplam veri boyutunu hesapla
mysql -u root -p << 'EOF'
SELECT
  table_schema AS 'Veritabani',
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Boyut (GB)'
FROM information_schema.TABLES
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;
EOF

# Sadece InnoDB tablolarının boyutunu gör
mysql -u root -p << 'EOF'
SELECT
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS 'Toplam InnoDB GB'
FROM information_schema.TABLES
WHERE engine = 'InnoDB';
EOF

Buffer Pool Boyutunu Artırmak

MySQL 5.7 ve sonrasında buffer pool boyutunu canlı olarak değiştirebilirsiniz, restart gerekmez. Bu büyük bir avantaj.

Dinamik Değişiklik (Restart Gerekmez)

# Örnek: 4GB'a çıkar
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 4294967296;"

# 8GB için
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 8589934592;"

# Değişikliği doğrula
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

Değişiklik hemen efektif olmaz. MySQL buffer pool’u yavaş yavaş büyütür. Bu işlemin tamamlandığını şöyle takip edebilirsiniz:

# Resize işleminin tamamlanıp tamamlanmadığını kontrol et
mysql -u root -p -e "
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';
"

my.cnf ile Kalıcı Konfigürasyon

Dinamik değişiklik restart’ta kaybolur. Kalıcı yapılandırma için /etc/mysql/my.cnf veya /etc/my.cnf dosyasını düzenleyin:

# Önce yedeğini al
cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup.$(date +%Y%m%d)

# Konfigürasyonu düzenle
cat >> /etc/mysql/my.cnf << 'EOF'

[mysqld]
# Buffer Pool Ayarlari
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M

# Log ve Flush Ayarlari
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# I/O Ayarlari
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
EOF

Parametrelerin anlamları:

• innodb_buffer_pool_size: Ana buffer pool boyutu, en kritik parametre
• innodb_buffer_pool_instances: Buffer pool’u paralel instance’lara böler, contention azalır. 8GB için 8 instance mantıklıdır
• innodb_buffer_pool_chunk_size: Her chunk boyutu. instances * chunk_size, buffer_pool_size’ı bölmeli
• innodb_log_file_size: Redo log boyutu. Büyük transaction workload’ları için artırın
• innodb_flush_method: O_DIRECT, OS önbelleğini bypass eder, double buffering önler
• innodb_io_capacity: InnoDB’nin arka plan işlemleri için kullanabileceği I/O kapasitesi

Buffer Pool Instance Hesabı

Buffer pool instances ile ilgili önemli bir kural var: innodb_buffer_pool_size, innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances değerinin tam katı olmalıdır. Aksi halde MySQL otomatik olarak buffer pool boyutunu yuvarlar ve sizi şaşırtır.

# Doğru hesaplama
# chunk_size varsayılan: 128MB
# Eğer 8GB buffer pool ve 8 instance istiyorsak:
# 8 * 128MB = 1024MB = 1GB, bu 8GB'ın tam katı? 8 / 1 = 8, evet tamam.

# Eğer 12GB buffer pool ve 8 instance istiyorsak:
# 8 * 128MB = 1024MB, 12288 / 1024 = 12, tamam.

# Kontrol sorgusu
mysql -u root -p -e "
SELECT
  @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS 'BP GB',
  @@innodb_buffer_pool_instances AS 'Instances',
  @@innodb_buffer_pool_chunk_size / 1024 / 1024 AS 'Chunk MB',
  (@@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances) / 1024 / 1024 AS 'Total Chunk MB';
"

Performans İzleme ve Alarmlar

Optimizasyon yaptıktan sonra sistemi izlemek şart. Reaktif değil, proaktif olmak lazım.

Basit İzleme Script’i

#!/bin/bash
# /usr/local/bin/mysql_buffer_check.sh

MYSQL_USER="monitor"
MYSQL_PASS="monitorpass"
ALERT_THRESHOLD=95  # Hit rate bu değerin altına düşerse alarm ver
LOG_FILE="/var/log/mysql_buffer_pool.log"

HIT_RATE=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -sN -e "
SELECT ROUND(
  (1 - (
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') /
    (SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests')
  )) * 100, 2
) AS hit_rate;
")

FREE_PAGES=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -sN -e "
SELECT free_buffers FROM information_schema.INNODB_BUFFER_POOL_STATS LIMIT 1;
")

TIMESTAMP=$(date '+%Y-%m-%d %H:%M:%S')
echo "$TIMESTAMP | Hit Rate: $HIT_RATE% | Free Pages: $FREE_PAGES" >> $LOG_FILE

if (( $(echo "$HIT_RATE < $ALERT_THRESHOLD" | bc -l) )); then
    echo "KRITIK: InnoDB Buffer Pool hit rate düştü! Mevcut: $HIT_RATE%" | 
    mail -s "MySQL Buffer Pool Alarmı - $(hostname)" [email protected]
fi

# Script'i çalıştırılabilir yap ve cron'a ekle
chmod +x /usr/local/bin/mysql_buffer_check.sh

# Her 5 dakikada bir çalıştır
echo "*/5 * * * * root /usr/local/bin/mysql_buffer_check.sh" >> /etc/crontab

Gerçek Dünya Senaryosu: E-ticaret Sitesi Krizi

Yukarıda bahsettiğim müşteri senaryosunu detaylandırayım. 16GB RAM’li bir sunucu, üzerinde MySQL ve PHP-FPM çalışıyor. MySQL konfigürasyonuna bakınca şunu gördüm:

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
# Sonuç: 134217728 (128MB!)

Bu bir MySQL kurulum default’uydu ve kimse değiştirmemişti. Veritabanı ise 12GB’tı. Yani buffer pool, veritabanının %1’ini bile tutamıyordu.

Müdahale adımları:

# 1. Önce disk I/O durumuna bak
iostat -x 1 5

# 2. MySQL process listesine bak, ne kadar sorgu bekliyor?
mysql -u root -p -e "SHOW PROCESSLIST;" | grep -v Sleep | wc -l

# 3. En ağır sorgulara bak
mysql -u root -p -e "
SELECT query, exec_count, avg_latency
FROM sys.statement_analysis
ORDER BY avg_latency DESC
LIMIT 10;
"

Disk I/O %95’in üzerindeydi, 47 sorgu bekliyordu. Hemen dinamik değişikliği uyguladım:

# 16GB RAM, MySQL + PHP-FPM var, 10GB buffer pool ayarladım
mysql -u root -p -e "
SET GLOBAL innodb_buffer_pool_size = 10737418240;
SET GLOBAL innodb_buffer_pool_instances = 8;
"

10 dakika içinde hit rate %71’den %97’ye çıktı. Disk I/O %20’nin altına düştü. Site normale döndü. Sabahleyin kalıcı konfigürasyonu my.cnf’e yazdım ve servisi planlı bakım penceresinde restart ettim.

Buffer Pool ile Birlikte Yapılması Gereken Diğer Ayarlar

Sadece buffer pool büyütmek her zaman yetmeyebilir. Birlikte yapılması gereken optimizasyonlar:

InnoDB Log Dosyası Boyutu

Buffer pool büyüdükçe daha fazla dirty page oluşur. Log dosyası da buna uygun olmalıdır:

# Mevcut log dosyası boyutunu gör
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_log_file_size';"

# Önerilen değer: Buffer pool'un %25'i, max 2GB
# 8GB buffer pool için 2GB log file
mysql -u root -p -e "SET GLOBAL innodb_log_file_size = 2147483648;"

Not: innodb_log_file_size değişikliği için MySQL’i durdurmanız ve log dosyalarını silmeniz gerekir. Bunu planlı bir bakım penceresinde yapın.

Read Ahead ve Prefetch Ayarları

mysql -u root -p -e "
SHOW VARIABLES LIKE 'innodb_read_ahead_threshold';
SHOW VARIABLES LIKE 'innodb_random_read_ahead';
"

# Sıralı okuma ağırlıklı workload için read ahead'i artır
mysql -u root -p -e "SET GLOBAL innodb_read_ahead_threshold = 32;"

Dirty Page Yüzdesini Kontrol Etmek

mysql -u root -p -e "
SELECT
  ROUND(dirty_pages * 100 / pool_size, 2) AS dirty_page_percent
FROM information_schema.INNODB_BUFFER_POOL_STATS;
"

Dirty page yüzdesi sürekli %75’in üzerindeyse innodb_max_dirty_pages_pct ve innodb_io_capacity değerlerini gözden geçirin.

Swap Kullanımı: Gizli Düşman

Buffer pool yetersizliğinin bir yan etkisi de Linux’un swap’a başvurmasıdır. MySQL ve swap birlikte kötü bir ikilidir.

# Swap kullanımını kontrol et
free -h
cat /proc/swaps
vmstat 1 5

# Hangi process ne kadar swap kullanıyor
for file in /proc/*/status; do
  awk '/VmSwap|Name/{printf $2 " " $3"n"}' $file
done | sort -k 2 -n -r | head -10

Eğer MySQL swap kullanıyorsa bu, performansın felç olacağı anlamına gelir. Çözüm ya buffer pool’u küçültmek (yani RAM’e sığacak kadar tutmak) ya da RAM eklemektir.

# MySQL process'inin swap kullanımını gör
PID=$(pidof mysqld)
grep VmSwap /proc/$PID/status

Swap kullanımını engellemek için /etc/mysql/my.cnf‘e şunu ekleyebilirsiniz:

[mysqld]
# MySQL'i RAM'de kilitle, swap'a gitme
# DİKKAT: Yeterli RAM olduğundan emin ol
innodb_buffer_pool_size = 8G  # RAM'in %70'inden fazla olmasın

Linux tarafında ise vm.swappiness değerini düşürebilirsiniz:

# Geçici
sysctl vm.swappiness=10

# Kalıcı
echo "vm.swappiness=10" >> /etc/sysctl.conf
sysctl -p

Performance Schema ile Derin Analiz

Performance Schema, buffer pool sorunlarını daha ayrıntılı incelemenizi sağlar:

mysql -u root -p << 'EOF'
-- En fazla bellek kullanan bileşenleri gör
SELECT
  event_name,
  ROUND(current_alloc / 1024 / 1024, 2) AS current_mb,
  ROUND(high_alloc / 1024 / 1024, 2) AS high_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE current_alloc > 1024 * 1024
ORDER BY current_alloc DESC
LIMIT 15;
EOF

mysql -u root -p << 'EOF'
-- Buffer pool'a en çok yüklenen tabloları bul
SELECT
  object_schema,
  object_name,
  count_read,
  count_write,
  ROUND(sum_timer_read / 1e12, 4) AS read_latency_sec,
  ROUND(sum_timer_write / 1e12, 4) AS write_latency_sec
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY sum_timer_read + sum_timer_write DESC
LIMIT 10;
EOF

Bu sorgular size hangi tabloların en fazla I/O oluşturduğunu gösterir. Eğer büyük ama nadiren erişilen bir tablo buffer pool’u işgal ediyorsa, o tablonun index yapısını ve erişim pattern’ını gözden geçirmeniz gerekebilir.

Buffer Pool Warmup: Soğuk Başlangıç Sorunu

MySQL restart ettikten sonra buffer pool boştur. Sistem normale dönene kadar performans düşük olacaktır. Bu “cold start” sorununu gidermek için InnoDB’nin buffer pool dump ve restore özelliğini kullanın:

# MySQL kapatılmadan önce buffer pool'u diske kaydet
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_dump_now = ON;"

# Kaydedilen dosyayı kontrol et
ls -lh /var/lib/mysql/ib_buffer_pool

# MySQL ayarlarına ekle, böylece her shutdown'da otomatik kayıt yapılır
# ve her startup'ta otomatik yüklenir

my.cnf‘e şu satırları ekleyin:

[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25  # En sık kullanılan %25'i kaydet

Bu sayede restart sonrasında MySQL eski “sıcak” haline çok daha hızlı kavuşur.

Sonuç

InnoDB buffer pool optimizasyonu, MySQL performans sorunlarının büyük çoğunluğunun temel çözümüdür. Yapılacaklar listesini özetleyelim:

• Hit rate’i düzenli kontrol edin, %99’un altı sorun sinyalidir
• Buffer pool boyutunu RAM’in %70-80’i olarak ayarlayın, veritabanı boyutunuzu da göz önünde bulundurun
• innodb_buffer_pool_instances değerini buffer pool GB sayısına eşitleyin
• innodb_flush_method = O_DIRECT ile double buffering’i önleyin
• Restart sonrası soğuk başlangıç sorununu dump/restore özelliğiyle giderin
• Swap kullanımını izleyin, MySQL’in swap’a gitmesine asla izin vermeyin
• Performance Schema ile hangi tabloların I/O yükü oluşturduğunu tespit edin

Çoğu MySQL performans sorunu, aslında tek bir konfigürasyon satırıyla çözülüyor: innodb_buffer_pool_size. Ama bunu körü körüne artırmak yerine sistemi anlayarak, ölçerek ve izleyerek yapmak önemli. Hem anlık krizleri çözer hem de uzun vadede sistemi sağlıklı tutarsınız.