MySQL Yavaş Sorgu Analizi ile Sistem Performansını Artırın

Bykomut

Prodüksiyondaki bir MySQL sunucusu yavaşlamaya başladığında, ilk içgüdü genellikle sunucuya daha fazla RAM eklemek ya da CPU yükseltmek olur. Ama çoğu zaman asıl sorun donanımda değil, kötü yazılmış sorgularda gizlidir. Yavaş sorgu analizi, bu sorguları tespit etmek ve düzeltmek için elimizdeki en güçlü araçlardan biridir. Bu yazıda MySQL’in yavaş sorgu günlüğünden başlayarak, pt-query-digest, EXPLAIN ve sistem düzeyindeki performans araçlarıyla nasıl kapsamlı bir analiz yapacağımızı gerçek dünya senaryolarıyla ele alacağız.

Yavaş Sorgu Günlüğünü Aktif Hale Getirmek

Analiz sürecinin temeli, MySQL’in yavaş sorgu günlüğüdür. Bu günlük varsayılan olarak kapalı gelir ve önce açmamız gerekir. İki yöntemle aktif hale getirebilirsiniz: kalıcı olarak my.cnf üzerinden veya geçici olarak çalışan sunucuda.

Kalıcı yapılandırma için /etc/mysql/my.cnf veya /etc/my.cnf dosyasına şunları ekleyin:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

Çalışan sunucuda geçici olarak aktif etmek için:

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

Burada long_query_time = 1 değeri, 1 saniyeden uzun süren sorguları kaydet demektir. Yoğun sistemlerde bu değeri 0.5 veya hatta 0.1’e çekebilirsiniz. log_queries_not_using_indexes parametresi ise hızlı çalışsa bile indeks kullanmayan sorguları da günlüğe yazar, bu özellikle erken optimizasyon için çok değerlidir.

Ayarların aktif olup olmadığını doğrulamak için:

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

Yavaş Sorgu Günlüğünü Manuel Analiz Etmek

Günlük birkaç saat toplandıktan sonra, ham dosyaya bakmak yerine önce mysqldumpslow aracıyla genel bir bakış yapalım. Bu araç MySQL kurulumunun içinde gelir ve fazladan kurulum gerektirmez.

# En çok zaman alan 10 sorguyu göster
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# En sık çalışan 10 yavaş sorguyu göster
mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log

# Tablo tarama yapan sorgular
mysqldumpslow -s l -t 10 /var/log/mysql/slow-query.log

mysqldumpslow çıktısı size sorgu kalıplarını gösterir. Sayısal değerler ve stringler N ve S ile soyutlanır, bu sayede aynı yapıdaki sorgular gruplanır. Tipik bir çıktı şöyle görünür:

Count: 847  Time=4.23s (3582s)  Lock=0.00s (0s)  Rows=1245.3 (1053927), app[app]@localhost
SELECT * FROM orders WHERE customer_id=N AND status='S' ORDER BY created_at DESC

Bu çıktıdan şunu anlıyoruz: Bu sorgu 847 kez çalışmış, ortalama 4.23 saniye sürmüş ve toplamda 3582 saniye harcamış. Sisteminize ciddi bir yük bindiriyor.

pt-query-digest ile Derinlemesine Analiz

mysqldumpslow iyi bir başlangıç noktası olsa da Percona Toolkit‘in pt-query-digest aracı çok daha ayrıntılı ve okunabilir raporlar üretir. Henüz kurulu değilse:

# Ubuntu/Debian
sudo apt-get install percona-toolkit

# CentOS/RHEL
sudo yum install percona-toolkit

# Manuel kurulum
wget https://downloads.percona.com/downloads/percona-toolkit/3.5.4/binary/tarball/percona-toolkit-3.5.4_x86_64.tar.gz
tar xzf percona-toolkit-3.5.4_x86_64.tar.gz
sudo cp percona-toolkit-3.5.4/bin/pt-query-digest /usr/local/bin/

Temel kullanım:

# Standart analiz raporu
pt-query-digest /var/log/mysql/slow-query.log

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

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

# Raporu dosyaya kaydet
pt-query-digest /var/log/mysql/slow-query.log > /tmp/query-report-$(date +%Y%m%d).txt

pt-query-digest çıktısı üç bölümden oluşur: genel istatistikler, sorgu grubu özeti ve her sorgu için detaylı analiz. Her sorgu grubu için şu metrikleri gösterir:

  • Rank: En pahalıdan başlayarak sıralama
  • Query ID: Sorgunun parmak izi
  • Response time: Toplam ve yüzde olarak katkı
  • Calls: Kaç kez çağrıldığı
  • R/Call: Ortalama yanıt süresi
  • Item: Sorgu özeti

EXPLAIN ile Sorgu Planı Analizi

Bir sorguyu tespit ettikten sonra, veritabanının onu nasıl çalıştırdığını anlamak için EXPLAIN komutunu kullanırız. Bu, optimizasyonun en kritik adımıdır.

EXPLAIN SELECT o.id, o.total, c.name, c.email
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.created_at DESC
LIMIT 100;

EXPLAIN çıktısında dikkat etmeniz gereken kritik alanlar:

  • type: Erişim türü. ALL tablo taraması demektir ve kötüdür. ref, range, index iyidir. const ve eq_ref en iyisidir.
  • key: Kullanılan indeks. NULL ise indeks kullanılmıyor demektir.
  • rows: Taranacak tahmini satır sayısı. Bu sayı ne kadar düşükse o kadar iyi.
  • Extra: Using filesort veya Using temporary görüyorsanız sorun var demektir.

Daha ayrıntılı bilgi için EXPLAIN ANALYZE kullanın (MySQL 8.0+):

EXPLAIN ANALYZE SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100G

G sonuna eklenmesi çıktıyı dikey formatta gösterir ve uzun satırları okumayı kolaylaştırır.

Gerçek Dünya Senaryosu: E-Ticaret Sitesinde Yavaşlama

Bir e-ticaret platformunda sabah saatlerinde yoğunluk artınca sayfaların 8-10 saniyeye çıktığı bildirildi. pt-query-digest raporuna bakıyoruz ve şu sorgunun sisteme en fazla yük bindirdiğini görüyoruz:

SELECT p.*, c.name as category_name, AVG(r.rating) as avg_rating
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE p.active = 1
AND p.stock > 0
GROUP BY p.id
ORDER BY p.created_at DESC
LIMIT 20 OFFSET 0;

Bu sorgu için EXPLAIN çalıştırıyoruz ve products tablosunda type: ALL, rows: 450000 görüyoruz. 450 bin satır taranıyor, bu kabul edilemez.

Önce mevcut indeksleri kontrol edelim:

SHOW INDEX FROM products;
SHOW INDEX FROM reviews;

Sorun açık: products.active, products.stock ve products.created_at üzerinde bileşik bir indeks yok. Ek olarak, reviews tablosundaki product_id sütununda da indeks eksik.

Çözüm olarak şu indeksleri ekliyoruz:

-- Ürün filtreleme için bileşik indeks
ALTER TABLE products 
ADD INDEX idx_active_stock_created (active, stock, created_at);

-- Reviews için yabancı anahtar indeksi
ALTER TABLE reviews 
ADD INDEX idx_product_id (product_id);

-- Sorguyu da optimize ediyoruz
SELECT p.id, p.name, p.price, p.image_url, c.name as category_name, 
       COALESCE(r.avg_rating, 0) as avg_rating
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN (
    SELECT product_id, AVG(rating) as avg_rating 
    FROM reviews 
    GROUP BY product_id
) r ON p.id = r.product_id
WHERE p.active = 1
AND p.stock > 0
ORDER BY p.created_at DESC
LIMIT 20;

Bu değişiklikten sonra sorgu süresi 8.2 saniyeden 0.04 saniyeye düştü.

Sistem Seviyesinde Performans Analizi

MySQL sorgu optimizasyonu tek başına yeterli olmayabilir. Sistem kaynaklarını da takip etmek gerekir. Önce MySQL’in sistem kaynaklarını nasıl kullandığına bakalım:

# MySQL prosesinin kaynak kullanımı
pidstat -u -p $(pgrep mysqld) 2 10

# Disk I/O istatistikleri
iostat -xz 2 10

# MySQL'in disk okuma/yazma oranı
iotop -P -p $(pgrep mysqld)

# Bellek kullanımı
free -h && vmstat 1 5

MySQL’in kendi istatistiklerini sorgulamak için Performance Schema kullanabilirsiniz:

-- En çok kaynak tüketen sorgular (Performance Schema)
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as execution_count,
    ROUND(AVG_TIMER_WAIT/1000000000, 3) as avg_seconds,
    ROUND(SUM_TIMER_WAIT/1000000000, 3) 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
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Tablo başına I/O istatistikleri
SELECT OBJECT_SCHEMA, OBJECT_NAME, 
       COUNT_READ, COUNT_WRITE,
       ROUND(SUM_TIMER_READ/1000000000, 2) as read_seconds,
       ROUND(SUM_TIMER_WRITE/1000000000, 2) as write_seconds
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY SUM_TIMER_READ + SUM_TIMER_WRITE DESC
LIMIT 10;

InnoDB Buffer Pool ve Bellek Optimizasyonu

Sorgu optimizasyonunun yanında, MySQL’in belleği nasıl kullandığı da performansı doğrudan etkiler. InnoDB buffer pool, en kritik bellek parametresidir.

-- Buffer pool kullanım oranını kontrol et
SHOW STATUS LIKE 'Innodb_buffer_pool_%';

-- Hit rate hesabı
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'
) reads,
(
    SELECT 
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) read_requests;

Buffer pool hit rate’in %95’in altında olması, daha fazla RAM tahsis etmeniz ya da gereksiz verilerin bellekte tutulduğu anlamına gelir. Genel kural olarak, innodb_buffer_pool_size değerini toplam RAM’in %60-70’i olarak ayarlayın. 16 GB RAM’li bir sunucuda bu yaklaşık 10-11 GB olmalıdır.

/etc/mysql/my.cnf içinde:

[mysqld]
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

innodb_flush_log_at_trx_commit = 2 değeri, tam ACID uyumluluğu biraz gevşeterek performansı artırır. Finansal sistemler dışında çoğu uygulama için bu kabul edilebilir bir tradeoff’tur.

Otomatik İzleme Script’i Yazmak

Sürekli manuel kontrol yapmak yerine, belirli aralıklarla çalışan ve kritik durumlarda uyarı veren bir script hazırlamak işimizi kolaylaştırır.

#!/bin/bash
# mysql-slow-query-monitor.sh
# Her saat başı cron ile çalıştırın: 0 * * * * /usr/local/bin/mysql-slow-query-monitor.sh

MYSQL_USER="monitor_user"
MYSQL_PASS="guclu_sifre"
LOG_FILE="/var/log/mysql/slow-query.log"
REPORT_DIR="/var/reports/mysql"
ALERT_EMAIL="[email protected]"
THRESHOLD_QUERIES=100

mkdir -p "$REPORT_DIR"
DATE=$(date +%Y%m%d_%H%M)
REPORT_FILE="$REPORT_DIR/slow_query_report_$DATE.txt"

# Son 1 saatin sorgularını analiz et
pt-query-digest 
    --since=3600 
    --output=report 
    "$LOG_FILE" > "$REPORT_FILE" 2>/dev/null

# Sorgu sayısını kontrol et
SLOW_COUNT=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e 
    "SHOW STATUS LIKE 'Slow_queries';" 2>/dev/null | 
    grep Slow_queries | awk '{print $2}')

# Buffer pool hit rate kontrolü
HIT_RATE=$(mysql -u"$MYSQL_USER" -p"$MYSQL_PASS" -e 
    "SELECT ROUND((1 - (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') / 
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS 
    WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')) * 100, 2) AS hit_rate;" 
    2>/dev/null | tail -1)

echo "=== MySQL Performans Raporu ===" >> "$REPORT_FILE"
echo "Tarih: $(date)" >> "$REPORT_FILE"
echo "Yavaş Sorgu Sayısı: $SLOW_COUNT" >> "$REPORT_FILE"
echo "Buffer Pool Hit Rate: %$HIT_RATE" >> "$REPORT_FILE"

# Eşik değeri aşıldıysa mail gönder
if [ "$SLOW_COUNT" -gt "$THRESHOLD_QUERIES" ]; then
    mail -s "[UYARI] MySQL Yavaş Sorgu Sayısı: $SLOW_COUNT" 
         "$ALERT_EMAIL" < "$REPORT_FILE"
fi

echo "Rapor oluşturuldu: $REPORT_FILE"

Script’i çalıştırılabilir yapın ve cron’a ekleyin:

chmod +x /usr/local/bin/mysql-slow-query-monitor.sh

# Cron'a ekle
echo "0 * * * * /usr/local/bin/mysql-slow-query-monitor.sh" | crontab -

Kilitlenen Sorgular ve Deadlock Analizi

Yavaş sorgular kadar tehlikeli bir diğer sorun da kilitlenmelerdir. Uzun süre kilit bekleyen sorgular tüm sistemi felç edebilir.

-- Şu anda bekleyen kilitleri göster
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query,
    TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id
ORDER BY wait_seconds DESC;

-- Son deadlock bilgisi
SHOW ENGINE INNODB STATUSG

SHOW ENGINE INNODB STATUS çıktısındaki LATEST DETECTED DEADLOCK bölümü, son deadlock’ta hangi sorguların karıştığını gösterir. Bu bilgiyi kullanarak uygulama kodundaki transaction sırasını düzenleyebilir veya sorgu yapısını değiştirebilirsiniz.

Uzun süre açık kalan transaction’ları kapatmak için:

-- 60 saniyeden uzun çalışan işlemleri listele
SELECT 
    id, user, host, db, command, time, state, 
    SUBSTRING(info, 1, 100) as query_preview
FROM information_schema.processlist
WHERE command != 'Sleep'
AND time > 60
ORDER BY time DESC;

-- Belirli bir prosesi sonlandır (dikkatli kullanın)
KILL QUERY 12345;
-- Veya bağlantıyı tamamen kapat
KILL 12345;

Grafana ve Prometheus ile Sürekli İzleme

Anlık analizin ötesinde, uzun vadeli performans trendlerini görmek için mysqld_exporter kullanarak Prometheus ve Grafana entegrasyonu kurabilirsiniz.

# mysqld_exporter kurulumu
wget https://github.com/prometheus/mysqld_exporter/releases/latest/download/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-0.15.1.linux-amd64.tar.gz
sudo cp mysqld_exporter-0.15.1.linux-amd64/mysqld_exporter /usr/local/bin/

# MySQL kullanıcısı oluştur
mysql -u root -p -e "
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'guclu_sifre' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;
"

# Yapılandırma dosyası
cat > /etc/mysql/.mysqld_exporter.cnf << EOF
[client]
user=exporter
password=guclu_sifre
EOF

chmod 600 /etc/mysql/.mysqld_exporter.cnf

# Systemd servis dosyası
cat > /etc/systemd/system/mysqld_exporter.service << EOF
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
ExecStart=/usr/local/bin/mysqld_exporter 
  --config.my-cnf=/etc/mysql/.mysqld_exporter.cnf 
  --collect.global_status 
  --collect.info_schema.innodb_metrics 
  --collect.perf_schema.eventsstatements 
  --collect.perf_schema.eventswaits
Restart=always

[Install]
WantedBy=multi-user.target
EOF

systemctl daemon-reload
systemctl enable --now mysqld_exporter

Grafana’da Percona’nın hazır MySQL dashboard’unu (ID: 7362) import ederek hemen kullanmaya başlayabilirsiniz. Bu dashboard yavaş sorgu oranı, buffer pool doluluk oranı, QPS (queries per second) ve bağlantı havuzu gibi kritik metrikleri gerçek zamanlı gösterir.

Sonuç

MySQL performans analizi, tek seferlik yapılan bir iş değil, sürekli bir süreçtir. Yavaş sorgu günlüğünü aktif tutmak, pt-query-digest ile düzenli raporlar üretmek ve EXPLAIN ile sorgu planlarını incelemek bu sürecin temelini oluşturur. Bir sorunu tespit ettiğinizde şu sırayı izleyin: önce EXPLAIN ile sorgunun neden yavaş olduğunu anlayın, uygun indeksleri ekleyin, sorguyu yeniden yazın ve sonuçta gerçekten iyileşme olduğunu ölçerek doğrulayın.

Sistem seviyesinde ise InnoDB buffer pool boyutunu doğru ayarlamak, disk I/O darboğazlarını iostat ile takip etmek ve kilitleme sorunlarını proaktif olarak izlemek performansın uzun vadede stabil kalmasını sağlar. Grafana ve Prometheus ile görselleştirme eklendiğinde, ani yavaşlamaları ve trend değişikliklerini fark etmek çok daha kolay hale gelir. En önemli kural şudur: ölçmeden optimize etmeye çalışmayın, her müdahalenin etkisini sayılarla doğrulayın.

Similar Posts

Bir yanıt yazın

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