Heutzutage entwickeln und implementieren Entwickler weiterhin Anwendungen mithilfe der LAMP-Architektur (Linux®, Apache, MySQL und PHP/Perl). Serveradministratoren haben jedoch häufig kaum Kontrolle über die Anwendungen selbst, da die Anwendungen von jemand anderem geschrieben wurden. In dieser dreiteiligen Serie werden eine Reihe von Serverkonfigurationsproblemen erörtert, die sich auf die Anwendungsleistung auswirken können. Dieser Artikel, der dritte und letzte Teil dieser Serie, konzentriert sich auf die Optimierung der Datenbankschicht für maximale Effizienz.
Bezüglich der MySQL-Optimierung
gibt es drei Methoden, um die Laufgeschwindigkeit des MySQL-Servers zu beschleunigen. Die Reihenfolge der Effizienz von niedrig nach hoch ist:
Ersetzen Sie die problematische Hardware. Optimieren Sie die Einstellungen des MySQL-Prozesses. Optimieren Sie die Abfrage.
Der Austausch problematischer Hardware ist oft unsere erste Überlegung, vor allem weil Datenbanken viele Ressourcen beanspruchen können. Aber diese Lösung geht nur so weit. Tatsächlich können Sie häufig die Geschwindigkeit Ihrer Zentraleinheit (CPU) oder Festplatte verdoppeln und den Speicher um das Vier- bis Achtfache erhöhen.
Die zweite Methode besteht darin, den MySQL-Server (auch als mysqld bekannt) zu optimieren. Um diesen Prozess zu optimieren, müssen Sie den Speicher entsprechend zuweisen und mysqld wissen lassen, welcher Art von Last er ausgesetzt sein wird. Die Beschleunigung des Festplattenbetriebs ist nicht so wichtig wie die Reduzierung der Anzahl der erforderlichen Festplattenzugriffe. Um sicherzustellen, dass der MySQL-Prozess ordnungsgemäß funktioniert, bedeutet das auch, dass er mehr Zeit mit der Bearbeitung von Abfragen verbringt als mit Hintergrundaufgaben wie der Arbeit mit temporären Festplattentabellen oder dem Öffnen und Schließen von Dateien. Das Optimieren von MySQL steht im Mittelpunkt dieses Artikels.
Der beste Ansatz besteht darin, sicherzustellen, dass die Abfrage optimiert wurde. Das bedeutet, dass entsprechende Indizes auf die Tabelle angewendet und Abfragen so geschrieben werden, dass die MySQL-Funktionen voll ausgenutzt werden. Obwohl sich dieser Artikel nicht mit der Abfrageoptimierung befasst (ein Thema, das in vielen Büchern behandelt wurde), konfiguriert er mysqld so, dass Abfragen gemeldet werden, die möglicherweise optimiert werden müssen.
Obwohl diesen Aufgaben die Reihenfolge zugewiesen wurde, müssen Sie dennoch auf die Hardware- und MySQL-Einstellungen achten, um die Abfrage richtig abzustimmen. Es ist in Ordnung, wenn die Maschine langsam ist. Ich habe gesehen, dass sehr schnelle Maschinen wegen hoher Auslastung beim Ausführen gut gestalteter Abfragen ausfielen, weil mysqld durch viel Arbeit belegt war und die Abfrage nicht bedienen konnte.
Langsame Abfragen protokollieren
In einem SQL-Server werden Datentabellen auf der Festplatte gespeichert. Indizes bieten dem Server die Möglichkeit, bestimmte Datenzeilen in einer Tabelle zu finden, ohne die gesamte Tabelle durchsuchen zu müssen. Wenn die gesamte Tabelle durchsucht werden muss, spricht man von einem Tabellenscan. Im Allgemeinen möchten Sie möglicherweise nur eine Teilmenge der Daten in der Tabelle abrufen, sodass ein vollständiger Tabellenscan viel Festplatten-E/A und damit viel Zeit verschwendet. Dieses Problem verschärft sich, wenn Daten verbunden werden müssen, da mehrere Datenzeilen auf beiden Seiten der Verbindung verglichen werden müssen.
Natürlich verursachen Tabellenscans nicht immer Probleme; manchmal ist es effizienter, die gesamte Tabelle zu lesen, als eine Teilmenge der Daten auszuwählen (diese Entscheidungen werden vom Abfrageplaner im Serverprozess getroffen). Wenn der Index ineffizient verwendet wird oder überhaupt nicht verwendet werden kann, verlangsamt er die Abfragen, und dieses Problem wird umso schwerwiegender, je höher die Auslastung des Servers und die Größe der Tabelle sind. Abfragen, deren Ausführung länger als ein bestimmter Zeitraum dauert, werden als langsame Abfragen bezeichnet.
Sie können mysqld so konfigurieren, dass diese langsamen Abfragen in einem entsprechend benannten Protokoll für langsame Abfragen protokolliert werden. Anschließend überprüfen Administratoren dieses Protokoll, um festzustellen, welche Teile der Anwendung einer weiteren Untersuchung bedürfen. Listing 1 zeigt die Konfiguration, die in my.cnf vorgenommen werden muss, um eine langsame Abfrageprotokollierung zu ermöglichen.
Listing 1. MySQL-Protokoll für langsame Abfragen aktivieren
[mysqld]; aktiviert das langsame Abfrageprotokoll, standardmäßig 10 Sekunden log-slow-queries; Protokollabfragen, die keine Indizes verwenden, auch wenn sie weniger als long_query_time dauern. Abfragen, die keine Indizes verwenden
Diese drei Einstellungen werden zusammen verwendet, um Abfragen zu protokollieren, deren Ausführung länger als 5 Sekunden dauert und die keine Indizes verwenden. Bitte beachten Sie die Warnung zu log-queries-not-using-indexes: Sie müssen MySQL 4.1 oder höher verwenden. Langsame Abfrageprotokolle werden im MySQL-Datenverzeichnis gespeichert und haben den Namen hostname-slow.log. Wenn Sie einen anderen Namen oder Pfad verwenden möchten, können Sie dazu log-slow-queries = /new/path/to/file in my.cnf verwenden.
Das Lesen langsamer Abfrageprotokolle erfolgt am besten über den Befehl mysqldumpslow. Durch Angabe des Pfads zur Protokolldatei können Sie eine sortierte Liste langsamer Abfragen sowie deren Häufigkeit in der Protokolldatei anzeigen. Eine sehr nützliche Funktion besteht darin, dass mysqldumpslow alle vom Benutzer angegebenen Daten entfernt, bevor die Ergebnisse verglichen werden, sodass verschiedene Aufrufe derselben Abfrage als ein einziger Aufruf gezählt werden. Dies kann dabei helfen, die Abfrage zu identifizieren, die die meiste Arbeit erfordert.
Caching-Abfragen
Viele LAMP-Anwendungen stützen sich stark auf Datenbanken, führen jedoch immer wieder dieselben Abfragen aus. Jedes Mal, wenn eine Abfrage ausgeführt wird, muss die Datenbank dieselbe Aufgabe erfüllen: die Abfrage analysieren, bestimmen, wie sie ausgeführt wird, die Informationen von der Festplatte laden und die Ergebnisse an den Client zurückgeben. MySQL verfügt über eine Funktion namens Abfrage-Cache, die Abfrageergebnisse (die später verwendet werden) im Speicher speichert. In vielen Fällen wird dadurch die Leistung erheblich verbessert. Das Problem besteht jedoch darin, dass das Abfrage-Caching standardmäßig deaktiviert ist.
Fügen Sie query_cache_size = 32M zu /etc/my.conf hinzu, um einen 32 MB großen Abfragecache zu aktivieren.
Überwachen des Abfragecaches
Nach der Aktivierung des Abfragecaches ist es wichtig zu verstehen, ob er effektiv genutzt wird. MySQL verfügt über mehrere Variablen, die Sie sich ansehen können, um zu verstehen, was im Cache vor sich geht. Listing 2 zeigt den Status des Caches.
Listing 2. Abfrage-Cache-Statistiken anzeigen
mysql> SHOW STATUS LIKE 'qcache%';+-------------------------+------------+ |. Variablenname |. Wert |+-----------+| |. 14640664 ||. Qcache_inserts || 042 |+------------- -------- ---+----------------+8 Zeilen im Satz (0,00 Sek.)
Die Erläuterung dieser Elemente ist in Tabelle 1 aufgeführt.
Tabelle 1. Beschreibung des Variablennamens der MySQL-Abfrage-Cache-Variable
Qcache_free_blocks Die Anzahl benachbarter Speicherblöcke im Cache. Eine große Zahl weist darauf hin, dass möglicherweise Fragmente vorhanden sind. FLUSH QUERY CACHE defragmentiert den Cache, um einen freien Block zu erhalten.
Qcache_free_memory Gibt Speicher im Cache frei.
Qcache_hits wird jedes Mal erhöht, wenn eine Abfrage im Cache trifft.
Qcache_inserts wird jedes Mal erhöht, wenn eine Abfrage eingefügt wird. Die Fehlschussquote ist die Anzahl der Treffer dividiert durch die Anzahl der Einfügungen. Subtrahieren Sie diesen Wert von 1, um die Trefferquote zu erhalten. Im obigen Beispiel landen etwa 87 % der Abfragen im Cache.
Qcache_lowmem_prunes Gibt an, wie oft der Cache nicht mehr über genügend Speicher verfügte und geleert werden musste, um Platz für weitere Abfragen zu schaffen. Diese Zahl lässt sich am besten über einen längeren Zeitraum hinweg betrachten. Wenn die Zahl zunimmt, kann dies auf eine starke Fragmentierung oder einen geringen Speicherbedarf hinweisen. (Die oben genannten free_blocks und free_memory können Ihnen sagen, um welchen Fall es sich handelt).
Qcache_not_cached Die Anzahl der Abfragen, die nicht zum Zwischenspeichern geeignet sind, normalerweise weil es sich nicht um SELECT-Anweisungen handelt.
Qcache_queries_in_cache Die Anzahl der derzeit zwischengespeicherten Abfragen (und Antworten).
Qcache_total_blocks Anzahl der Blöcke im Cache.
Der Unterschied lässt sich häufig dadurch erkennen, dass diese Variablen im Abstand von einigen Sekunden angezeigt werden. Dies kann dabei helfen, festzustellen, ob der Cache effizient genutzt wird. Durch Ausführen von FLUSH STATUS können einige Zähler zurückgesetzt werden, was sehr hilfreich sein kann, wenn der Server schon eine Weile läuft.
Es ist sehr verlockend, einen sehr großen Abfragecache zu verwenden und zu erwarten, dass alles zwischengespeichert wird. Da mysqld Wartungsarbeiten am Cache durchführen muss, beispielsweise eine Bereinigung, wenn der Speicher knapp wird, kann es sein, dass der Server beim Versuch, den Cache zu verwalten, stecken bleibt. Wenn der FLUSH QUERY CACHE lange dauert, ist der Cache in der Regel zu groß.
Durchsetzen von Grenzwerten
Sie können Grenzwerte in mysqld erzwingen, um sicherzustellen, dass die Systemlast nicht zu einer Erschöpfung der Ressourcen führt. Listing 3 zeigt einige wichtige ressourcenbezogene Einstellungen in my.cnf.
Listing 3. MySQL-Ressourceneinstellungen
set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100
In der ersten Zeile wird die maximale Anzahl an Verbindungen verwaltet. Ähnlich wie bei MaxClients in Apache besteht die Idee darin, sicherzustellen, dass nur die vom Dienst zugelassene Anzahl von Verbindungen hergestellt wird. Um die maximale Anzahl der derzeit auf dem Server hergestellten Verbindungen zu ermitteln, führen Sie SHOW STATUS LIKE 'max_used_connections' aus.
Zeile 2 weist mysqld an, alle Verbindungen zu beenden, die länger als 10 Sekunden inaktiv waren. In einer LAMP-Anwendung entspricht die Zeit, die zum Herstellen einer Verbindung zur Datenbank benötigt wird, normalerweise der Zeit, die der Webserver benötigt, um die Anfrage zu verarbeiten. Wenn die Last zu hoch ist, bleibt die Verbindung manchmal hängen und belegt Platz in der Verbindungstabelle. Wenn Sie mehrere interaktive Benutzer haben oder dauerhafte Verbindungen zur Datenbank verwenden, ist es nicht ratsam, diesen Wert niedriger einzustellen!
Die letzte Zeile ist eine sichere Methode. Wenn ein Host Probleme hat, eine Verbindung zum Server herzustellen, und es viele Male erneut versucht, bevor er aufgibt, wird der Host gesperrt und kann erst nach FLUSH HOSTS ausgeführt werden. Standardmäßig reichen 10 Fehler aus, um eine Sperre auszulösen. Wenn Sie diesen Wert auf 100 ändern, hat der Server genügend Zeit, das Problem zu beheben. Wenn die Verbindung nach 100 Wiederholungsversuchen nicht hergestellt werden kann, hilft die Verwendung eines höheren Werts nicht viel und es kann sein, dass überhaupt keine Verbindung hergestellt wird.
Puffer und Caching
MySQL unterstützt über 100 anpassbare Einstellungen, aber glücklicherweise genügt die Beherrschung einiger weniger. Um die richtigen Werte für diese Einstellungen zu finden, können Sie die Statusvariablen über den Befehl SHOW STATUS anzeigen, der feststellen kann, ob mysqld wie erwartet funktioniert. Der den Puffern und Caches zugewiesene Speicher darf den im System verfügbaren Speicher nicht überschreiten, daher erfordert die Optimierung normalerweise einige Kompromisse.
Die einstellbaren MySQL-Einstellungen können für den gesamten mysqld-Prozess oder für einzelne Clientsitzungen gelten.
Serverseitige Einstellungen
Jede Tabelle kann als Datei auf der Festplatte dargestellt werden, die zuerst geöffnet und dann gelesen werden muss. Um das Lesen von Daten aus Dateien zu beschleunigen, speichert mysqld diese geöffneten Dateien bis zu einer maximalen Anzahl zwischen, die durch table_cache in /etc/mysqld.conf angegeben wird. Listing 4 zeigt eine Möglichkeit, Aktivitäten im Zusammenhang mit dem Öffnen einer Tabelle anzuzeigen.
Listing 4. Aktivitäten anzeigen, die Tabellen öffnen
mysql> SHOW STATUS LIKE 'open%tables';+-------------+-------+|. -------+-------+|. 5000 |+-------------+------- +2 Reihen im Satz (0,00 Sek.)
Listing 4 zeigt, dass derzeit 5.000 Tabellen geöffnet sind und 195 Tabellen geöffnet werden müssen, da im Cache keine verfügbaren Dateideskriptoren vorhanden sind (da die Statistiken zuvor gelöscht wurden, sind möglicherweise nur 5.000 offene Tabellen vorhanden). . Wenn Opened_tables bei der erneuten Ausführung des Befehls SHOW STATUS schnell ansteigt, deutet dies darauf hin, dass die Cache-Trefferquote unzureichend ist. Wenn Open_tables viel kleiner als die table_cache-Einstellung ist, ist der Wert zu groß (aber Platz zum Wachsen zu haben ist nie eine schlechte Sache). Verwenden Sie beispielsweise table_cache = 5000, um den Cache der Tabelle anzupassen.
Ähnlich wie beim Tabellencache gibt es auch einen Cache für Threads. mysqld erzeugt nach Bedarf Threads, wenn Verbindungen empfangen werden. Auf einem ausgelasteten Server, auf dem sich Verbindungen schnell ändern, kann das Zwischenspeichern von Threads für die spätere Verwendung die anfängliche Verbindung beschleunigen.
Listing 5 zeigt, wie Sie feststellen können, ob genügend Threads zwischengespeichert sind.
Listing 5. Thread-Nutzungsstatistiken anzeigen
mysql> SHOW STATUS LIKE 'threads%';+-----+-------+|. --------------+-------+|. Threads_connected ||. 3 |+---- ---------------+--------+4 Reihen im Satz (0,00 Sek.)
Der wichtige Wert hier ist Threads_created. Dieser Wert wird jedes Mal erhöht, wenn mysqld einen neuen Thread erstellen muss. Wenn diese Zahl bei der Ausführung aufeinanderfolgender SHOW STATUS-Befehle schnell ansteigt, sollten Sie versuchen, den Thread-Cache zu vergrößern. Um dies zu erreichen, können Sie beispielsweise thread_cache = 40 in my.cnf verwenden.
Der Schlüsselpuffer enthält den Indexblock der MyISAM-Tabelle. Im Idealfall sollten Anfragen für diese Blöcke aus dem Speicher und nicht von der Festplatte kommen. Listing 6 zeigt, wie man ermittelt, wie viele Blöcke von der Festplatte und wie viele aus dem Speicher gelesen wurden.
Listing 6. Bestimmen der Keyword-Effizienz
mysql> zeigt Status wie '%key_read%';+-----+----------+| ----------+|. Key_read_requests || 98247 |+-------. -----------+-----------+2 Reihen im Satz (0,00 Sek.)
Key_reads stellt die Anzahl der Anfragen dar, die auf die Festplatte gelangen, und Key_read_requests ist die Gesamtzahl. Die Fehlerquote ist die Anzahl der Leseanforderungen, die auf die Festplatte gelangt sind, dividiert durch die Gesamtzahl der Leseanforderungen – in diesem Fall etwa 0,6 Fehler im Speicher pro 1.000 Anforderungen. Wenn die Anzahl der Festplattentreffer 1 pro 1.000 Anfragen überschreitet, sollten Sie eine Erhöhung des Schlüsselwortpuffers in Betracht ziehen. Beispielsweise setzt key_buffer = 384M den Puffer auf 384 MB.
Temporäre Tabellen können in komplexeren Abfragen verwendet werden, bei denen Daten vor der weiteren Verarbeitung in einer temporären Tabelle gespeichert werden müssen (z. B. eine GROUP BY-Klausel). Idealerweise wird die temporäre Tabelle im Speicher erstellt. Wenn die temporäre Tabelle jedoch zu groß wird, muss sie auf die Festplatte geschrieben werden. Listing 7 enthält Statistiken zur Erstellung temporärer Tabellen.
Listing 7. Bestimmen der Verwendung temporärer Tabellen
mysql> SHOW STATUS LIKE 'created_tmp%';+----------+-------+| |. +------+|. Created_tmp_tables || |. +-------+-------+3 Reihen im Satz (0,00 Sek.)
Created_tmp_tables wird jedes Mal erhöht, wenn eine temporäre Tabelle verwendet wird; Created_tmp_disk_tables wird auch für festplattenbasierte Tabellen erhöht. Für dieses Verhältnis gibt es keine strengen Regeln, da es von der jeweiligen Abfrage abhängt. Wenn Sie Created_tmp_disk_tables im Laufe der Zeit beobachten, sehen Sie das Verhältnis der erstellten Festplattentabellen und können die Effizienz Ihres Setups bestimmen. Sowohl tmp_table_size als auch max_heap_table_size steuern die maximale Größe temporärer Tabellen. Stellen Sie daher sicher, dass beide Werte in my.cnf festgelegt sind.
Sitzungsspezifische Einstellungen
Die folgenden Einstellungen gelten für jede Sitzung. Seien Sie beim Festlegen dieser Zahlen sehr vorsichtig, da diese Optionen multipliziert mit der Anzahl der möglicherweise vorhandenen Verbindungen eine große Speichermenge darstellen! Sie können diese Zahlen innerhalb einer Sitzung per Code ändern oder diese Einstellungen in my.cnf für alle Sitzungen ändern.
Wenn MySQL sortieren muss, weist es einen Sortierpuffer zu, der die Datenzeilen speichert, während diese von der Festplatte gelesen werden. Wenn die zu sortierenden Daten zu groß sind, müssen die Daten in einer temporären Datei auf der Festplatte gespeichert und erneut sortiert werden. Wenn die Statusvariable sort_merge_passes groß ist, weist dies auf Festplattenaktivität hin. Listing 8 zeigt einige Statuszählerinformationen im Zusammenhang mit der Sortierung.
Listing 8. Sortierstatistiken anzeigen
mysql> SHOW STATUS LIKE "sort%";+----+--------+|. -------+|. Sort_rows ||. ----+---------+4 Reihen im Satz (0,00 Sek.)
Wenn sort_merge_passes groß ist, bedeutet dies, dass Sie auf sort_buffer_size achten müssen. Beispielsweise setzt sort_buffer_size = 4M den Sortierpuffer auf 4 MB.
MySQL weist außerdem etwas Speicher zum Lesen der Tabelle zu. Im Idealfall stellt ein Index genügend Informationen bereit, um nur die benötigten Zeilen einzulesen, aber manchmal muss eine Abfrage (schlecht konzipiert oder aufgrund der Art der Daten) eine große Datenmenge aus der Tabelle lesen. Um dieses Verhalten zu verstehen, müssen Sie wissen, wie viele SELECT-Anweisungen ausgeführt wurden und wie oft die nächste Datenzeile in der Tabelle gelesen werden musste (anstatt direkt über den Index darauf zuzugreifen). Der Befehl zum Erreichen dieser Funktionalität ist in Listing 9 dargestellt.
Listing 9. Bestimmen des Tabellen-Scan-Verhältnisses
mysql> SHOW STATUS LIKE "com_select";+-------------+--------+|. ------+--------+|. Com_select |+----------+--------+1 Zeile im Satz (0,00 Sek.) mysql> SHOW STATUS LIKE "handler_read_rnd_next";+----------------------+----------- +| |+--------+----------+|. Handler_read_rnd_next |+- ----- ----+----------+1 Zeile im Satz (0,00 Sek.)
Handler_read_rnd_next / Com_select ergibt ein Tabellen-Scan-Verhältnis – in diesem Fall 521:1. Wenn der Wert 4000 überschreitet, sollten Sie read_buffer_size überprüfen, zum Beispiel read_buffer_size = 4M. Wenn diese Zahl 8 Millionen überschreitet, ist es an der Zeit, die Optimierung dieser Abfragen mit den Entwicklern zu besprechen!
3 Grundlegende Tools
Obwohl der Befehl SHOW STATUS beim Verständnis eines bestimmten Setups sehr nützlich sein kann, benötigen Sie auch einige Tools, um die großen Datenmengen zu interpretieren, die von mysqld bereitgestellt werden. Es gibt drei Tools, die ich für wichtig halte. Links dazu finden Sie im Abschnitt „Ressourcen“.
Die meisten Systemadministratoren sind mit dem Befehl top vertraut, der eine kontinuierlich aktualisierte Ansicht der von Aufgaben verbrauchten CPU und des Speichers bietet. mytop emuliert top; es bietet eine Ansicht aller verbundenen Clients und der von ihnen ausgeführten Abfragen. mytop bietet außerdem Live- und Verlaufsdaten zur Keyword-Puffer- und Abfrage-Cache-Effizienz sowie Statistiken zu laufenden Abfragen. Dies ist ein nützliches Tool, um zu sehen, was in Ihrem System vor sich geht (z. B. innerhalb von 10 Sekunden), Sie können sich Informationen zum Serverzustand ansehen und alle Verbindungen anzeigen, die Probleme verursachen.
mysqlard ist ein mit dem MySQL-Server verbundener Daemon, der dafür verantwortlich ist, alle 5 Minuten Daten zu sammeln und diese im Hintergrund in einer Round-Robin-Datenbank zu speichern. Es gibt eine Webseite, auf der Daten wie Tabellen-Cache-Nutzung, Schlüsselworteffizienz, verbundene Clients und temporäre Tabellennutzung angezeigt werden. Während mytop eine Momentaufnahme der Server-Gesundheitsinformationen bereitstellt, stellt mysqlard langfristige Gesundheitsinformationen bereit. Als Bonus verwendet mysqlard einige der gesammelten Informationen, um Vorschläge zur Optimierung des Servers zu machen.
Ein weiteres Tool zum Sammeln von SHOW STATUS-Informationen ist mysqlreport. Die Berichterstellung ist weitaus komplexer als bei MySQL, da jeder Aspekt des Servers analysiert werden muss. Dies ist ein großartiges Tool zum Optimieren Ihres Servers, da es entsprechende Berechnungen für Zustandsvariablen durchführt, um zu ermitteln, welche Probleme behoben werden müssen.
Fazit
In diesem Artikel wurden einige Grundkenntnisse zum Optimieren von MySQL vorgestellt und diese dreiteilige Serie zum Optimieren von LAMP-Komponenten abgeschlossen. Beim Tuning geht es im Wesentlichen darum, zu verstehen, wie Komponenten funktionieren, festzustellen, ob sie ordnungsgemäß funktionieren, einige Anpassungen vorzunehmen und eine Neubewertung durchzuführen. Jede Komponente – Linux, Apache, PHP oder MySQL – hat unterschiedliche Anforderungen. Wenn Sie jede Komponente einzeln verstehen, können Sie Engpässe reduzieren, die Ihre Anwendung verlangsamen können.