InnoDB Buffer Pool Boyutu Nasıl Ayarlanır

MySQL performansında tek bir ayar diğerlerinin hepsinden fazla fark yaratır: innodb_buffer_pool_size. InnoDB buffer pool, tablo verisinin ve index sayfalarının bellekte tutulduğu havuzdur. Sorgunun ihtiyaç duyduğu sayfa bu havuzdaysa cevap mikrosaniyeler içinde gelir; havuzda değilse disk okuması gerekir ve NVMe diskte bile aradaki fark yüz kat mertebesindedir. Yani veritabanı ayarı yapacaksan işe buradan başlarsın.

Buna rağmen sahada en sık gördüğüm hata, buffer pool'un varsayılan değerde bırakılmasıdır. MySQL 8'in varsayılanı 128 MB'tır ve 32 GB bellekli bir sunucuda bu değer, belleğin yüzde 99'unu kullanmadan bırakmak demektir. Bu yazıda buffer pool'un ne yaptığını, doğru boyutu nasıl ölçerek belirleyeceğini, hit oranını nasıl okuyacağını, instance sayısının ne zaman önemli olduğunu ve sunucuyu yeniden başlattıktan sonra havuzu nasıl "ısıtacağını" anlatacağım.

Buffer Pool Tam Olarak Ne Tutar#

Buffer pool yalnızca veri sayfalarını değil, aynı zamanda index sayfalarını, adaptive hash index'i, kilit bilgisini ve değişiklik tamponunu (change buffer) da barındırır. InnoDB bir satırı okumak istediğinde önce sayfanın havuzda olup olmadığına bakar; yoksa diskten okur ve havuza yerleştirir. Havuz doluysa en az kullanılan sayfalardan biri dışarı atılır. InnoDB bunun için klasik LRU'nun geliştirilmiş bir sürümünü kullanır: liste "young" ve "old" olarak ikiye ayrılmıştır ve yeni okunan sayfa doğrudan başa değil, old bölümünün başına konur.

Bu ayrım göründüğünden önemlidir. Gece çalışan bir yedekleme veya rapor sorgusu tabloyu baştan sona tarasa bile, o sayfalar old bölümünde kalır ve gündüz sıcak tuttuğun veriyi havuzdan dışarı atmaz. Aksi hâlde her tam tarama, uygulamanın performansını dakikalarca bozardı. Yazma tarafında ise değiştirilen sayfalar "kirli" (dirty) olarak işaretlenir ve arka planda diske yazılır; bu yüzden buffer pool sadece okumayı değil yazmayı da tamponlar.

-- Havuzun anlık durumu: toplam, boş ve kirli sayfa sayısı
SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Innodb_buffer_pool_pages_total',
  'Innodb_buffer_pool_pages_free',
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_buffer_pool_read_requests',
  'Innodb_buffer_pool_reads'
);

Buffer pool'un yalnızca InnoDB için çalıştığını unutma. MyISAM tabloların veri sayfaları bu havuza hiç girmez, onlar key_buffer_size ile ayrı bir alanda ve yalnızca index düzeyinde tamponlanır. İki motorun bu temel farkını InnoDB ve MyISAM karşılaştırması yazısında ayrıntılı ele aldım.

Doğru Boyutu Hesaplamak: Tahmin Değil Ölçüm#

Yaygın tavsiye "toplam belleğin yüzde 70-80'i" şeklindedir ve adanmış bir veritabanı sunucusunda makul bir başlangıçtır. Ama bu oran her yerde doğru değildir. Sunucuda aynı anda PHP-FPM, Nginx ve bir uygulama çalışıyorsa aynı oranı uygularsan sistem takas alanına düşer ve veritabanı hızlanmak yerine yavaşlar. Doğru yaklaşım, önce veri kümenin gerçek boyutunu ölçmektir:

-- Tüm InnoDB tablolarının veri + index toplamı (GB)
SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS innodb_gb
FROM information_schema.tables
WHERE engine = 'InnoDB';

Bu sayı buffer pool için hedefini belirler. Karar tablosu şöyle:

MySQL'in toplam bellek tüketiminin sadece buffer pool'dan ibaret olmadığını unutma. Bağlantı başına ayrılan sort_buffer_size, join_buffer_size, read_buffer_size gibi tamponlar, eşzamanlı bağlantı sayısıyla çarpılarak ciddi bir toplam üretebilir. Kaba bir güvenlik hesabı şöyle yapılır:

; Kabaca: buffer_pool + (max_connections x oturum tamponları) + OS payı <= toplam RAM
innodb_buffer_pool_size = 8G
max_connections         = 200
sort_buffer_size        = 2M
join_buffer_size        = 2M
read_buffer_size        = 256K

Bu örnekte oturum tamponları en kötü durumda 200 × ~4 MB = 800 MB civarı ek yük getirir. Bağlantı sayısı ve tamponların birlikte nasıl dengeleneceğini my.cnf optimizasyon rehberi yazısında ayrıntılandırdım.

Hit Oranını Ölçmek ve Yorumlamak#

Buffer pool'un yeterli olup olmadığını hissiyatla değil, hit oranıyla anlarsın. Formül basittir: diskten okunan sayfa sayısının, toplam okuma isteğine oranı.

SELECT
  ROUND(100 * (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')
  )), 4) AS hit_orani_yuzde;

Sağlıklı bir OLTP sisteminde bu değer yüzde 99'un üzerindedir. Yüzde 95'in altına düşüyorsa havuz veri kümesine göre küçüktür. Ancak tek başına bu sayıya bakma; sunucu yeni başlatıldıysa havuz henüz dolmamıştır ve oran yanıltıcı biçimde düşük çıkar. En az birkaç saatlik normal trafikten sonra ölçüm yapmalısın.

İkinci ve daha güvenilir gösterge boş sayfa sayısıdır. Innodb_buffer_pool_pages_free uzun süredir sıfırsa ve Innodb_buffer_pool_reads sürekli artıyorsa havuz doludur ve sürekli sayfa atıp yeniden okuyordur. Tersine, boş sayfa sayısı toplam sayfaların önemli bir kısmıysa havuzu gereğinden büyük ayarlamışsındır ve o belleği başka yere verebilirsin.

-- Ayrıntılı durum: LRU listesinin young/old dengesi, bekleyen okumalar
SHOW ENGINE INNODB STATUS\G
-- Çıktının BUFFER POOL AND MEMORY bölümüne bakılır:
-- Buffer pool hit rate 999 / 1000, young-making rate ... not 0

Diskten okumaların arttığı ama hit oranının hâlâ yüksek göründüğü durumlarda sorun genelde havuz boyutu değil, kötü sorgulardır; gereksiz tam taramalar havuzu hızla kirletir. O yüzden buffer pool'u büyütmeden önce mutlaka yavaş sorguları temizle; yöntemi MySQL yavaş sorgu bulma yazısında anlattım.

Instance Sayısı, Chunk Boyutu ve Çevrimiçi Değiştirme#

Buffer pool tek bir büyük yapı olarak tutulduğunda, yüksek eşzamanlılıkta iç kilit çekişmesi (mutex contention) oluşabilir. innodb_buffer_pool_instances bu havuzu birden çok bağımsız parçaya böler. Kural basittir: havuz 1 GB'ın altındaysa tek instance yeterlidir; üstündeyse her instance en az 1 GB olacak şekilde böl.

[mysqld]
innodb_buffer_pool_size      = 12G
innodb_buffer_pool_instances = 8      ; her instance 1.5G
innodb_buffer_pool_chunk_size = 128M  ; boyut bu değerin katı olmalı

Dikkat edilmesi gereken bir ayrıntı var: MySQL, innodb_buffer_pool_size değerini chunk_size × instances değerinin katına yuvarlar. Yani 5 GB yazıp 8 instance ve 128 MB chunk kullanırsan gerçek boyut 5 GB olmayabilir. Ayarı uyguladıktan sonra gerçek değeri mutlaka doğrula:

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS gercek_gb,
       @@innodb_buffer_pool_instances AS instance_sayisi,
       @@innodb_buffer_pool_chunk_size / 1024 / 1024 AS chunk_mb;

MySQL 5.7 ve sonrasında buffer pool'u yeniden başlatmadan büyütüp küçültebilirsin. İşlem arka planda parça parça yapılır ve bu sırada sunucu çalışmaya devam eder:

-- Çevrimiçi büyütme; ilerlemeyi hata günlüğünden izleyebilirsin
SET GLOBAL innodb_buffer_pool_size = 12884901888;  -- 12 GB
SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

Çevrimiçi değiştirme kalıcı değildir; sunucu yeniden başladığında my.cnf içindeki değere döner. Bu yüzden test ettiğin değeri yapılandırma dosyasına da yazmayı unutma. MySQL 8.0.23 ve sonrasında SET PERSIST ile ayarı doğrudan kalıcı hale getirebilirsin.

Yeniden Başlatma Sonrası Havuzu Isıtmak#

Sunucu yeniden başladığında buffer pool boştur ve tüm sorgular diskten okumak zorunda kalır. Büyük veri kümelerinde bu "soğuk başlangıç" dönemi saatlerce sürebilir ve tam da bakım sonrası trafiğin döndüğü anda performans dibe vurur. InnoDB bunun için havuzun içeriğini kapanışta diske yazıp açılışta geri yükleyebilir:

[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
innodb_buffer_pool_dump_pct         = 40   ; en sıcak yüzde 40 yeterlidir

Kaydedilen şey sayfaların kendisi değil, sayfa kimliklerinin listesidir; dosya ib_buffer_pool adıyla veri dizinine yazılır ve tipik olarak birkaç megabayttır. Açılışta InnoDB bu listeyi okuyup sayfaları arka planda yeniden yükler. İstersen dump işlemini elle de tetikleyebilirsin:

-- Şu anki sıcak sayfa listesini hemen diske yaz
SET GLOBAL innodb_buffer_pool_dump_now = ON;
-- Kaydedilmiş listeden hemen yükle
SET GLOBAL innodb_buffer_pool_load_now = ON;

innodb_buffer_pool_dump_pct değerini 100 yapmak cazip gelir ama gereksizdir; en sık kullanılan yüzde 25-40'lık dilim, ısınma süresinin neredeyse tamamını kurtarır ve kapanışı uzatmaz. Planlı bakımlarda bu ayar, geçiş süresini dakikalardan saniyelere indirir.

Sık Yapılan Hatalar ve Tuzaklar#

En pahalı hata, belleği aşırı tahsis edip sistemi takas alanına düşürmektir. Takasa düşen bir buffer pool, diskten okumaktan bile yavaştır çünkü aynı sayfa iki kez disk turu atar. Sunucuda vmstat 1 çıktısındaki si/so sütunları sıfırdan farklıysa buffer pool'u küçültmelisin. Linux tarafında vm.swappiness değerini düşük tutmak (örneğin 10) da yardımcı olur:

# Takas kullanımını anlık izle: si ve so sürekli sıfır olmalı
vmstat 1 5

# MySQL sürecinin gerçekte tuttuğu bellek
ps -o rss= -C mysqld | awk '{printf "%.1f GB\n", $1/1024/1024}'

İkinci yaygın hata, buffer pool'u büyütüp diğer ayarları hiç gözden geçirmemektir. Havuz büyüdükçe kirli sayfa miktarı da artar ve kapanışta ya da checkpoint sırasında yazma fırtınası oluşabilir. innodb_io_capacity ve innodb_io_capacity_max değerlerini diskinin gerçek yeteneğine göre ayarlamalısın; NVMe bir diskte varsayılan 200 değeri fazlasıyla düşüktür.

Üçüncü hata, sanallaştırılmış ortamda konak makinenin belleğini hesaba katmamaktır. Sunucuna 8 GB göründüğü hâlde balon sürücüsü nedeniyle gerçekte daha azına sahip olabilirsin; bu durumda agresif bir buffer pool doğrudan OOM killer'ı davet eder. Son olarak, tek bir dev tablo yüzünden havuzu büyütmeye çalışmak yerine o tabloyu bölümlendirmeyi değerlendir; ayrıntısını MySQL partitioning ile tablo bölümleme yazısında bulabilirsin.

Sıkça Sorulan Sorular#

innodb_buffer_pool_size değerini değiştirdikten sonra MySQL'i yeniden başlatmam gerekir mi#

MySQL 5.7 ve sonrasında gerekmez; SET GLOBAL innodb_buffer_pool_size = ... komutuyla çevrimiçi olarak büyütüp küçültebilirsin. İşlem arka planda parça parça yürür ve Innodb_buffer_pool_resize_status durum değişkeninden ilerlemesini izleyebilirsin. Ancak bu değişiklik kalıcı değildir; sunucu yeniden başladığında yapılandırma dosyasındaki değere döner, bu yüzden my.cnf dosyasını da güncellemelisin.

Buffer pool hit oranı yüzde kaç olmalı#

Sağlıklı bir üretim sisteminde yüzde 99 ve üzeri hedeflenir. Yüzde 95-99 arası kabul edilebilir ama iyileştirme alanı var demektir; yüzde 95'in altı, havuzun veri kümesine göre küçük olduğunu ya da çok fazla tam tablo taraması yapıldığını gösterir. Ölçümü sunucu yeni başlatıldıktan hemen sonra yapma; havuz dolmadan alınan oran gerçeği yansıtmaz.

Toplam belleğin yüzde 80'ini vermek her zaman doğru mu#

Hayır, bu oran yalnızca veritabanı çalıştıran adanmış sunucular için geçerlidir. Aynı makinede web sunucusu, PHP-FPM veya uygulama süreçleri varsa yüzde 25-40 bandı çok daha güvenlidir. Ayrıca veri kümen belleğin çok altındaysa fazla vermenin hiçbir faydası olmaz; 4 GB'lık bir veritabanı için 24 GB havuz ayırmak sadece kaynağı boşa harcar.

Buffer pool büyütmek yazma performansını da artırır mı#

Dolaylı olarak evet. Yazma işlemleri önce bellekteki sayfalar üzerinde yapılır ve diske arka planda toplu olarak aktarılır, bu yüzden büyük bir havuz yazmaları da tamponlar. Ancak yazma performansının asıl belirleyicileri innodb_log_file_size, innodb_flush_log_at_trx_commit ve diskin gerçek IOPS kapasitesidir. Havuzu büyütüp bu üçünü ihmal edersen kazanç sınırlı kalır.

ib_buffer_pool dosyasını silebilir miyim#

Evet, silinmesi veriye zarar vermez. Bu dosya yalnızca sıcak sayfa kimliklerinin listesini tutar; silersen sunucu bir sonraki açılışta havuzu ısıtamaz ve soğuk başlar, o kadar. Dosya bozulduğunda veya farklı bir sunucudan kopyalandığında yükleme başarısız olabilir; bu durumda silmek güvenli bir çözümdür ve bir sonraki düzgün kapanışta yeniden oluşturulur.

Buffer pool instance sayısını artırmak her zaman fayda sağlar mı#

Hayır. Instance bölmenin amacı yüksek eşzamanlılıkta iç kilit çekişmesini azaltmaktır; düşük trafikli bir sistemde ölçülebilir bir fark yaratmaz. Havuz 1 GB'ın altındaysa MySQL zaten tek instance kullanır. Genel kural, her instance'ın en az 1 GB olmasıdır; 4 GB'lık bir havuzu 16 parçaya bölmek fayda yerine parçalanma yaratır.

MyISAM tablolarım için de buffer pool ayarlamalı mıyım#

Hayır, buffer pool yalnızca InnoDB içindir. MyISAM tabloların index'leri key_buffer_size ile tamponlanır, veri sayfaları ise işletim sisteminin dosya önbelleğine bırakılır. Karışık bir sistemde her iki değeri de ayarlaman gerekir. Uzun vadede doğru hamle, MyISAM tabloları InnoDB'ye taşımak ve tüm belleği tek bir havuzda toplamaktır.

Kapanış#

Buffer pool ayarını doğru yapmak için aklında tutman gereken birkaç alışkanlık var: önce veri kümenin gerçek boyutunu ölç, oranla değil ölçümle karar ver; hit oranını ve boş sayfa sayısını birlikte izle; takas kullanımını vmstat ile sürekli sıfırda tut; ve dump/load ayarlarını açarak yeniden başlatma sonrası soğuk dönemi ortadan kaldır. Havuzu büyütmeden önce kötü sorguları temizlemek, çoğu zaman daha fazla bellekten daha çok kazandırır.

Bellek her zaman ayarla çözülmez; bir noktada gerçekten daha fazla RAM gerekir. Veritabanına ayrılmış, garanti edilen belleğe sahip bir makine istiyorsan VDS paketlerimiz iyi bir başlangıç; büyüme öngörülemiyorsa bulut sunucu ile kaynağı kesintisiz artırabilirsin. Yüksek eşzamanlılığa çıkan sistemlerde dedicated sunucu tercih edilebilir; ayarların ve izlemenin bizde olmasını isterseniz sunucu yönetimi hizmetimiz bu işi üstlenir.