Die bisherigen Teile der Artikelreihe haben Optimierungen beschrieben, die von normalen MySQL-Benutzern beim Anlegen und Indizieren von Tabellen vorgenommen werden. Es gibt aber auch Optimierungen, die nur von Systemadministratoren vorgenommen werden können, die die Kontrolle über den MySQL-Server oder den Rechner haben, auf dem er ausgeführt wird. Einige Serverparameter wirken sich direkt auf die Anfrageverarbeitung aus und können nach Bedarf eingestellt werden, und bestimmte Aspekte der Hardwarekonfiguration haben direkten Einfluss auf die Verarbeitungsgeschwindigkeit von Anfragen.
Bei der Durchführung administrativer Optimierungen sollten die folgenden Grundprinzipien beachtet werden:
-
Der Zugriff auf Daten im Speicher ist schneller als der Zugriff auf Daten, die auf der Festplatte gespeichert sind.
-
Je länger die Daten im Speicher gehalten werden, desto weniger Festplattenzugriffe sind notwendig.
-
Das Aufbewahren von Daten aus einem Index ist wichtiger als das Aufbewahren der Inhalte von Datensätzen.
In den folgenden Abschnitten werden Sie sehen, wie Sie diese Prinzipien anwenden können.
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |
Serverparameter
Der Server hat mehrere Parameter (Variablen), die Sie ändern können, um seinen Betrieb zu beeinflussen. Die wichtigsten Parameter sind die Größen des Tabellencaches und des Caches, der von den Tabellen-Handlern für Indizierungsoperationen verwendet wird.
Wenn Sie genug Speicher zur Verfügung haben, weisen sie ihn den Cachepuffern des Servers zu, um mehr Daten im Speicher zu halten und die Festplattenaktivitäten zu reduzieren. Dies ist von Vorteil, weil der Zugriff auf Daten im Speicher viel schneller ist als der Zugriff auf die Festplatte.
Der Tabellencache nimmt Informationen zu geöffneten Tabellen auf. Seine Größe wird mithilfe der Servervariablen table_cache gesteuert. Greift der Server auf viele Tabellen zu, dann wird dieser Cache gefüllt, und der Server muss Tabellen, die eine Zeit lang nicht verwendet wurden, schließen, um Platz zum Öffnen neuer Tabellen zu schaffen. Durch Überprüfen des Statusindikators Opened_tables können Sie ermitteln, wie effizient der Tabellencache ist:
SHOW STATUS LIKE ‘Opened_tables’;
Opened_tables gibt an, wie oft eine Tabelle geöffnet werden musste, weil sie zum gewünschten Zeitpunkt nicht geöffnet war (der Wert wird auch als Opens in der Ausgabe des Befehls mysqladmin status angezeigt). Ist die Zahl stabil oder leicht ansteigend, dann haben Sie wahrscheinlich einen geeigneten Wert gewählt. Nimmt der angezeigte Wert hingegen schnell zu, dann bedeutet dies, dass der Cache voll ist und Tabellen geschlossen werden müssen, damit andere Tabellen geöffnet werden können. Wenn Dateideskriptoren vorhanden sind, können Sie durch Erhöhen der Tabellencachegröße die Anzahl der Öffnungsoperationen für Tabellen verringern.
Weitere Servervariablen
-
Der Schlüsselpuffer wird von den MyISAM- und ISAM-Handlern zur Aufnahme von Indexblöcken für indexbezogene Operationen verwendet. Seine Größe wird durch die Servervariable
key_buffer_sizegesteuert. Größere Werte erlauben MySQL, mehr Indexblöcke gleichzeitig im Speicher zu halten, sodass es wahrscheinlicher wird, Schlüsselwerte im Speicher zu finden, ohne einen neuen Block von der Festplatte einlesen zu müssen. Die Standardgröße des Schlüsselpuffers beträgt 8 MByte. Dies ist, wenn Sie über viel Speicher verfügen, ein sehr konservativer Wert. Wenn Sie ihn wesentlich erhöhen, werden Sie eine beträchtliche Leistungsverbesserung bei indexbasierten Abrufvorgängen sowie bei der Erstellung und Modifikation von Indizes feststellen. -
Die InnoDB- und BDB-Handler verwenden zur Pufferung von Daten und Indexwerten eigene Caches. Deren Größe wird durch die Variablen
innodb_buffer_pool_sizebzw.bdb_cache_sizegesteuert. Der InnoDB-Handler arbeitet zudem mit einem Logpuffer, dessen Größe durch die Variableinnodb_log_buffer_sizebestimmt wird. -
Ein weiterer spezieller Cache ist der Anfragecache. Auf diesen werden wir in einem eigenen Abschnitt näher eingehen.
Ändern der Parameter
Halten Sie sich an die folgenden Regeln, wenn Sie die Parameter von Servereinstellungen ändern:
-
Ändern Sie immer nur einen Parameter gleichzeitig. Wenn Sie mehrere voneinander unabhängige Variablen ändern, wird die Bewertung der Folgen der einzelnen Änderungen unnötig erschwert.
-
Erhöhen Sie die Werte der Servervariablen immer nur ein wenig. Wenn Sie in der Annahme, davon schneller profitieren zu können, den Wert einer Variablen um einen riesigen Betrag erhöhen, steht Ihr System möglicherweise plötzlich ohne Ressourcen da; es kann sich aufhängen oder kriechend langsam werden, wenn Sie den Wert zu hoch einstellen.
-
Damit Sie eine Ahnung davon bekommen, welche Parametereinstellungen für Ihr System geeignet sein könnten, werfen Sie einen Blick in die Optionsdateien my-small.cnf, my-medium.cnf, my-large.cnf und my-huge.cnf, die mit den MySQL-Distributionen ausgeliefert werden (Sie finden Sie im Verzeichnis support-files in den Quellcodedistributionen bzw. im Verzeichnis share in den Binärdistributionen). Diese Dateien vermitteln Ihnen eine Vorstellung davon, welche Parameter für Server mit unterschiedlichem Betriebsumfang wichtig und welche Einstellungen für diese Parameter brauchbar sind.
Andere Strategien zur Optimierung
Es gibt noch andere Strategien, mit deren Hilfe Sie den Serverbetrieb optimieren können:
-
Deaktivieren Sie nicht benötigte Tabellen-Handler. Wenn Sie Handler deaktivieren, weist der Server ihnen keinen Speicher mehr zu, und diesen Speicher können Sie dann woanders einsetzen. Die ISAM-, InnoDB- und BDB-Handler können bei der Kompilierung des Servers vollständig deaktiviert werden, InnoDB- und BDB-Handler zusätzlich auch beim Serverstart.
-
Halten Sie die Berechtigungen für die Berechtigungstabellen einfach. Zwar legt der Server den Inhalt der Berechtigungstabelle im Speicher ab, sofern in den Tabellen tables_priv oder columns_priv Zeilen vorhanden sind, aber er muss die Berechtigungen auf Tabellen- und Spaltenebene bei jeder Anfrage überprüfen. Sind diese Tabellen leer, dann kann der Server die Berechtigungsüberprüfung so optimieren, dass diese Ebenen übersprungen werden.
-
Wenn Sie MySQL neu kompilieren, konfigurieren Sie es für den Einsatz statischer statt gemeinsam genutzter Bibliotheken. Dynamische Binärdateien, die gemeinsame Bibliotheken verwenden, sparen zwar Festplattenkapazität, aber statische Binärdateien sind schneller (Sie können statische Binärdateien allerdings nicht verwenden, wenn Sie benutzerdefinierte Funktionen laden wollen, denn der UDF-Mechanismus benötigt dynamische Verknüpfungen).
Der Anfragecache
Seit MySQL 4.0.1 kann der Server zur Beschleunigung wiederholt ausgeführter SELECT-Anweisungen einen Anfragecache verwenden. Die hiermit zu erzielende Leistungssteigerung ist oft dramatisch. Der Anfragecache funktioniert wie folgt:
-
Wird eine bestimmte
SELECT-Anweisung zum ersten Mal ausgeführt, dann merkt sich der Server sowohl die Anfrage als auch ihre Ergebnisse. -
Erkennt der Server die Anfrage erneut, dann führt er sie nicht noch einmal aus, sondern er holt sich das Ergebnis direkt aus dem Cache und gibt es an den Client zurück.
-
Die Anfragespeicherung basiert auf dem exakten Text der Anfragestrings, so wie sie vom Server empfangen wurden: Sind also die Stringtexte zweier Anfragen identisch, dann werden auch die Anfragen selbst als identisch betrachtet. Nicht identisch sind Anfragen, die sich in der Groß-/Kleinschreibung unterscheiden oder von Clients stammen, die verschiedene Zeichensätze oder Kommunikationsprotokolle einsetzen. Auch Anfragen, die sich auf unterschiedliche Tabellen beziehen, werden bei ansonsten identischem Aufbau als unterschiedlich eingestuft (dies gilt auch, wenn sie sich auf Tabellen gleichen Namens in unterschiedlichen Datenbanken beziehen).
-
Wird eine Tabelle aktualisiert, dann werden alle Anfragen, die sich auf diese Tabelle beziehen, ungültig und infolgedessen verworfen. Dadurch wird verhindert, dass der Server veraltete Daten zurückgibt.
Unterstützung
Die Unterstützung des Anfragecaches ist standardmäßig implementiert. Wollen Sie den Cache hingegen nicht nutzen, um den damit verbundenen – minimalen! – Ressourcenmehrbedarf zu umgehen, dann können Sie den Server ohne diese Unterstützung kompilieren, indem Sie das configure-Script mit der Option -- without-query-cache ausführen.
Bei Servern mit Anfragecacheunterstützung wird der Cachebetrieb von den Werten dreier Variablen gesteuert:
-
query_cache_size.Bestimmt die Größe des Anfragecaches. Der Wert 0 deaktiviert den Cache (dies ist auch die Voreinstellung, d.h. der Cache wird nicht verwendet, bis Sie ihn explizit aktivieren). Um den Cache zu aktivieren, weisen Sie der Variablen den gewünschten Größenwert in Byte zu. Mit dem folgenden Eintrag in der Optionsdatei etwa erstellen Sie einen Anfragecache mit einer Größe von 16 MByte:[mysqld]
set-variable = query_cache_size=16M -
query_cache_limit.Diese Variable bestimmt die Höchstgrenze für im Cache abgelegte Anfrageergebnisse. Ergebnisse, die umfangreicher sind als hier vorgegeben, werden nicht im Cache gespeichert. -
query_cache_type.Bestimmt den Betriebsmodus des Anfragecaches. Mögliche Werte sind:
–0:Kein Caching.
–1:Alle Anfragen mit Ausnahme solcher, die mitSELECT SQL_NO_CACHEbeginnen, werden gespeichert.
–2:Gezieltes Caching, d. h., es werden nur Anfragen gespeichert, die mitSELECT SQL_CACHEbeginnen.
Caching-Eigenschaften
Die einzelnen Clients verwenden zunächst immer die Caching-Eigenschaften, die als Standard vom Server vorgegeben sind. Mit der folgenden Anweisung können Sie bei einem Client die Art und Weise ändern, wie seine Anfragen durch den Server gespeichert werden:
SET SQL_QUERY_CACHE_TYPE = value;
wert kann – entsprechend der Werte für die Variable query_cache_type – die Werte 0, 1 oder 2 annehmen. Synonym zu diesen lassen sich außerdem die Schlüsselwörter OFF, ON und DEMAND verwenden.
Ein Client kann das Caching einzelner Anfragen auch steuern, indem er dem Schlüsselwort SELECT einen Modifikator nachstellt. Ist das gezielte Caching aktiviert, dann wird eine Anfrage durch Einleitung mit SELECT SQL_CACHE im Cache abgelegt; SELECT SQL_NO_CACHE hingegen bewirkt, dass eine Anfrage nicht gespeichert wird.
Die Unterdrückung des Cachings kann bei Anfragen sinnvoll sein, die Informationen aus einer sich fortwährend ändernden Tabelle abrufen. In diesem Fall wird der Cache wohl kaum von großem Nutzen sein. Angenommen, Sie protokollieren Anfragen an Ihren Webserver in eine MySQL-Tabelle und setzen außerdem regelmäßig einen Satz von Übersichtsanfragen an diese Tabelle ab.
Bei einem normal beschäftigten Webserver werden häufig neue Zeilen in die Tabelle eingefügt, weswegen alle Anfrageergebnisse, die für diese Tabelle im Cache gespeichert würden, schnell ungültig würden. Zwar werden die Übersichtsanfragen wiederholt abgesetzt, aber die Wahrscheinlichkeit, dass diese Anfragen vom Cache profitieren können, ist doch eher gering. Unter diesen Umständen sollten Sie die Anfragen mit dem Modifikator SQL_NO_CACHE versehen, um dem Server mitzuteilen, dass er die Ergebnisse nicht im Cache abzulegen braucht.
Hardwarefragen
Bislang haben wir in diesem Abschnitt Methoden zur Verbesserung der Serverleistung beschrieben, ohne die Hardware zu erwähnen. Sie können natürlich bessere Hardware kaufen, um Ihren Server zu beschleunigen, aber nicht alle hardwareseitigen Änderungen sind gleichermaßen sinnvoll. Wenn Sie wissen wollen, welche Verbesserungen Sie bei der Hardware ins Auge fassen sollen, beachten Sie auch hier die gleichen Grundprinzipien wie bei der Optimierung der Serverparameter: Legen Sie so viele Daten wie möglich in schnellem Arbeitsspeicher ab, und lassen Sie sie so lange wie möglich dort.
Es gibt bei der Hardwarekonfiguration mehrere Aspekte, die im Hinblick auf die Verbesserung der Serverleistung geändert werden können:
-
Installieren Sie mehr Speicher in Ihren Computer. Dadurch können Sie den Cache und die Puffergrößen auf dem Speicher vergrößern. Der Server kann die Informationen im Speicher häufiger nutzen und muss seltener Daten von der Festplatte laden.
-
Konfigurieren Sie Ihr System so um, dass alle Festplattenauslagerungen entfernt werden, falls Sie genügend RAM haben, um sämtliche Auslagerungen im Speicherdateisystem vorzunehmen. Andernfalls lagern einige Systeme weiterhin Daten auch dann auf die Festplatte aus, wenn genügend RAM zur Verfügung steht.
-
Installieren Sie schnellere Festplatten, um die I/O-Zeit zu verbessern. Die Suchzeit ist hier die wichtigste Kenngröße für die Performance. Eine seitliche Bewegung der Schreib-/Leseköpfe ist langsam; wenn sie positioniert sind, ist das Lesen von Blöcken innerhalb derselben Spur vergleichsweise schneller. Wenn Sie allerdings die Wahl zwischen dem Hinzufügen von Speicher und der Anschaffung schnellerer Festplatten haben, dann sollten Sie sich für den Speicher entscheiden. Speicher ist immer schneller als Festplatten, und das Hinzufügen von Speicher erlaubt die Verwendung großer Caches, was wiederum die Festplattenaktivitäten reduziert.
Festplattenaktivität verteilen
-
Versuchen Sie, die Festplattenaktivität auf Geräte zu verteilen. Wenn Sie Lese- und Schreibvorgänge über mehrere Geräte verteilen können, werden diese Vorgänge schneller ausgeführt als bei Verwendung nur eines Geräts. Wenn Sie etwa Datenbanken auf einem Gerät und die Logdateien auf einem anderen ablegen, ist das gleichzeitige Schreiben auf diese beiden Geräte schneller, als wenn Datenbanken und Logdateien auf dem gleichen Gerät gespeichert wären. Beachten Sie, dass es nicht ausreichend ist, unterschiedliche Partitionen auf derselben Festplatte zu verwenden; das nützt nichts, weil Sie dann weiterhin von derselben physischen Ressource abhängig sind (in diesem Fall von den Schreib-/Leseköpfen).
Bevor Sie Daten auf ein anderes Gerät verschieben, sollten Sie sich mit den Ladeeigenschaften Ihres Systems vertraut machen. Wenn bereits eine andere wichtige Aktivität auf einem bestimmten Gerät stattfindet, könnte das Ablegen der Datenbank auf diesem Gerät die Performance verschlechtern. Beispielsweise ist kein erkennbarer Vorteil zu erzielen, wenn Sie sehr viel Webverkehr verarbeiten und eine Datenbank auf das Gerät verlagern, auf dem sich das Dokumentenverzeichnis Ihres Webservers befindet (wenn Sie nur ein einziges Laufwerk haben, können Sie natürlich nicht viel Festplattenaktivität umverteilen).
Der Einsatz von RAID-Geräten kann ebenfalls zu Zeit- und Performance-Gewinnen durch parallelen Hardwareeinsatz führen.
-
Verwenden Sie Multiprozessorhardware. Bei Multithreading-Anwendungen wie MySQL erlaubt das Vorhandensein mehrerer Prozessoren die gleichzeitige Ausführung mehrerer Threads.
Ausblick
Mit diesem Artikel endet unsere Serie zur Optimierung von MySQL-Anfragen. Weitere Information rund um Datenbanken und deren Optimierung finden Sie in unserer Rubrik Server/Datenbank. (mzu)
Unsere neue Serie zur Optimierung von MySQL-Anfragen basiert auf Kapitel 4 “Optimierung von Anfragen“ des Standardwerks „MySQL 4 – Konfiguration, Administration und Entwicklung“ von Paul Dubois aus dem SAMS-Verlag. Weitere MySQL-Bücher und eBooks auch von Paul Dubois finden Sie in unserem Partner-Buchshop bei informit.de.
|
Teil 2: SQL-Optimierung: Indexnutzung in Queries manuell steuern |
|
Teil 4: SQL-Optimierung: Daten effizient in Tabellen einfügen |