MySQL’de Yüksek CPU Kullanan Sorguları Tespit Etme

Tarafındankomut
0 Comments

Bir gece yarısı üretim sunucunuzdan alarm geldi: MySQL CPU kullanımı %95’in üzerinde, uygulama yavaşlamış, kullanıcılar şikayet ediyor. Panik yapmadan önce yapmanız gereken ilk şey sorunu tespit etmek. Yüksek CPU kullanan sorguları bulmak aslında düşündüğünüzden daha sistematik bir süreç ve bu yazıda o süreci adım adım ele alacağız.

Sorunun Kökünü Anlamak

MySQL’de yüksek CPU kullanımı genellikle birkaç temel nedenden kaynaklanır. Optimize edilmemiş sorgular, eksik indeksler, çok büyük result set’ler veya kilit çakışmaları bunların başında gelir. Ama nedenini bulmadan önce “hangi sorgu” sorusunu yanıtlamanız gerekiyor. Bunun için elimizdeki araçları sırayla kullanacağız.

Önce mevcut durumu bir anlık görüntüyle yakalayalım. Sunucuya SSH ile bağlandıktan sonra MySQL process listesine bakın:

mysql -u root -p -e "SHOW FULL PROCESSLISTG" | less

Bu komut size o an çalışan tüm sorguları gösterir. G parametresi çıktıyı dikey formatta gösterir, geniş sorgularda okumayı kolaylaştırır. Time kolonuna dikkat edin: uzun süredir çalışan sorgular burada büyük değerler gösterir.

Performance Schema ile Gerçek Zamanlı İzleme

MySQL 5.6 ve üzeri versiyonlarda Performance Schema, sorgu performansı analizi için en güçlü araçtır. Aktif olup olmadığını kontrol edelim:

mysql -u root -p -e "SELECT @@performance_schema;"

Eğer 0 dönüyorsa my.cnf dosyasına performance_schema = ON ekleyip MySQL’i yeniden başlatmanız gerekir. Aktifse en çok CPU tüketen sorguları şu şekilde bulabilirsiniz:

mysql -u root -p << 'EOF'
SELECT
    digest_text,
    count_star AS execution_count,
    round(avg_timer_wait / 1000000000, 2) AS avg_exec_time_ms,
    round(sum_timer_wait / 1000000000, 2) AS total_exec_time_ms,
    round(sum_rows_examined / count_star, 0) AS avg_rows_examined,
    sum_rows_sent AS total_rows_sent
FROM
    performance_schema.events_statements_summary_by_digest
ORDER BY
    sum_timer_wait DESC
LIMIT 20;
EOF

Bu sorgu size toplam çalışma süresine göre sıralanmış sorguları verir. avg_rows_examined değeri yüksekse o sorgu muhtemelen tam tablo taraması yapıyordur, yani indeks kullanmıyordur.

Slow Query Log: En Güvenilir Kaynak

Slow query log, MySQL’in kendi içinde tuttuğu ve yavaş sorguları kaydeden bir log mekanizmasıdır. Üretim ortamında mutlaka aktif olmalıdır. Durumunu kontrol edelim:

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

Aktif değilse çalışırken açmak için:

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

long_query_time = 1 ile 1 saniyeden uzun süren tüm sorguları logluyoruz. Yoğun bir production ortamında bu değeri 2-3 saniyeye çekebilirsiniz, aksi halde log çok büyür.

Log dosyası oluşmaya başladıktan sonra mysqldumpslow aracıyla analiz edelim:

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

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

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

mysqldumpslow çıktısı benzer sorguları gruplar ve parametreleri soyutlar. Örneğin WHERE id = 5 ve WHERE id = 7 aynı grup altında gösterilir.

pt-query-digest: Daha Derin Analiz

Eğer Percona Toolkit kuruluysa pt-query-digest çok daha detaylı analiz sağlar. Kurulum için:

# Debian/Ubuntu
apt-get install percona-toolkit

# RHEL/CentOS
yum install percona-toolkit

Slow query log’unu analiz etmek için:

pt-query-digest /var/log/mysql/slow.log > /tmp/query_report.txt
cat /tmp/query_report.txt | less

Bu araç her sorgu grubu için şunları gösterir: toplam çalışma süresi, ortalama süre, kaç kez çalıştığı, incelenen satır sayısı ve gerçek sorgu örnekleri. Raporun en üstüne dikkat edin: en problemli sorgular orada sıralanır.

Gerçek zamanlı izleme için slow log yerine doğrudan MySQL’i dinleyebilirsiniz:

pt-query-digest --processlist h=localhost,u=root,p=sifre --interval 0.5 --run-time 60

Bu komut 60 saniye boyunca process list’i izler ve en yavaş sorguları raporlar. Sorun aktif olarak yaşanırken kullanmak için idealdir.

EXPLAIN ile Sorgu Planını Okumak

Problematik sorguyu bulduktan sonra asıl iş başlıyor: neden yavaş? EXPLAIN bize sorgunun nasıl çalıştığını gösterir:

mysql -u root -p veritabani_adi << 'EOF'
EXPLAIN SELECT
    u.username,
    o.order_date,
    o.total_amount
FROM
    users u
    JOIN orders o ON u.id = o.user_id
WHERE
    o.status = 'pending'
    AND o.created_at > '2024-01-01'
ORDER BY
    o.total_amount DESCG
EOF

EXPLAIN çıktısında en çok dikkat etmeniz gereken alanlar:

  • type: ALL görüyorsanız tam tablo taraması var, bu ciddi bir sorun. ref, range, eq_ref değerleri iyidir.
  • key: Hangi indeksin kullanıldığını gösterir. NULL ise indeks kullanılmıyor.
  • rows: Kaç satır inceleneceğinin tahmini. Milyonlarca satır görüyorsanız problem büyük.
  • Extra: Using filesort veya Using temporary görüyorsanız ek kaynak tüketimi var.

EXPLAIN ANALYZE ise gerçek çalışma süresini de gösterir (MySQL 8.0+):

mysql -u root -p veritabani_adi -e "EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending'G"

sys Schema ile Hızlı Teşhis

MySQL 5.7+ versiyonlarda built-in gelen sys schema, DBA’lerin favorisidir. Kullanımı son derece basit:

mysql -u root -p << 'EOF'
-- En çok kaynak tüketen sorgular
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10G

-- Tam tablo taraması yapan sorgular
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10G

-- Geçici tablo kullanan sorgular
SELECT
    query,
    exec_count,
    total_latency,
    tmp_tables,
    tmp_disk_tables
FROM
    sys.statements_with_temp_tables
ORDER BY
    total_latency DESC
LIMIT 10G
EOF

statements_with_full_table_scans görünümü özellikle değerlidir: indeks kullanmayan sorgular burada net biçimde listelenir.

Gerçek Dünya Senaryosu: E-Ticaret Sitesi Krizi

Geçen yıl bir e-ticaret müşterisinde tam bu durumu yaşadık. Sabahın 9’unda Kara Cuma kampanyası başladı ve 20 dakika içinde CPU %100’e çıktı. Sırayla yaptıklarımız:

Önce process listesine baktık ve bir sorgunun 180 saniyedir çalıştığını gördük:

mysql -u root -p -e "
SELECT id, user, time, state, LEFT(info, 200) as query
FROM information_schema.processlist
WHERE command != 'Sleep'
AND time > 30
ORDER BY time DESC;
"

Sorgu şuydu: SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1). Milyonlarca ürün kaydında category_id üzerinde indeks yoktu. Geçici çözüm olarak önce bu sorguları kill ettik:

mysql -u root -p -e "
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE time > 60
AND command != 'Sleep'
AND user != 'replication';
"

Bu çıktıyı alıp ilgili process’leri sonlandırdık. Sonra indeks ekledik:

mysql -u root -p veritabani_adi -e "
CREATE INDEX idx_products_category ON products(category_id);
ANALYZE TABLE products;
"

CPU anında düştü. Ama kalıcı çözüm için sorguyu da düzelttik: IN (subquery) yerine JOIN kullanmak çok daha verimli çalışır.

Otomatik İzleme Script’i

Yüksek CPU durumlarını otomatik olarak yakalamak için basit bir Bash script’i yazalım:

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

DB_USER="monitor_user"
DB_PASS="guclu_sifre"
LOG_DIR="/var/log/mysql_monitor"
THRESHOLD=80

mkdir -p "$LOG_DIR"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# MySQL CPU kullanimi kontrol et
MYSQL_CPU=$(ps aux | grep mysqld | grep -v grep | awk '{sum += $3} END {print int(sum)}')

if [ "$MYSQL_CPU" -gt "$THRESHOLD" ]; then
    echo "UYARI: MySQL CPU kullanimi %${MYSQL_CPU} - $(date)" | tee -a "$LOG_DIR/alerts.log"

    # Calisma sorguları kaydet
    mysql -u "$DB_USER" -p"$DB_PASS" -e "
    SELECT id, user, host, db, time, state, info
    FROM information_schema.processlist
    WHERE command != 'Sleep'
    ORDER BY time DESC
    LIMIT 30G" > "$LOG_DIR/processlist_${TIMESTAMP}.log" 2>/dev/null

    # Performance schema snapshot al
    mysql -u "$DB_USER" -p"$DB_PASS" -e "
    SELECT digest_text, count_star, round(avg_timer_wait/1000000000,2) as avg_ms,
    round(sum_timer_wait/1000000000,2) as total_ms
    FROM performance_schema.events_statements_summary_by_digest
    ORDER BY sum_timer_wait DESC
    LIMIT 20G" >> "$LOG_DIR/processlist_${TIMESTAMP}.log" 2>/dev/null

    # Email bildirimi (mail komutu kuruluysa)
    if command -v mail &> /dev/null; then
        mail -s "MySQL Yuksek CPU Alarmi - %${MYSQL_CPU}" [email protected] < "$LOG_DIR/processlist_${TIMESTAMP}.log"
    fi
fi

Bu script’i cron’a ekleyin:

# Her 2 dakikada bir kontrol et
*/2 * * * * /usr/local/bin/mysql_cpu_monitor.sh

İndeks Kullanımını Analiz Etme

Bazen sorun tek bir sorgu değil, genel indeks stratejisindeki eksikliklerdir. Kullanılmayan indeksleri bulalım:

mysql -u root -p << 'EOF'
SELECT
    t.table_schema,
    t.table_name,
    s.index_name,
    s.column_name,
    s.cardinality,
    t.table_rows
FROM
    information_schema.statistics s
    JOIN information_schema.tables t
        ON s.table_schema = t.table_schema
        AND s.table_name = t.table_name
WHERE
    s.table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
    AND s.index_name != 'PRIMARY'
ORDER BY
    t.table_rows DESC,
    s.cardinality ASC
LIMIT 20;
EOF

sys.schema_unused_indexes görünümü ise son yeniden başlatmadan bu yana hiç kullanılmayan indeksleri listeler. Bu indeksler silme adaylarıdır çünkü her indeks yazma işlemlerini yavaşlatır:

mysql -u root -p -e "SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN ('mysql', 'performance_schema');"

Query Cache ve Geçici Tablo Sorunları

MySQL 5.7 ve öncesinde Query Cache aktifse ve yoğun yazma trafiği varsa bu başlı başına bir CPU sorunu yaratabilir. Durumu kontrol edin:

mysql -u root -p << 'EOF'
SHOW STATUS LIKE 'Qcache%';
SHOW VARIABLES LIKE 'query_cache%';
EOF

Qcache_lowmem_prunes değeri yüksekse Query Cache çok küçük veya çok aktif kullanılıyor demektir. Yazma yoğun sistemlerde Query Cache’i tamamen kapatmak bazen CPU’yu düşürür:

mysql -u root -p -e "SET GLOBAL query_cache_size = 0; SET GLOBAL query_cache_type = 0;"

Geçici tablolarla ilgili sorunları da kontrol edelim:

mysql -u root -p -e "
SHOW STATUS LIKE 'Created_tmp%';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
"

Created_tmp_disk_tables değeri Created_tmp_tables değerinin %20’sinden fazlaysa geçici tablolar diske yazılıyor demektir. Bu ciddi performans kaybı yaratır. tmp_table_size ve max_heap_table_size değerlerini artırmayı düşünebilirsiniz.

InnoDB Buffer Pool Kullanımı

CPU sorunları bazen aslında I/O sorunlarından kaynaklanır: buffer pool çok küçükse MySQL sürekli diskten okumak zorunda kalır ve bu CPU’yu da etkiler:

mysql -u root -p << 'EOF'
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Buffer pool hit rate hesapla
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
    AS hit_rate_percent
FROM (
    SELECT
        (SELECT variable_value FROM performance_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
        (SELECT variable_value FROM performance_schema.global_status
         WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests
) AS stats;
EOF

Hit rate %95’in altındaysa buffer pool büyütülmeli. Genel kural: toplam RAM’in %70-80’i buffer pool’a ayrılabilir. my.cnf‘e eklemek için:

# /etc/mysql/mysql.conf.d/mysqld.cnf
innodb_buffer_pool_size = 8G  # 12GB RAM'li sunucu için
innodb_buffer_pool_instances = 8  # Her instance 1GB

Uzun Süren Transaction’ları Tespit Etme

Uzun süren transaction’lar kilit tutar ve diğer sorguların beklemesine neden olur. Bu hem CPU hem de genel performansı etkiler:

mysql -u root -p << 'EOF'
SELECT
    trx_id,
    trx_started,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
    trx_query,
    trx_rows_locked,
    trx_rows_modified
FROM
    information_schema.innodb_trx
WHERE
    trx_started < NOW() - INTERVAL 60 SECOND
ORDER BY
    trx_started ASC;
EOF

60 saniyeden uzun süren transaction’lar varsa ciddi bir sorun var demektir. Uygulamanızda commit’siz kalan veya hata sonrası rollback yapılmayan işlemler olabilir.

Proaktif Önlemler

Sorun çıktıktan sonra değil, çıkmadan önce önlem almak her zaman daha iyidir.

  • Slow query log’u her zaman açık tutun: Üretimde bile, 2-3 saniyelik threshold ile çalıştırmak makul
  • Her yeni deploy’dan sonra EXPLAIN çalıştırın: Kod değişikliği bazen beklenmedik plan değişikliklerine yol açar
  • Percona Monitoring and Management (PMM) kurun: Ücretsiz, kapsamlı ve görsel bir izleme çözümü
  • innodb_monitor_enable‘ı aktif edin: Daha detaylı InnoDB metrikleri sağlar
  • Application level’da sorgu timeout’u tanımlayın: Sonsuz çalışan sorguların önüne geçer
  • max_execution_time kullanın (MySQL 5.7.4+): Sunucu seviyesinde sorgu timeout’u
mysql -u root -p -e "SET GLOBAL max_execution_time = 30000;"
# 30 saniyeden uzun sorgular otomatik kesilir

Sonuç

MySQL’de yüksek CPU sorunu yaşandığında panik değil sistematik yaklaşım işe yarar. Önce SHOW PROCESSLIST ile anlık durumu yakalayın, slow query log ile problematik sorguları bulun, EXPLAIN ile neden yavaş olduklarını anlayın ve indeks veya sorgu optimizasyonuyla kalıcı çözüm üretin.

Araçlar açısından bir özet yapacak olursak: mysqldumpslow hızlı ilk analiz için, pt-query-digest derin analiz için, sys schema günlük rutin kontroller için ve Performance Schema programatik izleme için idealdir.

En önemli nokta şu: reaktif değil proaktif olun. Slow query log’unu açık tutun, düzenli olarak analiz edin ve yeni sorgularınızı production’a gitmeden önce test ortamında EXPLAIN ile gözden geçirin. Bu alışkanlıkları kazandığınızda gece yarısı alarmlara çok daha az maruz kalırsınız.

Bir yanıt yazın

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