MariaDB Performans Ayarları: Gerçek Dünya Notları

Veritabanı ayarı denince akla gelen ilk refleks konfigürasyon dosyasını açıp rakamları değiştirmek oluyor. Oysa sunucuda yavaşlık hissedildiğinde bir sonraki adım neredeyse hiçbir zaman yapılandırma dosyası olmamalı. Önce yavaş sorguları görmek, sonra o sorguların neden yavaş olduğunu anlamak gerekiyor. Ayarların çoğu bu sırada kendiliğinden gereksiz hâle gelir.

Bu metin, bir üretim sunucusunu rahatlatmak için hangi ayarların gerçekten işe yaradığını ve hangilerinin yalnızca bir his olarak kaldığını anlatıyor. Örnekler MariaDB üzerinde, küçük bir içerik platformu düşünülerek anlatılıyor. Ayar değerleri ortamınıza göre farklılaşır; ama ölçme yöntemi her ortamda aynıdır.

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

Yavaş sorgu günlüğü, veritabanının kendi itirafıdır. Belirli bir süreyi aşan her sorgu kaydedilir. Bu iki ayar çoğu durumda yeterlidir:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;

long_query_time değerini çok düşük tutarsanız günlük gürültüye dönüşür; çok yüksek tutarsanız gerçek sorunlar gözden kaçar. Yarım saniye, etkileşimli bir web uygulaması için makul bir başlangıç noktasıdır. Günlük birkaç gün toplandıktan sonra en sık tekrarlanan ve en uzun süren sorgulara bakın.

Günlüğü okumak için bir araç da gerekmez. Ancak sık çalışan sorguların sıklığını anlamak için mysqldumpslow gibi küçük araçlar işi hızlandırır. Amaç, en çok zaman harcayan üç beş sorguyu tek tek incelemek.

Veritabanı bir kara kutu değildir; yalnızca kimsenin açıp bakmadığı bir kutudur.

Sorgu planını okumak

Yavaş bir sorgu bulduğunuzda ilk iş onun planını görmektir. EXPLAIN komutu, veritabanının sorguyu nasıl çalıştırmayı planladığını gösterir. Plan içinde en çok dikkat edilmesi gereken üç alan vardır: kullanılan indeks, taranan satır sayısı ve geçici tablo ya da dosya sort işlemleri.

EXPLAIN SELECT id, title, published_at
FROM posts
WHERE status = 'published' AND category_id = 3
ORDER BY published_at DESC
LIMIT 8;

Bu tablo bir tanı listesi gibi okunabilir. Çoğu yavaş sorgu, tek bir eksik indeksle ya da yanlış sıralanmış bir indeksle açıklanır. Ayarlar ise tek başına bu sorunu çözmez; en fazla sorgunun ne kadar hızlı bekleyebileceğini etkiler.

Bileşik indeks: sütun sırası her şeydir

Birden fazla kolonu birlikte süzen bir sorgu için bileşik indeks gerekir. Ama kolonların sırası önemlidir. Genel kural basittir: önce eşitlik filtresi gelir, sonra aralık filtresi, en sonda sıralama kolonu.

Yukarıdaki sorgu için uygun indeks şöyle olur:

CREATE INDEX posts_status_category_published_idx
    ON posts (status, category_id, published_at);

Bu sırayla veritabanı önce status ve category_id üzerinden doğru satır bloğuna gider, sonra bu blok içinde published_at sıralamasını indeks sayesinde hazır bulur. Sıra ters yazıldığında indeks kullanılsa bile sıralama için ek iş yapılır.

• Filtre ve sıralama birlikte kullanılıyorsa bileşik indeks düşünün.
• Kardinalitesi çok düşük kolonları (örneğin yalnızca iki değer alan bir bayrak) tek başına indekslemekten kaçının.
• Bir indeks, önekiyle eşleşen sorgularda kullanılır; sonradan eklenen kolon yalnızca önceki kolonlar da filtrelenirse işe yarar.

Bellek: arabellek havuzu

innodb_buffer_pool_size, InnoDB'nin veri ve indeks sayfalarını bellekte tuttuğu alandır. Bu alan yeterince büyükse, sorgular diske çok daha az gider. Yani disk erişimi çoğu zaman en pahalı işlemdir ve bu ayar onu doğrudan etkiler.

Değer hakkında genel bir öneri vermek zordur. Sunucu yalnızca veritabanına ayrılmışsa bu değer belleğin büyük bölümüne, çoğu kurulumda yüzde yetmiş civarına kadar çıkarılabilir. Aynı makinede web sunucusu, kuyruk ve başka süreçler çalışıyorsa daha temkinli davranmak gerekir; aksi takdirde sistem bellek baskısı altında yavaşlar ve bu yavaşlık sorgu günlüğünde değil, tüm uygulamada görünür.

Arabellek havuzunun yeterliliğini ölçmek için küresel durum değişkenlerine bakmak yeterlidir. Diske giden okuma sayısı, toplam okuma isteğine oranla yüksekse havuz büyütülmeye aday olabilir.

Bağlantılar ve eşzamanlılık

Bağlantı sayısı bir yarış değil, bir denge meselesidir. Her bağlantının kendi bellek ayırması vardır ve çok sayıda bağlantı, aynı anda çalışan sorguların birbirini beklemesine yol açar. max_connections değerini yüksek tutmak, sorunu çözmez; yalnızca sunucuyu daha geç bir yerde zorlar.

Uygulama tarafında bağlantı havuzu kullanılıyorsa, havuzun büyüklüğü PHP-FPM süreç sayısı ve eşzamanlı istek hedefiyle uyumlu olmalıdır. Örneğin on iki işçi sürecine sahip bir uygulama, her biri bir bağlantı tutuyorsa, veritabanına yaklaşık on iki eşzamanlı bağlantı yükler. Bu rakamı bilmeden ayar yapmak, tahmin üzerine tahmin olur.

Dayanıklılık ile hız arasındaki takas

innodb_flush_log_at_trx_commit ayarı, işlem günlüğünün her işlemde diske ne sıklıkla yazılacağını belirler. Varsayılan değer, veri güvenliğini en üst düzeye çıkarır. Değer 2 yapıldığında yazma işlemleri daha hızlanır, ama işletim sistemi çöktüğünde son birkaç saniyelik işlem kaybedilebilir.

Bu bir teknik karar değil, bir iş kararıdır. Kullanıcı yorumları ya da görüntüleme sayacı gibi kayıp kabul edilebilir veriler için bu takas makul olabilir. Ödeme ya da sipariş tablosu için ise hiçbir zaman.

Ayarlardan önce sorguyu düzeltin

Bir ekip yavaşlığı fark ettiğinde ilk refleks genellikle sunucu ayarlarıdır. Ama üretimde en çok kazandıran değişiklik çoğu zaman uygulama kodunda çıkar. ORM'in ürettiği sorguları düzenli olarak okumak, bu alandaki en basit alışkanlıktır.

Örneğin bir yazı listesi her yazının yazarını ayrı bir sorguyla çekiyorsa, bu N+1 sorunudur. Yazarları önceden yüklemek tek bir ek sorguya düşürür. Benzer şekilde, yalnızca sayısı gösterilecek bir ilişki için tüm kayıtları çekmek gereksiz yük yaratır.

• Liste sayfalarında ilişkileri önceden yükleyin, döngü içinde sorgu atmayın.
• Yalnızca sayıya ihtiyaç varsa count ya da sayaç kolonu kullanın.
• Sayfalama için büyük OFFSET değerleri yerine, son görülen kaydın kimliğine göre ilerleyen bir yöntem tercih edin.

Değişikliği uygulamak ve doğrulamak

Bir ayar ya da indeks değiştirdiğinizde ölçüm öncesi ve sonrası aynı koşullarda alınmalıdır. Aynı sorguyu, aynı veri hacmiyle ve mümkünse aynı saatte çalıştırın. Ortalama süre tek başına yeterli değildir; en yavaş yüzde birlik kesimi de izleyin, çünkü kullanıcıların şikâyet ettiği genellikle o kesimdir.

1. Değişikliği önce bir kopya veritabanında deneyin.
2. Planı yeniden çıkarın ve rows değerinin düştüğünü doğrulayın.
3. Üretimde küçük bir pencerede uygulayın ve yavaş sorgu günlüğünü birkaç gün izleyin.
4. Değişikliği, hangi sorguyu hangi ölçümle iyileştirdiğinizle birlikte kaydedin.

İyi bir ayar, sorunu gizlemez; sorunu ortadan kaldırır ve nedenini bir sonraki kişiye bırakır.

Uzun işlemler ve kilit beklemeleri

Bir yavaşlığın her zaman sorgudan gelmediğini öğrenmek, bu işin en çok zaman kazandıran derslerinden biridir. Bazen sorgu hızlıdır ama bekler. Bunun nedeni, başka bir işlemin tuttuğu bir satır ya da tablo kilididir. Bu durum, yavaş sorgu günlüğünde uzun süre görünür, ama sorgunun planında hiçbir sorun yoktur.

En sık neden, bir işlemin içinde dış bir servise istek atılmasıdır. Ödeme ya da e-posta gönderimi bir işlem açıkken yapılırsa, o işlem saniyelerce açık kalır ve kilitleri tutar. Çözüm, dış işlemleri işlemin dışına almak ve işlemi yalnızca veritabanı yazmalarını kapsayacak kadar kısa tutmaktır.

-- Hangi işlemler neyi bekliyor?
SELECT * FROM information_schema.INNODB_TRX\G

-- Son kilit ve kilitlenme bilgisi
SHOW ENGINE INNODB STATUS\G

InnoDB'nin kilit bekleme süresi bir ayardır ve varsayılan olarak uzun tutulur. Bu süreyi düşürmek, sorunu gizlemek yerine hızlı görünür kılar. Bir istek on saniye beklemek yerine bir hata alırsa, uygulama bunu ele alabilir ve kullanıcıya anlamlı bir mesaj verebilir.

Geçici tablolar ve bellek sınırları

Plandaki Using temporary ve Using filesort işaretleri, veritabanının sıralama ya da gruplama için geçici bir alana ihtiyaç duyduğunu gösterir. Küçük sonuçlar bellekte işlenir; ama sonuç büyüdükçe veritabanı bu işi diske taşır ve süre belirgin biçimde uzar.

Bellek sınırlarını tmp_table_size ve max_heap_table_size değişkenleri belirler. Bu değerleri artırmak bir miktar yardımcı olabilir, ama asıl çözüm çoğu zaman sorguyu değiştirmektir. Sıralama kolonunun indekste olması, geçici alana ihtiyacı tamamen ortadan kaldırır.

• Büyük bir GROUP BY, sonucu daraltan bir koşul ya da uygun bir indeks ile hafifletilebilir.
• Sonuç kümesinde gereksiz kolonlar varsa, yalnızca gerekenleri seçin.
• Bellek ayarlarını değiştirdiğinizde, sunucunun toplam bellek kullanımını da kontrol edin.

Tip uyumsuzluğu: sessiz tarama

Bir kolon ile karşılaştırılan değerin tipi farklı olduğunda, veritabanı karşılaştırmadan önce değeri dönüştürmek zorunda kalabilir. Bu dönüşüm, kolonun indeksini kullanılamaz hâle getirir ve sorgu sessizce tam taramaya dönüşür. Hata mesajı çıkmaz, sonuç doğru görünür, ama süre yavaşlar.

-- Yavaş: slug metin kolonu, değer sayı olarak verilmiş
SELECT id FROM posts WHERE slug = 412;

-- Hızlı: değer kolonun tipiyle aynı
SELECT id FROM posts WHERE slug = '412';

Bu hata, uygulama tarafında bir sayı ile bir metnin karıştırılmasından kaynaklanır. Route parametreleri, form girdileri ve JSON alanları bu tür karışıklıkların en sık görüldüğü yerlerdir. Modelde tipleri tanımlamak (casts) ve doğrulamayı tipe göre yapmak bu hatayı önler.

Performans şemasıyla sorgu ailelerini izlemek

Yavaş sorgu günlüğü tek tek olayları gösterir. Ama asıl soru çoğu zaman şudur: hangi sorgu ailesi en çok zamanı harcıyor? Bu soruya cevap vermek için performans şeması kullanılabilir. Özet tabloları, aynı yapıdaki sorguları gruplayarak toplam süreyi gösterir.

SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT / 1000000000000 AS toplam_saniye
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

Bu liste, düzeltilecek sorunların önceliğini belirler. En üstteki sorgu, tek başına bir saniyelik gecikmeden çok, saatte binlerce kez çalıştığı için en büyük etkiye sahip olabilir. Bu bakış, hangi indeksin önce kurulacağına karar vermenize yardımcı olur.

Canlı veritabanında şema değişikliği

Bir tabloya indeks eklemek ya da bir kolonun tipini değiştirmek, büyük tablolarda uzun süren bir işlemdir. Bu işlem sırasında tablo kilitlenirse, uygulama bu süre boyunca yazamaz. Kullanıcılar için bu, sitenin kısa süreli donması demektir.

MariaDB, birçok şema değişikliğini tabloyu kilitlemeden yapabilir. ALTER TABLE komutunda algoritma ve kilit davranışını açıkça belirtmek, beklenmedik bir kilitlenmeyi önler:

ALTER TABLE posts
    ADD INDEX posts_status_category_published_idx (status, category_id, published_at),
    ALGORITHM=INPLACE, LOCK=NONE;

Komutun bu biçimde başarısız olması, veritabanının bu değişikliği kilitsiz yapamayacağını söyler. Bu bir hata değil, bir bilgidir: işlem için bakım penceresi gerektiği anlamına gelir. Değişikliği önce bir kopya üzerinde denemek ve yedek alındığını doğrulamak, her zaman ilk adım olmalıdır.

Yaygın hatalar

İlk hata, farklı kolonlar üzerinde OR kullanmaktır. İki farklı kolonda arama yapan bir OR koşulu, çoğu zaman tek bir indeksi kullanamaz. Bu durumda her koşul için ayrı bir sorgu yazıp birleştirmek ya da bir arama altyapısına geçmek daha iyi sonuç verir.

İkinci hata, başında joker karakter olan LIKE kullanmaktır. LIKE '%laravel' indeksin sıralamasından yararlanamaz. Önek araması (LIKE 'laravel%') ise indeksle çalışır; bu fark, arama kutusunun tasarımını etkileyebilir.

Üçüncü hata, her zaman SELECT * kullanmaktır. Geniş bir tabloda uzun metin alanları olduğunda, liste sayfası yalnızca başlığa ihtiyaç duyarken bu alanları da okur. Yalnızca gereken kolonları seçmek, hem disk hem de ağ yükünü azaltır.

Sonuç

MariaDB'yi rahatlatmak büyük bir sihir değil, sıkıcı ama tekrarlanabilir bir işler dizisidir: ölç, planı oku, indeksi doğru kur, ayarı kaynağa göre değiştir ve sonucu doğrula. Çoğu zaman en büyük kazanç bir indeksten ya da bir sorgunun yeniden yazılmasından gelir. Bellek ve bağlantı ayarları ise bu temelin üstüne oturur.

Başlamak için kısa bir kontrol listesi:

• Yavaş sorgu günlüğünü açın ve bir hafta boyunca tutun.
• En sık ve en uzun süren üç sorgunun planını çıkarın.
• Bileşik indeksleri eşitlik, aralık ve sıralama sırasıyla gözden geçirin.
• Arabellek havuzunu sunucunun gerçek kullanımına göre ayarlayın.
• Dayanıklılık ayarlarını verinin kaybedilebilirliğine göre seçin.