Q&A Klassiker: Hohe Load und MySQL Slow Queries

Kategorie: Q&A · Channel: #klassiker

Format: Bühnenspiel – Admin · Angreifer · Kunde

Die Situation

Uhrzeit:  14:23 Uhr (Mittagspeak)
Alarm:    Load Average > 40 auf 8-Kern-Server
Symptom:  Website träge, Shop-Bestellungen brechen ab
Kunde:    meldet sich um 14:31 Uhr

👨‍💻 Admin-Perspektive: Was sehe ich, was tue ich?

Schritt 1: Lagebild – wie schlimm ist es?

# Erster Blick: Load und Ressourcen
uptime
# 14:23:41 up 47 days, load average: 41.23, 38.91, 29.44
#                                     ↑ 1min  ↑ 5min  ↑ 15min
# → Load steigt seit ~15 Minuten kontinuierlich

# Load interpretieren:
# Load 1.0 auf 1 CPU = 100% ausgelastet
# Load 8.0 auf 8 CPUs = 100% ausgelastet
# Load 41 auf 8 CPUs = 5× überlastet!

# Was genau erzeugt die Load?
top -b -n 1 | head -30
# oder moderner:
htop

# Sind es CPU-Prozesse oder Warte-Prozesse (I/O)?
# In top: %us (user) %sy (system) %wa (iowait)
# Hoher %wa → I/O ist der Engpass, nicht CPU

Schritt 2: MySQL im Fokus

# MySQL Prozessliste – was läuft gerade?
mysql -e "SHOW PROCESSLIST\G" | head -80
# oder schöner:
mysql -e "SELECT id, user, host, db, command, time, state, 
          LEFT(info, 80) as query 
          FROM information_schema.processlist 
          WHERE command != 'Sleep' 
          ORDER BY time DESC;"

# Typische Ausgabe die Probleme zeigt:
# | id  | time | state             | query                    |
# | 142 | 847  | Waiting for lock  | UPDATE orders SET ...    |
# | 143 | 846  | Waiting for lock  | UPDATE orders SET ...    |
# | 144 | 845  | Waiting for lock  | UPDATE orders SET ...    |
# → Lock-Contention! Ein Query blockiert alle anderen

# Wie viele Verbindungen?
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -e "SHOW VARIABLES LIKE 'max_connections';"
# Wenn Threads_connected nahe max_connections → Verbindungs-Erschöpfung

Schritt 3: Slow Query Log aktivieren und lesen

# Slow Query Log status:
mysql -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -e "SHOW VARIABLES LIKE 'long_query_time';"

# Live aktivieren (ohne Restart!):
mysql -e "SET GLOBAL slow_query_log = 'ON';"
mysql -e "SET GLOBAL long_query_time = 1;"         # Queries > 1s loggen
mysql -e "SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';"

# Slow Log lesen und auswerten:
tail -50 /var/log/mysql/slow.log

# mysqldumpslow – Zusammenfassung der schlimmsten Queries:
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# -s t = sortiert nach Gesamtzeit
# -t 10 = Top 10

# pt-query-digest (Percona Toolkit) – noch besser:
pt-query-digest /var/log/mysql/slow.log | head -100

Schritt 4: Den schuldigen Query identifizieren

# EXPLAIN – was macht MySQL mit dem Query?
mysql -e "EXPLAIN SELECT * FROM orders 
          WHERE status = 'pending' 
          AND created_at > '2026-01-01'\G"

# Kritische Ausgabefelder:
# type: ALL     → FULL TABLE SCAN! Kein Index genutzt
# rows: 2847291 → scannt 2,8 Millionen Zeilen
# key: NULL     → kein Index

# Index fehlt? So hinzufügen (online, ohne Lock bei großen Tabellen):
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);
# oder für große Tabellen ohne Downtime:
pt-online-schema-change --alter "ADD INDEX idx_status_created (status, created_at)" \
  D=shopdb,t=orders --execute

Schritt 5: Lock-Contention lösen

# Welcher Query hält einen Lock?
mysql -e "SELECT * FROM information_schema.innodb_trx\G"
mysql -e "SELECT * FROM information_schema.innodb_lock_waits\G"

# Blockierenden Prozess killen (Notfall):
mysql -e "KILL QUERY 142;"     # nur den Query abbrechen
mysql -e "KILL 142;"           # gesamte Verbindung trennen

# InnoDB Status – detaillierte Lock-Infos:
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 30 "LATEST DETECTED DEADLOCK"

Schritt 6: I/O-Engpass diagnose

# Ist MySQL I/O-gebunden?
iostat -x 1 5
# %util nahe 100% → Disk ist Engpass

# Welche Datei schreibt/liest am meisten?
iotop -o -b -n 3

# MySQL I/O-Statistiken:
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';"
# Innodb_buffer_pool_reads: viele → zu wenig RAM für Buffer Pool
# Faustregel: Buffer Pool sollte 70-80% des verfügbaren RAM nutzen

# Buffer Pool Größe anpassen:
# /etc/mysql/mysql.conf.d/mysqld.cnf:
# innodb_buffer_pool_size = 4G    # auf Server mit 8G RAM

Schritt 7: Sofortentlastung

# Query-Cache prüfen (MySQL < 8.0):
mysql -e "SHOW VARIABLES LIKE 'query_cache%';"

# Verbindungen begrenzen (wenn zu viele):
mysql -e "SET GLOBAL max_connections = 200;"

# Langläufer killen (alle Queries > 60 Sekunden):
mysql -e "SELECT CONCAT('KILL ', id, ';') 
          FROM information_schema.processlist 
          WHERE time > 60 AND command != 'Sleep';" | \
  mysql -f

# MySQL-Status-Überblick:
mysqladmin status
mysqladmin extended-status | grep -E "Slow|Thread|Question|Connect"

🦹 Angreifer-Perspektive: Wie hätte ich das verursacht?

Methode 1: Slow Query Injection

Angreifer schickt absichtlich ineffiziente Queries:
→ SELECT * FROM products WHERE description LIKE '%suchbegriff%'
→ Kein Index auf description → Full Table Scan
→ Bei 100 gleichzeitigen Requests → 100 Full Table Scans
→ Load explodiert

Diagnose:
Slow Log zeigt viele identische Queries mit type: ALL
nginx access.log zeigt viele Requests auf Such-Endpoint

Methode 2: Connection Exhaustion

Angreifer öffnet viele Verbindungen ohne sie zu schließen:
→ max_connections erschöpft
→ neue legitime Verbindungen: "Too many connections"
→ Applikation kann keine DB-Verbindung aufbauen → 500

Diagnose:
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
netstat -an | grep 3306 | grep ESTABLISHED | wc -l

Methode 3: Lock-Flooding

Angreifer startet viele UPDATE/INSERT Transaktionen:
→ Hält Locks auf wichtigen Tabellen
→ alle anderen Queries warten auf Lock-Release
→ Queue wächst, Load steigt, Website hängt

Diagnose:
SHOW ENGINE INNODB STATUS → viele wartende Transaktionen
information_schema.innodb_trx → viele aktive Transaktionen

Methode 4: Cronjob-Kollision (kein Angreifer, aber klassisch)

Täglich um 14:00 läuft ein Report-Cronjob:
→ SELECT mit JOIN über 5 Tabellen, kein Index
→ läuft 15 Minuten, hält Read-Locks
→ Online-Shop-Queries warten

Diagnose:
crontab -l → was läuft um 14:00?
SHOW PROCESSLIST → Report-Query seit 900 Sekunden aktiv?

👔 Kunden-Perspektive: Was erlebe ich?

14:31 Uhr – Nachricht:

"Hallo, seit ca. 14:20 Uhr läuft unser Shop sehr langsam.
 Bestellungen werden teilweise nicht abgeschlossen.
 Wir sehen im Backend dass neue Bestellungen fehlen.
 Das ist Mittagszeit – wir haben gerade viel Traffic.
 Bitte dringend prüfen!"

Was der Kunde technisch erlebt:

Browser:     Seite lädt 30+ Sekunden → Timeout
             "504 Gateway Timeout" bei Bestellabschluss

Applikation: Verbindung zur DB dauert > 30s
             ORM wirft "Lock wait timeout exceeded"
             Bestellung wird nicht gespeichert

Folgen:
→ Kunde bricht ab
→ Bestellung verloren
→ Bei Umsatz-kritischer Zeit: direkter Schaden

Kommunikation:

14:35 Uhr:
"Wir haben eine Datenbanküberlastung identifiziert.
 Ursache: ein ineffizienter Query blockiert andere Prozesse.
 Wir beheben das gerade. Schätzung: 10-20 Minuten."

14:47 Uhr:
"Die Situation hat sich normalisiert. Load ist wieder normal.
 Ursache war ein fehlender Index der täglich um 14:00 Uhr
 durch einen Report-Job ausgelöst wurde.
 Wir haben den Index ergänzt und den Report-Job auf 02:00 Uhr verlegt."

🎯 Erkenntnis – Drei Perspektiven zusammen

Perspektive	Kernaussage
👨‍💻 Admin	`SHOW PROCESSLIST` + `EXPLAIN` zeigen wo der Brand ist
🦹 Angreifer	Full Table Scans sind billige DoS-Vektoren ohne Index
👔 Kunde	504-Fehler um 14:00 = täglich um 14:00 läuft ein Report

🔧 Prävention

# 1. Slow Query Log dauerhaft aktivieren
# /etc/mysql/mysql.conf.d/mysqld.cnf:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 2. Cronjobs auf Nebenzeiten verlegen
# Keine schweren Reports zwischen 08:00 und 20:00!
# crontab: 0 2 * * * /opt/scripts/daily_report.sh

# 3. Indizes regelmäßig prüfen
mysql -e "SELECT table_name, index_name, stat_value 
          FROM mysql.innodb_index_stats 
          WHERE stat_name = 'n_diff_pfx01' 
          ORDER BY stat_value DESC LIMIT 20;"

# 4. Connection Pooling in der Applikation
# Direkte DB-Verbindungen pro Request → schlecht
# Connection Pool (z.B. PgBouncer für PostgreSQL, ProxySQL für MySQL)

# 5. Read Replicas für Reports
# Schwere SELECT-Queries auf Read-Replica
# → kein Impact auf Master / Online-Shop

# 6. Monitoring: MySQL Metriken
# → Threads_connected, Slow_queries, Innodb_row_lock_waits
# → Alert bei Slow_queries > 10/min

📋 Schnellreferenz: Hohe Load / MySQL – Einzeiler

uptime                                              # Load anzeigen
top -b -n 1 | head -20                             # Was erzeugt Load?
mysql -e "SHOW PROCESSLIST\G"                       # MySQL-Aktivität
mysql -e "SET GLOBAL slow_query_log='ON';"          # Slow Log aktivieren
mysqldumpslow -s t -t 5 /var/log/mysql/slow.log    # Schlimmste Queries
mysql -e "SHOW ENGINE INNODB STATUS\G" | grep -A 20 "TRANSACTIONS"
mysql -e "SELECT * FROM information_schema.innodb_trx\G"  # Locks
mysql -e "KILL 142;"                               # Prozess killen
iostat -x 1 3                                      # I/O-Engpass?
iotop -o -b -n 3                                   # Wer schreibt/liest?