Dynamische Web-Applikationen optimieren
MySQL-Tuning

Einleitung

Dynamische Web-Applikationen optimieren

Die Optimierung von MySQL-Datenbanken und Datenbankzugriffen ist eine komplexe Angelegenheit, bei der mehrere Faktoren zu berücksichtigen sind. Zuerst geht es einmal darum, zu analysieren, an welchen Stellen im Gesamtsystem Flaschenhälse die Performance hemmen können.

Wie jede andere Software werden auch Datenbankoperationen nachhaltig von der Hardware beeinflusst, auf denen sie ablaufen. Die Rechnung ist einfach: Je schneller einer Festplatte, desto höher ist auch die Geschwindigkeit bei Zugriffen auf eine MySQL-Datenbank. Wenn Sie die Optimierungsmöglichkeiten auf einer vorhandenen Harddisk ausschöpfen, kann das ebenfalls spürbare Beschleunigung mit sich bringen.

Auch die CPU und die Dimension des Hauptspeichers spielen bei Datenbankoperationen eine entscheidende Rolle. Grundsätzlich gilt: Viel hilft viel. Wenn Sie also daran gehen, eine Website mit intensiven Datenbankzugriffen und hoher Besucherfrequenz aufzubauen, tun Sie gut daran, einen Server auszuwählen, dessen Hardware-Ausstattung dem daraus erwachsenden Druck auch dauerhaft standhält.

Weiterhin kann das Systemdesign eine entscheidende Rolle spielen. Es ist nicht klug, einen Server, der solchen Anforderungen im Kundenbetrieb ausgesetzt sein soll, parallel auch noch als Test- und Entwicklungsumgebung zu nutzen. Auch bei einer großzügigen Hardware-Ausstattung kann dies die entscheidenden Millisekunden kosten, die dann im Live-Betrieb fehlen.

Benchmarks durchführen

Dynamische Web-Applikationen optimieren

Applikation optimieren

Dynamische Web-Applikationen optimieren

Wenn Sie Ihr System optimal eingerichtet haben und mit Tests eventuelle Performance-Defizite ermittelt haben, können Sie mit der Optimierung Ihrer Applikation beginnen.

Ein Großteil der Datenbank-Programmierung besteht in der Regel aus Anfragen an eine Datenbank per select. Hier gibt es auch reichlich Spielräume für Tuning und Optimierung.

Ein wichtiger Punkt betrifft die Berechtigungen, die für den User gesetzt sind, der die Abfrage durchführt. Je komplexer das Berechtigungssystem ist, desto eher entsteht ein Zeit raubender Overhead. Diesen können Sie dadurch vermeiden, dass Sie nur die Berechtigungen setzen, die das Skript auch benötigt. Reine Administrator-Rechte wie zum Beispiel zum Löschen von Datenbanken oder Tabellen werden in der Regel im Rahmen einer Web-Applikation nicht benötigt.

Tabellen optimieren

Dynamische Web-Applikationen optimieren

Mit dem Befehl optimize table können Sie eine MySQL-Tabelle optimieren und damit auch den Zugriff beschleunigen. Sie sollten diesen Befehl immer dann anwenden, wenn Sie große Teile der Tabelle gelöscht haben oder bei Tabellen mit Zeilen variabler Länge viele Änderungen durchgeführt haben. Das betrifft vor allem Tabellen, die varchar-, blob- oder text-Spalten enthalten. Gelöschte Datensätze werden in einer verknüpften Liste vorgehalten, und nachfolgende insert-Operationen benutzen die Positionen alter Datensätze. Das kostet Zeit. Mit optimize table geben Sie unbenutzten Platz frei und defragmentieren die Daten-Datei.

Der optimize-Befehl führt folgende Aktionen durch:

– Wenn die Tabelle gelöschte oder aufgeteilte Zeilen hat, wird sie repariert.
– Wenn die Index-Seiten nicht sortiert sind, werden sie sortiert.
– Wenn die Statistiken nicht aktuell sind und eine Reparatur nicht durch das Sortieren des Indexes durchgeführt werden kann, werden sie aktualisiert.

Während der Zeit, in der optimize table läuft, ist die Tabelle gesperrt.

Query-Cache nutzen

Dynamische Web-Applikationen optimieren

Ab Version 4.0.1 besitzt der MySQL-Server einen Query-Cache. Dort wird der Text einer select-Anfrage zusammen mit dem entsprechenden Ergebnis, das an den Client gesendet wird, gespeichert. Bei einer weiteren identischen Anfrage kann der Server die Ergebnisse aus dem Cache holen, statt dieselbe Anfrage noch einmal zu parsen und auszuführen.

Der Query-Cache kann seine Nützlichkeit vor allem in Umgebungen ausspielen, in denen sich Tabellen nicht häufig ändern und auf die viele identische Anfragen erfolgen. Bei klassischen Content-Management-Systemen, bei denen die Inhalte nicht kontinuierlich aktualisiert und verändert werden, kann dies einen erheblichen Zuwachs an Geschwindigkeit mit sich bringen.

Um den Query-Cache nutzen zu können, müssen Sie in der MySQL-Konfigurationsdatei (my.cnf bei Linux beziehungsweise my.ini bei Windows) folgende Parameter anpassen:

– Mit query_cache_limit legen Sie die Größe fest, ab der keine Ergebnisse mehr zwischengespeichert werden (Voreinstellung: 1 MByte).
– Mit query_cache_size weisen dem Cache Arbeitsspeicher zu. Ist der Wert 0 (Voreinstellung), ist der Query-Cache abgeschaltet.
– Mit dem Parameter query_cache_start up_type können Sie den Cache ein- oder ausschalten (Wert 1 oder 0). Der Wert 2 steht für on demand. Bei dieser Einstellung müssen Sie bei jeder Abfrage mit select sql_cache den Cache explizit aktivieren.

Folgende Befehle stehen ebenfalls im Zusammenhang mit dem Abfrage-Cache zur Verfügung. Mit flush query cache können Sie den Cache defragmentieren, um den Speicher besser zu benutzen und noch ein wenig Speed zu erzielen. Dieser Befehl entfernt keine Anfragen aus dem Cache. Der Befehl flush tables schreibt auch den Inhalt des Query-Cache zurück auf die Festplatte. Der Befehl reset query cache entfernt alle Anfragenergebnisse aus dem Cache.

Optimale Tabellenstruktur

Dynamische Web-Applikationen optimieren

Wenn Sie Ihre Tabellenstruktur so anlegen, dass die Daten möglichst wenig Platz auf der Platte und im Arbeitsspeicher benötigen, kann das die Performance verbessern, weil Lesezugriffe von der Platte schneller ablaufen und weniger Hauptspeicher benötigt wird. Das Indexieren nimmt darüber hinaus weniger Ressourcen in Anspruch, wenn es auf kleinere Spalten durchgeführt wird. MySQL unterstützt verschiedene Tabellentypen und Zeilenformate. Benutzen Sie möglichst die effizientesten Typen. Der Typ mediumint ist zum Beispiel besser als int, wenn es um Geschwindigkeit geht.

Deklarieren Sie Spalten, wenn möglich, immer als not null. Das macht alles schneller und Sie sparen ein Bit pro Spalte. Vermeiden Sie, einfach alle Spalten auf null zu haben, nur weil dies die Voreinstellung ist. Wenn Sie keine Spalten variabler Länge benutzen (varchar, text oder blob), wird ein Festgrößenformat benutzt, das schneller ist.

Der primäre Index einer Tabelle sollte immer so kurz wie möglich sein. Das macht die Identifikation einer Zeile sehr schnell. Kürzere Indexe sind nicht nur schneller, weil sie weniger Plattenplatz brauchen, sondern auch, weil Sie mehr Treffer im Index-Cache halten können und daher weniger Festplattenzugriffe benötigen. Erzeugen Sie nur die Indexe, die Sie tatsächlich brauchen. Indexe sind zwar gut für schnelle Abfragen, aber schlecht beim Speichern von Daten.

Unter bestimmten Umständen kann es vorteilhaft sein, eine Tabelle zu teilen, die sehr oft gescannt wird. Das gilt insbesondere, wenn sie ein dynamisches Tabellenformat hat und Sie durch die Zerlegung kleinere Tabellen mit statischem Formaten erhalten.

Weitere Optimierungstipps

Dynamische Web-Applikationen optimieren

Benutzen Sie persistente Verbindungen zur Datenbank. Ist das auf Grund der Systemgegebenheiten nicht möglich, sollten Sie den Wert der thread_cache_size-Variablen im Konfigurationsfile ändern.

Überprüfen Sie immer, ob Ihre Anfragen auch tatsächlich die Indexe benutzen, die Sie in den Tabellen erzeugt haben. Sie können dazu den explain-Befehl benutzen.

Versuchen Sie, komplexe select-Anfragen auf Tabellen zu vermeiden, die viel aktualisiert werden. Sortieren Sie eine Tabelle komplett neu in der Reihenfolge, in der Sie die Daten im Skript am häufigsten benutzen. Dies kann nach größeren Änderungen einen erheblichen Geschwindigkeitsvorteil mit sich bringen. Normalerweise nützt es nichts, eine Tabelle in verschiedene Tabellen aufzuteilen. Der einzige Fall, wo es wirklich etwas ausmacht, ist, wenn die Tabelle dynamische Zeilenlänge hat, was nicht in eine feste Zeilenlänge umgewandelt werden kann, oder wenn Sie die Tabelle sehr oft scannen müssen, die meisten der Spalten hierfür aber nicht benötigen. Dann kann ein Tabellen-Splitting Voteile bringen.

Wenn Sie sehr oft etwas auf der Grundlage von Informationen aus sehr vielen Zeilen berechnen müssen, ist es wahrscheinlich besser, eine neue Tabelle einzuführen und den Zähler in Echtzeit zu aktualisieren.

In einigen Fällen bringt es mehr Speed, Daten zu komprimieren und in einem Blob zu speichern. In diesem Fall müssen Sie in Ihrer Applikation zusätzlichen Code einbauen, um die Daten im Blob zu packen beziehungsweise zu entpacken. Das kann aber unter Umständen etliche Zugriffe einsparen.

Normalerweise sollten Sie versuchen, Daten nicht redundant zu speichern. Scheuen Sie sich aber nicht, von dieser Prämisse abzuweichen, wenn Sie dadurch einen Geschwindigkeitsvorteil erzielen können. Benutzen Sie insert /*! delayed */, wenn Sie nicht wissen, wann Ihre Daten geschrieben werden. Das erhöht die Geschwindigkeit, weil viele Datensätze mit einem einzigen Festplattenschreibzugriff geschrieben werden können. Benutzten Sie insert /*! low_priority */, wenn Sie wollen, dass Ihre Selects höhere Priorität haben.

Bilder separat speichern

Dynamische Web-Applikationen optimieren

Mit select /*! high_priority */ können Sie selects in der Warteschlange nach vorn bringen. Ein solcher select wird sogar dann durchgeführt, wenn jemand darauf wartet, etwas zu schreiben.

Benutzen Sie das mehrzeilige insert-Statement, um viele Zeilen mit einem SQL-Befehl zu speichern.

Bei einer normalen Webserver-Konfiguration sollten Bilder als separate Dateien gespeichert werden. Das heißt, Sie speichern nur einen Verweis zur Datei in der Datenbank. Normale Webserver können Dateien viel besser cachen als Datenbankinhalte. Daher ist dies ein einfacher Weg zu mehr Speed.

Spalten mit identischen Informationen in unterschiedlichen Tabellen sollten identisch deklariert sein und identische Namen haben. Versuchen Sie, die Namen kurz zu halten.