MySQL Bellek Kullanımı Yüksek: Parametre Analizi ve Çözüm Yolları

Tarafındankomut
0 Comments

Sabah 9’da telefon çalıyor, müşteri panikle “sunucu çöktü, MySQL memory yüzde 95’te” diyor. Tanıdık geldi mi? MySQL bellek sorunları, production ortamlarında en sık karşılaşılan ve en çok baş ağrıtan problemlerin başında geliyor. Asıl sorun genellikle MySQL’in kendisinde değil, yanlış yapılandırılmış parametrelerde yatıyor. Bu yazıda hangi parametrelerin ne işe yaradığını, nasıl analiz edeceğini ve gerçek senaryolarda nasıl müdahale edeceğini adım adım anlatacağım.

Önce Durumu Anlayalım: Bellek Nereye Gidiyor?

MySQL bellek tüketimini iki ana kategoride düşünebilirsin: global bellek alanları ve bağlantı başına ayrılan bellek alanları. Bu ayrımı kavramadan parametreleri anlamlandırmak çok zor.

Global bellek alanları, MySQL instance’ı ayağa kalktığında bir kez tahsis edilir ve tüm bağlantılar tarafından paylaşılır. InnoDB buffer pool bunun en büyük örneği. Bağlantı başına bellek alanları ise her yeni bağlantı açıldığında ayrıca tahsis edilir ve bağlantı kapandığında serbest bırakılır. 500 aktif bağlantın varsa, her biri için ayrılan sort buffer, join buffer gibi alanlar birikince ciddi bir bellek tüketimine yol açar.

Basit bir formülle genel bir tahmin yapabilirsin:

Toplam Bellek = Global Bellek + (Bağlantı Başına Bellek x Maksimum Bağlantı Sayısı)

Ama bu teorik maksimum, gerçekte tüm bellek alanları her zaman tam olarak kullanılmaz. Şimdi gerçek analize geçelim.

Mevcut Durumu Tespit Etme

Sunucuya bağlandıktan sonra ilk iş sistemi ve MySQL’i birlikte incelemek. İşletim sistemi seviyesinde ne kadar bellek kullanıldığını görmek için:

# Genel bellek durumu
free -h

# MySQL process'inin anlık bellek kullanımı
ps aux | grep mysqld | awk '{print $4, $6}' | head -5

# Daha detaylı process bilgisi
cat /proc/$(pidof mysqld)/status | grep -i vm

# smaps ile gerçek bellek haritası
pmap -x $(pidof mysqld) | tail -5

MySQL içinden de durumu görebilirsin:

mysql -u root -p -e "
SELECT 
  SUBSTRING_INDEX(event_name, '/', 2) AS memory_area,
  ROUND(SUM(current_alloc) / 1024 / 1024, 2) AS current_mb,
  ROUND(SUM(high_alloc) / 1024 / 1024, 2) AS peak_mb
FROM performance_schema.memory_summary_global_by_event_name
WHERE current_alloc > 0
GROUP BY memory_area
ORDER BY current_mb DESC
LIMIT 20;"

Bu sorgu sana MySQL’in kendi perspektifinden belleği nerede kullandığını gösterir. Performance Schema açık değilse performance_schema = ON parametresini my.cnf’e ekleyip restart atmak gerekiyor, ancak production’da dikkatli ol, küçük bir overhead getirir.

Kritik Global Bellek Parametreleri

InnoDB Buffer Pool

MySQL bellek tüketiminin kraliçesi. Neredeyse her “MySQL çok bellek yiyor” şikayetinin baş sorumlusu ya bu parametrenin çok büyük ayarlanmış olması ya da yanlış beklentiler.

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool%';"

innodb_buffer_pool_size: InnoDB tablolarının ve indekslerinin önbelleğe alındığı yer. Genel kural, dedicated MySQL sunucularında toplam RAM’in yüzde 70-80’i, paylaşımlı sunucularda yüzde 50-60’ı. 8GB RAM’li bir sunucuda 6GB buffer pool makul bir başlangıç noktası.

innodb_buffer_pool_instances: Buffer pool’u parçalara böler, çok çekirdekli sistemlerde mutex çakışmalarını azaltır. Her instance minimum 1GB olmalı. 8GB buffer pool için 8 instance mantıklı.

innodb_buffer_pool_chunk_size: MySQL 5.7.5 ile gelen bu parametre, buffer pool’un büyütülüp küçültülmesini kontrol eder. Chunk_size x instances değerinin buffer_pool_size’ı tam bölmesi gerekiyor, aksi halde MySQL otomatik ayarlama yapıyor ve beklenmedik bellek kullanımı oluşabilir.

Bu üçünü birlikte kontrol et:

mysql -u root -p << 'EOF'
SELECT 
  @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_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 effective_chunk_total_mb,
  @@innodb_buffer_pool_size / (@@innodb_buffer_pool_chunk_size * @@innodb_buffer_pool_instances) AS chunk_multiplier;
EOF

chunk_multiplier tam sayı çıkmazsa sorun var demektir.

Key Buffer ve Query Cache

mysql -u root -p -e "SHOW VARIABLES LIKE 'key_buffer_size'; SHOW VARIABLES LIKE 'query_cache%';"

key_buffer_size: MyISAM tabloları kullanıyorsan önemli, pure InnoDB ortamlarda 8-16MB yeterli. Yüzlerce MB ayarlanmış görürsen boşa giden bellektir.

query_cache_size ve query_cache_type: MySQL 5.7’den itibaren deprecated, 8.0’da tamamen kaldırıldı. 5.6 veya 5.7 kullanıyorsan ve query cache açıksa, yüksek yazma trafiğinde global mutex nedeniyle performans canavarına dönüşebilir. Kapatmak genellikle daha iyidir.

Table Open Cache ve Diğerleri

mysql -u root -p -e "
SHOW VARIABLES WHERE Variable_name IN (
  'table_open_cache',
  'table_definition_cache', 
  'open_files_limit',
  'thread_cache_size',
  'host_cache_size'
);"

table_open_cache: Açık tablo tanımlarını önbellekte tutar. Her açık tablo yaklaşık birkaç KB bellek alır ama binlerce tablo varsa bu hızla toplanır. SHOW GLOBAL STATUS LIKE 'Opened_tables'; ile büyüyen bir sayı görüyorsan cache küçük demektir.

table_definition_cache: Tablo tanımlarının (.frm dosyası içeriği) önbelleği. Çok sayıda tablo olan ortamlarda bu değer tablo sayısına yakın olmalı.

Bağlantı Başına Bellek Parametreleri: Asıl Tehlike Burada

Global parametreler genellikle gözle görülür ve kontrol altında tutulur. Asıl sinsi tehlike bağlantı başına ayrılan bellek alanlarında. 1000 bağlantı açık ve her biri 8MB sort buffer kullanıyorsa, bu tek başına 8GB’a tekabül eder.

mysql -u root -p -e "
SHOW VARIABLES WHERE Variable_name IN (
  'sort_buffer_size',
  'join_buffer_size',
  'read_buffer_size',
  'read_rnd_buffer_size',
  'tmp_table_size',
  'max_heap_table_size',
  'binlog_cache_size',
  'net_buffer_length',
  'thread_stack',
  'bulk_insert_buffer_size'
);"

sort_buffer_size: ORDER BY ve GROUP BY sorgularında kullanılır. Varsayılan 256KB-2MB civarındadır. Bunu 64MB’a çeken DBA’ler gördüm, bu ciddi bir hata. Eğer sort buffer yetmezse MySQL disk kullanır (filesort), bu performansı etkiler ama belleği öldürmez. Dengeyi korumak önemli, 2-4MB genellikle yeterli.

join_buffer_size: Index kullanmayan JOIN’lerde kullanılır. Asıl çözüm index eklemek olmalı, join_buffer’ı büyütmek değil. Yine de 1-4MB arası makul.

read_buffer_size: Sequential scan’larda kullanılır. MyISAM için daha kritik. 128KB-256KB yeterli çoğu senaryoda.

tmp_table_size ve max_heap_table_size: Geçici tablolar için bellek limiti. Bu ikisi eşit ayarlanmalı. Aşıldığında MySQL geçici tabloyu diske taşır. Çok küçük ayarlanırsa diskte çok geçici tablo oluşur, çok büyük ayarlanırsa bellek sorunu yaşarsın.

Maksimum Teorik Bellek Hesabı

Gerçekten ne kadar bellek tüketebilirsin diye hesap yapmak istersen:

mysql -u root -p << 'EOF'
SELECT 
  ROUND(
    ( @@innodb_buffer_pool_size 
    + @@innodb_log_buffer_size
    + @@key_buffer_size
    + @@query_cache_size
    + (
        ( @@read_buffer_size
        + @@read_rnd_buffer_size
        + @@sort_buffer_size
        + @@join_buffer_size
        + @@binlog_cache_size
        + @@thread_stack
        + @@tmp_table_size
        + 2 * 1024 * 1024
        ) * @@max_connections
      )
    ) / 1024 / 1024 / 1024, 2
  ) AS theoretical_max_gb,
  @@max_connections AS max_connections,
  @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS buffer_pool_gb;
EOF

Bu rakam sunucu RAM’inden büyükse ama actual_connections hiç max_connections’a ulaşmıyorsa paniklemene gerek yok. Ama production’da ani spike’lar olabilir, risk altında demektir.

Gerçek Dünya Senaryosu 1: E-ticaret Sitesi Çöküşü

16GB RAM’li bir sunucuda çalışan WooCommerce backend’i vardı. MySQL 12GB bellek yiyor, sunucu swap’a girmiş, her şey yavaşlamış.

Analiz ettiğimde şunu bulduk:

# Bağlantı durumu
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_connected'; SHOW STATUS LIKE 'Max_used_connections';"
# Sonuç: Threads_connected: 847, Max_used_connections: 1200

# Sort buffer durumu
mysql -u root -p -e "SHOW VARIABLES LIKE 'sort_buffer_size';"
# Sonuç: 32MB (!)

# Hesap: 847 * 32MB = ~27GB sadece sort buffer'dan

Birisi sort_buffer_size’ı 32MB’a çekmiş, “performansı artıralım” niyetiyle. Bu tek parametre değişikliği sunucuyu dizleri üstüne çöktürmüştü. Düzeltme basitti:

# my.cnf değişikliği
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 256K

# Servisi restart etmeden online değiştir
mysql -u root -p -e "SET GLOBAL sort_buffer_size = 2097152;"
mysql -u root -p -e "SET GLOBAL join_buffer_size = 2097152;"

10 dakika içinde bellek kullanımı 6GB’a düştü.

Gerçek Dünya Senaryosu 2: Kademeli Bellek Artışı

Bu senaryo daha sinsidir. Sistem günler içinde yavaş yavaş bellek doluyor, aniden çökmüyor ama her gün restart atmak zorunda kalıyorsun.

Bunu yaşayan bir müşteride sorun performance schema memory instrumentasyon verisinden tespit ettik:

mysql -u root -p -e "
SELECT event_name, 
       ROUND(current_alloc/1024/1024, 2) AS current_mb,
       ROUND(high_alloc/1024/1024, 2) AS peak_mb,
       current_count_used
FROM performance_schema.memory_summary_global_by_event_name
WHERE event_name LIKE 'memory/innodb/%'
ORDER BY current_alloc DESC
LIMIT 15;"

Sorun InnoDB’nin undo log alanıydı. Uzun süre açık kalan transaction’lar undo tablespace’i şişiriyordu. Uygulama tarafında commit edilmeyen transaction’lar vardı.

# Uzun süre açık transaction'ları bul
mysql -u root -p -e "
SELECT trx_id, trx_state, trx_started, 
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
       trx_query, trx_rows_modified
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;"

Duration_sec değeri saatlerle ifade edilen transaction’lar bulundu. Uygulama connection pool’unda yanlış konfigürasyon vardı, bağlantılar transaction açık bırakılıyordu.

Performans Schema ile Derin Analiz

Sorun devam ediyorsa daha derin analiz gerekebilir:

mysql -u root -p << 'EOF'
-- Bağlantı başına bellek kullanımı
SELECT 
  processlist_user AS user,
  processlist_host AS host,
  processlist_db AS db,
  ROUND(SUM(memory_summary.current_alloc)/1024/1024, 2) AS current_mb
FROM performance_schema.memory_summary_by_thread_by_event_name AS memory_summary
JOIN performance_schema.threads USING (thread_id)
WHERE processlist_user IS NOT NULL
GROUP BY processlist_user, processlist_host, processlist_db
ORDER BY current_mb DESC
LIMIT 10;
EOF

Hangi kullanıcının, hangi veritabanı bağlantısının en çok bellek yediğini görmek son derece faydalı. Bazen tek bir kötü yazılmış uygulama bağlantısının tüm belleği tükettiğini görürsün.

Önerilen my.cnf Parametreleri

Her ortam farklı, tek doğru konfigürasyon diye bir şey yok. Ama 16GB RAM’li dedicated bir MySQL sunucusu için makul bir başlangıç noktası:

# /etc/mysql/mysql.conf.d/mysqld.cnf veya /etc/my.cnf

[mysqld]
# InnoDB Buffer Pool - RAM'in %70-75'i
innodb_buffer_pool_size = 11G
innodb_buffer_pool_instances = 11
innodb_buffer_pool_chunk_size = 128M

# Log buffer
innodb_log_buffer_size = 64M

# Bağlantı başına bellek
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 256K
read_rnd_buffer_size = 512K
tmp_table_size = 64M
max_heap_table_size = 64M
thread_stack = 256K
binlog_cache_size = 1M

# Bağlantı limiti
max_connections = 200

# Table cache
table_open_cache = 4000
table_definition_cache = 2000

# Query cache kapalı (MySQL 5.7)
query_cache_type = 0
query_cache_size = 0

Bu konfigürasyonu kendi ortamına uyarlamadan doğrudan kopyalama. RAM miktarına, bağlantı sayısına ve iş yüküne göre değiştirmen gerekiyor.

Değişiklikleri Uygulamak: Restart Gerekiyor mu?

Bazı parametreler online değiştirilebilir, bazıları için restart şart:

# Restart gerektirmeyen değişiklikler (dikkatli kullan, test et)
mysql -u root -p -e "
SET GLOBAL sort_buffer_size = 2097152;
SET GLOBAL join_buffer_size = 2097152;
SET GLOBAL tmp_table_size = 67108864;
SET GLOBAL max_heap_table_size = 67108864;
SET GLOBAL table_open_cache = 4000;
SET GLOBAL max_connections = 200;
"

# innodb_buffer_pool_size MySQL 5.7.5+ dinamik değiştirilebilir
mysql -u root -p -e "SET GLOBAL innodb_buffer_pool_size = 11811160064;"

InnoDB buffer pool’u online küçültürken dikkatli ol. Boyutu azaltırken dirty page’lerin flush edilmesi gerekir, bu süreç IO spike’ına yol açabilir. Production’da off-peak saatinde yap.

Değişikliği my.cnf’e de eklemeyi unutma, yoksa restart sonrası eski değerlere döner:

# Değişiklik sonrası kontrol
mysql -u root -p -e "SHOW GLOBAL VARIABLES LIKE 'sort_buffer_size';"

# my.cnf senkronizasyonu kontrol
grep "sort_buffer_size" /etc/mysql/mysql.conf.d/mysqld.cnf

Monitoring: Sorun Tekrar Yaşanmasın

Sadece söndürmek değil, yangın alarm sistemi kurmak da sysadmin işinin parçası:

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

MYSQL_USER="monitor"
MYSQL_PASS="your_password"
THRESHOLD_GB=13
ALERT_EMAIL="[email protected]"

CURRENT_MB=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -N -e "
SELECT ROUND(SUM(current_alloc)/1024/1024, 0) 
FROM performance_schema.memory_summary_global_by_event_name 
WHERE current_alloc > 0;")

CURRENT_GB=$(echo "scale=2; $CURRENT_MB/1024" | bc)

if (( $(echo "$CURRENT_GB > $THRESHOLD_GB" | bc -l) )); then
    echo "MySQL bellek kullanimi kritik: ${CURRENT_GB}GB" | 
    mail -s "ALERT: MySQL High Memory on $(hostname)" $ALERT_EMAIL
    
    # Yuksek bellek kullanan baglantilari logla
    mysql -u$MYSQL_USER -p$MYSQL_PASS -e "
    SELECT processlist_user, processlist_host, 
           ROUND(SUM(memory_summary.current_alloc)/1024/1024, 2) AS mb
    FROM performance_schema.memory_summary_by_thread_by_event_name AS memory_summary
    JOIN performance_schema.threads USING (thread_id)
    WHERE processlist_user IS NOT NULL
    GROUP BY processlist_user, processlist_host
    ORDER BY mb DESC LIMIT 5;" >> /var/log/mysql_memory_alert.log
fi

Bu script’i crontab’a ekle:

# Her 5 dakikada bir kontrol
*/5 * * * * /usr/local/bin/mysql_memory_check.sh

Swap Kullanımı ve OOM Killer

MySQL swap’a girdiğinde performans uçurum gibi düşer. Bazı yöneticiler swappiness = 0 yapar, bu da MySQL OOM killer tarafından öldürülme riskini artırır. Orta yol:

# Mevcut durumu kontrol
cat /proc/sys/vm/swappiness
swapon --show

# MySQL process OOM score'u kontrol et
cat /proc/$(pidof mysqld)/oom_score
cat /proc/$(pidof mysqld)/oom_score_adj

# MySQL'i OOM killer'dan koruma altına al (dikkatli kullan)
echo -1000 > /proc/$(pidof mysqld)/oom_score_adj

# Kalıcı yapmak için systemd service override
mkdir -p /etc/systemd/system/mysql.service.d/
cat > /etc/systemd/system/mysql.service.d/oom.conf << 'EOF'
[Service]
OOMScoreAdjust=-1000
EOF
systemctl daemon-reload

OOMScoreAdjust=-1000 MySQL’i OOM killer’ın listesinden tamamen çıkarır. Ama bu durumda bellek baskısında MySQL yerine başka process’ler öldürülür. Tradeoff’u değerlendir.

Sonuç

MySQL bellek sorunları genellikle tek bir parametrenin “ayarlanması” sonucu ortaya çıkar. Birisi bir blog yazısında “sort_buffer_size’ı büyüt, hızlanır” der, yapılır, 3 ay sonra sunucu çöker. Parametreleri tek tek değil, bir sistem olarak düşünmek gerekiyor.

Analiz sırasında şu sırayı takip et: önce gerçek bellek tüketimini ölç, global parametreleri kontrol et, bağlantı başına parametreleri incele, aktif bağlantı sayısına bak, teorik maksimumu hesapla. Sonra değişiklikleri tek tek yap ve her değişikliğin etkisini ölç.

Her ortam farklıdır. 200 bağlantılı bir SaaS uygulaması ile 50 bağlantılı bir muhasebe yazılımı tamamen farklı parametreler ister. Kopyala-yapıştır konfigürasyonlar başlangıç noktası olabilir, ama nihai ayarlama mutlaka kendi ortamında load test ile yapılmalı.

Monitoring’i ihmal etme. Yangın çıkmadan alarm kurulursa, sabah 9’daki panikli telefon aramaları çok daha azalır.

Benzer Konular

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir