Eine optimierte MySQL-Konfiguration kann die Performance deutlich verbessern. Die Standardwerte sind oft zu konservativ für moderne Server.

Konfigurationsdatei finden

# MySQL
mysql --help | grep "Default options" -A 1
# Typisch: /etc/mysql/my.cnf, /etc/my.cnf

# MariaDB
mariadbd --help | grep "Default options" -A 1

# Aktive Konfiguration anzeigen
mysql -e "SHOW VARIABLES;"

Die wichtigsten Parameter

InnoDB Buffer Pool

Der wichtigste Parameter für InnoDB-Performance:

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

[mysqld]
# 70-80% des verfügbaren RAM für dedizierte DB-Server
# 50% wenn andere Dienste laufen
innodb_buffer_pool_size = 4G

# Bei > 1GB Buffer Pool: mehrere Instances
innodb_buffer_pool_instances = 4

Faustregel für innodb_buffer_pool_size

| Server-RAM | Buffer Pool (dediziert) | Buffer Pool (mit Webserver) | |------------|-------------------------|----------------------------| | 2 GB | 1 GB | 512 MB | | 4 GB | 3 GB | 1.5 GB | | 8 GB | 6 GB | 3 GB | | 16 GB | 12 GB | 6 GB | | 32 GB | 24 GB | 12 GB |

InnoDB Log-Einstellungen

[mysqld]
# Redo-Log Größe (für Write-Performance)
# Größer = besser für Writes, länger Recovery
innodb_log_file_size = 512M

# Log-Buffer
innodb_log_buffer_size = 64M

# Flush-Verhalten
# 1 = ACID-konform (sicher, langsamer)
# 2 = Flush jede Sekunde (schneller, minimal unsicherer)
innodb_flush_log_at_trx_commit = 1

Verbindungen

[mysqld]
# Maximale gleichzeitige Verbindungen
max_connections = 200

# Timeout für inaktive Verbindungen
wait_timeout = 300
interactive_timeout = 300

# Thread-Cache
thread_cache_size = 50

Query Cache (MySQL 5.7, MariaDB)

[mysqld]
# Für MySQL 8: Query Cache entfernt!
# Für MySQL 5.7 und MariaDB:

# Query Cache aktivieren
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Bei vielen Writes: Query Cache deaktivieren
# query_cache_type = 0
# query_cache_size = 0

Hinweis: MySQL 8.0 hat den Query Cache entfernt. Bei vielen Writes ist er ohnehin kontraproduktiv.

Temporäre Tabellen

[mysqld]
# Größe für In-Memory temp tables
tmp_table_size = 256M
max_heap_table_size = 256M

# Temporäres Verzeichnis (am besten auf SSD)
tmpdir = /var/lib/mysql/tmp

Table Cache

[mysqld]
# Anzahl offener Tabellen im Cache
table_open_cache = 4000

# Definition-Cache
table_definition_cache = 2000

Vollständige Beispielkonfiguration

Für 8 GB RAM (dedizierter DB-Server)

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

[mysqld]
# === InnoDB Engine ===
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 4
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1

# === Connections ===
max_connections = 200
thread_cache_size = 50
wait_timeout = 300
interactive_timeout = 300

# === Memory ===
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 4000
table_definition_cache = 2000

# === Query Cache (nicht für MySQL 8) ===
# query_cache_type = 0
# query_cache_size = 0

# === Logging ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# === Networking ===
max_allowed_packet = 64M

Für 4 GB RAM (mit Webserver)

[mysqld]
innodb_buffer_pool_size = 1536M
innodb_buffer_pool_instances = 2
innodb_log_file_size = 256M
innodb_log_buffer_size = 32M

max_connections = 100
thread_cache_size = 25
wait_timeout = 180

tmp_table_size = 128M
max_heap_table_size = 128M
table_open_cache = 2000

Performance analysieren

Aktuellen Status prüfen

-- Buffer Pool Nutzung
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Wichtig: Read Hit Ratio
-- Sollte > 99% sein
SELECT
    (1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100
    AS buffer_pool_hit_ratio
FROM (
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_reads
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) a,
(
    SELECT
        VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
    FROM performance_schema.global_status
    WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) b;

-- Verbindungen
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Max_used_connections';

-- Temp Tables auf Disk
SHOW STATUS LIKE 'Created_tmp%';

Slow Query Log analysieren

# Aktivieren
mysql -e "SET GLOBAL slow_query_log = 'ON';"
mysql -e "SET GLOBAL long_query_time = 2;"

# Analysieren
mysqldumpslow /var/log/mysql/slow.log

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

mysqltuner verwenden

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

# Ausführen
./mysqltuner.pl --user root --pass your_password

# Gibt konkrete Empfehlungen

Index-Optimierung

Fehlende Indizes finden

-- Queries ohne Index
SHOW STATUS LIKE 'Select_scan';
SHOW STATUS LIKE 'Select_full_join';

-- In performance_schema (MySQL 5.7+)
SELECT * FROM sys.statements_with_full_table_scans
ORDER BY no_index_used_count DESC
LIMIT 10;

Index erstellen

-- Index auf häufig gefilterte Spalten
CREATE INDEX idx_user_email ON users(email);

-- Zusammengesetzter Index
CREATE INDEX idx_order_user_date ON orders(user_id, created_at);

-- Index-Nutzung prüfen
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

Änderungen anwenden

Online (ohne Neustart)

-- Einige Variablen können live geändert werden
SET GLOBAL innodb_buffer_pool_size = 6442450944;  -- 6GB
SET GLOBAL max_connections = 200;

-- Prüfen welche dynamisch sind
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Mit Neustart

# Konfiguration bearbeiten
nano /etc/mysql/mysql.conf.d/performance.cnf

# Syntax prüfen
mysqld --validate-config

# Neustart
systemctl restart mysql

Monitoring

Wichtige Metriken

-- Queries pro Sekunde
SHOW STATUS LIKE 'Queries';

-- InnoDB Metriken
SHOW ENGINE INNODB STATUS\G

-- Aktive Prozesse
SHOW PROCESSLIST;

Mit Performance Schema

-- Top Queries nach Zeit
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

-- Wartezeiten
SELECT * FROM sys.wait_classes_global_by_latency;

Häufige Probleme

"Too many connections"

# max_connections erhöhen
max_connections = 300

# Aber auch prüfen ob Connections geschlossen werden
wait_timeout = 180

Langsame Queries

1. Slow Query Log aktivieren 2. EXPLAIN auf langsame Queries 3. Fehlende Indizes hinzufügen 4. Query optimieren

Hohe CPU-Last

# Laufende Queries prüfen
mysql -e "SHOW PROCESSLIST"

# Problematische Queries beenden
mysql -e "KILL query_id"

Disk I/O hoch

# Buffer Pool vergrößern
innodb_buffer_pool_size = größerer_wert

# Flush-Verhalten anpassen (wenn Datenverlust akzeptabel)
innodb_flush_log_at_trx_commit = 2

Checkliste

□ innodb_buffer_pool_size auf 50-70% RAM
□ Slow Query Log aktiviert
□ Fehlende Indizes hinzugefügt
□ max_connections angepasst
□ wait_timeout reduziert
□ mysqltuner ausgeführt
□ Buffer Pool Hit Ratio > 99%
□ Keine Full Table Scans auf große Tabellen

Fazit

Die wichtigste Einstellung ist innodb_buffer_pool_size – sie sollte so groß wie möglich sein, ohne andere Dienste zu beeinträchtigen. Aktivieren Sie das Slow Query Log, um problematische Queries zu finden. Nutzen Sie Tools wie mysqltuner für konkrete Empfehlungen. Testen Sie Änderungen zunächst in einer Testumgebung, bevor Sie sie auf Produktivsystemen anwenden.