Slow Queries in MySQL finden und beschleunigen

MySQL Queries optimieren
Kommentare

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.

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 aus. Eine Übersicht der notwendigen Parameter kann man über -help erhalten. Die betreffende Logdatei kann als Parameter angegeben werden (Listing 1).

Listing 1

shell> mysqldumpslow

Reading mysql slow query log from /usr/local/mysql/data/mysqld51-apple-slow.log
Count: 1  Time=4.32s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1

Count: 3  Time=2.53s (7s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t2 select * from t1 limit N

Count: 3  Time=2.13s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
 insert into t1 select * from t1

Wie man an dem Beispiel unschwer erkennen kann, kann man sich nun erst einmal freuen, wenn jeder Benutzer nur eine einzige Datenbank verwendet. Ansonsten steht man ganz schnell vor dem Problem, dass man nicht lokalisieren kann, in welcher DB eine Query ausgeführt worden ist. Meistens helfen hier jedoch die Tabellennamen. Das Log liefert, wie man sieht, alle notwendigen Informationen:

  • Wie oft wurde die Query ausgeführt?
  • Welche Ausführungszeit wurde dafür benötigt?
  • Ist die Query so schlimm, dass sie Anfragen blockiert hat (z. B. komplexe INSERTs)?
  • Wie viele Zeilen wurden als Ergebnis zurückgeliefert?
  • Welcher Benutzer hat die Query ausgeführt?
  • Und zu guter Letzt: die Query an sich.

Nach der Konfiguration kann man übrigens einen kleinen Test durchführen. Um sicher zu gehen, dass man alles richtig gemacht hat, kann man einfach die Query SELECT SLEEP(30); ausführen. Sie sollte auf jeden Fall im Slow-Query-Log auftauchen, wird aber auch 30 Sekunden für ihre Ausführung benötigen.

Noch zu erwähnen bliebe an dieser Stelle, dass man das Log nicht einschaltet, den Server restartet und nach 30 Sekunden ein sinnvolles Ergebnis geliefert bekommt. Sollte man sich für eine Optimierung über das Slow-Query-Log entscheiden, ist dies ein langwieriger Prozess, der sich dadurch auszeichnet, dass man dem Server ausreichend Zeit geben muss, die Slow Queries zu lokalisieren, zu sammeln und in das Log einzutragen. Denn wie man in dem Beispiel sieht, können sich auch viele kleine halbwegs vernünftige Queries zu einem Problem entwickeln.

Wie optimiert man Slow Queries?

Hat man Slow Queries lokalisiert, steht man sehr schnell vor dem Problem, dass man nicht weiß, wie man es besser machen könnte. Erschwerend kommt hinzu, dass gerade MySQL mehrere Angriffsmöglichkeiten zur Bekämpfung von Slow Queries bietet. Auch die Nutzung diverser Tabellenformate (InnoDB) verlangsamt den Server per se. Die Grundkonfiguration ist nicht auf Performance optimiert.

Die erste und in der Praxis am häufigsten vorkommende Möglichkeit zur Beschleunigung von Slow Queries ist das geschickte Setzen von Indexen. Übernimmt man eine Datenbank von Dritten, kommt es nicht selten vor, dass einem Table schlechte oder sogar gar keine Indizes hinzugefügt worden sind. Auch kaputte oder fragmentierte Tables (Stichwort: repair table) tragen zu langsamen Queries bei. Analysiert man den Select, kommt man sehr schnell darauf, welche Spalten (oder Spaltenkombinationen) mit einem Index versehen schneller abgefragt werden könnten. Dies ist die einfachste Art der Optimierung, die oft den größten Nutzen bringt. Jedoch sollte man bei einem professionellen Programmierer davon ausgehen, dass dieser Fall eigentlich nicht vorkommen sollte.

Die zweite Möglichkeit ist die, nur die Daten abzufragen, die man auch wirklich braucht. Geht man davon aus, dass man in einer Tabelle mit 1 Mio. Einträgen nur die Top 3 für eine Darstellung benötigt, muss man sich nicht trotzdem die gesamte Million sortiert zurückgeben lassen. Begrenzt man die Query mit einem Limit, reduziert man den Speicherverbrauch drastisch.

Dies war es dann fast auch schon zum Thema der einfachen Optimierungen. Der Rest an Optimierungen, der durchgeführt werden kann, basiert in der Regel auf Ausprobieren und zumeist erschließt sich einem nicht, wieso eine geänderte Query schneller ausgeführt wird als die „alte“. In der Praxis haben sich insbesondere InnoDB und dessen Foreign Keys in Verbindung mit JOINs als Datenwunder erwiesen. Ein JOIN mehrerer Tabellen über Foreign Keys oder ein GROUP BY kann sich problemlos zu einer Slow Query entwickeln, fügt man die Tabellen jedoch in einer anderen Reihenfolge zusammen, kann dies durchaus zu einem Geschwindigkeitsgewinn führen. Verwendet man die Optionen ON DELETE oder ON CASCADE oder gar TRIGGER, kann selbst eine einfache Datenbankabfrage einen Rattenschwanz an Operationen in MySQL lostreten, der nicht mehr überschaubar ist. Ich rate nicht generell vom Einsatz dieser Techniken ab, doch in der Regel machen sie in der Standardkonfiguration des Servers keinen Sinn. Man sollte sich an dieser Stelle stets fragen, ob man diese Funktionen aus Bequemlichkeit nutzt, oder ob es anders nicht besser und schneller geht.

Eine ganz eigene Logik hinsichtlich Slow Queries findet man vor, wenn man in den Bereich der Strings und der Mathematik geht. Hier steht man sehr schnell vor einem architektonischen Problem, nämlich der Frage, wohin die Programmlogik gehört: in die Programmiersprache, die den Datenbankserver nutzt, oder den Datenbankserver selbst? Natürlich kann ich in MySQL drei Spalten zu einer einzelnen zusammenfassen, bei der Gelegenheit direkt den Mittelwert errechnen und alle gespeicherten Datumsformate umformatieren, aber ist das sinnvoll? Und gehört das überhaupt in den Datenbankserver?

Eine Frage, die jeder für sich selbst beantworten muss, aber an dieser Stelle auch unheimlich Optimierungspotenzial besitzt. Nicht selten beschleunigt man eine Anwendung drastisch, wenn man diese Logik zwischen Datenbankserver und Programmiersprache austauscht – und lustigerweise funktioniert es ab und an in beide Richtungen.

Wie man sieht, gibt es keine Universallösung für das Optimieren von Queries und am Ende hilft nur Ausprobieren. Trotzdem sollte man stets dran denken, dass eine Datenbankabfrage, die funktioniert, nicht diejenige sein muss, die auch schnell funktioniert. In der Regel muss man nicht optimieren, da wir hier durchaus über Mikrooptimierungen reden, aber es kann durchaus Queries geben, deren Ausführung mehrere Sekunden dauert, die jedoch drastisch beschleunigt werden könnten.

Tools für den Umgang mit Slow Queries

Da wir nun Slow Queries finden und analysieren können, kann man zum einen froh sein, dass MySQL diese Funktionen zur Verfügung stellt, zum anderen wird man sehr schnell an die Grenzen des Systems stoßen. Es ergeben sich hier zwei Grundprobleme: die Übersichtlichkeit der Logdatei und die Frage, wie man die Query eigentlich optimieren kann, um sie zu beschleunigen. Ein Fakt der die Sachlage insbesondere verschärft, ist der Kunde, der seine Anwendung schneller haben möchte. In der Regel ist diesem nicht vermittelbar, dass man zwei Tage berechnet, um MySQL zu optimieren – für etwas, dass man von vorne herein hätte besser machen können. Oder anders ausgedrückt: Der Kunde besitzt ziemlich schnell den Eindruck, dass er etwas zahlen muss, was er nicht zahlen braucht: Bugfixing.

Hier begibt man sich in ein argumentatives Vakuum, da man zugegebenermaßen wirklich Bugfixing betreibt, und dies in der Regel auf eigene Kosten. Deshalb sollte man Slow Queries also von vorne herein vermeiden, oder man greift auf die entsprechenden Tools zurück, die diesen Optimierungsprozess beschleunigen.

Da dies nicht unbedingt einfach ist, verlässt man an dieser Stelle ziemlich schnell den Bereich der Open Source. Hier muss man abwägen, ob es nicht trotzdem sinnvoll ist, einmalig 500 Euro zu bezahlen, oder ob es besser ist, zwei Tage unbezahlt vor dem Rechner zu sitzen.

Zur Grundausstattung eines MySQL-Servers gehört heutzutage auf jeden Fall das Percona Toolkit. Percona ist eine Firma, die Dienstleistungen rund um MySQL anbietet und den hervorragenden MySQL-Performanceblog betreibt. Percona ist unter anderem auf die Performanceoptimierung von MySQL spezialisiert und bietet einige der in diesem Zusammenhang entwickelten Tools als Open Source an – darunter auch das Toolkit. Es erweitert MySQL um einige Kommandozeilentools, die sich unter anderem auch mit der Analyse des Slow-Query-Logs beschäftigen. Das früher für diese Zwecke oft verwendete mk-query-digest ist inzwischen Bestandteil des Percona Toolkits und wird separat nicht mehr weiterentwickelt.

Das Vorhandensein entsprechender Kommandozeilentools macht die Lage zwar angenehmer, aber nicht unbedingt besser, da eine grafische Aufbereitung der Lage in der Regel besser und schneller weiterhilft. Genau an dieser Stelle verlässt man, wenn man vernünftig arbeiten will, den Bereich der Open Source. Dies betrifft an dieser Stelle übrigens den gesamten Bereich der grafischen Benutzeroberflächen für MySQL. Die von MySQL angebotenen Tools entsprechen nicht meinen Anforderungen, auf Windows habe ich jahrelang HeidiSQL eingesetzt. Inzwischen sieht es jedoch so aus, dass ich lieber einmalig Geld für ein vernünftiges Tool bezahle, als mich über mangelnde Funktionen aufzuregen. Aus diesem Grund setze ich seit einem Wechsel auf Mac nur noch Navicat ein.

Im Bereich des MySQL Profiling (was dann im Gegensatz zu mysql slow queries dann auch eine geeignete Suche bei Google darstellt) gibt es spezialisierte Benutzeroberflächen, die sowohl die Anzeige als auch die Analyse von Slow Queries zulassen und direkt entsprechende Optimierungstipps geben (Abb. 1).

Abb.1: Grafische Darstellung einer Analyse des MySQL Profilers

Abb.1: Grafische Darstellung einer Analyse des MySQL Profilers

Ich habe bereits 2009 in meinem Blog über den Jet Profiler berichtet, der neben der reinen Analyse auch Tipps gibt. Bleibt man zunächst im Bereich der Open Source, wird man sehr schnell den SQL Profiler finden, der für die meisten Anwendungsfälle ausreicht. Über eine Remote-Verbindung connectet das Tool, das für Windows, Mac und Linux erhältlich ist, zu einem oder mehreren MySQL-Servern. Die eigentliche Aufgabe des Tools ist das generelle Monitoring der Server, es analysiert also CPU und Speicherverbrauch. Darüber hinaus kann es aber auch Slow Queries sammeln und anzeigen. Nach eigenem Bekunden ist es effektiver und präziser als Jet Profiler. Letzterer sammelt einen Großteil seiner Informationen über den Befehl SHOW STATUS, wohingegen der SQL Profiler als echter Sniffer arbeitet und mehr Informationen sammeln (sollte). Die Wahrheit liegt wahrscheinlich irgendwo dazwischen, ändert aber nichts an der Tatsache, dass das Tool interessante Ergebnisse liefert. Auch hier ist es so, dass es eine komplette Session über einen längeren Zeitraum speichert und dann analysiert. An dieser Stelle sollte man übrigens nicht die Performanceeinbuße unterschätzen, die ein solches Profiling-Tool in einem Server verursacht. Es empfiehlt sich eine Performanceanalyse nicht zu Stoßzeiten durchzuführen – auch wenn man zu diesen Zeiten die besten Ergebnisse geliefert bekommt.

Der Vorteil des Jet Profilers gegenüber dem SQL Profiler liegt trotzdem klar auf der Hand: Es werden nicht nur Slow Queries gefunden, sondern auch Tipps gegeben, wie diese optimiert werden können. Der Jet Profiler unterscheidet zwischen einer kostenlosen Version und einer kostenpflichtigen und ist für Windows, Mac und Linux verfügbar. Der Unterschied zwischen den beiden Versionen für unseren Anwendungsfall ist der, dass in der kostenlosen Version ein Monitoring maximal 2 Stunden dauert, aber leider keine Slow Queries angezeigt werden – faktisch also nicht nutzbar ist. Stattdessen werden die Topanfragen angezeigt, also die Queries, die am häufigsten durchgeführt werden (Abb. 2).

Abb. 2: Darstellung der Top-Queries in Jet Profiler

Abb. 2: Darstellung der Top-Queries in Jet Profiler

Auch hier bietet sich Optimierungspotenzial, aber nicht in dem Maße wie Slow Queries sie bieten. Ein weiterer Vorteil von Jet Profiler ist die Nutzung eines SSH Tunnels (auch in der freien Version), die es ermöglicht, via SSH auf Server zuzugreifen, die keine Remote Connection zulassen. Das ist ein nicht zu vernachlässigender Vorteil, da die meisten gehosteten MySQL-Server nur über localhost erreichbar sind und keine Remote-Verbindung zulassen.

In beiden Fällen werden über die Slow Queries hinaus interessante Funktionen zur Verfügung gestellt, wie zum Beispiel die am meisten benutzten Datenbanken, die Tabellen, die die größte Last erzeugen, aber auch die Queries, die am häufigsten ausgeführt werden. Zumindest letztere sind ein guter Ansatzpunkt für ein Caching, wobei wir uns hier wieder im Bereich der Mikrooptimierung befinden. Slow Queries sind jedoch nach wie vor die Queries mit dem größten Optimierungspotenzial und diejenigen, die den Server am stärksten belasten. Aufgrund der Struktur dürfte ferner davon auszugehen sein, dass ein Monitoring mit Jet Profiler weniger Ressourcen verschlingt, als eines mit dem SQL Profiler.

Die Tippfunktion des Jet Profilers ist recht angenehm, denn in der Regel schafft sie es anzuzeigen, wieso eine Query langsam ist, und wie man diese optimiert. Allerdings arbeitet sie auch nicht immer zuverlässig, und es ist eher das Gesamtpaket, das klare Vorteile zur Analyse von MySQL bringt. Auf der Jet-Profiler-Homepage zeigt ein einminütiges Video schnell und effektiv die Möglichkeiten des Tools.

Beschränkt man sich auf das Hauptaugenmerk des Artikels – nämlich die Slow Queries – sollte eine Analyse über die Kommandozeile unter Zuhilfenahme der Percona-Tools ausreichend sein, denn letztendlich geht es um das Aufspüren dieser Queries und nicht um eine komplette Serveranalyse. Die Optimierung der Queries bleibt immer bei einem selbst hängen. Keines der dargestellten Tools bietet hier eine Universallösung, die mittels Knopfdruck die Probleme löst. Teilweise werden Tipps gegeben und meistens passen sie. Als erfahrener Datenbankentwickler und Programmierer sollte man allerdings durchaus wissen, wie man solche Probleme behebt – vorausgesetzt man findet sie. Letztendlich ist das die große Herausforderung, bei der einem alle dargestellten Tools helfen können. Alle Programme sind entweder Open Source oder können ausgiebig getestet werden.

Fazit

Die von MySQL gebotenen Optimierungsmöglichkeiten sind schier unendlich. Für eine einfache Webanwendung lässt sich in der Regel ein großer Geschwindigkeitsgewinn durch die Nutzung geeigneter Indizes erreichen. Auch eine dem Einsatz angepasste Konfiguration des Datenbankservers, dessen Default-Einstellungen nicht sonderlich optimiert sind, hilft in vielen Fällen weiter. Trotzdem kann man Queries erstellen, deren Ausführungszeit nicht akzeptabel ist. In manchen Fällen – gerade im Bereich des Data Mining – lässt sich dies nicht verhindern. In den meisten Fällen ist es jedoch so, dass eine funktionierende Query nicht unbedingt eine optimale Query darstellen muss.

Man muss an vielen Stellen genau abwägen, ob man sein Ziel nicht auch anders oder einfacher erreichen kann, und ob die in MySQL durchgeführten Operationen nicht besser in der Programmiersprache verankert wären. Es gibt hier keine Ideallösung, ein Entschärfen der Lage wird in der Regel nur durch vielfältigste Experimente erreicht. In manchen Fällen stellt man einfach die Query um, in anderen Fällen fügt man die Daten in PHP schneller zusammen, und ab und an löst man das Problem durch eine Anwendung, die wesentlich komplexer ist als die Ausgangs-Query, aber trotzdem schneller abgearbeitet wird.

Gerade MySQL-Optimierung ist ein oft vernachlässigter Bereich, was oft der Unwissenheit des Auftraggebers geschuldet ist. Die Abnahme einer Anwendung erfolgt meistens nicht in einer Lastsituation und nachträgliche Optimierungsarbeiten gehen oft zu Lasten des Programmierers. Dieser sieht – was durchaus verständlich ist – keine Notwendigkeit, eine zeitintensive Optimierung kostenlos durchzuführen. Gerade bei Anwendungen, die eine entsprechende Last erzeugen, sodass eine Optimierung notwendig wird, ist von zwei Umständen auszugehen: a) Der Programmierer hat schlecht gearbeitet oder b) Die Anwendung ist so wichtig/erfolgreich, dass eine Optimierung durchaus im Etat vorhanden sein sollte. Da wir alle erfahrene Programmierer sind, sollte von Punkt a) nicht auszugehen sein, der Kunde aber bezüglich des zweiten Punkts sensibilisiert werden. In Zeiten, in denen Google die Ladezeit einer Webanwendung als Rankingfaktor ansieht, sollte jede Möglichkeit genutzt werden und dem Kunden bekannt sein.

Aufmacherbild: Dolphins in blue sea wave.Cartoons seascape.Mesh.Raste r von Shutterstock / Urheberrecht: Tancha

Unsere Redaktion empfiehlt:

Relevante Beiträge

Meinungen zu diesem Beitrag

X
- Gib Deinen Standort ein -
- or -