Eine datenbankbasierte Webseite ist mit PHP und MySQL schnell gestrickt. Da MySQL sehr schnell ist, fallen umständliche Queries und fehlende Indexe bei kleinen Projekten meist nicht auf.

Wird die Datenbank größer und die Webseite bekannter, kommt es plötzlich zu Engpässen. War anfangs eine Query mit einer Sekunde Verarbeitungszeit noch akzeptabel, kann dies bei einer gut besuchten Webseite den Server zum Stillstand bringen. Das System schaukelt sich auf, da mehr Anfragen reinkommen als abgearbeitet werden können.

Bei einem Internetshop hatte ich das Problem, das er im Laufe der Monate immer langsamer wurde. Der Seitenaufbau wurde immer zäher bis es fast unmöglich wurde in diesem Shop einzukaufen. Ursache war eine Statistik-Tabelle in die alle Useraktivitäten geloggt wurden. Logisch das diese Tabelle am Anfang leer war, aber sie wuchs mit jedem Seitenaufruf. Erst nach mehreren Monaten war die Tabelle 500MB groß und die Statistik-Queries benötigten auf Grund eines fehlenden Index bis zu 20 Sekunden!

Wenn MySQL scheinbar zu langsam ist, sollte man als erstes die Queries und Indizes optimieren. Man neigt schnell dazu gleich an der my.cnf rumzuschrauben oder Caches und Beschleuniger einzusetzen, aber die eigentliche Gründe für eine langsame MySQL sind oft falsche oder fehlende Indizes.

Um die Ursachen zu finden nutze ich zuerst Slow-Logs und den Explain Befehl.
Das loggen langsamer Queries kann durch folgende Einträge in die my.cnf aktiviert werden. Das Logfile erscheint im MySQL-Datenverzeichnis mit dem Dateinamen Servername-slow.log

log-slow-queries
long_query_time = 3

Mit dem Parameter "long_query_time" wird eingestellt, ab wieviel Sekunden eine Query als langsam gilt. In obigem Fall erscheinen alle Queries die länger als 3 Sekunden benötigen im Logfile. Es kann aber passieren, das eine eigentlich schnelle Query im Logfile erscheint weil der Server gerade überlastet war und länger für die Bearbeitung gebraucht hat oder die Tabelle auf Grund von anderen Prozessen gesperrt war. Deshalb sollte man die Einträge im Slow.log genauer betrachten und mittels Explain analysieren. Am besten man beginnt bei den Queries, die sehr oft im slow.log stehen. (Mit "log-long-format" könnte man sogar zusätzlich alle Queries loggen, die keinen Index benutzen.)

Als nächstes analysiere ich die vermeintlich langsamen Queries mit dem Explain- Befehl per MySQL Konsole oder im phpMyAdmin.

EXPLAIN SELECT inhalt, vname, name, eingabedatum FROM zitate WHERE name= "Goethe" and vname = "Johann Wolfgang von" ORDER BY eingabedatum;

Die Ausgabe könnte so aussehen:

id: 1
select_type: SIMPLE
table: zitate
type: ref
possible_keys: name, vname
key: name
key_len: 150
ref: const
rows: 1229
Extra: Using where; Using filesort

Aus diesen Daten kann man einiges lernen. So zeigt "possible_keys" welche Indizes theoretisch verwendet werden können und "key" welcher Index von MySQL tatsächlich verwendet wird.
Wichtig: MySQL verwendet immer nur einen Index pro Tabelle! Und zwar den, der wahrscheinlich am wenigsten Zeilen zurückliefert. Im obigen Beispiel ist es der Index "name". MySQL hätte sich auch für vname entscheiden können, kann jedoch niemals beide Indizes gleichzeitig nutzen. Hier lohnt der Aufbau eines mehrspaltigen Index.

Unter "Extra" erscheint die Meldung: "Using filesort", da ich mit "ORDER BY" die Ergebnisse nach Eingabedatum sortieren lasse. Sortiervorgänge sind immer ungünstig für die Performance aber für die Aufbereitung der Daten kann man nicht immer darauf verzichten.
Folgender Trick hat mir bei Sortiervorgängen schon oft geholfen: Man legt einen mehrspaltigen Index an, dessen letzte Spalte die zu sortierende Spalte ist. Für obiges Beispiel würde ich einen Index name+vname+eingabedatum anlegen. Wer danach die Query noch einmal mit Explain überprüft wird feststellen, dass das Extra "Using filesort" plötzlich verschwunden ist. Da der Index bereits richtig sortiert ist, benötigt MySQL keinen extra Sortiervorgang. Dieser Trick bringt enorme Performancevorteile. Benötigte die Query vorher 0,6 Sekunden so kommt die Antwort jetzt in 0,003 Sekunden.

Nach der Analyse der slow-logs ist es ratsam, die log-slow-queries zu deaktivieren, da es Performance schluckt.

Als nächsten Schritt sollte man in der my.cnf Konfigurationsdatei überprüfen ob der key_buffer so groß ist, dass möglichst alle Indizies in den Buffer passen und table_cache in etwa der Anzahl der Tabellen entspricht. Dabei macht es wenig Sinn die Werte extrem zu erhöhen ohne das der Bedarf dafür vorhanden ist. Eine Überprüfung der MySQL-Statusvariablen liefert genaue Angaben wie viele Tabellen geöffnet wurden und ob der key_buffer ausgelastet ist. Wer bei MySQL nur von localhost anspricht, kann in der my.cnf mit skip-networking die Netzwerkfunktionalität deaktivieren. Das bringt als kleinen Nebeneffekt etwas mehr Sicherheit.

Seit MySQL 4 gibt es einen Query-Cache. Diesen sollte man unbedingt aktivieren, da die Auswirkungen auf die Performance gerade bei den typischen Webseitenanwendungen deutlich sind. Häufig wiederkehrende Queries werden aus dem Cache geliefert ohne überhaupt die Datenbank abzufragen. (Solange sich die Tabelle nicht geändert hat). Mit dem Parameter query_cache_size wird dessen Größe festgelegt. Wie gut der Cache genutzt wird und ob es groß genug ist, erfährt man aus den Statusvariablen. Ich würde mit einer Cache- Größe von 8MB beginnen und die Statusvariablen beobachten.

©05.2005 - Thomas Schefter