InnoDB Buffer Pool Boyutu Nasıl Hesaplanır

yazar

MySQL veya MariaDB kurulumunuzu yaptınız, tablolarınızı oluşturdunuz ve sisteminiz çalışıyor. Ancak sorgu performansı beklediğiniz gibi değil, disk I/O yüksek ve veritabanı yavaş yanıt veriyor. Bu durumun en yaygın nedenlerinden biri InnoDB Buffer Pool’un yanlış yapılandırılmış olmasıdır. Buffer Pool, MySQL’in en kritik bellek yapısıdır ve doğru boyutlandırıldığında performans farkı gece ile gündüz arasındaki kadar belirgin olabilir.

InnoDB Buffer Pool Nedir?

InnoDB Buffer Pool, MySQL’in veri ve index sayfalarını bellekte önbelleklediği ana bellek alanıdır. Veritabanı bir sorgu çalıştırdığında, önce bu alana bakar. Veri burada bulunuyorsa disk okuma işlemi gerçekleşmez, doğrudan bellekten okunur. Bu olaya buffer pool hit denir ve disk I/O’yu dramatik biçimde azaltır.

Basit bir analoji kuralım: Buffer Pool, sık kullandığınız kitapları masanızın üzerinde tutmanız gibidir. Her seferinde kütüphaneye (diske) gidip kitabı aramak yerine, masanızda hazır bulunuyor. Masa ne kadar büyükse, o kadar çok kitap tutabilirsiniz ve kütüphaneye gitme sıklığınız o kadar azalır.

Buffer Pool şu yapıları önbellekler:

  • Tablo veri sayfaları (16KB boyutunda data pages)
  • Index sayfaları
  • Adaptive hash index verileri
  • Insert buffer verileri
  • Kilit (lock) bilgileri

Mevcut Durumu Anlama

Yapılandırmaya geçmeden önce mevcut durumunuzu anlamanız gerekiyor. MySQL’e bağlanarak şu sorguyu çalıştırın:

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

Bu komut size şöyle bir çıktı verecektir:

+---------------------------------------+------------------+
| Variable_name                         | Value            |
+---------------------------------------+------------------+
| Innodb_buffer_pool_pages_total        | 8192             |
| Innodb_buffer_pool_pages_free         | 1024             |
| Innodb_buffer_pool_pages_data         | 6890             |
| Innodb_buffer_pool_read_requests      | 98432156         |
| Innodb_buffer_pool_reads              | 1245678          |
+---------------------------------------+------------------+

Bu değerlerden hit ratio hesaplayabilirsiniz. Hit ratio, veritabanının kaç sorguda diske gitmek zorunda kalmadığını gösterir:

mysql -u root -p -e "
SELECT 
  (1 - (
    SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
  ) / (
    SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
  )) * 100 AS hit_ratio_percent;
"

Hit ratio %99 ve üzerinde olmalıdır. Eğer bu değer düşükse, Buffer Pool yetersiz demektir ve sistem sürekli diskten veri okumak zorunda kalıyordur.

Sunucu Belleğini Anlama

İlk adım sunucunuzdaki toplam RAM miktarını ve ne kadarının kullanıldığını anlamaktır:

# Toplam bellek durumunu görüntüle
free -h

# Daha detaylı bilgi için
cat /proc/meminfo | grep -E "MemTotal|MemFree|MemAvailable|Cached|Buffers"

# NUMA mimarisi varsa
numactl --hardware 2>/dev/null || echo "NUMA mevcut degil"

Tipik bir çıktı şöyle görünür:

MemTotal:       32768 MB
MemFree:         1024 MB
MemAvailable:   18432 MB
Cached:         12288 MB
Buffers:         2048 MB

Bu değerleri not alın, hesaplama sürecinde kullanacaksınız.

Buffer Pool Boyutu Hesaplama Formülü

Genel kural şudur: Toplam RAM’in %50 ile %75’i Buffer Pool için ayrılabilir. Ancak bu kural çok kaba bir başlangıç noktasıdır. Gerçek dünyada daha dikkatli bir hesaplama yapmanız gerekir.

Bellek Harcayan Diğer Bileşenler

MySQL’in Buffer Pool dışında da bellek tükettiğini unutmayın:

  • innodb_log_buffer_size: Varsayılan 16MB, genellikle 64-256MB arası yeterlidir
  • key_buffer_size: MyISAM tabloları için, saf InnoDB ortamlarda 32MB yeterli
  • query_cache_size: MySQL 8.0’da kaldırıldı, eski sürümlerde 0 veya küçük değer
  • tmp_table_size ve max_heap_table_size: Her bağlantı için geçici tablolar
  • sort_buffer_size: Her bağlantı başına ayrılır
  • join_buffer_size: Her bağlantı başına ayrılır
  • read_buffer_size: Her bağlantı başına ayrılır
  • thread_stack: Her thread için yaklaşık 256KB

Bağlantı başına düşen belleği hesaplamak için şunu çalıştırın:

mysql -u root -p -e "
SELECT 
  @@sort_buffer_size / 1024 / 1024 AS sort_buffer_MB,
  @@join_buffer_size / 1024 / 1024 AS join_buffer_MB,
  @@read_buffer_size / 1024 / 1024 AS read_buffer_MB,
  @@read_rnd_buffer_size / 1024 / 1024 AS read_rnd_buffer_MB,
  @@tmp_table_size / 1024 / 1024 AS tmp_table_MB,
  @@max_connections AS max_connections;
"

Pratik Hesaplama Adımları

Diyelim ki 32GB RAM’e sahip, sadece MySQL çalıştıran bir sunucunuz var:

# Toplam RAM: 32 GB
# İşletim sistemi için ayır: 2 GB (minimum)
# MySQL per-connection overhead (max_connections=200, her biri ~4MB): 800 MB
# InnoDB log buffer: 128 MB
# Diger MySQL bellek yapilari: 512 MB
# Guvenlik tamponu: 512 MB

# Hesaplama:
# Kullanilabilir = 32 - 2 - 0.8 - 0.128 - 0.512 - 0.512 = 28 GB
# Buffer Pool = yaklasik 28 GB, ama %75 kuralini asmayin
# 32 * 0.75 = 24 GB

# Sonuc: innodb_buffer_pool_size = 24G

Daha otomatik bir yaklaşım için şu script’i kullanabilirsiniz:

#!/bin/bash
# buffer_pool_hesapla.sh

TOTAL_RAM_MB=$(grep MemTotal /proc/meminfo | awk '{print $2/1024}' | cut -d. -f1)
MAX_CONN=$(mysql -u root -p"$MYSQL_PASS" -se "SELECT @@max_connections;" 2>/dev/null || echo 150)
PER_CONN_MB=4

OS_RESERVE=2048
CONN_OVERHEAD=$((MAX_CONN * PER_CONN_MB))
MYSQL_OTHER=640
SAFETY_BUFFER=512

AVAILABLE=$((TOTAL_RAM_MB - OS_RESERVE - CONN_OVERHEAD - MYSQL_OTHER - SAFETY_BUFFER))
BUFFER_75=$((TOTAL_RAM_MB * 75 / 100))

if [ $AVAILABLE -lt $BUFFER_75 ]; then
    RECOMMENDED=$AVAILABLE
else
    RECOMMENDED=$BUFFER_75
fi

echo "Toplam RAM: ${TOTAL_RAM_MB}MB"
echo "Maks. baglanti overhead: ${CONN_OVERHEAD}MB"
echo "Kullanilabilir bellek: ${AVAILABLE}MB"
echo "Onerilen Buffer Pool: ${RECOMMENDED}MB ($(echo "scale=1; $RECOMMENDED/1024" | bc)GB)"

Veritabanı Boyutunu Hesaba Katmak

Buffer Pool boyutunu belirlerken veritabanınızın toplam boyutu da kritik bir faktördür. Eğer veritabanınız 5GB ise ve sunucunuzda 32GB RAM varsa, 24GB’lık bir Buffer Pool gereksizdir. Tüm veritabanı zaten belleğe sığar.

# Veritabani boyutlarini goruntule
mysql -u root -p -e "
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;
"
# Toplam InnoDB veri boyutunu hesapla
mysql -u root -p -e "
SELECT 
  ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS toplam_GB
FROM information_schema.tables
WHERE engine = 'InnoDB';
"

Gerçek dünya senaryosu: 32GB RAM’li sunucunuzda 8GB’lık bir InnoDB veritabanı var. Bu durumda 16GB’lık bir Buffer Pool, tüm veriyi rahatça barındırır ve ekstra marge sağlar. 24GB’a çıkmanın anlamlı bir faydası olmaz.

Genel rehber:

  • Veritabanı boyutu < Kullanılabilir RAM'in %50'si ise, veritabanı boyutunun 1.25 katı Buffer Pool yeterlidir
  • Veritabanı boyutu > Kullanılabilir RAM ise, mümkün olan maksimum değeri kullanın
  • Hot data (sık erişilen veri) tüm verinin genellikle %20-30’udur, bunu da göz önünde bulundurun

Buffer Pool’u Yapılandırma

Değeri belirledikten sonra MySQL yapılandırma dosyasını düzenlemeniz gerekiyor:

# MySQL yapilandirma dosyasini ac
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# veya
sudo nano /etc/my.cnf
# veya MariaDB icin
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Dosyaya şu satırları ekleyin veya güncelleyin:

[mysqld]
# Ana Buffer Pool boyutu
innodb_buffer_pool_size = 24G

# Buffer Pool instance sayisi (her instance icin min 1GB olmali)
# 24GB icin 8 instance idealdir
innodb_buffer_pool_instances = 8

# Buffer Pool dump/load (yeniden baslatmada isınma suresi icin)
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25

innodb_buffer_pool_instances Neden Önemli?

Buffer Pool büyüdükçe, birden fazla thread aynı anda erişmeye çalıştığında mutex contention (kilitleme çakışması) sorunu ortaya çıkar. innodb_buffer_pool_instances parametresi, Buffer Pool’u birden fazla bağımsız bölgeye böler ve bu çakışmayı azaltır.

Kural olarak:

  • Buffer Pool 1GB’dan küçükse: 1 instance yeterli
  • 1GB ile 8GB arası: 2-4 instance
  • 8GB ile 32GB arası: 8 instance
  • 32GB üzeri: 16 instance (maksimum 64)

Her instance’ın boyutu en az 1GB olmalıdır. Yani 8GB Buffer Pool için 16 instance açmak hatalıdır.

Canlı Sistemde Buffer Pool Boyutunu Değiştirme

MySQL 5.7.5 ve MariaDB 10.2.2 sürümünden itibaren Buffer Pool boyutunu yeniden başlatmadan değiştirebilirsiniz. Ancak bu işlem dikkatli yapılmalıdır:

# Mevcut boyutu kontrol et
mysql -u root -p -e "SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS current_size_GB;"

# Canli olarak boyutu degistir (arka planda gerceklesiyor, birkaç dakika surebilir)
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 25769803776;"
# 24GB = 24 * 1024 * 1024 * 1024 = 25769803776 bytes

# Degisikligi izle
mysql -u root -p -e "
SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES 
FROM information_schema.INNODB_BUFFER_POOL_STATS;
"

Dikkat: Bu değişiklik my.cnf‘ye yazılmadığı sürece sunucu yeniden başladığında eski değere döner. Her zaman kalıcı yapılandırma dosyasını güncellemeyi unutmayın.

Monitoring ve İzleme

Buffer Pool’un ne kadar verimli çalıştığını düzenli olarak izlemeniz gerekiyor. Şu sorguları periyodik olarak çalıştırın:

# Kapsamli Buffer Pool durumu
mysql -u root -p -e "
SELECT 
  POOL_ID,
  ROUND(POOL_SIZE * 16 / 1024, 0) AS pool_size_MB,
  ROUND(FREE_BUFFERS * 16 / 1024, 0) AS free_MB,
  ROUND(DATABASE_PAGES * 16 / 1024, 0) AS used_MB,
  ROUND(HIT_RATE / 1000 * 100, 2) AS hit_rate_pct,
  NUMBER_PAGES_READ AS disk_reads,
  NUMBER_PAGES_WRITTEN AS disk_writes
FROM information_schema.INNODB_BUFFER_POOL_STATSG
"

Cron ile düzenli izleme için basit bir script:

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

LOGFILE="/var/log/mysql/buffer_pool_stats.log"
MYSQL_CMD="mysql -u monitor -pMONITOR_PASS -e"
DATE=$(date '+%Y-%m-%d %H:%M:%S')

HIT_RATIO=$($MYSQL_CMD "
SELECT ROUND((1 - (
  SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) / NULLIF((
  SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
  WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
), 0)) * 100, 4);" 2>/dev/null | tail -1)

FREE_PCT=$($MYSQL_CMD "
SELECT ROUND(
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_free') /
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_total') * 100
, 2);" 2>/dev/null | tail -1)

echo "$DATE | Hit Ratio: ${HIT_RATIO}% | Free: ${FREE_PCT}%" >> $LOGFILE

# Uyari esigi: hit ratio %95 altina dustuysa
if (( $(echo "$HIT_RATIO < 95" | bc -l) )); then
    echo "$DATE UYARI: Buffer Pool hit ratio dusuk: ${HIT_RATIO}%" | 
    mail -s "MySQL Buffer Pool Uyarisi" [email protected]
fi

Gerçek Dünya Senaryo: E-Ticaret Sitesi

Müşteri: Orta ölçekli bir e-ticaret şirketi. Sipariş, ürün ve kullanıcı tabloları dahil toplam 45GB InnoDB verisi. Sunucu: 64GB RAM, 16 core CPU. Sorun: Yoğun saatlerde sayfa yüklenme süresi 8-10 saniyeye çıkıyor.

Tanı aşaması:

# Mevcut hit ratio kontrolu
mysql -u root -p -e "
SELECT 
  Innodb_buffer_pool_reads.VARIABLE_VALUE AS disk_reads,
  Innodb_buffer_pool_read_requests.VARIABLE_VALUE AS total_reads
FROM 
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
  (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests;
"
# Sonuc: disk_reads 2.3 milyon, total_reads 18 milyon
# Hit ratio: (1 - 2300000/18000000) * 100 = %87.2 -- Cok dusuk!

Mevcut yapılandırma incelendiğinde innodb_buffer_pool_size = 4G görüldü. 45GB veri için 4GB Buffer Pool felaketten farklı değil.

Yeni yapılandırma:

# 64GB RAM icin hesaplama:
# OS rezervi: 4GB
# Max 300 baglanti x 4MB = 1.2GB
# Diger MySQL: 1GB
# Guvenlik: 1GB
# Kullanilabilir: 64 - 4 - 1.2 - 1 - 1 = 56.8GB
# %75 kurali: 64 * 0.75 = 48GB
# Veritabani 45GB, 48GB idealdir (8GB marge var)

[mysqld]
innodb_buffer_pool_size = 48G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 40

Değişiklik sonrası 2 gün beklendikten sonra (Buffer Pool’un ısınması için) hit ratio %99.3’e çıktı, yoğun saatlerdeki sayfa yüklenme süresi 8-10 saniyeden 1.2 saniyeye düştü.

Sık Yapılan Hatalar

RAM’in tamamını Buffer Pool’a vermek: Sistem OOM Killer devreye girer ve MySQL süreci öldürülebilir. Her zaman OS ve diğer süreçler için pay bırakın.

Çok fazla instance açmak: 8GB Buffer Pool için 64 instance açmak her instance’ı 128MB yapar ve bu verimsizdir. Her instance minimum 1GB olmalı.

Hit ratio’yu düşük sayıda sorgu üzerinden hesaplamak: Sunucu yeni başlatıldıktan hemen sonra hit ratio düşük görünür. En az birkaç saatlik veri üzerinden değerlendirme yapın.

Buffer Pool’u tek başına düşünmek: Buffer Pool yüksek olsa bile yavaş sorgular, eksik index’ler veya tablolar arası karmaşık join’ler performansı mahvedebilir. Bütüncül bir optimizasyon yapın.

Sonuç

InnoDB Buffer Pool, MySQL performansının merkezinde yer alan en kritik yapılandırma parametresidir. Doğru boyutlandırma için şu adımları izleyin: Önce mevcut hit ratio’yu ölçün, sunucu belleğini ve diğer bileşenlerin ihtiyaçlarını hesaba katın, veritabanı boyutunu göz önünde bulundurun ve %75 kuralını aşmadan optimum değeri belirleyin.

Yapılandırma bir kez yapılıp unutulmamalıdır. Veritabanınız büyüdükçe, sunucunuza RAM ekledikçe veya kullanım örüntüleri değiştikçe bu değeri yeniden gözden geçirin. Düzenli monitoring ile Buffer Pool durumunu takip edin ve hit ratio %99’un altına düştüğünde müdahale edin.

Son olarak şunu belirteyim: Buffer Pool doğru boyutlandırılmış bir sistem, disk I/O baskısını önemli ölçüde azaltır, sorgu yanıt sürelerini kısaltır ve genel sistem kararlılığını artırır. Birkaç satır yapılandırma değişikliği ile elde edeceğiniz bu performans kazanımı, pahalı donanım yatırımlarından çok daha hızlı ve maliyet etkin bir çözümdür.

Yorum yapın