MySQL Yavaş Sorgu Tespiti: slow_query_log Kullanımı

Bykomut

Üretim ortamında çalışan bir MySQL sunucusu var, uygulama yavaşlıyor, kullanıcılar şikayet ediyor ama tam olarak neyin problem olduğunu bilmiyorsunuz. İşte bu noktada çoğu sysadmin “show processlist” açıp birkaç sorgu izler, bir şey bulamazsa kafayı karıştırır. Oysa MySQL’in kendi bünyesinde çok güçlü bir araç var: slow query log. Bu yazıda bu aracı her detayıyla ele alacağız, gerçek dünya senaryolarıyla nasıl kullanacağınızı göstereceğiz.

Slow Query Log Nedir ve Neden Önemlidir?

MySQL’in slow query log özelliği, belirli bir eşik süresinin üzerinde çalışan sorguları otomatik olarak bir log dosyasına yazar. Bu eşiği siz belirlersiniz. 2 saniyenin üzerinde mi? 500 milisaniyenin üzerinde mi? Hatta 0 saniye yapıp her sorguyu mu loglamak istiyorsunuz? Hepsi mümkün.

Peki neden bu kadar önemli? Çünkü “yavaş sorgu” problemi çoğu zaman anlık olarak yakalanması çok zor bir şeydir. Bir sorgu sabah 03:00’te bir toplu iş sırasında 45 saniye sürüyor olabilir, siz gündüz baktığınızda hiçbir iz kalmaz. Slow query log bu geçmişi sizin için tutar. Ayrıca hangi sorgular en çok kaynak tüketiyor, hangi tablolar düzgün index’lenmemiş, hangi uygulama kodu verimsiz SQL üretiyor gibi sorulara da net cevaplar verir.

Slow Query Log Nasıl Aktif Edilir?

İki yöntem var: kalıcı aktifleştirme (my.cnf/my.ini üzerinden) ve anlık aktifleştirme (runtime’da değişken set ederek). İkisini de bilmek gerekir çünkü farklı senaryolarda farklısı işe yarar.

my.cnf Üzerinden Kalıcı Aktifleştirme

# /etc/mysql/mysql.conf.d/mysqld.cnf veya /etc/my.cnf dosyasını açın
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Aşağıdaki satırları [mysqld] bloğu altına ekleyin:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 10
min_examined_row_limit = 100

Değişiklikleri uygulamak için MySQL’i yeniden başlatmanız gerekir:

sudo systemctl restart mysql
# veya
sudo systemctl restart mysqld

Runtime’da Aktifleştirme (Sunucuyu Durdurmadan)

Üretim ortamında sunucuyu durdurmak çoğu zaman mümkün olmaz. Neyse ki MySQL 5.1’den itibaren bu ayarları sunucu çalışırken de yapabiliyoruz:

mysql -u root -p
-- Slow query log'u aktif et
SET GLOBAL slow_query_log = 'ON';

-- Log dosyasının yerini belirt
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 2 saniyenin üzerindeki sorguları yakala
SET GLOBAL long_query_time = 2;

-- Index kullanmayan sorgular da loglansın
SET GLOBAL log_queries_not_using_indexes = 1;

-- Mevcut ayarları doğrula
SHOW GLOBAL VARIABLES LIKE '%slow%';
SHOW GLOBAL VARIABLES LIKE 'long_query_time';

Önemli: Runtime’da yaptığınız değişiklikler MySQL yeniden başlatıldığında sıfırlanır. Kalıcı olmasını istiyorsanız my.cnf’e de yazmanız şart.

Temel Parametrelerin Açıklaması

Hangi parametrenin ne işe yaradığını bilmeden ayar yapmak kör uçuşa benzer. İşte önemli parametreler:

  • slow_query_log: Log özelliğini açıp kapatır. ON veya 1 ile aktif edilir.
  • slow_query_log_file: Log dosyasının tam yolu. MySQL kullanıcısının bu dizine yazma yetkisi olmalı.
  • long_query_time: Saniye cinsinden eşik değer. Bu süreyi aşan sorgular loglanır. Ondalık değer girebilirsiniz, örneğin 0.5 yarım saniye demektir.
  • log_queries_not_using_indexes: Index kullanmayan sorgular ne kadar hızlı olursa olsun loglanır. Performans sorunlarını önceden tespit etmek için altın bir ayar.
  • log_throttle_queries_not_using_indexes: Dakika başına kaç tane index kullanmayan sorgunun loglanacağını sınırlar. Log dosyasının patlamasını önler.
  • min_examined_row_limit: En az bu kadar satırı inceleyen sorgular loglanır. Küçük tablolardaki “masum” sorgulardan gelen gürültüyü azaltır.
  • log_slow_admin_statements: ALTER TABLE, CREATE INDEX gibi admin sorgularını da loglar.

Log Dosyasını Okumak

Log dosyası ham haliyle okunabilir ama büyük sistemlerde binlerce satır olur ve elle okumak pratik değildir.

Ham Log Okuma

# Son 100 satırı göster
sudo tail -100 /var/log/mysql/slow.log

# Belirli bir sorguyu ara
sudo grep -A 10 "Query_time: [5-9]" /var/log/mysql/slow.log

# Kaç tane yavaş sorgu var
sudo grep -c "^# Query_time" /var/log/mysql/slow.log

Ham log şöyle görünür:

# Time: 2024-01-15T03:42:17.123456Z
# User@Host: app_user[app_user] @ localhost []  Id: 1847
# Query_time: 8.234156  Lock_time: 0.000234 Rows_sent: 1  Rows_examined: 2847392
SET timestamp=1705286537;
SELECT * FROM orders WHERE created_at > '2024-01-01' AND status = 'pending';

Bu çıktıda dikkat etmeniz gereken değerler:

  • Query_time: Sorgunun toplam çalışma süresi (saniye)
  • Lock_time: Kilit bekleme süresi
  • Rows_sent: Uygulamaya gönderilen satır sayısı
  • Rows_examined: MySQL’in incelediği satır sayısı (bu iki değer arasındaki büyük fark kötü index işareti)

mysqldumpslow ile Analiz

MySQL ile birlikte gelen mysqldumpslow aracı, log dosyasını gruplandırıp özetler. Yüzlerce farklı sorguda hangileri en çok problem çıkarıyor, bunu görmek için idealdir.

# En yavaş 10 sorguyu göster
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# En çok tekrar eden 10 sorguyu göster
sudo mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# En fazla satır inceleyen 10 sorguyu göster
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow.log

# Belirli bir veritabanına ait sorgular
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log | grep "mydb"

Sıralama seçenekleri:

  • -s t: Toplam sorgu süresine göre sırala
  • -s at: Ortalama sorgu süresine göre sırala
  • -s c: Çalışma sayısına göre sırala
  • -s r: İncelenen satır sayısına göre sırala
  • -t N: Gösterilecek sorgu sayısı

pt-query-digest ile Derinlemeli Analiz

Percona Toolkit’in bir parçası olan pt-query-digest, mysqldumpslow‘dan çok daha güçlüdür. Eğer ciddi bir MySQL ortamı yönetiyorsanız bu araç olmadan olmaz.

# Percona Toolkit kurulumu (Ubuntu/Debian)
sudo apt-get install percona-toolkit

# CentOS/RHEL için
sudo yum install percona-toolkit

# Temel analiz
sudo pt-query-digest /var/log/mysql/slow.log

# Sadece belirli bir zaman aralığını analiz et
sudo pt-query-digest --since="2024-01-15 00:00:00" --until="2024-01-15 06:00:00" /var/log/mysql/slow.log

# Sonuçları dosyaya kaydet
sudo pt-query-digest /var/log/mysql/slow.log > /tmp/query_analysis.txt

# Sonuçları MySQL tablosuna yaz (uzun vadeli takip için)
sudo pt-query-digest --review h=localhost,D=analysis,t=query_review 
  --history h=localhost,D=analysis,t=query_history 
  /var/log/mysql/slow.log

pt-query-digest çıktısı şu bilgileri sunar: her sorgu grubunun toplam ve ortalama çalışma süresi, kaç kez çalıştığı, en kötü ve en iyi çalışma süreleri, standart sapma, ve tam sorgu örneği.

Gerçek Dünya Senaryosu: E-ticaret Sitesi

Şimdi gerçek bir senaryoya bakalım. Büyük bir e-ticaret sitesi yönetiyorsunuz. Gündüz saatlerinde yoğunluk arttıkça sayfa yükleme süreleri 8-10 saniyeye çıkıyor. Geliştirici ekibi “uygulama tarafında bir sorun yok” diyor. Peki sorun nerede?

Önce slow query log’u aktif edelim ve long_query_time değerini düşük tutalım:

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

Birkaç saat sonra log’u analiz edelim:

sudo pt-query-digest /var/log/mysql/slow.log | head -100

Diyelim ki çıktıda şunu gördük: SELECT * FROM products p JOIN categories c ON p.cat_id = c.id WHERE c.slug = 'electronics' ORDER BY p.created_at DESC LIMIT 20 sorgusu, günde 15.000 kez çalışıyor ve her seferinde ortalama 3.2 saniye sürüyor. Toplam günlük yük: 48.000 saniye, yani 13 saat!

Bu sorguyu EXPLAIN ile inceleyelim:

mysql -u root -p mydb -e "
EXPLAIN SELECT * FROM products p 
JOIN categories c ON p.cat_id = c.id 
WHERE c.slug = 'electronics' 
ORDER BY p.created_at DESC 
LIMIT 20G"

EXPLAIN çıktısı bize muhtemelen şunu gösterecek: categories tablosunda slug kolonu için index yok, products tablosunda cat_id ve created_at için composite index yok. MySQL 2 milyonluk products tablosunu full scan yapıyor.

Çözüm:

mysql -u root -p mydb -e "
ALTER TABLE categories ADD INDEX idx_slug (slug);
ALTER TABLE products ADD INDEX idx_cat_created (cat_id, created_at);
"

Index eklendikten sonra aynı sorgu 3.2 saniyeden 0.012 saniyeye iniyor. Günlük toplam yük 48.000 saniyeden 180 saniyeye düşüyor. Sayfalar anında açılıyor.

Log Rotasyonu ve Yönetimi

Slow query log aktifken dosya hızla büyüyebilir. Özellikle log_queries_not_using_indexes = 1 açıkken bu daha da belirginleşir. Log rotasyonu için logrotate kullanın.

# /etc/logrotate.d/mysql-slow-query dosyası oluştur
sudo nano /etc/logrotate.d/mysql-slow-query
/var/log/mysql/slow.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        if test -x /usr/bin/mysqladmin && 
           /usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping &>/dev/null
        then
            /usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf flush-logs
        fi
    endscript
}

Manuel olarak log’u sıfırlamak isterseniz:

# Mevcut log'u kapat ve yeni başlat
mysql -u root -p -e "FLUSH SLOW LOGS;"

# Veya log dosyasını sıfırla
sudo mv /var/log/mysql/slow.log /var/log/mysql/slow.log.bak
mysql -u root -p -e "FLUSH SLOW LOGS;"

Performance Schema ile Entegrasyon

MySQL 5.6 ve sonrasında performance_schema ile slow query log’u birlikte kullanmak çok daha kapsamlı analiz imkanı sunar.

mysql -u root -p -e "
-- En yavaş 10 sorguyu bul
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as execution_count,
    ROUND(AVG_TIMER_WAIT/1000000000, 3) as avg_time_sec,
    ROUND(MAX_TIMER_WAIT/1000000000, 3) as max_time_sec,
    ROUND(SUM_TIMER_WAIT/1000000000, 3) as total_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10G
"

Bu sorgu size slow query log’un gösteremediklerini de gösterir: kısa ama çok sık çalışan ve toplam yük açısından problem yaratan sorgular.

İzleme ve Alarm Sistemi Kurmak

Slow query log’u sadece sorun çıktıktan sonra bakmak için değil, proaktif izleme için de kullanabilirsiniz. Basit bir bash script ile düzenli rapor alabilirsiniz:

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

LOG_FILE="/var/log/mysql/slow.log"
THRESHOLD=50  # Belirlenen süre içinde bu kadar yavaş sorgu gelirse alarm ver
REPORT_FILE="/tmp/slow_query_report_$(date +%Y%m%d_%H%M).txt"
EMAIL="[email protected]"

# Son 1 saatteki yavaş sorgu sayısı
SLOW_COUNT=$(sudo grep -c "^# Query_time" $LOG_FILE 2>/dev/null || echo 0)

if [ "$SLOW_COUNT" -gt "$THRESHOLD" ]; then
    echo "UYARI: Son periyotta $SLOW_COUNT yavaş sorgu tespit edildi!" > $REPORT_FILE
    echo "" >> $REPORT_FILE
    echo "=== En Yavaş 10 Sorgu ===" >> $REPORT_FILE
    sudo mysqldumpslow -s t -t 10 $LOG_FILE >> $REPORT_FILE 2>/dev/null
    
    mail -s "MySQL Yavaş Sorgu Alarmı - $(hostname)" $EMAIL < $REPORT_FILE
    
    # Log'u temizle ve yenile
    mysql -u root --defaults-file=/etc/mysql/debian.cnf -e "FLUSH SLOW LOGS;" 2>/dev/null
fi

Bu scripti cron’a ekleyin:

# Her saat başı çalıştır
echo "0 * * * * root /usr/local/bin/slow_query_report.sh" | sudo tee -a /etc/cron.d/mysql-monitoring

Sık Yapılan Hatalar

long_query_time’ı çok yüksek tutmak: 10 saniye olarak bırakırsanız zaten herkes şikayet ettikten sonra yakalarsınız. Başlangıçta 2, sonra 1, sonra 0.5 yaparak indirin.

log_queries_not_using_indexes’i sürekli açık bırakmak: Bu ayar aktifken log dosyası çok hızlı büyür. Sorun tespiti döneminde açın, tespit ettikten sonra kapatın.

Log dosyasını analiz etmemek: Çok sayıda ekip slow query log açıyor ama hiç bakmıyor. Log açmak sorunları çözmüyor, analiz etmek çözüyor.

Her yavaş sorguya hemen index eklemek: Bazen sorun index eksikliği değil, kötü yazılmış sorgudur. EXPLAIN çıktısını okuyun, önce sorguyu optimize etmeyi deneyin.

Replikasyon slave’inde test etmemek: Master’da index eklemenin replikasyon gecikmesine yol açabileceğini unutmayın. Büyük tablolarda ALTER TABLE slave’i geride bırakabilir.

Sonuç

MySQL slow query log, veritabanı performans sorunlarının yüzde seksenini tespit etmenizi sağlayan basit ama son derece etkili bir araçtır. Kurulumu dakikalar alır, analiz için mysqldumpslow veya pt-query-digest ile hızlıca sonuç alırsınız.

Pratik önerim şu: her MySQL sunucusunda slow query log’u long_query_time = 2 ile her zaman açık tutun, log rotasyonunu ayarlayın, haftalık olarak pt-query-digest raporu alın. Sorun çıkmadan önce hangi sorgularınızın riskli olduğunu bu şekilde görürsünüz.

Yavaşlama şikayeti aldığınızda ise long_query_time = 0.5‘e indirin, bir iki saat bekleyin, analiz edin. Büyük ihtimalle 2-3 sorgudan kaynaklanan bir sorun olduğunu görürsünüz. O sorguları EXPLAIN ile inceleyin, index eksikliklerini veya sorgu problemlerini tespit edin ve düzeltin.

Veritabanı performans optimizasyonu bir defalık iş değil, sürekli takip gerektiren bir süreçtir. Slow query log bu sürecin temel taşıdır.

Benzer Konular

Bir yanıt yazın

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