MariaDB InnoDB Buffer Pool, Redo Log ve Bellek Ayarları

MariaDB belleği nasıl kullanır?

Bellek rehberi MariaDB'nin belleği üç geniş grupta kullandığını anlatır. Hangi ayarın toplam tüketimi en çok değiştirdiğini bilmek, sorun çıktığında nereden başlayacağınızı belirler.

Belgelere göre bellek sorunları genellikle iki nedenden birinden çıkar: genel önbellekler fazla büyüktür ya da bağlantı başına arabellekler mevcut eşzamanlılık için fazla büyüktür. Kapsayıcı (container), cgroup veya sanal makine ortamında boyutu fiziksel RAM'e göre değil, MariaDB'nin gördüğü bellek sınırına göre hesaplamanız gerekir. Sunucunun toplam bellek kullanımını görmek için şu durum değişkenine bakabilirsiniz:

SHOW GLOBAL STATUS LIKE 'memory_used';

Buffer pool nasıl çalışır?

Buffer pool son erişilen veri sayfalarını tutar. InnoDB sıralı erişim fark ettiğinde yakın sayfaları önceden okur ve yer açmak için en uzun süredir kullanılmayan sayfaları çıkaran bir LRU algoritması uygular. Liste pratikte iki alt listeye ayrılır: yakın zamanda kullanılan bilgilerin bulunduğu new ve daha eski bilgilerin bulunduğu old listesi. Varsayılan olarak listenin yüzde 37'si old listesine ayrılır.

Listede olmayan yeni bir sayfa okunduğunda old listesinin başına yerleşir ve old listesinin en eski öğesi atılır. Old listesindeki bir sayfaya tekrar erişilirse sayfa new listesinin başına taşınır. Bu yapının amacı, seyrek ama büyük tam tablo taramalarının bütün önbelleği silip süpürmesini engellemektir. Oranı innodb_old_blocks_pct (yüzde 5 ile 95 arası) ve bir bloğun old listesinden new listesine geçmeden önce beklemesi gereken süreyi innodb_old_blocks_time (varsayılan 1000) değiştirir. Belgeler bu iki değeri, sisteminizin önbelleği nasıl kullandığını anlamadan değiştirmemenizi önerir.

innodb_buffer_pool_size için doğru boyut

Belgelerin başlangıç önerileri motor kullanımınıza göre değişir:

• Ağırlıklı InnoDB: innodb_buffer_pool_size kullanılabilir belleğin yaklaşık yüzde 60 ile 70'i; MyISAM index'i kullanmıyorsanız key_buffer_size küçük, örneğin 16M ile 64M arası.
• Ağırlıklı MyISAM: key_buffer_size kullanılabilir belleğin yaklaşık yüzde 20'si. innodb_buffer_pool_size değerini 0 yapmak yalnızca InnoDB'yi hiç kullanmıyorsanız doğrudur.
• Karışık: önce InnoDB'ye öncelik verin, sonra key_buffer_size değerini gerçek MyISAM index kullanımına göre belirleyin.
• 4 GB'tan az RAM: daha düşük yüzdeler kullanın ve fazladan pay bırakın.

Farklı belge sayfaları farklı oranlar verir. Daha eski "Configuring MariaDB for Optimal Performance" sayfası ve değişken açıklaması, adanmış bir sunucuda bellek yüzde 80'e kadar verilebileceğini söyler; güncel bellek rehberi ise toplam yerine kullanılabilir RAM üzerinden yüzde 60 ile 70'i başlangıç noktası olarak gösterir. Tek bir sihirli oran yoktur: asıl hedef, aktif veri kümesinin büyük kısmının bellekte durması ve sistemin swap kullanmamasıdır. Buffer pool sayfası, değerin fazla büyük olmasının swap'a yol açacağını ve bunun büyük buffer pool'un faydasını fazlasıyla sileceğini açıkça belirtir.

Küçük veya paylaşımlı sunucularda belgelerin önerdiği başka bir yöntem de verinin gerçek boyutuna bakmaktır: tüm InnoDB tablolarının Data_length ve Index_length toplamını hesaplayın ve buffer pool'u bu toplamın yüzde 110'undan büyük yapmayın. Şu sorgu motor başına veri ve index boyutunu megabayt olarak verir; çok sayıda tablonuz varsa çalışması zaman alabilir:

SELECT  ENGINE,
        ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
        ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
        ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
        COUNT(*) "Num Tables"
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE  table_schema NOT IN (
             "information_schema",
             "PERFORMANCE_SCHEMA",
             "SYS_SCHEMA",
             "ndbinfo")
    GROUP BY  ENGINE;

Boyut yeterli mi? İsabet oranını ölçmek

Sunucu normal yükünde ısındıktan sonra Innodb_buffer_pool_reads (diskten okunan sayfalar) durum değişkeni, Innodb_buffer_pool_read_requests (mantıksal okuma istekleri) değişkenine göre çok küçük kalmalıdır. Belgelerin önerdiği yöntem, iki değerin bir dakika içindeki değişimine bakmaktır: Innodb_buffer_pool_reads değişimi, Innodb_buffer_pool_read_requests değişiminin yüzde 1'inden azsa buffer pool iyi kullanılıyordur. Innodb_buffer_pool_wait_free sürekli artıyorsa buffer pool yetersizdir ya da sayfa boşaltma yeterince sık yapılmıyordur.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';

Değeri ayarlamak: dosyada ve çalışırken

Kalıcı ayar için değeri yapılandırma dosyasının [mariadb] bölümüne yazar ve sunucuyu yeniden başlatırsınız. Dosyada okunabilirlik için M ve G son eklerini kullanabilirsiniz; çarpan 1024'tür.

[mariadb]
innodb_buffer_pool_size = 5G

Çoğu değişken çalışan sunucuda SET GLOBAL ile de değiştirilebilir, ancak bu değişiklik yeniden başlatmada kaybolur. Belgeler iki yolu birlikte kullanmayı önerir: SET ile hemen uygulayın, dosyaya da yazın ki sonraki açılışta aynı değer gelsin. SET deyiminde M veya G kullanılamaz, değeri bayt olarak ya da bir çarpım ifadesi olarak yazmanız gerekir.

SET GLOBAL innodb_buffer_pool_size = (8 * 1024 * 1024 * 1024);
SELECT @@GLOBAL.innodb_buffer_pool_size;

Çalışırken boyutlandırma son sürümlerde önemli ölçüde değişti. Belgelere göre MariaDB 10.11.12, 11.4.6 ve 11.8.2'den itibaren innodb_buffer_pool_chunk_size kullanımdan kaldırılmış ve yok sayılıyor; boyut artık 1 megabaytlık adımlarla, yalnızca başlangıçta ayarlanabilen salt okunur innodb_buffer_pool_size_max değerine kadar değiştirilebiliyor. MariaDB 10.11.17, 11.4.11, 11.8.7 ve 12.3.2'den itibaren innodb_buffer_pool_size_max, IBM AIX dışındaki 64 bit sistemlerde varsayılan olarak 8 TiB'tır; bu bir bellek ayırması değil, sanal adres alanı rezervasyonudur. Böylece başlangıçta hiçbir şey ayarlamadan buffer pool'u çalışırken büyütebilirsiniz. Bu sürümlerden önce varsayılan değer başlangıçtaki innodb_buffer_pool_size olduğu için büyütmek isteyen kişinin innodb_buffer_pool_size_max değerini önceden ayarlaması gerekiyordu. Üst sınırın üzerine çıkma denemesi Warning 1292 ile sonuçlanır.

SET GLOBAL innodb_buffer_pool_size artık boyutlandırma bitene ya da KILL, SHUTDOWN, istemci bağlantısının kopması veya bir kesme ile iptal edilene kadar bekler; ilerlemeyi gösteren Innodb_buffer_pool_resize_status durum değişkeni kaldırılmıştır. Parça boyutundan ve bu durum değişkeninden bahseden "Setting Innodb Buffer Pool Size Dynamically" sayfası eski davranışı anlatır. Linux'ta innodb_buffer_pool_size_auto_min değişkeni, bir bellek baskısı olayında buffer pool'un ne kadar küçültülebileceğini belirler; varsayılan değer innodb_buffer_pool_size_max ile değiştirildiği için bellek baskısı olayları varsayılan olarak yok sayılır. 16k sayfa boyutunda pratik alt sınır 6m civarındadır.

Yeniden başlatmadan sonra ısınma

Sunucu açıldığında buffer pool boştur ve sık kullanılan veriler zamanla belleğe dolar; bu süreye ısınma denir. InnoDB kapanışta buffer pool'daki sayfaların numaralarını bir dosyaya yazıp açılışta aynı sayfaları yeniden yükleyebilir. innodb_buffer_pool_dump_at_shutdown ve innodb_buffer_pool_load_at_startup değişkenlerinin ikisi de varsayılan olarak ON durumundadır. Varsayılan olarak her buffer pool'un en sıcak yüzde 25'i kaydedilir (innodb_buffer_pool_dump_pct) ve dosyanın adı ib_buffer_pool olur. Çalışırken innodb_buffer_pool_dump_now ve innodb_buffer_pool_load_now ile elle döküm ve yükleme yapabilir, uzun süren bir yüklemeyi innodb_buffer_pool_load_abort ile durdurabilirsiniz. Buffer pool büyükse yüklemenin daha uzun sürdüğünü unutmayın; belgeler başlangıçta innodb_io_capacity değerini artırmanın yüklemeyi hızlandırabileceğini söyler.

Redo log: ib_logfile0 ve kapasite

InnoDB redo log, verinin çökme durumunda bile güvenle diske yazılmasını sağlayan işlem günlüğüdür. Günlük tek bir dosyadır: ib_logfile0. Konumu innodb_log_group_home_dir ayarlanmışsa oradadır, yoksa datadir içindedir. ib_logfile101 adlı ikinci dosya yalnızca günlük yeniden boyutlandırılırken veya yeniden oluşturulurken görünür. Boyutu innodb_log_file_size belirler; değişken açıklamasındaki varsayılan değer 96 MB'tır ve MariaDB 10.9'dan itibaren değişken dinamiktir.

SET GLOBAL innodb_log_file_size=(2 * 1024 * 1024 * 1024);

SHOW GLOBAL VARIABLES
   LIKE 'innodb_log_file_size';

+----------------------+------------+
| Variable_name        | Value      |
+----------------------+------------+
| innodb_log_file_size | 2147483648 |
+----------------------+------------+

Yeniden boyutlandırma arka planda yeni bir ib_logfile101 yazılıp ib_logfile0 üzerine yeniden adlandırılarak yapılır. Yeni boyutun yeniden başlatmadan sonra da geçerli olması için aynı değeri yapılandırma dosyasına da yazmalısınız. Günlük kayıtları için kullanılabilen kapasite, dosyanın ilk 12288 baytı başlık ve kontrol noktası blokları için ayrıldığından ve kalanın yüzde 10'u güvenlik payı olarak tutulduğundan şu formülle bulunur: (innodb_log_file_size - 12288) x 0,9. Belgelerdeki örnekte 2G için bu yaklaşık 1,8G eder.

Büyük bir redo log, sayfa yazmalarını erteler: InnoDB aynı sayfayı birçok değişiklik boyunca bellekte kirli tutup tek seferde yazabilir. Bu, aynı sayfaları sürekli değiştiren yazma ağırlıklı iş yüklerinde yazma çoğalmasını ve depolama aşınmasını azaltır. Bedeli çökme kurtarmasında ödenir: kurtarma, redo kayıtlarını buffer pool'dan alınan bloklarda tuttuğu için uygulanacak günlük buffer pool'un kabaca üçte birini aşarsa InnoDB çok geçişli kurtarmaya geçer ve hata günlüğüne InnoDB: Multi-batch recovery needed at LSN ile başlayan bir mesaj yazar. Kurtarma yine tamamlanır, yalnızca yavaşlar.

Redo log'un yetip yetmediğini anlamak için kontrol noktası yaşına bakarsınız: Innodb_checkpoint_age durum değişkeni son kontrol noktasından bu yana redo log'a yazılan veri miktarını verir. Bu değeri günlük kapasitesine böldüğünüzde doluluk oranını bulursunuz; oran 1,0'a çok yakınsa redo log iş yükü için küçük olabilir. Aynı bilgiyi SHOW ENGINE INNODB STATUS çıktısının LOG bölümündeki Log sequence number ile Last checkpoint at farkından da hesaplayabilirsiniz.

innodb_flush_log_at_trx_commit: hız ile dayanıklılık arasında seçim

Bu değişken her commit'te redo log'un ne zaman yazılıp diske zorlanacağını belirler. Varsayılan değer 1'dir ve tam ACID uyumu için gereklidir. Belgeler en yüksek hata toleransı için bunun sync_binlog=1 ile birlikte kullanılmasını önerir.

Binary log açıkken ve değer 1 iken, grup commit sayesinde commit başına bir senkronizasyon daha az yapılır. MariaDB 12.3 ile gelen InnoDB tabanlı binary log (binlog_storage_engine=innodb) kullanılıyorsa bu seçenek hem binlog dosyalarının hem tablo verisinin dayanıklılığını yönetir ve ayrı bir binlog fsync adımı kalmaz. Binary log seçenekleri için binary log ve PITR yazısına bakın.

Disk yazma yöntemi ve G/Ç kapasitesi

innodb_flush_method MariaDB 11.0'dan itibaren kullanımdan kaldırılmış olarak işaretlidir. Hâlâ ayarlanabilir, ancak belgelerin önerdiği yol çalışırken SET GLOBAL ile değiştirilebilen dört Boolean değişkendir: innodb_log_file_buffering, innodb_data_file_buffering, innodb_log_file_write_through ve innodb_data_file_write_through. Unix'te varsayılan değer O_DIRECT olarak listelenir. innodb_log_file_buffering ve innodb_log_file_write_through için yapılan bir değişikliğin bazı durumlarda sessizce yok sayılabileceğini belgeler not eder; değeri SELECT @@GLOBAL... ile geri okuyarak doğrulayın.

innodb_io_capacity, birleştirme ve sayfa boşaltma gibi arka plan işleri için G/Ç sınırıdır; varsayılan değeri 200'dür ve sisteminizin saniyede kaldırabildiği G/Ç işlemi sayısı civarında olmalıdır. innodb_io_capacity_max acil durumlarda çıkılabilecek üst sınırdır; varsayılan değer 2000 ya da innodb_io_capacity değerinin iki katıdır, hangisi büyükse. Belgeler gereğinden yüksek bir değerin verinin arabelleklerden çok hızlı atılmasına ve önbelleğin etkisinin azalmasına yol açabileceğini hatırlatır.

Bağlantı başına bellek ve geçici tablolar

sort_buffer_size, join_buffer_size, read_buffer_size gibi arabellekler her oturumda ayrı ayrı ayrılabilir; büyük değerler yalnızca aynı anda az oturum bunları kullanıyorsa güvenlidir. max_connections değerini gerçek trafiği karşılayacak kadar yüksek, ama en kötü durumda bağlantı başına arabelleklerin RAM'i tüketemeyeceği kadar düşük tutun. Uygulama tarafında her istek için yeni bağlantı açmak yerine bağlantı havuzu kullanmak belgelerin önerisidir; PHP tarafı için PHP PDO bağlantı yazısına bakabilirsiniz.

Sorgular sırasında oluşan iç geçici tabloların ne kadarının diske taştığını şu komutla görebilirsiniz. Created_tmp_tables toplam iç geçici tablo sayısını, Created_tmp_disk_tables ise bunlardan diske yazılanları gösterir. Bellekteki geçici tablolar için alanı max_heap_table_size ve tmp_memory_table_size belirler ve bu değerler de bağlantı başınadır.

show global status like "Created%tables%";

Çok sayıda kısa bağlantı açılıp kapanıyorsa back_log değerini artırmak, aynı anda 128'den fazla hızlı sorgu çalışıyorsa thread_handling değerini pool_of_threads yapmak belgelerde önerilen ayarlardır. thread_cache_size nadiren elle ayar ister; eşzamanlı bağlantıları sınırlamaz, yalnızca kopan iş parçacıklarından kaçının yeniden kullanım için bekletileceğini belirler.

Küçük ama önemli diğer ayarlar

• Query cache: ölçümler açık bir fayda göstermiyorsa query_cache_type değerini OFF, query_cache_size değerini 0 tutun. Her uygun SELECT önbelleğe bakmak zorundadır ve tabloya yapılan her yazma o tablonun önbelleğini geçersiz kılar.
• MyISAM kullanmıyorsanız: key_buffer_size çok düşük bir değerde kalabilir.
• Aria: MariaDB iç geçici dosyalar için varsayılan olarak Aria kullanır; çok geçici tablo oluşuyorsa aria_pagecache_buffer_size (varsayılan 128M) değerini artırmak gerekebilir.
• table_open_cache: varsayılan değer 2000'dir. Opened_files / Uptime oranı saniyede 5'ten fazlaysa artırmayı düşünün; her tablo 1 ile 3 dosya açar ve bölümlenmiş tablolarda her bölüm ayrı tablo gibi sayılır.
• Swap: adanmış Linux sunucularında düşük bir vm.swappiness değeri genellikle daha iyi sonuç verir, ama doğru bellek boyutlandırmasının yerini tutmaz.
• Binary log saklama: binary log açıksa dosyalar siz temizleyene kadar birikir; güncel sürümlerde saklama süresi için binlog_expire_logs_seconds tercih edilir.

Varsayılandan farklı olan değişkenleri bulmak bellek sorunlarının kaynağını görmenin hızlı bir yoludur. Belgelerdeki sorgu information_schema.system_variables ile information_schema.global_variables tablolarını birleştirip varsayılan değeri ile güncel değeri farklı olanları listeler. Sunucu belleği yetersiz kaldığında suçlu çoğu zaman bu listededir. Her değişiklikten sonra swap, bellek kullanımı ve sorgu gecikmesini yeniden ölçün; belgeler çok sayıda bellek değişkenini aynı anda değiştirmemenizi, önce en büyük ayarlardan başlamanızı önerir.

Küçük bir VPS için örnek düşünce süreci

Yalnızca MariaDB ve bir web uygulamasının çalıştığı, 4 GB RAM'li bir sunucu düşünün. Belgeler 4 GB'tan küçük sunucularda daha düşük yüzde önerdiği için sınırda bir durumdasınız ve web sunucusunun, PHP süreçlerinin ve işletim sisteminin payını önce çıkarmanız gerekir. Kalan kullanılabilir bellek üzerinden yüzde 60 ile 70 aralığının alt ucundan başlayın, yukarıdaki sorguyla InnoDB verinizin toplam boyutunu ölçün ve buffer pool'u bu toplamın yüzde 110'undan büyük yapmayın. Ardından bir dakikalık aralıkla Innodb_buffer_pool_reads ve Innodb_buffer_pool_read_requests değişimlerini karşılaştırın. Swap kullanımı başlarsa değeri düşürün. Bu adımların hiçbiri bir sayıyı ezberlemenizi gerektirmez; her biri ölçüme dayanır.

[mariadb]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
key_buffer_size = 16M

Bu blok yalnızca biçimi gösteren bir örnektir; değerleri kendi ölçümlerinize göre değiştirin. Dosyayı değiştirdikten sonra sunucuyu yeniden başlatmanız gerekir; servis komutları için MariaDB systemd yazısına bakın.

Sık yapılan hatalar

• Toplam RAM'e göre boyutlandırmak: Hesap kullanılabilir belleğe, kapsayıcıda ise kapsayıcının bellek sınırına göre yapılmalıdır.
• Buffer pool'u ayırılabilecek bellekten büyük yapmak: InnoDB bellek ayıramazsa sunucu başlarken InnoDB: Fatal error: cannot allocate memory for the buffer pool mesajıyla çöker; ayrıntı için MariaDB sık hatalar yazısına bakın.
• SET GLOBAL ile değiştirip dosyaya yazmamak: Değişiklik ilk yeniden başlatmada kaybolur.
• SET deyiminde 2G yazmak: M ve G son ekleri yalnızca yapılandırma dosyasında geçerlidir.
• ib_logfile0 silmek: 10.8.1 ve sonrasında sunucu başlamaz.
• Hız için innodb_flush_log_at_trx_commit=0 seçip riski unutmak: Sunucu çökmesinde son saniyenin commit edilmiş transaction'ları kaybolabilir.
• Bağlantı başına arabellekleri cömertçe büyütmek: Bu değerler eşzamanlı oturum sayısıyla çarpılır.

Sık Sorulan Sorular

innodb_buffer_pool_size için yüzde 80 mi, yüzde 70 mi?

Belgelerde ikisi de geçer. Güncel bellek rehberi kullanılabilir belleğin yüzde 60 ile 70'ini başlangıç olarak önerir, daha eski performans sayfası ise adanmış sunucu için yüzde 80'den bahseder. Ölçüt sabit oran değil, aktif verinin bellekte kalması ve swap olmamasıdır.

Buffer pool'u yeniden başlatmadan büyütebilir miyim?

Evet. SET GLOBAL innodb_buffer_pool_size ile değiştirebilirsiniz. 12.3.2 ve sonrasında 64 bit sistemlerde üst sınır varsayılan olarak 8 TiB sanal adres alanıdır; daha eski sürümlerde büyütme için innodb_buffer_pool_size_max başlangıçta ayarlanmış olmalıdır.

Redo log ne kadar büyük olmalı?

Belgeler sabit bir değer vermez. Büyük günlük yazma çoğalmasını azaltır ama çökme kurtarmasını uzatabilir. Kontrol noktası yaşının günlük kapasitesine oranı 1,0'a yaklaşıyorsa günlüğü büyütmeyi düşünün.

Query cache'i açmalı mıyım?

Belgeler, ölçümler açık bir fayda göstermedikçe kapalı tutmayı önerir. Açacaksanız boyutu küçük (genellikle 50M'den fazla değil) tutun, DEMAND modunu tercih edin ve yalnızca tekrar kullanımdan fayda gören sorgularda SQL_CACHE kullanın.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: MariaDB öğrenme rehberi · MariaDB my.cnf yapılandırma dosyaları · MariaDB EXPLAIN ve ANALYZE · MariaDB slow query log · MariaDB binary log · SQL sorgu optimizasyonu