MySQL ya da MariaDB kurduğunuzda, varsayılan konfigürasyon dosyası sunucunuzu “çalışır” hale getirir ama asla “iyi çalışır” hale getirmez. Özellikle production ortamında, yüzlerce ya da binlerce eş zamanlı bağlantı altında varsayılan ayarlarla çalışan bir veritabanı sunucusu, zamanla yavaşlayan sorgular, artan I/O beklemeleri ve zaman zaman patlayan bağlantı havuzuyla sizi uğraştırmaya başlar. İşte bu noktada my.cnf dosyasını elinize almanız gerekir.
Bu yazıda, gerçek production senaryolarından yola çıkarak MySQL ve MariaDB için my.cnf optimizasyonunu adım adım ele alacağız. Sadece parametreleri listelemekle kalmayıp, hangi durumda ne değer vermeniz gerektiğini ve sonuçlarını nasıl ölçeceğinizi de göstereceğim.
my.cnf Dosyasının Konumu ve Yapısı
Linux sistemlerde MySQL/MariaDB konfigürasyon dosyası genellikle birkaç farklı yerde bulunabilir. Sunucunun hangi dosyayı kullandığını öğrenmek için:
mysqld --verbose --help | grep -A 1 "Default options"Tipik çıktı şu şekilde olur:
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnfYapı olarak my.cnf dosyası bölümler halinde organize edilmiştir. En sık kullandığımız bölümler [mysqld], [mysql] ve [client] bölümleridir. Optimizasyon ayarlarının büyük çoğunluğu [mysqld] altına girer.
cat /etc/mysql/my.cnfEğer dosya dağınık ya da çok parçalıysa, bütün konfigürasyonları tek bir dosyada toplamak için /etc/mysql/mysql.conf.d/ dizinini kullanabilirsiniz. Özellikle Debian tabanlı sistemlerde bu yaklaşım daha temiz bir yönetim sağlar.
Temel Bellek Parametreleri
InnoDB Buffer Pool
my.cnf optimizasyonunun kalbi burasıdır. InnoDB Buffer Pool, veritabanının veriyi ve index’leri bellekte tuttuğu alandır. Disk I/O’yu azaltmanın en etkili yolu bu değeri doğru ayarlamaktır.
Genel kural: Sunucunun toplam RAM’inin %70-80’ini bu değere verin. Tabii ki sunucu sadece MySQL çalıştırıyorsa. Eğer aynı sunucuda uygulama ya da web sunucusu da varsa bu oranı %50-60’a çekin.
# 32GB RAM'li, sadece MySQL çalışan bir sunucu için
[mysqld]
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 8innodb_buffer_pool_instances parametresi, buffer pool’u kaç parçaya böleceğinizi belirler. Buffer pool 8GB’ın üzerindeyse, her 1GB için bir instance mantığıyla gidebilirsiniz, ama 8-16 arasında tutmak genellikle yeterlidir. Bu ayar, çok çekirdekli sistemlerde lock contention’ı azaltır.
Buffer pool’un ne kadar verimli kullanıldığını görmek için:
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';"Burada Innodb_buffer_pool_reads ve Innodb_buffer_pool_read_requests oranına bakın. Hit rate %99’un üzerindeyse buffer pool boyutunuz yeterlidir.
Query Cache (MySQL 5.7 ve Öncesi)
MySQL 8.0 ile query cache tamamen kaldırıldı. Ama hala 5.7 ya da MariaDB 10.x kullanıyorsanız, dikkatli olmanız gerekir. Yüksek yazma trafiği olan sistemlerde query cache tam anlamıyla performans katili olabilir. Her tablo güncellendiğinde, o tabloya ait tüm önbellek geçersiz hale gelir.
# Yüksek okuma, düşük yazma trafiği için
[mysqld]
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M
# Yüksek yazma trafiği için tamamen kapat
# query_cache_type = 0
# query_cache_size = 0InnoDB Performans Ayarları
Log Dosyası ve Flush Ayarları
Bu bölüm, özellikle yüksek yazma trafiği olan sistemlerde kritik önem taşır. Bir e-ticaret sitesini düşünün: yoğun saatlerde saniyede yüzlerce sipariş kaydı yapılıyor. Yanlış log ayarları bu trafiği ciddi şekilde yavaşlatır.
[mysqld]
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECTinnodb_flush_log_at_trx_commit parametresi üç değer alır:
- 0: Her saniye log’a yazar ve diske flush eder. En hızlı ama en az güvenli seçenek. Sunucu crash’inde son 1 saniyelik veri kaybolabilir.
- 1: Her transaction commit’te diske yazar. En güvenli ama en yavaş. ACID uyumluluğu gerektiren sistemler için zorunludur.
- 2: Her commit’te log’a yazar ama diske flush’ı işletim sistemine bırakır. Güzel bir orta yol. Sunucu crash’inde veri kaybı olmaz ama OS crash’inde olabilir.
Çoğu web uygulaması için 2 değeri makul bir risk/performans dengesi sunar.
InnoDB I/O Kapasitesi
[mysqld]
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8Bu değerler disk türüne göre ayarlanmalıdır. SSD diskler için innodb_io_capacity = 2000 ve üzeri uygundur. Eski spinning disk kullanıyorsanız 200-400 arasında kalın. NVMe diskler için 4000-10000 rahatlıkla kullanılabilir.
Disk I/O performansınızı test etmek için:
# fio ile basit bir I/O testi
fio --name=randwrite --ioengine=libaio --rw=randwrite --bs=4k
--direct=1 --size=1G --numjobs=4 --runtime=60 --group_reportingBağlantı Yönetimi
Max Connections ve Thread Handling
Gerçek bir senaryo: Bir müşterinin web uygulaması “Too many connections” hatası vermeye başladı. İlk refleks max_connections‘ı artırmak olur, ama bu çoğunlukla yanlış yaklaşımdır. Her bağlantı bellek tüketir; 1000 bağlantı demek gigabyte’larca ek bellek demektir.
[mysqld]
max_connections = 500
max_connect_errors = 10000
connect_timeout = 10
wait_timeout = 600
interactive_timeout = 600
# Thread cache - yeni bağlantılar için thread oluşturma maliyetini azaltır
thread_cache_size = 100wait_timeout ve interactive_timeout değerlerini varsayılan 8 saatten indirmek, uyku durumundaki bağlantıların birikmesini önler. Pek çok sistemde “Too many connections” sorununu bu iki değeri ayarlayarak çözdüm.
Mevcut bağlantı durumunu izlemek için:
mysql -u root -p -e "SHOW PROCESSLIST;" | grep -v Sleep | wc -l
# ya da daha detaylı
mysql -u root -p -e "SHOW STATUS LIKE 'Threads_%';"Thread Pool (MariaDB)
MariaDB, MySQL’den farklı olarak thread pool özelliğini yerleşik olarak sunar. Yüzlerce eş zamanlı bağlantı olan sistemlerde thread pool kullanmak, bellek tüketimini ve context switching maliyetini ciddi ölçüde azaltır.
[mysqld]
# MariaDB thread pool
thread_handling = pool-of-threads
thread_pool_size = 32
thread_pool_max_threads = 1000
thread_pool_idle_timeout = 60Tablo ve Dosya Yönetimi
[mysqld]
table_open_cache = 4000
table_definition_cache = 2000
open_files_limit = 65535
# Her tablo için ayrı dosya - production'da mutlaka açık olmalı
innodb_file_per_table = ONinnodb_file_per_table özellikle önemlidir. Kapalıyken tüm tablolar tek bir büyük ibdata dosyasına yazılır ve bu dosya asla küçülmez. Büyük tabloları DROP etseniz bile disk alanı geri kazanılmaz. Her tablo kendi .ibd dosyasına yazdığında, tabloyu sildikten sonra OPTIMIZE TABLE ile disk alanını geri kazanabilirsiniz.
Geçici Tablo ve Sort Ayarları
Karmaşık sorgular, GROUP BY, ORDER BY ve subquery’ler geçici tablolar oluşturur. Bu geçici tablolar önce bellekte tutulur, bellek dolunca diske taşınır. Disk’e giden geçici tablolar performansı önemli ölçüde düşürür.
[mysqld]
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4MGeçici tabloların ne kadarının diske gittiğini kontrol etmek için:
mysql -u root -p -e "SHOW GLOBAL STATUS LIKE 'Created_tmp%';"Created_tmp_disk_tables / Created_tmp_tables oranı %10’un altındaysa iyidir. Üzerindeyse tmp_table_size değerini artırmayı düşünün.
Slow Query Log ile Darboğaz Tespiti
Optimizasyonun en değerli araçlarından biri slow query log’dur. my.cnf ayarlarını yapmadan önce hangi sorguların yavaş çalıştığını bilmek gerekir.
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000Log dosyasını analiz etmek için mysqldumpslow aracını kullanın:
# En yavaş 10 sorguyu listele
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
# En çok tekrar eden yavaş sorguları listele
mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.logDaha gelişmiş analiz için pt-query-digest kullanabilirsiniz:
# Percona Toolkit kurulumundan sonra
pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_report.txtBinary Log Ayarları (Replikasyon Ortamları)
Master-slave replikasyon çalıştırıyorsanız binary log ayarları da performansı etkiler.
[mysqld]
# Binary log açık değilse replication çalışmaz
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
sync_binlog = 1
# GTID tabanlı replikasyon için (MariaDB 10.x / MySQL 5.7+)
gtid_mode = ON
enforce_gtid_consistency = ONsync_binlog = 1 her commit’te binary log’u diske yazar. En güvenli seçenek ama performans maliyeti vardır. Replika gecikmesi tolere edilebiliyorsa sync_binlog = 0 deneyin.
Gerçek Dünya: Örnek Konfigürasyon
Şimdi tüm bu ayarları bir araya getirelim. Bu yapılandırma, 32GB RAM’e sahip, sadece MySQL/MariaDB çalıştıran, yüksek trafikli bir web uygulaması için hazırlanmıştır:
[mysqld]
# --- Temel Ayarlar ---
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
bind-address = 127.0.0.1
# --- InnoDB Buffer Pool ---
innodb_buffer_pool_size = 22G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
# --- InnoDB Log ve Flush ---
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_stats_on_metadata = OFF
# --- InnoDB I/O ---
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_lru_scan_depth = 256
# --- Bağlantı Yönetimi ---
max_connections = 400
max_connect_errors = 1000000
wait_timeout = 300
interactive_timeout = 300
thread_cache_size = 64
table_open_cache = 4000
table_definition_cache = 2000
# --- Bellek Tamponları ---
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
tmp_table_size = 256M
max_heap_table_size = 256M
# --- Karakter Seti ---
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
# --- Loglama ---
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON
min_examined_row_limit = 1000
general_log = OFF
# --- Binary Log ---
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 512M
sync_binlog = 1
[client]
default-character-set = utf8mb4
socket = /var/run/mysqld/mysqld.sock
[mysql]
default-character-set = utf8mb4Değişiklikleri Uygulama ve Doğrulama
Konfigürasyon dosyasını değiştirdikten sonra servisi yeniden başlatın. Production ortamında bunu mesai saatleri dışında yapın.
# Konfigürasyon sözdizimini kontrol et
mysqld --verbose --help > /dev/null
# Servisi yeniden başlat
systemctl restart mysql # ya da mariadb
# Değişkenlerin uygulandığını doğrula
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
mysql -u root -p -e "SHOW VARIABLES LIKE 'max_connections';"Bazı değişkenler MySQL yeniden başlatılmadan da uygulanabilir. Dinamik değişkenleri anında aktif etmek için:
mysql -u root -p -e "SET GLOBAL innodb_io_capacity = 2000;"
mysql -u root -p -e "SET GLOBAL slow_query_log = ON;"Ancak SET GLOBAL ile yapılan değişiklikler my.cnf‘ye yansımaz. Sunucu yeniden başladığında eski değerlere döner. Bu yüzden her iki yere de yazın.
Performansı İzleme ve Ölçme
Optimizasyon sonrası iyileşmeyi ölçmek için şu komutları kullanabilirsiniz:
# Genel durum bilgisi
mysql -u root -p -e "SHOW GLOBAL STATUS;" > /tmp/mysql_status_after.txt
# Buffer pool hit rate hesapla
mysql -u root -p << 'EOF'
SELECT
(1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
AS buffer_pool_hit_rate
FROM (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_reads
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) r,
(
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) rr;
EOFUzun vadeli izleme için Percona Monitoring and Management (PMM) ya da Prometheus + MySQL Exporter kombinasyonunu öneririm. Grafana üzerindeki görselleştirme, hangi parametrenin ne zaman darboğaza yol açtığını çok daha net gösterir.
Sık Yapılan Hatalar
- innodb_buffer_pool_size’ı çok büyük ayarlamak: Sunucu RAM’ini aşan ya da işletim sistemine neredeyse hiç boş yer bırakmayan bir buffer pool değeri, swapping’e ve dolayısıyla tam tersi bir performans sorununa neden olur.
- max_connections’ı gereksiz yüksek tutmak: Her bağlantı bellek tüketir. 10.000 max connection ayarlayıp sadece 50 kullanıyorsanız, connection pooling düzeltmeniz gereken asıl problemdir.
- Tüm değerleri aynı anda değiştirmek: Değişiklikleri birer birer yapın, her birinin etkisini ölçün. Bir seferde her şeyi değiştirirseniz neyin işe yarayıp neyin yaramadığını anlayamazsınız.
- sort_buffer_size ve join_buffer_size’ı çok büyük yapmak: Bu değerler her thread için ayrı ayrı ayrılır. 500 bağlantıda 64MB sort_buffer_size demek 32GB bellek demektir.
- Değişiklikleri sadece SET GLOBAL ile yapıp my.cnf’ye yazmamak: Sunucu yeniden başladığında her şey sıfırlanır.
Sonuç
my.cnf optimizasyonu tek seferlik bir iş değildir. Uygulamanızın iş yükü değiştikçe, veri büyüdükçe ve kullanıcı sayısı arttıkça bu ayarların gözden geçirilmesi gerekir. Temel yaklaşım şu olmalıdır: önce ölç, sonra değiştir, tekrar ölç.
Hangi parametreden başlayacağınızı bilmiyorsanız sıralamayı şöyle yapın: önce innodb_buffer_pool_size, ardından innodb_log_file_size ve innodb_flush_log_at_trx_commit, sonra max_connections ve timeout değerleri. Bu üç grup bile büyük çoğunlukla %30-50 civarında performans iyileştirmesi sağlar.
Değişikliklerinizi bir versiyon kontrol sisteminde (git) takip edin. Bir yıl sonra neden o değeri o şekilde ayarladığınızı hatırlayamazsınız ama commit mesajları size anlatır.