„Die Website ist langsam“ läuft fast immer auf die Datenbank hinaus. Die Prüfung dauert wenige Minuten, wenn man sie in der richtigen Reihenfolge macht: zuerst das, was während der Verlangsamung selbst sichtbar ist, dann das, was sich über Wochen angesammelt hat.

Sehen Sie hin, während das Problem auftritt

Die Grundregel: Die wertvollsten Angaben sind genau dann verfügbar, wenn alles langsam ist. Der erste Befehl für MySQL:

sudo mysqladmin processlist
mysql -e 'SHOW FULL PROCESSLIST'

Für PostgreSQL:

sudo -u postgres psql -c "SELECT pid, state, wait_event, now()-query_start AS dur, query
FROM pg_stat_activity WHERE state != 'idle' ORDER BY dur DESC;"

Sie sehen, was gerade läuft und wie lange. Meist ist die Antwort sofort da: Eine schwere Abfrage hält die übrigen auf, oder hundert gleichartige Abfragen warten auf die Freigabe einer Sperre.

Verbindungen

mysql -e "SHOW GLOBAL STATUS LIKE 'Threads_connected'"
mysql -e "SHOW GLOBAL STATUS LIKE 'Max_used_connections'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"

Kommt Max_used_connections nahe an max_connections heran, erhält die Anwendung zeitweise die Abweisung „zu viele Verbindungen“. Das Limit anzuheben ist nicht das Erste: Jede Verbindung belegt Speicher, und eine Erhöhung auf einem Server, dem der Speicher ohnehin knapp ist, beschleunigt nur den Weg in den Swap. Klären Sie zuerst, warum Verbindungen nicht freigegeben werden: meist eine lange Abfrage oder ein fehlender Verbindungspool auf Anwendungsseite.

Es lohnt sich auch, dieselbe Sache von der anderen Seite zu betrachten: Ein starker Anstieg der Verbindungen kann nicht von wachsender Beliebtheit herrühren, sondern davon, dass Bots eine schwere Seite bearbeiten — eine Suche, einen Katalogfilter, einen Berichtsgenerator.

Langsame Abfragen

Ohne Protokoll langsamer Abfragen kommt man nicht weiter. MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Einen Tag später:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

PostgreSQL, in postgresql.conf:

log_min_duration_statement = 1000

Danach die Erweiterung pg_stat_statements, die die Gesamtzeit je Abfrage direkt zeigt:

SELECT calls, round(total_exec_time) AS total_ms, query
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

Beachten Sie: Entscheidend ist nicht die längste Abfrage, sondern die mit der größten Gesamtzeit. Eine Abfrage von 50 Millisekunden, die tausendmal pro Minute läuft, richtet mehr Schaden an als ein Bericht von zehn Sekunden einmal am Tag.

Die am häufigsten vergessene Einstellung

Bei MySQL und MariaDB ist es die Größe des InnoDB-Pufferpools. Die Voreinstellung liegt bei 128 Megabyte und hat sich seit Jahrzehnten nicht verändert. Auf einem Server, dessen Datenbank mehrere Gigabyte belegt, bedeutet das ständiges Lesen von der Platte statt aus dem Speicher.

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"

Ein vernünftiger Richtwert für einen reinen Datenbankserver ist die Hälfte des Arbeitsspeichers; für einen Server, auf dem daneben Webserver und PHP laufen, ein Viertel — mit Blick darauf, nicht in den Swap zu geraten. Diese eine Einstellung bringt meist mehr als eine Woche Abfrageoptimierung.

Zwei Dinge, die still die Platte auffressen

Die Binärprotokolle von MySQL. Sie werden für Replikation und Wiederherstellung auf einen Zeitpunkt gebraucht, wachsen ununterbrochen und werden standardmäßig mitunter nie entfernt. Zur Prüfung:

sudo du -sh /var/lib/mysql/*bin.*
mysql -e "SHOW VARIABLES LIKE 'binlog_expire_logs_seconds'"

Haben Sie keine Replikation und nutzen keine Wiederherstellung auf einen Zeitpunkt, setzen Sie die Aufbewahrung auf vernünftige wenige Tage.

WAL-Dateien in PostgreSQL bei einem vergessenen Replikationsslot. Ein weniger bekannter und gefährlicherer Fall: Existiert ein Replikationsslot, dessen Abnehmer sich nicht mehr verbindet, ist PostgreSQL verpflichtet, sämtliche Protokolle seit der Trennung aufzubewahren — und es bewahrt sie auf, bis die Platte voll ist.

sudo -u postgres psql -c "SELECT slot_name, active, restart_lsn FROM pg_replication_slots;"

Ein inaktiver Slot, den niemand braucht, gehört gelöscht. Das ist eine der wenigen Situationen, in denen eine Datenbank eine Platte garantiert auf null bringt.

Größe und Wachstum

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;

Nützlich ist weniger die aktuelle Zahl als die Geschwindigkeit ihrer Veränderung. Eine Datenbank, die sich in einem Monat verdoppelt hat, ohne dass der Verkehr entsprechend gewachsen wäre, bedeutet meist, dass irgendeine Protokolltabelle beschrieben und nie bereinigt wird — Sitzungen, Aufgabenhistorie, das Ereignisprotokoll eines Plugins.

Sicherheit in zwei Zeilen

Wenn Sie schon die Datenbankkonsole offen haben, lohnen sich zwei Prüfungen. Erstens, ob die Datenbank von außen erreichbar ist (ss -tulpn | grep 3306): Nach außen muss sie fast nie zeigen. Zweitens, Konten, die sich von beliebigen Adressen verbinden dürfen:

mysql -e "SELECT user, host FROM mysql.user"

Einträge mit host = '%' bedeuten „Verbindungen von überall“. Ist zugleich der Port offen, steht zwischen der Datenbank und dem Internet nur ein Passwort.

Sicherungen

Zum Schluss noch dies. Die Existenz einer Sicherungsdatei garantiert nichts; nur eine erprobte Wiederherstellung tut das. Ein Dump, der nie zurückgespielt wurde, erweist sich mit ebenso großer Wahrscheinlichkeit als abgeschnitten, mit einem Sperrfehler erstellt oder als die falsche Datenbank enthaltend. Das ist im Voraus und auf einer separaten Maschine zu prüfen, nicht an dem Tag, an dem die Kopie gebraucht wird.

Für die tägliche Beobachtung genügen drei Zahlen: die Zahl der Verbindungen, die Menge langsamer Abfragen pro Tag und die Größe der Datenbank. Alle drei ändern sich langsam und vorhersehbar, und jede plötzliche Bewegung bei einer davon ist einen Blick wert. Wie sie auf einer Seite aussehen, zeigt die Demo unten.