Yavaş Sorguları Tespit Etmek için Slow Query Log Kullanımı

Tarafındankomut
0 Yorum

Veritabanı performans sorunları çoğu zaman sessiz sedasız büyür. Uygulama yavaşlar, kullanıcılar şikayet etmeye başlar, sen de sunucuya bakarsın her şey normal görünür. İşte tam bu noktada slow query log devreye giriyor. MySQL ve MariaDB’nin bu yerleşik özelliği, belirlediğin eşik değerinin üzerinde süren sorguları kayıt altına alarak sana net bir yol haritası sunuyor. Bu yazıda slow query log’u nasıl aktif edeceğini, doğru şekilde yapılandıracağını ve üretilen logları nasıl analiz edeceğini gerçek dünya senaryolarıyla ele alacağız.

Slow Query Log Nedir ve Neden Önemlidir

Bir e-ticaret sitesi düşün. Sabah saatlerinde her şey yolunda giderken öğleden sonra sipariş sayfası 8-10 saniyede açılmaya başlıyor. Uygulama sunucusu gayet iyi çalışıyor, ağ trafiği normal, ama veritabanı sunucusu sessizce ızdırap çekiyor. Bu senaryoda slow query log olmasaydı hangi sorgunun sorun çıkardığını bulmak için saatler harcaman gerekirdi.

Slow query log, MySQL ve MariaDB’nin yerleşik bir tanı aracıdır. Belirttiğin long_query_time değerini aşan her sorguyu, o sorgunun ne kadar sürdüğünü, kaç satır incelediğini ve kaç satır döndürdüğünü kayıt altına alır. Bu bilgiler sayesinde indeks eksikliğini, kötü yazılmış JOIN’leri veya gereksiz tam tablo taramalarını kolayca tespit edebilirsin.

Özellikle şu durumlarda slow query log kritik önem taşır:

  • Uygulama katmanında belirgin bir sorun olmadığı halde veritabanı yüklü görünüyorsa
  • Belirli saatlerde ya da belirli kullanıcı işlemlerinde performans düşüşü yaşanıyorsa
  • Yeni bir özellik veya güncelleme sonrasında sorgu süreleri artmışsa
  • Production ortamına geçmeden önce sorgu optimizasyonu yapılacaksa

Slow Query Log’u Aktif Etmek

MySQL ve MariaDB Konfigürasyon Dosyasını Düzenlemek

En kalıcı yöntem my.cnf veya my.ini dosyasını düzenlemektir. Linux sistemlerde bu dosya genellikle /etc/mysql/my.cnf, /etc/my.cnf veya /etc/mysql/mysql.conf.d/mysqld.cnf yolunda bulunur.

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] bölümüne şu satırları ekle:

[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 = 100
log_slow_admin_statements = 1

Bu parametrelerin ne işe yaradığını tek tek açıklayalım:

  • slow_query_log: Slow query log özelliğini açar veya kapar. 1 aktif, 0 pasif demektir.
  • slow_query_log_file: Log dosyasının nereye yazılacağını belirtir. Belirtilmezse datadir içinde hostname-slow.log olarak oluşur.
  • long_query_time: Saniye cinsinden eşik değeridir. Bu süreyi aşan sorgular loglanır. Ondalıklı değer de kabul eder, örneğin 0.5 yarım saniyeyi aşan sorguları yakalar.
  • log_queries_not_using_indexes: İndeks kullanmayan sorguları, long_query_time değerinden bağımsız olarak loglar. Performans sorunlarının büyük kısmı buradan kaynaklanır.
  • min_examined_row_limit: En az bu kadar satır inceleyen sorguları loglar. Çok küçük tablolardaki indekssiz sorgularda gürültüyü azaltmak için kullanılır.
  • log_slow_admin_statements: ALTER TABLE, OPTIMIZE TABLE gibi yönetimsel komutları da loglar.

Konfigürasyonu kaydettikten sonra servisi yeniden başlat:

sudo systemctl restart mysql
# veya MariaDB için
sudo systemctl restart mariadb

Sunucuyu Yeniden Başlatmadan Aktif Etmek

Production ortamında servisi yeniden başlatmak her zaman mümkün olmayabilir. MySQL 5.1 ve sonrası ile MariaDB’de slow query log’u dinamik olarak aktif edebilirsin:

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

Bu değişiklikler sunucu yeniden başlatıldığında sıfırlanır. Kalıcı olmasını istiyorsan my.cnf‘e de eklemeyi unutma.

Mevcut ayarları kontrol etmek için:

mysql -u root -p -e "
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
"

Log Dosyasının İzinlerini Ayarlamak

Log dosyasını manuel oluşturuyorsan MySQL kullanıcısının yazma iznine sahip olduğundan emin ol:

sudo touch /var/log/mysql/mysql-slow.log
sudo chown mysql:mysql /var/log/mysql/mysql-slow.log
sudo chmod 640 /var/log/mysql/mysql-slow.log

Logrotate ile log dosyasının şişmesini engellemek için /etc/logrotate.d/mysql-slow dosyası oluşturabilirsin:

/var/log/mysql/mysql-slow.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    postrotate
        if [ -d /var/run/mysqld ] && [ -r /var/run/mysqld/mysqld.pid ]; then
            mysqladmin flush-logs
        fi
    endscript
}

Slow Query Log Dosyasını Okumak

Log dosyası ham haliyle okunabilir ama büyük sistemlerde binlerce satır olabilir. Önce ham formata bakalım:

sudo tail -f /var/log/mysql/mysql-slow.log

Tipik bir slow query log girişi şöyle görünür:

# Time: 2024-01-15T14:23:45.123456Z
# User@Host: appuser[appuser] @ web01 [192.168.1.50]
# Query_time: 5.234123  Lock_time: 0.000145 Rows_sent: 1  Rows_examined: 2847392
SET timestamp=1705327425;
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
AND o.created_at > '2024-01-01'
ORDER BY o.total_amount DESC;

Bu girişi okumak için dikkat edilmesi gereken alanlar:

  • Query_time: Sorgunun toplam çalışma süresi (saniye)
  • Lock_time: Kilitleme için harcanan süre
  • Rows_sent: İstemciye gönderilen satır sayısı
  • Rows_examined: Sorgunun incelediği satır sayısı

Yukarıdaki örnekte sorgu 1 satır döndürmek için 2.8 milyon satır incelemiş. Bu açık bir indeks eksikliği işaretidir.

mysqldumpslow ile Log Analizi

MySQL’in kendi aracı olan mysqldumpslow benzer sorguları gruplayarak özetler:

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

# En çok çalıştırılan yavaş sorguları göster
sudo mysqldumpslow -s c -t 10 /var/log/mysql/mysql-slow.log

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

# Belirli bir veritabanı veya tablo içeren sorguları filtrele
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log | grep "orders"

-s parametresi sıralama kriterini belirler:

  • t: Query_time’a göre sırala (toplam süre)
  • at: Ortalama Query_time’a göre sırala
  • c: Sorgunun kaç kez çalıştığına göre sırala
  • r: İncelenen satır sayısına göre sırala
  • l: Lock time’a göre sırala

pt-query-digest ile Gelişmiş Analiz

Percona Toolkit’in pt-query-digest aracı çok daha detaylı analiz sunar. Önce Percona Toolkit’i kur:

# Debian/Ubuntu
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
sudo dpkg -i percona-release_latest.generic_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit

# RHEL/CentOS
sudo yum install percona-toolkit

Temel kullanım:

# Tam rapor oluştur
pt-query-digest /var/log/mysql/mysql-slow.log

# Son 1 saatin loglarını analiz et
pt-query-digest --since 3600 /var/log/mysql/mysql-slow.log

# Sadece belirli bir veritabanının sorgularını analiz et
pt-query-digest --filter '$event->{db} && $event->{db} eq "myapp"' /var/log/mysql/mysql-slow.log

# Raporu dosyaya yaz
pt-query-digest /var/log/mysql/mysql-slow.log > /tmp/slow_query_report.txt

pt-query-digest çıktısında her sorgu için şunları göreceksin:

  • Kaç kez çalıştırıldığı
  • Toplam ve ortalama çalışma süresi
  • Minimum ve maksimum süreler
  • Yüzde kaçını oluşturduğu
  • Örnek sorgu metni

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

Diyelim ki bir e-ticaret sitesinin veritabanı yöneticisisin ve her gün öğleden sonra yavaşlama şikayeti alıyorsun. Slow query log’u aktif edip birkaç saat bekledikten sonra pt-query-digest ile analiz yapıyorsun. Rapor şöyle bir sorguyu öne çıkardı:

SELECT p.*, c.name as category_name, b.name as brand_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
WHERE p.is_active = 1
AND p.price BETWEEN 100 AND 500
AND p.stock > 0
ORDER BY p.created_at DESC
LIMIT 20;

Bu sorgu 3.2 saniye sürüyor ve günde 15.000 kez çalışıyor. EXPLAIN ile bakalım:

mysql -u root -p myapp -e "
EXPLAIN SELECT p.*, c.name as category_name, b.name as brand_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN brands b ON p.brand_id = b.id
WHERE p.is_active = 1
AND p.price BETWEEN 100 AND 500
AND p.stock > 0
ORDER BY p.created_at DESC
LIMIT 20;
"

Çıktıda type: ALL görüyorsun, yani tam tablo taraması var. 500.000 ürünlük bir tabloda bu felaket demek. Çözüm şu indeksi eklemek:

ALTER TABLE products
ADD INDEX idx_active_price_stock_created (is_active, price, stock, created_at);

Bu indeks eklendikten sonra sorgu süresi 3.2 saniyeden 0.04 saniyeye düştü. Günlük 15.000 çalışma düşünüldüğünde bu ciddi bir kazanım.

Performance Schema ile Slow Query Log’u Tamamlamak

MySQL 5.6 ve MariaDB 10.0 sonrasında Performance Schema, slow query log’a güzel bir alternatif ve tamamlayıcı sunar. Yeniden başlatma gerektirmez:

mysql -u root -p -e "
-- En yavaş 10 sorguyu getir
SELECT
    DIGEST_TEXT,
    COUNT_STAR as execution_count,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) as avg_seconds,
    ROUND(MAX_TIMER_WAIT/1000000000, 2) as max_seconds,
    ROUND(SUM_TIMER_WAIT/1000000000, 2) as total_seconds,
    SUM_ROWS_EXAMINED as total_rows_examined,
    SUM_ROWS_SENT as total_rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
"

Performance Schema ve slow query log’u birlikte kullanmak en iyi yaklaşımdır. Slow query log anlık sorunları yakalarken Performance Schema kümülatif istatistikler sunar.

Eşik Değerini Doğru Ayarlamak

long_query_time değeri kuruma göre farklılık gösterir. Çok yüksek tutarsan gerçek sorunları kaçırırsın, çok düşük tutarsan log dosyası şişer ve analiz zorlaşır.

Genel yaklaşım:

  • Başlangıç aşaması: 2 saniye ile başla, sorunlu sorguları tespit et ve optimize et.
  • Olgun sistem: 1 saniyeye indir, ince ayar yap.
  • Yüksek performans gerektiren sistem: 0.5 veya 0.1 saniyeye indir.
  • Geliştirme ortamı: 0 değeri tüm sorguları loglar, geliştirme sırasında faydalı olabilir.

Mevcut sisteminizde ortalama sorgu süresini görmek için:

mysql -u root -p -e "
SELECT
    ROUND(AVG(TIMER_WAIT)/1000000000, 4) as avg_query_seconds,
    ROUND(MAX(TIMER_WAIT)/1000000000, 4) as max_query_seconds,
    COUNT(*) as total_queries
FROM performance_schema.events_statements_history_long;
"

Bu çıktıya bakarak long_query_time değerini gerçekçi biçimde belirleyebilirsin.

Slow Query Log Rotasyonu ve Yönetimi

Aktif bir sistemde slow query log hızla büyüyebilir. Günlük log boyutunu izlemek için:

# Log boyutunu kontrol et
ls -lh /var/log/mysql/mysql-slow.log

# Log içindeki toplam sorgu sayısını say
grep -c "^# Query_time" /var/log/mysql/mysql-slow.log

# Son 100 yavaş sorguyu görüntüle
tail -n 1000 /var/log/mysql/mysql-slow.log | grep -A 5 "Query_time"

Log dosyasını elle döndürmek için:

# Mevcut log dosyasını yedekle
sudo mv /var/log/mysql/mysql-slow.log /var/log/mysql/mysql-slow-$(date +%Y%m%d).log

# MySQL'e yeni log dosyasına geçmesini söyle
sudo mysqladmin flush-logs

# Eski log dosyasını sıkıştır
sudo gzip /var/log/mysql/mysql-slow-$(date +%Y%m%d).log

Slow Query Log’dan Öğrenilen Dersleri Uygulamak

Sorguyu tespit etmek ilk adım, asıl iş optimizasyon kısmındadır. Slow query log’da sık karşılaşılan sorun tipleri ve çözümleri:

Tam tablo taraması (type: ALL): WHERE koşulundaki kolonlara indeks ekle.

Gereksiz yüksek Rows_examined / Rows_sent oranı: Sorgu binlerce satır inceliyor ama az satır döndürüyorsa indeks eksikliği ya da kötü sorgu yapısı var demektir.

Yüksek Lock_time: Tablo kilitleme sorunu var. SHOW PROCESSLIST ve SHOW ENGINE INNODB STATUS komutlarıyla derinlemesine incele.

SELECT * kullanımı: Sadece ihtiyacın olan kolonları seç. Gereksiz veri transferini azaltır, covering index kullanımını mümkün kılar.

Bir sorguyu optimize ettikten sonra slow query log’da takibini yapmak için şu bash betiğini kullanabilirsin:

#!/bin/bash
# slow_query_monitor.sh
# Slow query logundaki son N girişi analiz eder

LOG_FILE="/var/log/mysql/mysql-slow.log"
THRESHOLD=5  # Kaç kez görünürse uyar

echo "=== Slow Query Raporu - $(date) ==="
echo ""
echo "Son 1 saatteki yavaş sorgu sayısı:"
awk '/^# Time:/{time=$3} /^# Query_time:/{print time, $3}' "$LOG_FILE" | 
    awk -v limit=$(date -d '1 hour ago' +%Y-%m-%dT%H:%M:%S) '$1 >= limit {count++} END {print count " sorgu"}'

echo ""
echo "En çok tekrar eden yavaş sorgular:"
mysqldumpslow -s c -t 5 "$LOG_FILE" 2>/dev/null

Sonuç

Slow query log, veritabanı yönetiminin vazgeçilmez araçlarından biridir. Pasif bir izleme mekanizması gibi görünse de doğru yapılandırıldığında sana saatlerce sürecek sorun giderme süreçlerini dakikalara indirgeyecek veriler sunar.

Önemli noktalara bakalım. long_query_time değerini sisteme uygun şekilde ayarla, çok yüksek bırakma. log_queries_not_using_indexes parametresini mutlaka aktif et çünkü indeks kullanmayan sorgular bazen eşik değerine ulaşmadan da ciddi sorunlar çıkarabilir. mysqldumpslow hızlı bir bakış için yeterlidir ama pt-query-digest çok daha kapsamlı analiz sunar.

Slow query log’u sürekli açık tutmak en iyi pratiktir. Log boyutunu logrotate ile kontrol altında tutarak hem sürekli izleme yapabilir hem de disk dolmasını engelleyebilirsin. Performance Schema ile birlikte kullandığında gerçek anlamda kapsamlı bir veritabanı performans izleme altyapısına sahip olursun.

Son olarak şunu unutma: Yavaş sorguyu bulmak başarının yarısıdır. Diğer yarısı EXPLAIN çıktısını okuyup doğru indeksi eklemek veya sorguyu yeniden yazmaktır. Slow query log bu yolculuğun haritasını çizer, yürümek sana kalır.

Bir yanıt yazın

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