MySQL Performance Tuning ist essentiell für schnelle Webanwendungen. Die richtige Konfiguration und Query-Optimierung können die Geschwindigkeit vervielfachen.

Performance-Analyse

Slow Query Log aktivieren

-- Prüfen ob aktiv
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Aktivieren
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Sekunden
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
# /etc/mysql/mysql.conf.d/mysqld.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

mysqldumpslow

# Top 10 langsame Queries
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Nach Anzahl sortiert
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Mit Lock-Zeit
mysqldumpslow -s l -t 10 /var/log/mysql/slow.log

pt-query-digest

# Installation
apt install percona-toolkit

# Analyse
pt-query-digest /var/log/mysql/slow.log

# Nur bestimmte Queries
pt-query-digest --filter '$event->{fingerprint} =~ m/SELECT/' /var/log/mysql/slow.log

EXPLAIN analysieren

EXPLAIN verwenden

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Ausführungsdetails
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- JSON-Format (detaillierter)
EXPLAIN FORMAT=JSON SELECT ...;

Output verstehen

| Spalte | Bedeutung | |--------|-----------| | type | Join-Typ (ALL=schlecht, const=gut) | | possible_keys | Mögliche Indizes | | key | Verwendeter Index | | rows | Geschätzte Zeilen | | Extra | Zusätzliche Info |

Type-Werte (Qualität)

| Type | Beschreibung | Qualität | |------|--------------|----------| | system | Eine Zeile | Beste | | const | Genau eine Zeile (Primary Key) | Sehr gut | | eq_ref | Eine Zeile pro Join | Gut | | ref | Mehrere Zeilen via Index | Gut | | range | Index-Bereich | OK | | index | Full Index Scan | Schlecht | | ALL | Full Table Scan | Schlechteste |

Index-Optimierung

Index erstellen

-- Einfacher Index
CREATE INDEX idx_email ON users(email);

-- Zusammengesetzter Index
CREATE INDEX idx_status_created ON orders(status, created_at);

-- Prefix-Index (für lange Strings)
CREATE INDEX idx_name ON users(name(20));

-- Fulltext-Index
CREATE FULLTEXT INDEX idx_content ON articles(title, content);

Index-Nutzung prüfen

-- Ungenutzte Indizes
SELECT * FROM sys.schema_unused_indexes;

-- Index-Statistiken
SELECT * FROM sys.schema_index_statistics
ORDER BY rows_selected DESC;

-- Fehlende Indizes
SELECT * FROM sys.statements_with_full_table_scans;

Index-Tipps

-- WHERE-Klausel
-- Index auf: status, created_at (in Reihenfolge der Query)
SELECT * FROM orders WHERE status = 'active' AND created_at > '2024-01-01';

-- JOIN
-- Index auf Foreign Key
SELECT * FROM orders o JOIN users u ON o.user_id = u.id;

-- ORDER BY
-- Index auf sort-Spalte
SELECT * FROM products ORDER BY price DESC LIMIT 10;

-- Covering Index (alle Spalten im Index)
CREATE INDEX idx_covering ON users(email, name, status);
SELECT email, name, status FROM users WHERE email LIKE 'a%';

Query-Optimierung

SELECT optimieren

-- Schlecht: SELECT *
SELECT * FROM users WHERE id = 1;

-- Besser: Nur benötigte Spalten
SELECT id, name, email FROM users WHERE id = 1;

-- Schlecht: Funktion auf indizierte Spalte
SELECT * FROM users WHERE YEAR(created_at) = 2024;

-- Besser: Range-Query
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

JOIN optimieren

-- Schlecht: Subquery
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

-- Besser: JOIN
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

-- Index auf Join-Spalten
CREATE INDEX idx_user_id ON orders(user_id);

LIMIT und Pagination

-- Schlecht: Große Offsets
SELECT * FROM products ORDER BY id LIMIT 10000, 10;

-- Besser: Keyset Pagination
SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 10;

-- Oder: Covering Index
SELECT id FROM products ORDER BY id LIMIT 10000, 10;
SELECT * FROM products WHERE id IN (...);

COUNT optimieren

-- Schlecht: COUNT(*)
SELECT COUNT(*) FROM large_table;

-- Besser: Approximation
SELECT TABLE_ROWS FROM information_schema.TABLES
WHERE TABLE_NAME = 'large_table';

-- Oder: Cache verwenden

Server-Konfiguration

InnoDB Buffer Pool

# /etc/mysql/mysql.conf.d/mysqld.cnf

# 70-80% des verfügbaren RAM
innodb_buffer_pool_size = 4G

# Mehrere Instanzen (für > 1GB)
innodb_buffer_pool_instances = 4

# Neue Seiten zuerst warm halten
innodb_old_blocks_time = 1000

Query Cache (MySQL 5.7)

# Deaktiviert in MySQL 8.0+
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

Temporäre Tabellen

tmp_table_size = 64M
max_heap_table_size = 64M

Verbindungen

max_connections = 200
wait_timeout = 600
interactive_timeout = 600

Thread-Cache

thread_cache_size = 16

Join und Sort Buffer

join_buffer_size = 256K
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 512K

InnoDB Log

innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2  # 1=sicher, 2=schneller

Beispiel-Konfiguration

Server mit 8GB RAM

# /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# InnoDB
innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# Connections
max_connections = 200
thread_cache_size = 32

# Memory
tmp_table_size = 128M
max_heap_table_size = 128M
join_buffer_size = 256K
sort_buffer_size = 256K

# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

# Binary Log (für Replikation)
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7

Monitoring

Status-Variablen

-- Alle Statuswerte
SHOW GLOBAL STATUS;

-- Bestimmte
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Verbindungen
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

Buffer Pool Effizienz

-- Hit Ratio sollte > 99% sein
SELECT
  (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
  AS buffer_pool_hit_ratio
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;

mysqltuner

# Installation
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# Ausführen
perl mysqltuner.pl --host localhost --user root --pass password

Table Maintenance

OPTIMIZE TABLE

-- Defragmentieren
OPTIMIZE TABLE users;

-- Alle Tabellen
mysqlcheck -o --all-databases -u root -p

ANALYZE TABLE

-- Statistiken aktualisieren
ANALYZE TABLE users;

CHECK TABLE

-- Tabelle prüfen
CHECK TABLE users;

Replikation Performance

Parallel Replication

# Slave
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

Binary Log

binlog_format = ROW
sync_binlog = 0       # Schneller, weniger sicher
# sync_binlog = 1     # Sicher, langsamer

Common Issues

Lock Waits

-- Blockierende Queries
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- Locks anzeigen
SELECT * FROM performance_schema.data_locks;

-- Transaktion beenden
KILL connection_id;

Table Locks

-- Locks prüfen
SHOW OPEN TABLES WHERE In_use > 0;

-- Lock-Wartezeit
SHOW GLOBAL STATUS LIKE 'Table_locks%';

Connections

-- Zu viele Verbindungen
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

-- Idle Connections beenden
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.processlist
WHERE command = 'Sleep' AND time > 300;

Zusammenfassung

| Konfiguration | Empfehlung | |---------------|------------| | innodb_buffer_pool_size | 70-80% RAM | | innodb_log_file_size | 256M-1G | | max_connections | Nach Bedarf | | thread_cache_size | 8-32 |

| Tool | Verwendung | |------|------------| | EXPLAIN | Query-Analyse | | mysqldumpslow | Slow Log analysieren | | pt-query-digest | Detaillierte Analyse | | mysqltuner | Server-Tuning |

| Metrik | Zielwert | |--------|----------| | Buffer Pool Hit Ratio | > 99% | | Slow Queries | Minimieren | | Threads_connected | < max_connections | | Table_locks_waited | Minimieren |

Fazit

MySQL Performance Tuning ist ein iterativer Prozess. Die größten Gewinne kommen oft von korrekten Indizes und optimierten Queries. Die Server-Konfiguration sollte an die Hardware angepasst werden, wobei der InnoDB Buffer Pool der wichtigste Parameter ist. Regelmäßiges Monitoring und Analyse der Slow Query Logs helfen, Probleme frühzeitig zu erkennen.