© DrHitch/Shutterstock.com
Datenbank-Tuning

1 MySQL Queries optimieren


Slow Queries in MySQL finden und beschleunigen

Trotz des Anlegens normalisierter Tabellenstrukturen und des Setzens geeigneter Indizes können in MySQL Queries generiert werden, deren Ausführung einige Sekunden dauert. Bei einem Einsatz im Web sind solche Antwortzeiten nicht akzeptabel und zumeist durch einfaches Umstellen der Abfragen optimierbar – vorausgesetzt man erkennt überhaupt, dass Slow Queries im System vorhanden sind.

Die Geschwindigkeit einer Datenbank ist von vielen Faktoren abhängig. Die Arbeit beginnt hier bereits beim Design der Datenbank, da sowohl Struktur als auch Indizes maßgeblich zur Geschwindigkeit von Queries beitragen. Aber selbst wenn man alles richtig gemacht hat, kann es beim Vorhandensein großer Datenmengen – zum Beispiel bei komplexen Joins, aber auch bei der Nutzung von Foreign Keys (InnoDB) – vorkommen, dass eine Query eine Rechenleistung von einigen Sekunden verschlingt. Hierbei gibt es keine festen Regeln, wann man in eine solche Falle gerät, aber die Optimierungsmöglichkeiten in MySQL sind diesbezüglich schier unendlich.

Anstatt das Übel an der Wurzel zu packen, wird in der Regel mit einem Caching gearbeitet. Dies erfüllt zwar seinen Zweck, funktioniert aber nur so lange man nicht auf Echtzeitdaten angewiesen ist. Der Benutzer wäre zum Beispiel nicht sehr erfreut, wenn Facebook seinen Newsfeed nur einmal pro Stunde aktualisieren würde.

Erschwerend kommt bei Geschwindigkeitseinbußen dieser Art hinzu, dass man sie in der Regel erst Monate nach Inbetriebnahme bemerkt. Nämlich genau dann, wenn aufgrund der Benutzerzahlen die Anzahl der Queries zu groß wird, oder die Datenmenge exponenziell angestiegen ist. Den Fehler wird man in einer lokalen Entwicklungsumgebung mit begrenzten Datensätzen nicht aufspüren, unter Umständen hilft es noch nicht einmal, die Daten zu spiegeln. Stattdessen muss ein genaues Monitoring des Servers über einen längeren Zeitraum erfolgen. MySQL bietet hierfür die notwendigen Werkzeuge.

Was sind Slow Queries und wie entstehen sie?

Die Definition, was eine Slow Query ist, bleibt jedem selbst überlassen. Im Falle von MySQL ist die maximale Ausführungszeit, die erreicht werden muss, damit eine Query als Slow Query gilt, in der Konfigurationsdatei einstellbar. Dies macht auch durchaus Sinn, da je nach Anwendungsfall verschiedene Ausführungszeiten akzeptiert werden können. Im Frontend einer Webanwendung geht es zum Beispiel nur um eine reine Geschwindigkeitsoptimierung, wohingegen man im Backend bei statistischen Auswertungen durchaus längere Laufzeiten akzeptieren kann.

Entsprechend ist eine Slow Query eine Query, deren Ausführungszeit über einer vom Administrator definierten Grenze liegt. Diese Grenze kann sehr eng gefasst sein und wird somit viele Slow Queries oder sehr weit und (wenn überhaupt) nur sehr wenige Ergebnisse liefern. In der Default-Einstellung von MySQL liegt die Grenze für eine Slow Query bei 10 Sekunden. Ein Wert von dem man eigentlich denken könnte, dass er nur schwer zu erreichen ist, der aber durchaus auftritt.

Die Gründe für das Auftreten von Slow Queries sind verschieden und unter anderem auch abhängig von der gesetzten Grenze zur Definition einer solchen. Natürlich kann man diese so klein setzen, dass man eine Vielzahl von Slow Queries geliefert bekommt, die aber nicht optimiert werden können. Aus diesem Grund muss die maximale Laufzeit einer Query agil und der Anwendung entsprechend gewählt werden. In der Regel tritt eine Slow Query auf, weil die entsprechende Anfrage an MySQL ungeschickt ist, bzw. der Datenbestand nicht auf diese Anfrage hin optimiert wurde. Im einfachsten Fall lässt sich eine Slow Query durch Einführen von Indizes oder eine geschicktere Tabellenstruktur beheben, im kompliziertesten Fall muss MySQL bei der Datenauswahl mit PHP geholfen werden. Zwischen diesen beiden Lösungen existieren noch viele weitere, die darauf basieren, dass man die Query an sich umstellt. Zumeist tauchen die Probleme bei komplexen JOINs auf bzw. bei der Nutzung von Group By, Foreign Keys und Triggern.

Wie findet man Slow Queries?

Slow Queries werden in der my.cnf serverweit für alle Datenbanken eingeschaltet. Vom Prinzip her funktioniert das Konstrukt wie folgt: Wenn die Variable log_slow_queries auf on steht, werden in die Datei slow_query_log_file alle Queries geschrieben, die länger dauern als long_query_time-Sekunden. Alle diese Werte können MySQL entweder beim Start als Parameter übergeben oder in der Konfigurationsdatei fest hinterlegt werden.

Darüber hinaus gibt es noch weitere Möglichkeiten, nicht optimierte Queries zu finden, nämlich alle die, die bei Ihren Abfragen nicht auf Indizes zurückgreifen. Hierfür muss der Wert log_queries_not_using_indexes ebenfalls auf on gesetzt werden. Ferner können nur Queries beobachtet werden, die große Datenmengen zurückliefern. Hierfür muss die Variable min_examined_row_limit entsprechend gesetzt werden.

Einschalten des Slow-Query-Logs in MySQL

Durch Setzen der Variable log_slow_queries auf on werden nach Neustart alle Slow Queries von MySQL in der Datei slow_query_log_file gespeichert. Hierbei werden nur solche Dateien gespeichert, die eine Ausführungszeit länger als long_query_time Sekunden besitzen.

Durch Setzen von long_queries_not_using_indexes werden alle Queries gelogt, die keine geeigneten Indizes verwenden.

Das hierbei entstehende Logfile ist weder leserlich noch sinnvoll. Das dachte man sich wohl auch bei MySQL und aus diesem Grund sollte man zur Ausgabe des Logs den Befehl mysqldumpslow verwenden. Dieser gibt die entstehenden Logeinträge in einem sinnvollen und auf Wunsch auch gefilterten Format ...

Neugierig geworden? Wir haben diese Angebote für dich:

Angebote für Gewinner-Teams

Wir bieten Lizenz-Lösungen für Teams jeder Größe: Finden Sie heraus, welche Lösung am besten zu Ihnen passt.

Das Library-Modell:
IP-Zugang

Das Company-Modell:
Domain-Zugang