MariaDB: die drei Einstellungen, die zählen, und vier Fallen

MariaDB läuft nach der Installation und trägt kleine Anwendungen jahrelang ohne Zutun. Wenn es langsam wird, sind fast immer dieselben Dinge die Ursache und die Reihenfolge, in der man sie prüft, spart die meiste Zeit.

Die drei Einstellungen#

# /etc/mysql/mariadb.conf.d/60-eigene.cnf
[mysqld]
innodb_buffer_pool_size = 4G       # auf einer 8-GB-Maschine, die nur DB macht
innodb_log_file_size    = 512M
innodb_flush_log_at_trx_commit = 1

innodb_buffer_pool_size ist die wichtigste Zahl im gesamten System. Sie bestimmt, wie viel von den Daten im Arbeitsspeicher liegt. Faustregeln:

Der letzte Fall ist häufiger, als man denkt: Eine 800-MB-Datenbank braucht keinen 4-GB-Puffer.

-- Wie groß sind die Daten wirklich?
SELECT table_schema, ROUND(SUM(data_length+index_length)/1024/1024) AS mb
FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;

innodb_log_file_size puffert Schreibvorgänge. Zu klein bedeutet häufige Zwangsabgleiche auf die Platte, spürbar bei Schreiblast.

innodb_flush_log_at_trx_commit ist eine Sicherheitsentscheidung, keine Leistungsschraube: 1 schreibt jede Transaktion sofort dauerhaft weg, bei einem Stromausfall geht nichts verloren. 2 ist schneller und kann die letzte Sekunde kosten. Für alles, was Geld oder Bestellungen berührt, bleibt es bei 1.

Langsame Abfragen finden#

Ohne Protokoll ist jede Optimierung geraten.

[mysqld]
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 1
log_queries_not_using_indexes = 1

# Ein paar Tage laufen lassen, dann zusammenfassen:
mariadb-dumpslow -s t -t 10 /var/log/mysql/slow.log

Für jede auffällige Abfrage dann die eine Frage: Benutzt sie einen Index?

EXPLAIN SELECT ... ;
-- Spalte "type": ALL bedeutet Tabellendurchlauf.
-- Spalte "rows": geschätzte gelesene Zeilen. Große Zahl bei kleinem Ergebnis = Index fehlt.

In der Praxis lösen fehlende Indizes mehr Leistungsprobleme als alle Konfigurationswerte zusammen. Ein Index auf eine Spalte, nach der ständig gefiltert oder sortiert wird, verwandelt Sekunden in Millisekunden.

-- Vorsicht: jeder Index kostet beim Schreiben. Ungenutzte finden:
SELECT * FROM sys.schema_unused_indexes;   -- falls sys-Schema vorhanden

Vier Fallen#

1. utf8 ist nicht UTF-8#

Der historische Zeichensatz utf8 in MySQL/MariaDB speichert maximal drei Byte pro Zeichen. Damit fehlen Emoji und einige asiatische Zeichen. Das Symptom: Beim Speichern eines Textes mit Emoji bricht die Anwendung ab oder schneidet den Text ab genau dieser Stelle ab.

SHOW VARIABLES LIKE 'character_set_%';
-- Richtig ist utf8mb4, in der Regel mit einer Sortierung wie utf8mb4_unicode_ci.
ALTER DATABASE meineapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE meinetabelle CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Neuere Versionen setzen utf8mb4 als Standard. Gewachsene Datenbanken tragen den alten Wert oft noch und es fällt erst auf, wenn ein Nutzer ein Emoji tippt.

2. MyISAM in alten Tabellen#

MyISAM kennt keine Transaktionen und sperrt beim Schreiben die ganze Tabelle. Bei alten Anwendungen liegen einzelne Tabellen noch so vor.

SELECT table_name, engine FROM information_schema.tables
WHERE table_schema = 'meineapp' AND engine <> 'InnoDB';
ALTER TABLE alt ENGINE=InnoDB;

3. Der Zeichensatz der Verbindung#

Datenbank auf utf8mb4, Anwendung verbindet sich aber mit latin1, dann sind die Daten in der Datenbank korrekt und in der Anzeige kaputt, oder umgekehrt. Der Verbindungszeichensatz gehört in die Konfiguration der Anwendung, nicht nur in die der Datenbank.

4. root ohne Passwort über Socket#

Die lokale Anmeldung als root läuft über unix_socket-Authentifizierung, praktisch, aber dazu gehört, dass kein Anwendungsbenutzer dieselben Rechte hat:

SELECT user, host, plugin FROM mysql.user;
SHOW GRANTS FOR 'app'@'localhost';
-- Eine Anwendung braucht selten mehr als SELECT, INSERT, UPDATE, DELETE
-- auf ihrer eigenen Datenbank. Kein GRANT ALL auf *.*.

Was im Betrieb zu beobachten ist#

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Verhältnis von reads (aus dem Puffer) zu read_requests: je näher am Puffer, desto besser
SHOW ENGINE INNODB STATUS\G
-- Abschnitt "BUFFER POOL AND MEMORY", und Sperren unter "TRANSACTIONS"

Zwei Zeichen dafür, dass der Puffer zu klein ist: Der Anteil der von Platte gelesenen Seiten steigt über Wochen, und die Antwortzeiten wachsen mit der Datenbankgröße statt mit der Nutzerzahl. Beides gehört auf denselben Bildschirm wie der Rest (Monitoring aufsetzen).

Häufige Fragen#

Wie stelle ich fest, ob die Datenbank überhaupt der Engpass ist?#

Vergleiche die Zeit einer langsamen Seite mit der Summe der Abfragezeiten im langsamen Protokoll. Liegen die weit auseinander, liegt es an der Anwendung, nicht an der Datenbank.

Bringt eine SSD viel?#

Bei Schreiblast ja, deutlich. Bei Leselast nur so lange, bis die Daten im Puffer liegen, danach ist die Platte fast unbeteiligt.

Soll ich den Query-Cache einschalten?#

Nein. Er ist in neueren Versionen standardmäßig aus und bei nebenläufiger Last eher hinderlich; MariaDB hat ihn zugunsten anderer Mechanismen abgelöst.

Was ist mit sql_mode?#

Ein strenger Modus (etwa mit STRICT_TRANS_TABLES) lässt die Datenbank Fehler melden, statt Werte stillschweigend zurechtzuschneiden. Für neue Anwendungen richtig; bei alten vorher prüfen, ob sie das aushalten.

Wie viele Verbindungen sind zu viele?#

Wenn max_connections regelmäßig erreicht wird, hält die Anwendung Verbindungen zu lange offen. Der Pool gehört dann in die Anwendung, ein höherer Wert verschiebt das Problem nur.

Kurz gesagt#

• innodb_buffer_pool_size ist die eine wichtige Zahl, aber nie größer als die Datenbank.
• innodb_flush_log_at_trx_commit = 1 bleibt, wo Daten zählen.
• Langsames Protokoll einschalten, EXPLAIN lesen: fehlende Indizes sind die häufigste Ursache.
• utf8 ist nicht UTF-8, utf8mb4 prüfen, auch in der Verbindung.
• Tuning-Skripte sind Anhaltspunkte, keine Anweisungen.