mariadb-optimization/README.md

# 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.

```sql
-- 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

```sql
-- 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:**

```ini
[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

```bash
# 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:

```bash
# 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:

```bash
# 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:
1. JTL hat sie bei bestimmten Versionen nicht erstellt
2. Updates erstellen keine Indizes nachträglich
3. 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:**

```bash
# 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

```bash
# 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

```bash
# 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

```bash
# 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

```bash
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](LICENSE)

## 👤 Autor

Thomas Ciesla - JTL-Hosting

## 🔗 Links

- [Repository](https://git.jtl-hosting.de/thomasciesla/mariadb-optimization)
- [Skript (Direktlink)](https://git.jtl-hosting.de/thomasciesla/mariadb-optimization/raw/branch/main/mariadb-optimization.py)
- [JTL-Shop Dokumentation](https://guide.jtl-software.de/jtl-shop/)
- [MariaDB Index-Dokumentation](https://mariadb.com/kb/en/optimization-and-indexes/)
- [Percona Toolkit](https://www.percona.com/software/database-tools/percona-toolkit)