MySQL ve MariaDB Optimizasyonu: Temel Ayarlar Rehberi

MySQL ve MariaDB optimizasyonunda en etkili ayar, tabloların ve indekslerin bellekte tutulduğu innodb_buffer_pool_size'dır. Yalnızca veritabanı çalıştıran bir sunucuda RAM'in yaklaşık %50–75'i, web sunucusuyla aynı makinede ise daha düşük bir pay ayrılır. Ayarlardan önce yavaş sorgu günlüğü açılmalıdır; çoğu yavaşlık eksik indeks gibi sorgu kaynaklı sorunlardan gelir ve EXPLAIN ile bulunur.

Bu yazıda önce ölçüm araçları, ardından en etkili ayarlar, MySQL 8.0/8.4 ile MariaDB arasındaki sürüm farkları ve RAM'e göre başlangıç değerleri yer alıyor. Veritabanını PHP ve önbellek katmanıyla birlikte ele almak isterseniz sunucu optimizasyonu hizmeti için teklif alabilirsiniz.

Veritabanı neden en sık görülen darboğazdır?

Çünkü dinamik bir sayfa, üretilirken çok sayıda sorgu çalıştırır ve bu sorguların hızı verinin bellekte olup olmamasına, indekslere ve kilitlere bağlıdır. Tek bir yavaş sorgu, aynı anda onlarca ziyaretçi tarafından tetiklendiğinde tüm siteyi yavaşlatır.

Etki zincirleme ilerler: PHP süreçleri sorgu yanıtını beklerken meşgul kalır, PHP-FPM havuzu dolar, yeni istekler kuyruğa girer. Dışarıdan PHP sorunu gibi görünen durumun kaynağı çoğu zaman veritabanıdır. Hangi katmanın darboğaz olduğundan emin değilseniz önce darboğaz tespiti akışını izleyin.

Ayar değiştirmeden önce sürümünüzü öğrenin; aşağıdaki birçok ayar sürüme göre farklıdır:

mysql -V
sudo mysql -e "SELECT VERSION();"

Önce ölçüm: yavaş sorgu günlüğü

Yavaş sorgu günlüğü, hangi sorguların zaman harcadığını gösteren ilk ve en güvenilir veridir. Bellek ayarlarına bu veriden sonra geçin.

slow_query_log ve long_query_time

Günlük varsayılan olarak kapalıdır; eşik değeri long_query_time ise 10 saniyedir ve web uygulamaları için yüksektir. Kalıcı ayar için ayrı bir yapılandırma dosyası oluşturun: Ubuntu/Debian'da /etc/mysql/conf.d/, AlmaLinux/Rocky'de /etc/my.cnf.d/ altında, örneğin 90-ayar.cnf:

[mysqld]
slow_query_log = 1
long_query_time = 1

Servisi yeniden başlatmak yerine önce çalışma anında açabilirsiniz; SET GLOBAL ile değiştirilen eşik yeni bağlantılara uygulanır:

sudo mysql -e "SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 1;"
sudo mysql -e "SHOW VARIABLES LIKE 'slow_query_log_file';"

log_queries_not_using_indexes seçeneği indeks kullanmayan tüm sorguları yazar; küçük tablolarda da tetiklendiği için günlüğü hızla şişirebilir, kısa süreli teşhis için açın.

pt-query-digest ile özetleme

Birkaç saatlik yoğun trafik biriktikten sonra günlüğü Percona Toolkit'teki pt-query-digest ile özetleyin. Araç Ubuntu/Debian'da sudo apt install percona-toolkit ile, AlmaLinux/Rocky'de Percona'nın resmi deposundan kurulur.

sudo pt-query-digest "$(sudo mysql -Nse 'SELECT @@slow_query_log_file')" > ~/sorgu-raporu.txt

Rapor, sorguları parmak izine (değerleri çıkarılmış kalıp) göre gruplar ve toplam süreye göre sıralar. En üstteki birkaç kalıp, genellikle toplam veritabanı süresinin büyük kısmını oluşturur; optimizasyon oradan başlar. Araç kurulu değilse MySQL ile gelen mysqldumpslow daha basit bir özet verir.

innodb_buffer_pool_size

InnoDB buffer pool, tablo ve indeks sayfalarının bellekte tutulduğu alandır. Aktif veri buraya sığarsa sorgular diske gitmeden yanıtlanır. Varsayılan değer 128 MB'tır ve üretim sunucuları için neredeyse her zaman küçüktür.

MySQL belgelerine göre yalnızca veritabanına ayrılmış sunucularda fiziksel belleğin yüzde 80'ine kadarı sıklıkla buffer pool'a verilir. MySQL'in innodb_dedicated_server seçeneği de benzer bir mantıkla, 1–4 GB bellekte yarısını, 4 GB'ın üzerinde yüzde 75'ini ayırır; belgeler bu seçeneği yalnızca MySQL'in tek başına çalıştığı sunucular için önerir. MariaDB belgeleri ise çok büyük bir değerin swap'a yol açacağı ve buffer pool'un faydasını fazlasıyla sileceği konusunda uyarır.

Değerin yeterli olup olmadığını diskten okuma oranıyla ölçün:

sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';"

Innodb_buffer_pool_reads buffer pool'da bulunamayıp diskten okunan sayfa sayısı, Innodb_buffer_pool_read_requests toplam okuma isteğidir. Birincinin ikinciye oranı küçüldükçe veri bellekten geliyordur. Sayaçlar açılıştan itibaren birikir; karşılaştırmayı sunucu en az bir iş günü çalıştıktan sonra yapın. Güncel sürümlerde değer SET GLOBAL innodb_buffer_pool_size = ... ile yeniden başlatmadan değiştirilebilir; kalıcı olması için yapılandırma dosyasına da yazın.

Yalnızca DB çalışan sunucu ile web + DB aynı sunucu

Veritabanı kendi sunucusundaysa belleğin büyük kısmı buffer pool'a verilebilir. Web sunucusu ve PHP-FPM aynı makinedeyse RAM paylaşılır: PHP süreçlerinin toplam belleği, işletim sistemi ve bağlantı başına veritabanı belleği düşüldükten sonra kalan pay buffer pool'a ayrılır. PHP tarafının hesabı PHP-FPM pm.max_children hesaplama yazısında anlatıldı. Paketlere göre değerler aşağıdaki tabloda yer alıyor.

Diğer önemli InnoDB ayarları

Buffer pool'dan sonra en çok etkisi olan ayarlar redo log kapasitesi ve yazmaların diske ne zaman kalıcı olarak işlendiğidir. Bu ayarların adları ve varsayılanları sürüme göre değişir.

Redo log kapasitesi (innodb_redo_log_capacity / innodb_log_file_size)

Redo log, değişikliklerin veri dosyalarına yazılmadan önce kaydedildiği günlüktür. Kapasite küçükse InnoDB yoğun yazma anlarında kirli sayfaları diske aceleyle boşaltmak zorunda kalır ve yazma performansı dalgalanır.

MySQL 8.0.30 ve sonrası (8.4 dahil): innodb_redo_log_capacity kullanılır; varsayılanı 100 MB'tır ve SET GLOBAL ile çalışma anında değiştirilebilir. Eski innodb_log_file_size ve innodb_log_files_in_group kullanımdan kaldırılma sürecindedir.
MySQL 8.0.30 öncesi: kapasite innodb_log_file_size × innodb_log_files_in_group ile belirlenir ve değişiklik yeniden başlatma gerektirir.
MariaDB: innodb_redo_log_capacity yoktur; kapasite innodb_log_file_size ile ayarlanır. 10.5'ten beri redo log tek dosyadır (ib_logfile0), innodb_log_files_in_group 10.6'da kaldırılmıştır ve 10.9'dan itibaren boyut yeniden başlatmadan değiştirilebilir.

Yaygın bir yaklaşım, kapasiteyi yoğun bir saatteki redo yazımını karşılayacak kadar seçmektir. Yoğun saatte bir dakikalık yazımı ölçüp 60 ile çarpın (iki "Log sequence number" değeri arasındaki fark bayt cinsindendir):

sudo mysql -e "SHOW ENGINE INNODB STATUS\G" | grep 'Log sequence number'
sleep 60
sudo mysql -e "SHOW ENGINE INNODB STATUS\G" | grep 'Log sequence number'

innodb_flush_log_at_trx_commit

Varsayılan değer 1'dir: her işlem (transaction) onaylandığında redo log diske yazılır ve kalıcı hale getirilir. 2 değerinde günlük her onayda işletim sistemine yazılır ama diske saniyede bir kez işlenir; 0 değerinde hem yazma hem diske işleme saniyede bir yapılır. 2 ve 0 yazma hızını artırır, karşılığında elektrik kesintisi veya işletim sistemi çökmesinde yaklaşık son bir saniyelik işlemler kaybolabilir (0'da yalnızca MySQL sürecinin çökmesi de yeterlidir). Sipariş ve ödeme verisi tutan sistemlerde 1'de bırakın.

innodb_flush_method

Veri dosyalarına nasıl yazılacağını belirler. O_DIRECT, veriyi hem buffer pool'da hem işletim sistemi önbelleğinde tutmayı (çift önbelleği) önler. Linux'ta varsayılan MySQL 8.0'da fsync, MySQL 8.4'te destekleniyorsa O_DIRECT'tir. MariaDB'de varsayılan 10.6 ile O_DIRECT olmuştur; 11.0'dan itibaren bu ayar kullanımdan kaldırılma sürecindedir ve yerini çalışma anında değiştirilebilen innodb_log_file_buffering, innodb_data_file_buffering gibi ayarlara bırakır. Güncel sürümlerde bu ayara dokunmanız genellikle gerekmez.

Bağlantı ayarları: max_connections

max_connections, aynı anda açık olabilecek istemci bağlantısı sayısıdır; MySQL ve MariaDB'de varsayılan 151'dir. Sınır aşılınca uygulama Too many connections hatası alır.

Değeri körlemesine yükseltmek risklidir: sıralama ve birleştirme tamponları (sort_buffer_size, join_buffer_size gibi) bağlantı başına ayrılır ve en kötü durumda bellek ihtiyacı bağlantı sayısıyla çarpılarak büyür. Doğru değeri ölçerek bulun:

sudo mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'; SHOW GLOBAL STATUS LIKE 'Threads_connected';"

PHP uygulamalarında her PHP-FPM süreci genellikle en fazla bir veritabanı bağlantısı açar. Bu yüzden tüm havuzların pm.max_children toplamı, cron ve yönetim bağlantıları için küçük bir pay eklenmiş haliyle, max_connections için iyi bir başlangıçtır.

Sorgu tarafı: EXPLAIN ve indeksler

Yavaş sorgu günlüğünün öne çıkardığı sorguyu EXPLAIN ile inceleyin; sorgunun hangi indeksi kullandığını ve kaç satır taramayı planladığını gösterir.

EXPLAIN SELECT * FROM siparisler WHERE musteri_id = 42 ORDER BY tarih DESC LIMIT 10;

Çıktıda type sütunu ALL ise tablo baştan sona taranıyordur; key sütunu boşsa hiçbir indeks kullanılmıyordur; rows tahmini taranacak satır sayısıdır. Bu örnekte hem filtreyi hem sıralamayı karşılayan birleşik bir indeks sorunu çözer:

ALTER TABLE siparisler ADD INDEX idx_musteri_tarih (musteri_id, tarih);

İkincil indeks ekleme InnoDB'de çoğu durumda tabloyu yazmaya kilitlemeden yapılır, ancak büyük tablolarda uzun sürer ve yoğun disk G/Ç'si üretir. İşlemi sakin saatlerde ve yedek aldıktan sonra yapın. Gerçek çalışma sürelerini görmek için MySQL 8.0.18 ve sonrasında EXPLAIN ANALYZE, MariaDB'de ANALYZE deyimi kullanılır. İkisi de sorguyu gerçekten çalıştırır; veri değiştiren sorgularda kullanmayın.

Araçlar: MySQLTuner

MySQLTuner, çalışan sunucunun durum sayaçlarını okuyup yapılandırma önerileri üreten bir Perl betiğidir. Salt okuma yapar, ayarları kendisi değiştirmez:

wget https://mysqltuner.pl/ -O mysqltuner.pl
sudo perl mysqltuner.pl

Projenin kendi uyarıları dikkate alınmalıdır: sonuçların anlamlı olması için sunucunun en az 24 saattir çalışıyor olması gerekir ve öneriler körlemesine uygulanmamalıdır; bir alandaki iyileştirme başka bir alanı olumsuz etkileyebilir. Önerileri yavaş sorgu analiziyle birlikte değerlendirin ve her birini tek tek uygulayıp ölçün.

RAM'e göre başlangıç değerleri tablosu

Aşağıdaki değerler genel başlangıç değerleridir; kendi ölçümünüzle ayarlayın. Bulutzone'da yapılmış bir test sonucu değildir; RAM değerleri Bulutzone TR Cloud VDS paketlerinden alınmıştır. Web + veritabanı sütunu, PHP-FPM'in de aynı sunucuda çalıştığı tipik bir PHP sitesini varsayar.

Sunucu RAM'i	Rol: yalnızca veritabanı	Rol: web + veritabanı aynı sunucuda
4 GB (TR Cloud v2)	2–2,5 GB	512–768 MB
8 GB (TR Cloud v4)	5–6 GB	1,5–2 GB
16 GB (TR Cloud v5)	11–12 GB	5–6 GB

Veritabanınızın aktif boyutu bu değerlerden küçükse buffer pool'u daha da büyütmek fayda sağlamaz. Tersine, aktif veri sürekli büyüyor ve diskten okuma oranı düşmüyorsa daha fazla RAM gerekir; kademeler için VDS RAM seçimi rehberine, disk tarafının etkisi için NVMe diskin sunucu performansına etkisi yazısına bakabilirsiniz.

Sık sorulan sorular

innodb_buffer_pool_size ne kadar olmalı?

Yalnızca veritabanı çalışan bir sunucuda RAM'in yaklaşık yüzde 50–75'i iyi bir başlangıçtır; web sunucusu ve PHP aynı makinedeyse pay belirgin biçimde düşer. Doğru değeri, diskten okuma oranı ve swap kullanımı birlikte gösterir.

MySQL neden yavaş?

En sık nedenler eksik indeksler, aktif veriye göre küçük kalan buffer pool, kilit bekleyen sorgular ve swap'a düşen bellektir. Yavaş sorgu günlüğü ve SHOW FULL PROCESSLIST hangisinin geçerli olduğunu gösterir.

Query cache açılmalı mı?

MySQL'de bu seçenek yoktur: query cache 5.7.20'de kullanımdan kaldırılmış, MySQL 8.0'da tamamen çıkarılmıştır ve query_cache_size satırı MySQL 8'in açılmasını engeller. MariaDB'de hâlâ bulunur ama varsayılan olarak kapalıdır; çok çekirdekli sunucularda tek bir iç kilit üzerinden çalıştığı için ölçeklenmez. Sonuç önbelleği gerekiyorsa uygulama katmanında Redis gibi bir çözüm daha uygundur.

MySQLTuner güvenli mi?

Betik yalnızca okuma yapar ve ayar değiştirmez; bu açıdan güvenlidir. Risk, önerilerin test edilmeden topluca uygulanmasındadır. Her öneriyi tek tek uygulayın ve etkisini ölçün.

Önerilen çalışma sırası

Veritabanı optimizasyonunda sıra, sonucun kendisi kadar önemlidir:

Sürümü öğrenin ve yapılandırma ile veritabanının yedeğini alın.
Yavaş sorgu günlüğünü açın, en az bir iş günü veri toplayın.
pt-query-digest raporundaki ilk birkaç sorguyu EXPLAIN ile inceleyip eksik indeksleri ekleyin.
Buffer pool'u role ve RAM'e göre ayarlayın, diskten okuma oranını izleyin.
Yazma yoğunsa redo log kapasitesini ölçüme göre büyütün.
Her adımdan sonra aynı saatte aynı ölçümleri tekrarlayın.

Diğer katmanlarla birlikte ele almak için Linux sunucu optimizasyonu rehberine bakabilirsiniz. Veritabanı ve sunucu ayarlarını bir ekibe bırakmak isterseniz tek seferlik sunucu optimizasyonu hizmeti için teklif alabilirsiniz. Bulutzone'un sunucu yönetimi ekibinin yaptığı işler arasında SQL sunucu kurulumu ve yapılandırması da yer alır; SLA-3 Gelişmiş paketinde web, SQL ve mail servisleri ayrıca izlenir.

Kaynaklar

Sunucu Optimizasyonu konusundaki hizmetlerimiz için sunucu optimizasyonu hizmeti sayfasına göz atabilirsiniz.

İncele

MySQL ve MariaDB Optimizasyonu: innodb_buffer_pool_size ve Yavaş Sorgu Analizi