Magento MySQL-Einstellungen richtig konfigurieren

1. sql_mode: Die von Magento geforderten Modi

Die Einstellung sql_mode bestimmt, wie MySQL SQL-Syntax und Datenwerte behandelt. Magento ist hier sehr präzise: Es benötigt bestimmte Modi, um korrekt zu funktionieren und Datenintegrität zu gewährleisten. Ein falscher sql_mode ist eine der häufigsten Ursachen für obskure Fehler nach Servermigrationen.

Die von Magento geforderten Modi und ihre Bedeutung:

• ONLY_FULL_GROUP_BY: Verhindert mehrdeutige GROUP BY-Queries. Alle SELECT-Spalten müssen entweder in GROUP BY enthalten oder Aggregatfunktionen sein.
• STRICT_TRANS_TABLES: Bei ungültigen Werten wirft MySQL einen Fehler, statt einen Defaultwert zu setzen.
• NO_ZERO_IN_DATE: Verhindert Datumswerte mit Nullen (z.B. 2025-00-01).
• NO_ZERO_DATE: Verhindert 0000-00-00 als gültiges Datum.
• ERROR_FOR_DIVISION_BY_ZERO: Division durch 0 wirft einen Fehler statt NULL zurückzugeben.
• NO_ENGINE_SUBSTITUTION: Wenn eine Storage Engine nicht verfügbar ist, wird ein Fehler geworfen statt eine alternative zu verwenden.

-- Aktuellen sql_mode prüfen
SELECT @@sql_mode;

-- sql_mode global setzen (bis zum nächsten Neustart)
SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- Magento-Prüfung:
-- bin/magento setup:db:status
-- meldet wenn der sql_mode nicht den Anforderungen entspricht
    

2. innodb_buffer_pool_size: Der wichtigste Performance-Hebel

Der InnoDB Buffer Pool hält InnoDB-Datenseiten und Indizes im RAM, sodass häufig genutzte Daten nicht von der Festplatte gelesen werden müssen. Er ist der effektivste einzelne Performance-Hebel für eine Magento-Datenbank.

Empfehlung: 70–80% des verfügbaren RAMs auf einem dedizierten Datenbankserver. Auf einem 16-GB-Server also 11–13 GB. Die Buffer Pool Hit Ratio sollte dauerhaft über 99% liegen.

-- Buffer Pool Hit Ratio berechnen (sollte > 99% sein)
SELECT
    (1 - (
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
        /
        (SELECT VARIABLE_VALUE FROM performance_schema.global_status
         WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    )) * 100 AS hit_ratio_percent;

-- Aktuellen Wert prüfen
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

-- In MySQL 8: Buffer Pool kann dynamisch angepasst werden
SET GLOBAL innodb_buffer_pool_size = 12884901888;  -- 12 GB in Bytes
    

3. max_connections: Richtig berechnen statt raten

Ein zu niedriger Wert führt zu "Too many connections"-Fehlern in Stoßzeiten. Ein zu hoher Wert verschwendet RAM, da jede Verbindung Puffer allokiert. Die korrekte Berechnung basiert auf tatsächlichen PHP-FPM-Worker-Zahlen.

-- max_connections Berechnung
-- (PHP-FPM-Worker × Web-Server) + Admin + Cron + Monitoring + 20% Puffer
-- Beispiel: 2 Web-Server × 50 PHP-FPM-Worker = 100
--           + 10 Admin/CLI + 5 Cron + 5 Monitoring = 120 + 20% Puffer = 144

-- Aktuell und historisches Maximum prüfen
SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';  -- historisches Max
SHOW GLOBAL STATUS LIKE 'Threads_connected';     -- aktuelle Verbindungen

-- Pro-Verbindungs-RAM-Verbrauch schätzen (aus einzelnen Buffer-Einstellungen)
SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'read_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';
-- Schätzung: ~2-4 MB pro aktiver Verbindung bei normaler Last
-- 150 Verbindungen × 4 MB = 600 MB reservierter Verbindungs-RAM
    

4. tmp_table_size und max_heap_table_size

Viele SQL-Operationen erstellen intern temporäre Tabellen: GROUP BY auf nicht-indexierten Spalten, ORDER BY auf berechneten Feldern, DISTINCT, Subqueries. Sind die Tabellen zu groß für den RAM, werden sie auf Disk ausgelagert – mit deutlich schlechterer Performance.

-- Aktuelle Einstellungen prüfen
SHOW VARIABLES LIKE 'tmp_table_size';       -- Standard: 16M – oft zu wenig
SHOW VARIABLES LIKE 'max_heap_table_size';  -- Muss identisch mit tmp_table_size sein

-- Wie viele tmp-Tabellen auf Disk ausgelagert wurden (seit letztem Neustart)
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- Verhältnis: disk_tables / total_tables sollte unter 10% bleiben
-- Wenn Ratio hoch ist: Werte erhöhen

-- Dynamisch erhöhen (zum Testen ohne Neustart)
SET GLOBAL tmp_table_size = 268435456;       -- 256M
SET GLOBAL max_heap_table_size = 268435456;  -- 256M - beide identisch setzen!
    

5. max_allowed_packet für große Inhalte

Magento speichert in manchen Tabellen sehr große Datenwerte: CMS-Seiteninhalte in cms_block und cms_page, E-Mail-Templates, Produktbeschreibungen und importierte Binärdaten. Der Standard-Wert von 4 MB ist für viele Magento-Szenarien zu klein.

-- Aktuellen Wert prüfen
SHOW VARIABLES LIKE 'max_allowed_packet';
-- Standard: 4194304 (4 MB) - zu klein für Magento

-- Symptome bei zu kleinem max_allowed_packet:
-- "MySQL server has gone away" beim Speichern großer CMS-Inhalte
-- "Packet too large" beim Produktdaten-Import
-- Fehler beim Importieren großer SQL-Dumps

-- Große Textfelder in Magento-Tabellen prüfen
SELECT
    table_name,
    column_name,
    data_type,
    character_maximum_length
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND data_type IN ('text', 'mediumtext', 'longtext')
ORDER BY table_name, column_name;

-- cms_block.content und cms_page.content können sehr groß werden
SELECT MAX(LENGTH(content)) AS max_content_bytes FROM cms_page;
    

6. innodb_file_per_table=ON

Mit innodb_file_per_table=ON speichert InnoDB jede Tabelle in einer eigenen .ibd-Datei statt alles in die globale ibdata1-Datei zu schreiben. Das macht Tabellen-Management erheblich einfacher und verhindert das unkontrollierte Wachstum der ibdata1-Datei.

-- Einstellung prüfen
SHOW VARIABLES LIKE 'innodb_file_per_table';

-- Tabellengrößen auf Filesystem-Ebene (wenn innodb_file_per_table=ON):
-- ls -la /var/lib/mysql/magento_db/*.ibd | sort -k5 -rn | head -20

-- Größte Tabellen in Magento finden
SELECT
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
    ROUND(data_free / 1024 / 1024, 2) AS fragmented_mb,
    table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 15;

-- Fragmentierte Tabellen optimieren (setzt innodb_file_per_table=ON voraus)
-- OPTIMIZE TABLE catalog_product_flat_1;
-- Achtung: Sperrt die Tabelle während der Operation - in Produktion mit Vorsicht!
    

7. character-set-server=utf8mb4

MySQL's altes utf8 ist kein vollständiges UTF-8 – es unterstützt nur Zeichen bis 3 Bytes und kann 4-Byte-Zeichen wie Emoji, bestimmte Sonderzeichen und außereuropäische Schriften nicht speichern. utf8mb4 ist vollständiges Unicode und muss auf Server-Ebene, in der Datenbankverbindung und in den Tabellen konsistent gesetzt sein.

-- Zeichensatz-Einstellungen prüfen
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation%';

-- Datenbank auf utf8mb4 prüfen
SELECT schema_name, default_character_set_name, default_collation_name
FROM information_schema.schemata
WHERE schema_name = DATABASE();

-- Tabellen mit falschem Zeichensatz finden
SELECT table_name, table_collation
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_collation NOT LIKE 'utf8mb4%'
ORDER BY table_name;

-- Datenbank konvertieren (Backup vorher zwingend!)
ALTER DATABASE magento_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    

8. slow_query_log: Diagnose langsamer Queries

Der Slow Query Log ist das wichtigste Diagnosewerkzeug für Datenbankprobleme in Magento. Er protokolliert alle Queries, die länger als long_query_time Sekunden dauern, und sollte dauerhaft aktiviert sein.

-- Slow Query Log aktivieren (dynamisch, kein Neustart nötig)
SET GLOBAL slow_query_log              = ON;
SET GLOBAL long_query_time             = 2;    -- Queries > 2 Sekunden
SET GLOBAL log_queries_not_using_indexes = ON;  -- auch schnelle Queries ohne Index
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Status prüfen
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- Top 10 langsamste Queries aus Performance Schema
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(AVG_TIMER_WAIT / 1000000000000, 3) AS avg_sec,
    ROUND(MAX_TIMER_WAIT / 1000000000000, 3) AS max_sec,
    SUM_NO_GOOD_INDEX_USED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;
    

9. Vollständiges my.cnf für einen 16-GB-Server

Das folgende my.cnf ist ein produktionsnaher Ausgangspunkt für einen dedizierten MySQL-Datenbankserver mit 16 GB RAM für eine Magento-Instanz. Alle Werte sollten anhand des tatsächlichen Workloads, der Tabellengröße und der Verbindungsanzahl individuell angepasst werden.

# /etc/mysql/mysql.conf.d/magento.cnf
# Magento 2 optimierte MySQL-Konfiguration – 16 GB Dedicated DB Server

[mysqld]
# ── Zeichensatz ──────────────────────────────────────────────
character-set-server        = utf8mb4
collation-server            = utf8mb4_unicode_ci
init_connect                = 'SET NAMES utf8mb4'

# ── SQL Mode (Magento-Pflicht) ────────────────────────────────
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# ── InnoDB Buffer Pool (~75% von 16 GB) ───────────────────────
innodb_buffer_pool_size      = 12G
innodb_buffer_pool_instances = 8       # je ~1.5 GB pro Instanz
innodb_buffer_pool_chunk_size = 1G

# ── InnoDB Allgemein ──────────────────────────────────────────
innodb_file_per_table        = ON
innodb_flush_log_at_trx_commit = 1     # ACID: 1=sicher, 2=schneller, 0=riskant
innodb_flush_method          = O_DIRECT
innodb_log_file_size         = 512M
innodb_log_buffer_size       = 64M
innodb_read_io_threads       = 8
innodb_write_io_threads      = 8

# ── Verbindungen ──────────────────────────────────────────────
max_connections              = 200
max_allowed_packet           = 64M
wait_timeout                 = 28800
interactive_timeout          = 28800
connect_timeout              = 30

# ── Temporäre Tabellen ────────────────────────────────────────
tmp_table_size               = 256M
max_heap_table_size          = 256M

# ── Caches und Offene Dateien ─────────────────────────────────
table_open_cache             = 4096
table_definition_cache       = 2048
open_files_limit             = 65536

# ── Slow Query Log ────────────────────────────────────────────
slow_query_log               = ON
slow_query_log_file          = /var/log/mysql/slow.log
long_query_time              = 2
log_queries_not_using_indexes = ON

# ── Sonstige ─────────────────────────────────────────────────
max_sort_length              = 1024
sort_buffer_size             = 4M
join_buffer_size             = 4M
read_buffer_size             = 2M
read_rnd_buffer_size         = 8M

[client]
default-character-set        = utf8mb4

[mysql]
default-character-set        = utf8mb4
    

10. Zusammenfassung

Die Magento MySQL-Konfiguration ist kein einmaliges Thema, sondern erfordert kontinuierliche Beobachtung. Der sql_mode muss exakt den Magento-Anforderungen entsprechen. innodb_buffer_pool_size sollte 70–80% des RAMs erhalten und die Buffer Pool Hit Ratio regelmäßig überwacht werden. max_connections berechnet man aus tatsächlichen PHP-FPM-Workern. Der Slow Query Log sollte dauerhaft aktiv sein – er ist das wichtigste Instrument für proaktive Datenbankdiagnose in Magento-Produktivumgebungen.

11. FAQ: Magento MySQL-Konfiguration