Büyük Veri Setlerinde MySQL Tablo Optimizasyonu

Milyonlarca satır veri biriktikten sonra MySQL veritabanınızın yavaşlamaya başlaması, gecede bir kez çalışan raporların sabaha kadar bitmemesi ya da basit bir SELECT sorgusunun dakikalarca sürmesi… Bunların hepsini yaşadıysanız, büyük veri setlerinde tablo optimizasyonunun ne kadar kritik olduğunu zaten biliyorsunuzdur. Bu yazıda, production ortamında gerçekten işe yarayan optimizasyon tekniklerini, somut kod örnekleriyle ele alacağız.

Neden Tablo Optimizasyonu Gereklidir?

MySQL tabloları zamanla şişer. INSERT, UPDATE ve DELETE işlemleri tamamlandıkça tabloda boşluklar oluşur, indeksler parçalanır ve sorgu planları eskir. Özellikle e-ticaret siteleri, log tabloları veya IoT verisi toplayan sistemlerde bu durum kaçınılmazdır.

Bir tabloyu optimize etmeden önce mevcut durumu anlamak gerekir. Aşağıdaki sorgu ile tablo boyutlarını ve parçalanma oranını görebilirsiniz:

mysql -u root -p -e "
SELECT 
  table_name,
  ROUND(data_length/1024/1024, 2) AS 'Veri (MB)',
  ROUND(index_length/1024/1024, 2) AS 'Index (MB)',
  ROUND(data_free/1024/1024, 2) AS 'Bos Alan (MB)',
  ROUND((data_free/(data_length+index_length))*100, 2) AS 'Parcalanma %'
FROM information_schema.tables
WHERE table_schema = 'production_db'
  AND data_free > 0
ORDER BY data_free DESC;
"

Bu sorgudan dönen sonuçta Parcalanma % değeri 30’un üzerindeyse, o tablo ciddi optimizasyon gerektiriyor demektir. Bazı sistemlerde bu değerin %80’lere çıktığını gördüm; özellikle sık DELETE işlemi yapılan log tablolarında bu çok yaygındır.

InnoDB vs MyISAM: Doğru Motor Seçimi

Optimizasyona geçmeden önce, tablo motorunuzu kontrol etmeniz şart. 2024’te hala MyISAM kullanan tablolar görüyorum ve bu büyük bir performans kaybı nedenidir. InnoDB, büyük veri setleri için çok daha üstündür.

# Hangi tablolar MyISAM kullanıyor?
mysql -u root -p production_db -e "
SELECT table_name, engine 
FROM information_schema.tables 
WHERE table_schema = 'production_db' 
  AND engine = 'MyISAM';
"

# MyISAM tabloyu InnoDB'ye dönüştür
mysql -u root -p production_db -e "
ALTER TABLE eski_log_tablosu ENGINE=InnoDB;
"

Bu dönüşüm işlemi büyük tablolarda uzun sürebilir. 50 GB’lık bir tablo için birkaç saat beklemeye hazır olun. Production ortamında bu işlemi mutlaka maintenance window’da yapın.

ANALYZE TABLE ve OPTIMIZE TABLE Komutları

Bu iki komut çok sık karıştırılır ama farklı şeyler yapar.

ANALYZE TABLE: İstatistikleri günceller, sorgu planlarını iyileştirir. Tabloyu kilitlemez, nispeten hızlıdır.

OPTIMIZE TABLE: Veriyi yeniden düzenler, boş alanları geri kazanır, indeksleri yeniden oluşturur. Daha ağır bir işlemdir.

# Tek bir tablo için analiz
mysql -u root -p production_db -e "ANALYZE TABLE siparisler;"

# Birden fazla tablo için
mysql -u root -p production_db -e "
ANALYZE TABLE siparisler, urunler, musteriler, stok_hareketleri;
"

# Optimize işlemi (dikkatli kullanın!)
mysql -u root -p production_db -e "OPTIMIZE TABLE siparisler;"

InnoDB tablolarda OPTIMIZE TABLE aslında arka planda ALTER TABLE işlemi çalıştırır. Bu yüzden büyük tablolarda pt-online-schema-change veya gh-ost gibi araçları tercih etmenizi öneririm. Bunları birazdan anlatacağım.

İndeks Optimizasyonu: Asıl Fark Burada

Büyük veri setlerinde en büyük performans kazancını doğru indeksleme sağlar. Ama hem eksik hem de fazla indeks sorun yaratır. Fazla indeks, yazma işlemlerini yavaşlatır ve disk alanı tüketir.

Kullanılmayan İndeksleri Tespit Etme

mysql -u root -p -e "
SELECT 
  object_schema,
  object_name,
  index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
  AND count_star = 0
  AND object_schema = 'production_db'
ORDER BY object_name;
"

Bu sorgu, hiç kullanılmayan indeksleri listeler. Onları silmekten korkmayın, yazma performansınız artacaktır.

Eksik İndeksleri Bulma

mysql -u root -p -e "
SELECT 
  db,
  query,
  rows_examined,
  rows_sent,
  ROUND(rows_examined/rows_sent, 0) AS oran
FROM mysql.slow_log
WHERE rows_examined > 10000
  AND rows_sent < 100
ORDER BY rows_examined DESC
LIMIT 20;
"

rows_examined / rows_sent oranı yüksek olan sorgular genellikle uygun indeksten yoksundur. Bu sorguları inceleyip EXPLAIN ile analiz edin:

mysql -u root -p production_db -e "
EXPLAIN SELECT o.id, o.musteri_id, m.ad, m.soyad
FROM siparisler o
JOIN musteriler m ON o.musteri_id = m.id
WHERE o.durum = 'beklemede'
  AND o.tarih >= '2024-01-01'
ORDER BY o.tarih DESC;
"

EXPLAIN çıktısında type kolonuna bakın. ALL görüyorsanız full table scan yapılıyor demektir. ref, range veya eq_ref görmek istiyorsunuz.

Composite İndeks Oluşturma

# Tek kolonlu indeks yerine composite indeks kullanın
mysql -u root -p production_db -e "
-- Yanlış: Her kolon için ayrı indeks
-- CREATE INDEX idx_durum ON siparisler(durum);
-- CREATE INDEX idx_tarih ON siparisler(tarih);

-- Doğru: Composite indeks, sorgu sırasına göre kolonları sırala
CREATE INDEX idx_durum_tarih ON siparisler(durum, tarih);
"

Composite indeks oluştururken kolon sırasına dikkat edin. Kardinality yüksek olan (daha fazla benzersiz değere sahip) kolon önce gelmelidir, ama sorgunuzdaki WHERE koşullarına göre de ayarlama yapmanız gerekebilir.

Partition Kullanarak Büyük Tabloları Yönetme

100 milyonun üzerinde satıra sahip tablolarda partitioning hayat kurtarır. Özellikle tarih bazlı sorgularda inanılmaz fark yaratır.

# Var olan büyük tabloyu partition'a dönüştürme
mysql -u root -p production_db -e "
ALTER TABLE siparis_loglari
PARTITION BY RANGE (YEAR(islem_tarihi)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p_gelecek VALUES LESS THAN MAXVALUE
);
"

Partition’ların asıl gücü partition pruning‘den gelir. Eğer WHERE islem_tarihi >= '2024-01-01' gibi bir sorgu çalıştırırsanız, MySQL yalnızca ilgili partition’ı tarayacaktır.

Yıllık bazda log tablosu yönetimi için otomatik partition script’i:

#!/bin/bash
# add_monthly_partition.sh
# Her ay başında çalıştırın (crontab ile)

DB_HOST="localhost"
DB_USER="admin"
DB_PASS="sifre"
DB_NAME="production_db"
TABLE="siparis_loglari"

NEXT_YEAR=$(date -d "+1 month" +%Y)
NEXT_MONTH=$(date -d "+1 month" +%m)
PARTITION_NAME="p${NEXT_YEAR}${NEXT_MONTH}"
LESS_THAN_DATE="${NEXT_YEAR}-${NEXT_MONTH}-01"

mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME -e "
ALTER TABLE $TABLE 
REORGANIZE PARTITION p_gelecek INTO (
  PARTITION $PARTITION_NAME VALUES LESS THAN ('$LESS_THAN_DATE'),
  PARTITION p_gelecek VALUES LESS THAN MAXVALUE
);
"

echo "$(date): $PARTITION_NAME partition olusturuldu." >> /var/log/mysql_partition.log

pt-online-schema-change ile Sıfır Downtime Optimizasyon

Production ortamında OPTIMIZE TABLE çalıştırmak genellikle sorun yaratır çünkü tablo kilitlenir. Percona Toolkit’in pt-osc aracı bu sorunu çözer.

# Percona Toolkit kurulumu (RHEL/CentOS)
yum install percona-toolkit -y

# Ubuntu/Debian
apt-get install percona-toolkit -y

# pt-osc ile büyük tabloyu optimize et
pt-online-schema-change 
  --host=localhost 
  --user=admin 
  --password=sifre 
  --database=production_db 
  --table=siparisler 
  --alter="ENGINE=InnoDB" 
  --chunk-size=1000 
  --max-load="Threads_running=50" 
  --critical-load="Threads_running=100" 
  --set-vars="wait_timeout=60" 
  --progress=time,30 
  --print 
  --execute

Bu araç şu şekilde çalışır: Önce tablonun bir kopyasını oluşturur, veriyi parça parça kopyalar, trigger’lar aracılığıyla değişiklikleri senkronize eder ve sonunda tabloları atomik olarak değiştirir. Production trafik altında bile güvenle çalıştırabilirsiniz.

–max-load parametresi çok önemlidir. Sunucu yükü belirtilen değeri aşarsa araç otomatik olarak duraklar ve yük düştüğünde devam eder.

InnoDB Buffer Pool Optimizasyonu

Disk I/O’yu azaltmanın en etkili yolu buffer pool’u doğru boyutlandırmaktır. Sunucunuzun RAM’inin %70-80’ini buffer pool’a ayırabilirsiniz (başka servisler yoksa).

# Mevcut buffer pool kullanımını kontrol et
mysql -u root -p -e "
SHOW STATUS LIKE 'Innodb_buffer_pool%';
"

# Buffer pool hit rate hesapla
mysql -u root -p -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 'Buffer Pool Hit Rate %';
"

Hit rate %95’in altındaysa buffer pool boyutunuzu artırmanız gerekiyor.

/etc/mysql/mysql.conf.d/mysqld.cnf dosyasına ekleyin:

[mysqld]
# 16 GB RAM olan sunucu için
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_chunk_size = 128M

# Log dosyası boyutu (buffer pool'un %25'i)
innodb_log_file_size = 3G
innodb_log_buffer_size = 64M

# Flush ayarları
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

# Read/Write thread sayısı
innodb_read_io_threads = 8
innodb_write_io_threads = 8

innodb_flush_log_at_trx_commit = 2 ayarı bazı durumlarda veri kaybına yol açabilir (saniyede bir flush yapar). Eğer ACID uyumluluğu kritikse 1 olarak bırakın. Performans ve güvenlik arasındaki bu dengeyi her ortam için ayrı değerlendirin.

Slow Query Log ile Sorunlu Sorguları Tespit Etme

Hangi sorguların probleme yol açtığını bilmeden optimizasyon yapamazsınız. Slow query log bu konuda çok değerlidir.

# my.cnf'e ekleyin
# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000

Ayarları MySQL’i yeniden başlatmadan uygulayabilirsiniz:

mysql -u root -p -e "
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
"

Slow log’u analiz etmek için mysqldumpslow veya pt-query-digest kullanın:

# En yavaş 10 sorguyu listele
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# pt-query-digest ile detaylı analiz (çok daha güçlü)
pt-query-digest /var/log/mysql/mysql-slow.log 
  --limit=10 
  --output=report 
  > /tmp/slow_query_rapor.txt

cat /tmp/slow_query_rapor.txt

pt-query-digest çıktısı size her sorgu için ortalama süre, çalışma sayısı, toplam bekleme süresi ve çok daha fazlasını verir. Bu raporu haftada bir incelemek iyi bir alışkanlıktır.

Gerçek Dünya Senaryosu: E-Ticaret Sipariş Tablosu

Bir e-ticaret müşterisinde 80 milyon satır sipariş tablosunun olduğunu ve raporların 20-30 dakika sürdüğünü hayal edin. Bu tip bir senaryoyu birkaç adımda çözdüm:

Adım 1: Mevcut durumu tespit et

mysql -u root -p eticaret_db -e "
-- Tablo boyutu ve parçalanma
SELECT 
  table_name,
  table_rows,
  ROUND(data_length/1024/1024/1024, 2) AS 'Veri (GB)',
  ROUND(data_free/1024/1024/1024, 2) AS 'Bos (GB)'
FROM information_schema.tables
WHERE table_schema = 'eticaret_db'
  AND table_name = 'siparisler';
"

Adım 2: En ağır sorguları bul, EXPLAIN ile incele

mysql -u root -p eticaret_db -e "
EXPLAIN ANALYZE
SELECT 
  DATE(siparis_tarihi) AS gun,
  COUNT(*) AS siparis_sayisi,
  SUM(toplam_tutar) AS gunluk_ciro
FROM siparisler
WHERE siparis_tarihi >= '2024-01-01'
  AND durum IN ('tamamlandi', 'kargolandi')
GROUP BY DATE(siparis_tarihi)
ORDER BY gun;
"

Adım 3: Eksik indeksleri ekle, yıl bazlı partition oluştur

Bu iki adımı uyguladıktan sonra rapor süresi 20 dakikadan 45 saniyeye düştü. Tablo parçalanma oranı %67’den %8’e indi.

Otomatik Optimizasyon Script’i

Bu script’i cron ile haftalık çalıştırarak rutin bakımı otomatikleştirebilirsiniz:

#!/bin/bash
# mysql_optimize.sh
# Yuksek parcalanma oranli tablolari otomatik optimize eder

DB_HOST="localhost"
DB_USER="admin"
DB_PASS="sifreniz"
DB_NAME="production_db"
PARCALANMA_ESIK=30
LOG_FILE="/var/log/mysql_optimize.log"

echo "=== $(date) - Optimizasyon Basladi ===" >> $LOG_FILE

# Parcalanma orani yuksek tablolari bul ve optimize et
mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -N -e "
SELECT table_name
FROM information_schema.tables
WHERE table_schema = '$DB_NAME'
  AND data_free > 0
  AND (data_free/(data_length+index_length))*100 > $PARCALANMA_ESIK
  AND engine = 'InnoDB';
" | while read TABLE; do
  
  echo "$(date): $TABLE tablosu optimize ediliyor..." >> $LOG_FILE
  
  # pt-osc varsa onu kullan, yoksa standart ANALYZE yap
  if command -v pt-online-schema-change &> /dev/null; then
    pt-online-schema-change 
      --host=$DB_HOST 
      --user=$DB_USER 
      --password=$DB_PASS 
      --database=$DB_NAME 
      --table=$TABLE 
      --alter="ENGINE=InnoDB" 
      --chunk-size=500 
      --max-load="Threads_running=30" 
      --execute 
      --quiet 2>> $LOG_FILE
  else
    mysql -h $DB_HOST -u $DB_USER -p$DB_PASS $DB_NAME 
      -e "ANALYZE TABLE $TABLE;" >> $LOG_FILE 2>&1
  fi
  
  echo "$(date): $TABLE optimizasyon tamamlandi." >> $LOG_FILE
done

echo "=== $(date) - Optimizasyon Bitti ===" >> $LOG_FILE

Crontab ayarı (her Pazar gece 02:00’de çalışsın):

0 2 * * 0 /usr/local/bin/mysql_optimize.sh

Performance Schema ile Derinlemesine İzleme

MySQL’in Performance Schema’sı çoğu sysadmin tarafından yeterince kullanılmıyor. Bekleme sürelerini ve darboğazları tespit etmek için çok güçlüdür:

# En cok bekleme yaratan sorgular
mysql -u root -p -e "
SELECT 
  DIGEST_TEXT,
  COUNT_STAR AS cagri_sayisi,
  ROUND(AVG_TIMER_WAIT/1000000000, 3) AS 'Ort Bekleme (sn)',
  ROUND(SUM_TIMER_WAIT/1000000000, 3) AS 'Toplam Bekleme (sn)'
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'production_db'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
"

# Tablo bazli I/O bekleme analizi
mysql -u root -p -e "
SELECT 
  object_name,
  count_read,
  count_write,
  ROUND(sum_timer_read/1000000000, 2) AS 'Okuma Bekleme (sn)',
  ROUND(sum_timer_write/1000000000, 2) AS 'Yazma Bekleme (sn)'
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'production_db'
ORDER BY sum_timer_read + sum_timer_write DESC
LIMIT 10;
"

Bu sorgulardan çıkan sonuçlar, optimizasyon çabalarınızı nereye yönlendirmeniz gerektiğini net biçimde gösterir.

Sonuç

Büyük veri setlerinde MySQL optimizasyonu tek seferlik bir iş değildir. Veri büyüdükçe indeksler parçalanır, sorgu planları eskir ve yeni darboğazlar ortaya çıkar. Düzenli izleme, periyodik analiz ve proaktif müdahale olmadan en iyi şekilde kurulmuş bir sistem bile zamanla yavaşlar.

Öncelik sırasına göre özetlemek gerekirse: Önce slow query log’u aktif edin ve sorunlu sorguları tespit edin. Ardından EXPLAIN ile bu sorguları analiz edip eksik indeksleri ekleyin. Buffer pool boyutunu sunucunuzun RAM kapasitesine göre ayarlayın. Büyük tablolar için partitioning değerlendirin. Rutin bakımı otomatikleştirmek için script’leri ve pt-osc gibi araçları kullanın.

Production ortamında her büyük değişikliği önce test ortamında deneyin, yedek aldığınızdan emin olun ve mümkünse değişiklikleri düşük trafik saatlerine planlayın. Bu teknikleri sistematik biçimde uygularsanız, büyük veri setlerinde bile MySQL’in pekala hızlı ve kararlı çalışabileceğini göreceksiniz.