JTL-Shop MariaDB Optimization
Automatisierte Index-Optimierung, FULLTEXT-Suche und MariaDB-Konfiguration für JTL-Shop-Datenbanken auf Multi-Shop-Servern.
🎯 Problemstellung
Bei der Analyse eines Plesk-Servers mit 50 JTL-Shops wurden massive Performance-Probleme identifiziert:
- 24,4% aller Queries waren Slow Queries (>1 Sekunde)
- ~250 Slow Queries pro Sekunde
- Hauptursache: Fehlende Indizes und deaktivierte FULLTEXT-Suche
📊 Analyse-Ergebnisse
Systemübersicht (Beispiel-Server)
| Ressource | Wert |
|---|---|
| RAM | 98 GB |
| CPU | 14 Kerne |
| Disk | SSD (NVMe) |
| MariaDB | 10.11.13 |
| DB Größe | ~20 GB |
| Shops | 28 Datenbanken |
| Tabellen | ~14.350 |
Top Slow Queries nach Ursache
| Rang | Problem | Anteil | Ursache |
|---|---|---|---|
| #1 | Suchcache INSERT | 68,6% | FULLTEXT deaktiviert, LIKE '%...%' |
| #2 | tbesucherarchiv | 9,3% | INSTR() verhindert Index-Nutzung |
| #3-6 | Filter-Queries | ~6% | JTL-interne Query-Struktur |
| #14 | tlieferscheinposinfo | 0,5% | Fehlender Index |
| #17 | tkampagnevorgang | 0,4% | Suboptimaler Index |
| #19 | tlieferscheinpos | 0,3% | Fehlender Index |
Identifizierte fehlende Indizes
| Tabelle | Fehlender Index | Betroffene Shops | Rows Examined |
|---|---|---|---|
tartikel |
FULLTEXT | 25 von 28 | bis 468.000 |
tlieferscheinposinfo |
kLieferscheinPos |
11 von 28 | ~280.000 |
tlieferscheinpos |
kLieferschein |
11 von 28 | ~215.000 |
tkampagnevorgang |
kKampagneDef, kKey |
28 von 28 | ~50.000 |
txsellkauf |
kXSellArtikel |
28 von 28 | bis 2.000.000 |
Hauptproblem: FULLTEXT-Suche
Befund: Nur 2 von 28 Shops hatten die FULLTEXT-Suche aktiviert.
-- JTL mit deaktiviertem FULLTEXT (langsam):
WHERE tartikel.cName LIKE '%suchbegriff%'
-- → Full Table Scan über 468.000 Zeilen!
-- JTL mit aktiviertem FULLTEXT (schnell):
WHERE MATCH(cName, cSuchbegriffe) AGAINST('suchbegriff')
-- → Index-Nutzung, wenige Millisekunden
Der FULLTEXT-Index existierte auf den meisten Shops nicht, und selbst wo er existierte, war die JTL-Einstellung suche_fulltext auf N gesetzt.
🔧 Lösung
Das Skript mariadb-optimization.py behebt diese Probleme automatisiert:
Was das Skript macht
| Phase | Aktion | Details |
|---|---|---|
| 1 | Backup | Sichert alle suche_fulltext Einstellungen |
| 2 | Standard-Indizes | Erstellt 4 fehlende Index-Typen |
| 3 | FULLTEXT-Index | Erstellt FULLTEXT-Index auf tartikel |
| 4 | Einstellung aktivieren | Setzt suche_fulltext = 'Y' |
| 5 | MariaDB-Config | Erstellt optimierte Konfiguration & Restart |
Erstellte Indizes
-- 1. Lieferschein-Position Info
CREATE INDEX idx_kLieferscheinPos ON tlieferscheinposinfo (kLieferscheinPos);
-- 2. Lieferschein-Position
CREATE INDEX idx_kLieferschein ON tlieferscheinpos (kLieferschein);
-- 3. Kampagne-Vorgang (Composite)
CREATE INDEX idx_kKampagneDef_kKey ON tkampagnevorgang (kKampagneDef, kKey);
-- 4. Cross-Selling
CREATE INDEX idx_kXSellArtikel ON txsellkauf (kXSellArtikel);
-- 5. FULLTEXT für Artikelsuche
CREATE FULLTEXT INDEX idx_tartikel_fulltext ON tartikel
(cName, cSeo, cSuchbegriffe, cArtNr, cKurzBeschreibung, cBeschreibung, cBarcode, cISBN, cHAN);
MariaDB-Konfiguration (Phase 5)
Das Skript erstellt automatisch eine optimierte Konfiguration unter:
/etc/mysql/conf.d/ZZ-ztl-final-override.cnf
Dynamische Buffer Pool Berechnung:
- Ermittelt Gesamtgröße aller Datenbanken
- Berechnet:
DB-Größe × 1.2, aufgerundet auf ganze GB - Beispiel: 18.74 GB × 1.2 = 23 GB Buffer Pool
Konfigurationsinhalt:
[mysqld]
# InnoDB Buffer Pool (dynamisch berechnet)
innodb_buffer_pool_size = XXG
innodb_buffer_pool_instances = 16
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
# InnoDB Performance
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
# Query Cache deaktiviert (bei MariaDB 10.1.7+ empfohlen)
query_cache_type = 0
query_cache_size = 0
# Table Cache für viele Shops
table_definition_cache = 20000
table_open_cache = 15000
open_files_limit = 30000
# Performance
skip_name_resolve = 1
optimizer_search_depth = 0
key_buffer_size = 64M
# Slow Query Log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 0
# Connections
max_connections = 400
thread_cache_size = 100
wait_timeout = 120
# Buffer für JTL-Queries
join_buffer_size = 4M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
Wichtige Einstellungen erklärt:
| Parameter | Wert | Funktion |
|---|---|---|
innodb_buffer_pool_dump/load |
1 | Buffer Pool bleibt nach Restart "warm" |
skip_name_resolve |
1 | Schnellere Verbindungen (kein DNS-Lookup) |
optimizer_search_depth |
0 | Auto-Optimierung für JOINs |
key_buffer_size |
64M | Reduziert (nur für MyISAM, JTL nutzt InnoDB) |
query_cache |
OFF | Bei write-heavy Workloads kontraproduktiv |
🚀 Installation & Verwendung
Voraussetzungen
- Python 3.6+
- Root-Zugriff auf den Server
- MariaDB/MySQL Client
Ausführung
# Direkt vom Git-Repository ausführen
wget https://git.jtl-hosting.de/thomasciesla/mariadb-optimization/raw/branch/main/mariadb-optimization.py
python3 mariadb-optimization.py
Oder:
# Repository klonen
git clone https://git.jtl-hosting.de/thomasciesla/mariadb-optimization.git
cd mariadb-optimization
python3 mariadb-optimization.py
Empfohlene Ausführungszeit
⚠️ Zu Zeiten geringer Last ausführen (nachts/früh morgens)
Der FULLTEXT-Index auf großen Shops (z.B. 468.000 Artikel) kann mehrere Minuten dauern und erhöht kurzzeitig die Server-Last. Am Ende wird MariaDB automatisch neugestartet.
📁 Ausgabe-Dateien
Nach der Ausführung werden folgende Dateien erstellt:
| Datei | Zweck |
|---|---|
/root/jtl_search_backup_TIMESTAMP.json |
Backup aller Sucheinstellungen |
/root/jtl_search_rollback_TIMESTAMP.py |
Rollback-Skript für Sucheinstellungen |
/root/mariadb_config_backup_TIMESTAMP.cnf |
Backup der vorherigen MariaDB-Config |
/root/jtl_index_log_TIMESTAMP.txt |
Vollständiges Ausführungs-Log |
🔄 Rollback bei Problemen
Falls nach der Optimierung Probleme auftreten:
# Rollback der Sucheinstellungen (setzt suche_fulltext auf vorherigen Wert)
python3 /root/jtl_search_rollback_TIMESTAMP.py
# Rollback der MariaDB-Konfiguration
cp /root/mariadb_config_backup_TIMESTAMP.cnf /etc/mysql/conf.d/ZZ-ztl-final-override.cnf
systemctl restart mariadb
Hinweis: Die erstellten Indizes müssen nicht zurückgerollt werden – sie haben keine negative Auswirkung und können bei Bedarf manuell entfernt werden.
❓ FAQ
Ist das Erstellen von Indizes gefährlich?
Nein. Ein Index ist eine separate Datenstruktur neben der Tabelle – wie ein Stichwortverzeichnis am Ende eines Buches:
- ✅ Ändert keine Daten
- ✅ Ändert keine Spalten
- ✅ Ändert kein Verhalten der Anwendung
- ✅ Kann jederzeit wieder gelöscht werden
- ✅ MariaDB entscheidet selbst, ob es den Index nutzt
Was ändert sich durch suche_fulltext = 'Y'?
Diese JTL-Einstellung ändert, wie JTL nach Artikeln sucht:
| Einstellung | Suchmethode | Performance |
|---|---|---|
N |
LIKE '%text%' |
Langsam (Full Table Scan) |
Y |
MATCH() AGAINST() |
Schnell (FULLTEXT Index) |
Mögliche Unterschiede im Suchverhalten:
- FULLTEXT sucht nach ganzen Wörtern
- LIKE sucht nach Teilstrings
- Beispiel: Suche nach "gel" findet mit LIKE auch "Nagel", mit FULLTEXT evtl. nicht
Warum fehlen die Indizes überhaupt?
Typische Gründe:
- JTL hat sie bei bestimmten Versionen nicht erstellt
- Updates erstellen keine Indizes nachträglich
- Die Tabellen waren ursprünglich klein
Wie wird der Buffer Pool berechnet?
Buffer Pool = Gesamtgröße aller Datenbanken × 1.2 (aufgerundet)
Beispiel: 18.74 GB × 1.2 = 22.49 GB → 23 GB
Warum ist Query Cache deaktiviert?
Bei Multi-Shop-Servern mit vielen Schreiboperationen (Bestellungen, Lagerbestände, etc.) ist der Query Cache kontraproduktiv:
- Jedes INSERT/UPDATE/DELETE invalidiert den Cache
- Lock Contention bei vielen gleichzeitigen Queries
- MySQL 8.0 hat den Query Cache komplett entfernt
Wie kann ich den Effekt messen?
Vorher/Nachher Vergleich:
# Slow Query Rate prüfen
mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN ('Questions','Slow_queries');"
# Berechnung: Slow_queries / Questions * 100 = Slow Query Rate (%)
📈 Erwartete Verbesserungen
| Metrik | Vorher | Nachher (erwartet) |
|---|---|---|
| Slow Query Rate | ~24% | <5% |
| Suchcache-Queries | 68,6% der Zeit | <5% |
| Artikelsuche | Sekunden | Millisekunden |
🔍 Analyse-Befehle
Die folgenden Befehle wurden für die Analyse verwendet und können zur Überprüfung genutzt werden:
Slow Query Analyse
# pt-query-digest installieren (falls nicht vorhanden)
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
apt update && apt install percona-toolkit -y
# Slow Query Report erstellen
pt-query-digest --limit=50 /var/log/mysql/slow.log > /tmp/slow_report.txt
Index-Status prüfen
# Shops ohne FULLTEXT-Index
mysql -N -B -e "SELECT t.table_schema, t.table_rows
FROM information_schema.tables t
WHERE t.table_name = 'tartikel'
AND t.table_schema NOT IN (
SELECT DISTINCT table_schema
FROM information_schema.statistics
WHERE table_name = 'tartikel' AND index_type = 'FULLTEXT'
)
ORDER BY t.table_rows DESC;"
# FULLTEXT-Einstellung pro Shop
mysql -N -B -e "SELECT table_schema FROM information_schema.tables
WHERE table_name = 'teinstellungen'" | while read db; do
val=$(mysql -N -B -e "SELECT cWert FROM \`$db\`.teinstellungen
WHERE cName = 'suche_fulltext' LIMIT 1" 2>/dev/null)
echo "$db: ${val:-NICHT GESETZT}"
done
Datenbankgrößen prüfen
# Gesamtgröße aller Datenbanken
mysql -N -B -e "SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2)
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');"
# Größe pro Datenbank
mysql -e "SELECT table_schema AS 'Datenbank',
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Größe (MB)'
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;"
Buffer Pool & Key Metrics
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';"
mysql -e "SHOW GLOBAL STATUS WHERE Variable_name IN
('Connections','Max_used_connections','Threads_connected',
'Threads_running','Questions','Slow_queries','Uptime');"
🐛 Bekannte Einschränkungen
Nicht durch Indizes lösbar
| Problem | Ursache | Workaround |
|---|---|---|
| tbesucherarchiv (9,3%) | INSTR(cIP, '...') in Query |
JTL-interner Code, nicht änderbar |
| Komplexe Filter-Queries | JTL Query-Struktur | Bereits optimale Indizes vorhanden |
FULLTEXT-Einschränkungen
- Minimum Wortlänge: 3-4 Zeichen (konfigurierbar in MariaDB)
- Stoppwörter werden ignoriert
- Keine Wildcard-Suche am Wortanfang
📜 Lizenz
MIT License - Siehe LICENSE
👤 Autor
Thomas Ciesla - JTL-Hosting