15 wichtige Variablen, die Sie zur MySQL-Leistungsoptimierung kennen müssen (Zusammenfassung)

Vorwort:

MYSQL dürfte die beliebteste WEB-Backend-Datenbank sein. Obwohl NOSQL in letzter Zeit immer häufiger erwähnt wird, glaube ich, dass die meisten Architekten sich weiterhin für MYSQL zur Datenspeicherung entscheiden werden. Der Autor dieses Artikels fasst 15 wichtige Variablen für die MySQL-Leistungsoptimierung zusammen und sortiert sie. Wenn es Mängel gibt, die ergänzt werden müssen, weisen Sie bitte darauf hin.

1.STANDARD_SPEICHER_ENGINE

Wenn Sie bereits MySQL 5.6 oder 5.7 verwenden und Ihre Tabellen alle InnoDB sind, sind Sie bereits startklar. Wenn nicht, stellen Sie sicher, dass Sie Ihre Tabellen in InnoDB konvertieren und default_storage_engine auf InnoDB setzen.

Warum? Kurz gesagt, weil InnoDB die beste Speicher-Engine für MySQL (einschließlich Percona Server und MariaDB) ist – es unterstützt Transaktionen, hohe Parallelität und hat eine sehr gute Leistung (bei richtiger Konfiguration). Hier ist eine ausführliche Version, die erklärt, warum

2. INNODB_BUFFER_POOL_SIZE

Dies ist die wichtigste Variable von InnoDB. Wenn Ihre primäre Speicher-Engine InnoDB ist, ist diese Variable für Sie bei MySQL die wichtigste.

Grundsätzlich gibt innodb_buffer_pool_size an, wie viel Speicher MySQL dem InnoDB-Pufferpool zuweisen soll, der zum Speichern zwischengespeicherter Daten, sekundärer Indizes, schmutziger Daten (Daten, die geändert, aber nicht auf die Festplatte geschrieben wurden) und verschiedener interner Strukturen wie adaptiver Hash-Indizes verwendet wird.

Als Faustregel gilt: Auf einem eigenständigen MySQL-Server sollten Sie 80 % des gesamten Arbeitsspeichers des Rechners MySQL zuweisen. Wenn Ihr MySQL auf einem gemeinsam genutzten Server ausgeführt wird oder Sie wissen möchten, ob die Größe des InnoDB-Pufferpools richtig eingestellt ist, finden Sie hier weitere Einzelheiten.

3. INNODB_LOG_FILE_SIZE

Die InnoDB-Redo-Logdateieinstellung wird in der MySQL-Community auch Transaktionsprotokoll genannt. Bis MySQL 5.6.8 war der Standardwert des Transaktionsprotokolls innodb_log_file_size=5M der größte einzelne Leistungskiller von InnoDB. Ab MySQL 5.6.8 wurde der Standardwert auf 48 M erhöht, für viele leicht ausgelastete Systeme ist er jedoch immer noch viel zu niedrig.

Als Faustregel sollten Sie die Protokollgröße so einstellen, dass 1–2 Stunden Schreibvorgänge gespeichert werden, wenn Ihr Server ausgelastet ist. Wenn Sie sich nicht so viel Mühe machen möchten, erzielen Sie eine gute Leistung, indem Sie die Größe auf 1–2 G einstellen. Auch diese Variable ist sehr wichtig. Eine ausführlichere Einführung finden Sie hier.

Bevor wir zur nächsten Variable übergehen, erwähnen wir kurz innodb_log_buffer_size. „Kurz erwähnt“, weil es oft schlecht verstanden und überbetont wird. Tatsächlich müssen Sie die meiste Zeit nur einen kleinen Puffer verwenden – gerade genug, um Ihre kleinen Transaktionsänderungen aufzubewahren, bevor die Transaktion festgeschrieben und auf die Festplatte geschrieben wird.

Wenn Sie viele große Transaktionsänderungen haben, können Sie Ihre Leistung natürlich bis zu einem gewissen Grad verbessern, indem Sie die Größe des InnoDB-Protokollpuffers auf einen größeren Wert als den Standardwert ändern. Wenn Sie jedoch Autocommit verwenden oder Ihre Transaktionsänderungen weniger als ein paar KB betragen, ist es besser, den Standardwert beizubehalten.

4.INNODB_FLUSH_LOG_AT_TRX_COMMIT

Standardmäßig ist innodb_flush_log_at_trx_commit auf 1 gesetzt, was bedeutet, dass InnoDB die Synchronisierungsdaten unmittelbar nach dem Festschreiben jeder Transaktion auf die Festplatte schreibt. Wenn Sie Autocommit verwenden, ist jede Ihrer INSERT-, UPDATE- oder DELETE-Anweisungen ein Transaktions-Commit.

Die Synchronisierung ist ein aufwändiger Vorgang (insbesondere, wenn Sie keinen Rückschreibcache haben), da dabei ein tatsächlich synchroner physischer Schreibvorgang auf die Festplatte erfolgt. Es wird daher empfohlen, nach Möglichkeit nicht den Standardwert zu verwenden.

Die beiden möglichen Werte sind 0 und 2:

* 0 bedeutet, dass auf die Festplatte geschrieben wird, aber nicht synchronisiert wird (kein tatsächlicher IO-Vorgang beim Festschreiben einer Transaktion)

* 2 bedeutet keine Aktualisierung und keine Synchronisierung (kein tatsächlicher IO-Vorgang)

Wenn Sie den Wert also auf 0 oder 2 setzen, wird der Synchronisierungsvorgang einmal pro Sekunde durchgeführt. Der offensichtliche Nachteil besteht darin, dass die in letzter Sekunde übermittelten Daten verloren gehen können. Insbesondere wenn Ihre Transaktion übermittelt wurde, der Server dann aber sofort die Stromversorgung verliert, ist die Übermittlung gleichbedeutend damit, dass sie nicht erfolgt ist.

Wie sich herausstellt, ist dies für Finanzinstitute wie Banken unerträglich. Für die meisten Websites können Sie jedoch innodb_flush_log_at_trx_commit=0|2 festlegen, und selbst wenn der Server schließlich abstürzt, tritt kein großes Problem auf. Schließlich verwendeten noch vor wenigen Jahren viele Sites MyISAM und verloren bei einem Absturz 30 Sekunden an Daten (ganz zu schweigen vom unerträglich langsamen Wiederherstellungsprozess).

Was ist also der eigentliche Unterschied zwischen 0 und 2? Der spürbare Leistungsunterschied ist vernachlässigbar, da das Leeren in den Betriebssystem-Cache sehr schnell erfolgt. Daher sollte der Wert offensichtlich auf 0 gesetzt werden. Falls MySQL abstürzt (nicht die ganze Maschine), gehen keine Daten verloren, da sich die Daten bereits im Betriebssystem-Cache befinden und schließlich mit der Festplatte synchronisiert werden.

5.SYNC_BINLOG

Es gibt bereits eine Menge Dokumentation zu sync_binlog und seiner Beziehung zu innodb_flush_log_at_trx_commit, also stellen wir es kurz vor:

a) Wenn Ihr Server nicht als Slave eingerichtet ist und Sie keine Backups durchführen, ist die Einstellung sync_binlog=0 gut für die Leistung.

b) Wenn Sie Slave-Server haben und Backups durchführen, es Ihnen aber nichts ausmacht, dass einige Ereignisse im Binärprotokoll verloren gehen, wenn der Master abstürzt, setzen Sie zur besseren Leistung sync_binlog=0.

c) Wenn Sie über Slaves und Backups verfügen und Ihnen die Konsistenz der Slaves und die Möglichkeit zur Wiederherstellung bis zu einem bestimmten Zeitpunkt sehr wichtig sind (die Möglichkeit, die Datenbank mithilfe des aktuellsten konsistenten Backups und der Binärprotokolle bis zu einem bestimmten Zeitpunkt wiederherzustellen), sollten Sie innodb_flush_log_at_trx_commit=1 festlegen und ernsthaft die Verwendung von sync_binlog=1 in Erwägung ziehen.

Das Problem besteht darin, dass sync_binlog=1 teuer ist – jetzt muss jede Transaktion auch mit der Festplatte synchronisiert werden. Sie fragen sich vielleicht, warum Sie die beiden Synchronisierungen nicht zu einer einzigen kombinieren sollten. Und Sie haben Recht – neuere Versionen von MySQL (5.6 und 5.7, MariaDB und Percona Server) verfügen bereits über die Möglichkeit, Commits zusammenzuführen. In diesem Fall ist die Operation sync_binlog=1 also nicht so aufwändig, hat aber dennoch erhebliche Auswirkungen auf die Leistung älterer MySQL-Versionen.

6. INNODB_FLUSH_METHOD

Setzen Sie innodb_flush_method auf O_DIRECT, um doppeltes Puffern zu vermeiden. Sie sollten O_DIRECT nur dann nicht verwenden, wenn Ihr Betriebssystem es nicht unterstützt. Wenn Sie jedoch Linux verwenden, verwenden Sie O_DIRECT, um direkte E/A zu aktivieren.

Ohne direkte E/A kommt es zu doppelter Pufferung, weil alle Datenbankänderungen zuerst in den Betriebssystem-Cache geschrieben und dann mit der Festplatte synchronisiert werden. Der InnoDB-Pufferpool und der Betriebssystem-Cache enthalten also gleichzeitig eine Kopie derselben Daten. Insbesondere wenn Ihr Pufferpool auf 50 % des Gesamtspeichers beschränkt ist, bedeutet dies, dass Sie in einer schreibintensiven Umgebung möglicherweise bis zu 50 % Ihres Speichers verschwenden. Wenn die Beschränkung auf 50 % nicht besteht, verwendet der Server möglicherweise aufgrund der hohen Belastung des Betriebssystem-Cache den Swap-Speicher.

Setzen Sie einfach innodb_flush_method=O_DIRECT.

7. INNODB_BUFFER_POOL_INSTANCES

MySQL 5.5 führte gepufferte Instanzen ein, um interne Sperrkonflikte zu reduzieren und so den MySQL-Durchsatz zu verbessern.

In MySQL 5.5 trägt dies nur wenig zur Verbesserung des Durchsatzes bei, in MySQL 5.6 ist diese Verbesserung jedoch sehr groß, sodass Sie in MySQL 5.5 innodb_buffer_pool_instances=4 konservativ festlegen können und in MySQL 5.6 und 5.7 den Wert auf 8-16 Pufferpoolinstanzen einstellen können.

Sobald Sie es eingestellt haben, werden Sie keine große Leistungssteigerung feststellen, aber es sollte Ihnen in den meisten Situationen mit hoher Belastung eine ordentliche Leistung bieten.

Erwarten Sie übrigens nicht, dass diese Einstellung die Antwortzeit Ihrer einzelnen Anfragen verkürzt. Dieser Unterschied ist nur auf Servern mit hoher gleichzeitiger Auslastung sichtbar. Beispielsweise erledigen mehrere Threads viele Dinge gleichzeitig.

8. INNODB_THREAD_CONCURRENCY

Sie hören möglicherweise oft, dass Sie innodb_thread_concurrency=0 festlegen und es vergessen sollten. Dies gilt jedoch nur bei Verwendung auf einem Server mit geringer Auslastung. Wenn die CPU- oder IO-Auslastung Ihres Servers jedoch gesättigt ist, insbesondere mit gelegentlichen Spitzen, und das System in der Lage sein möchte, Abfragen bei Überlastung normal zu verarbeiten, wird dringend empfohlen, auf innodb_thread_concurrency zu achten.

InnoDB verfügt über eine Möglichkeit, die Anzahl der parallel ausgeführten Threads zu steuern – wir nennen dies einen Mechanismus zur Parallelitätskontrolle. Das meiste davon wird durch den innodb_thread_concurrency-Wert gesteuert. Wenn der Wert auf 0 gesetzt ist, wird die Parallelitätskontrolle ausgeschaltet, sodass InnoDB alle eingehenden Anfragen sofort verarbeitet (so viele wie möglich).

Dies ist in Ordnung, wenn Sie 32 CPU-Kerne und nur 4 Anfragen haben. Stellen Sie sich aber vor, Sie verfügen nur über 4 CPU-Kerne und 32 Anfragen – wenn Sie 32 Anfragen gleichzeitig verarbeiten lassen, fangen Sie an, sich Probleme zuzuschreiben. Da diese 32 Anfragen nur über 4 CPU-Kerne verfügen, sind sie offensichtlich mindestens 8-mal langsamer als üblich (tatsächlich mehr als 8-mal). Dennoch verfügt jede dieser Anfragen über ihre eigenen äußeren und inneren Sperren, wodurch die Wahrscheinlichkeit einer Anforderungsstapelung hoch ist.

So ändern Sie diese Variable, ausgeführt in der MySQL-Eingabeaufforderung:

SETZEN Sie global innodb_thread_concurrency=X;

Für die meisten Workloads und Server ist eine Einstellung von 8 ein guter Ausgangspunkt. Sie können den Wert dann schrittweise erhöhen, wenn der Server dieses Limit erreicht und die Ressourcen nicht mehr ausreichend genutzt werden. Sie können den aktuellen Status der Abfrageverarbeitung über „show engine innodb status\G“ anzeigen und nach Zeilen ähnlich der folgenden suchen:

22 Abfragen innerhalb von InnoDB, 104 Abfragen in der Warteschlange

9.SKIP_NAME_RESOLVE

Dies muss erwähnt werden, da es immer noch viele Leute gibt, die es nicht hinzugefügt haben. Sie sollten skip_name_resolve hinzufügen, um eine DNS-Auflösung beim Verbinden zu vermeiden.

In den meisten Fällen werden Sie nichts bemerken, wenn Sie dies ändern, da die DNS-Serverauflösung in den meisten Fällen ziemlich schnell ist. Wenn der DNS-Server jedoch ausfällt, wird dies auf Ihrem Server als „nicht authentifizierte Verbindung“ angezeigt und deshalb werden alle Anfragen plötzlich langsamer.

Warten Sie also nicht, bis dies geschieht, um Änderungen vorzunehmen. Fügen Sie jetzt diese Variable hinzu und vermeiden Sie eine hostnamenbasierte Autorisierung.

10. INNODB_IO_CAPACITY, INNODB_IO_CAPACITY_MAX

* innodb_io_capacity: wird verwendet, um die Menge der Schreib-E/A zu steuern, die von MySQL pro Sekunde beim Aktualisieren fehlerhafter Daten ausgeführt wird.

* innodb_io_capacity_max: Steuert unter Druck die Menge an Schreib-E/A pro Sekunde, die MySQL beim Löschen fehlerhafter Daten durchführt.

Erstens hat dies nichts mit Lesevorgängen zu tun – also damit, was eine SELECT-Abfrage macht. Bei Lesevorgängen tut MySQL sein Bestes, um die Ergebnisse zu verarbeiten und zurückzugeben. Bei Schreibvorgängen aktualisiert MySQL in einer Schleife im Hintergrund. In jeder Schleife wird geprüft, wie viele Daten aktualisiert werden müssen, und es werden für Aktualisierungsvorgänge nicht mehr als die von innodb_io_capacity angegebene Anzahl verwendet. Hierzu gehört auch das Zusammenführen von Änderungspuffern (der Änderungspuffer ist der Schlüssel zur sekundären Speicherung schmutziger Seiten, bevor diese auf die Festplatte geschrieben werden).

Zweitens muss ich erklären, was mit „unter Druck“ gemeint ist. In MySQL wird es „Notfall“ genannt. Das ist der Fall, wenn MySQL im Hintergrund löscht und einige Daten löschen muss, um neue Schreibvorgänge zu ermöglichen. Dann verwendet MySQL innodb_io_capacity_max.

Warum also sollten innodb_io_capacity und innodb_io_capacity_max festgelegt werden?

Der beste Ansatz besteht darin, den zufälligen Schreibdurchsatz Ihres Speicher-Setups zu messen und dann innodb_io_capacity_max auf die maximalen IOPS einzustellen, die Ihr Gerät erreichen kann. innodb_io_capacity sollte auf 50–75 % davon eingestellt werden, insbesondere wenn Ihr System hauptsächlich schreibintensiv ist.

Normalerweise können Sie die IOPS Ihres Systems vorhersagen. Beispielsweise kann ein RAID10, das aus 8 15k-Festplatten besteht, etwa 1000 zufällige Schreibvorgänge pro Sekunde durchführen, daher können Sie innodb_io_capacity=600 und innodb_io_capacity_max=1000 festlegen. Viele günstige Enterprise-SSDs erreichen 4.000–10.000 IOPS usw.

Es ist keine große Sache, wenn dieser Wert nicht perfekt eingestellt ist. Beachten Sie jedoch, dass die Standardwerte von 200 und 400 Ihren Schreibdurchsatz begrenzen, sodass es gelegentlich zu Störungen beim Flush-Vorgang kommen kann. Wenn dies passiert, kann es sein, dass der Schreib-IO-Durchsatz Ihrer Festplatte erreicht wurde oder der Wert zu klein eingestellt ist und den Durchsatz begrenzt.

11. INNODB_STATS_ON_METADATA

Wenn Sie MySQL 5.6 oder 5.7 ausführen, müssen Sie den Standardwert von innodb_stats_on_metadata nicht ändern, da dieser bereits richtig eingestellt ist.

In MySQL 5.5 oder 5.1 wird jedoch dringend empfohlen, diese Variable zu deaktivieren. Wenn sie aktiviert ist, fragen Befehle wie „show table status“ INFORMATION_SCHEMA sofort ab, anstatt vor der Ausführung einige Sekunden zu warten, was zusätzliche E/A-Vorgänge erfordern würde.

Ab Version 5.1.32 ist dies eine dynamische Variable, d. h. Sie müssen den MySQL-Server nicht neu starten, um sie auszuschalten.

12. INNODB_BUFFER_POOL_DUMP_AT_SHUTDOWN und INNODB_BUFFER_POOL_LOAD_AT_STARTUP

Die Variablen innodb_buffer_pool_dump_at_shutdown und innodb_buffer_pool_load_at_startup haben nichts mit der Leistung zu tun, aber wenn Sie den MySQL-Server gelegentlich neu starten (etwa um Wirkung zu erzielen), dann sind sie relevant. Wenn beide aktiviert sind, wird der Inhalt des MySQL-Pufferpools (genauer gesagt die zwischengespeicherten Seiten) in einer Datei gespeichert, wenn Sie MySQL stoppen. Wenn Sie MySQL das nächste Mal starten, wird im Hintergrund ein Thread gestartet, um den Inhalt des Pufferpools zu laden und so die Aufwärmgeschwindigkeit um das Drei- bis Fünffache zu erhöhen.

Zwei Dinge:

Erstens wird beim Schließen nicht der Inhalt des Pufferpools in eine Datei kopiert, sondern nur die Tablespace-ID und die Seiten-ID. Das sind genügend Informationen, um die Seite auf der Festplatte zu lokalisieren. Anschließend können diese Seiten mit einer großen Anzahl sequenzieller Lesevorgänge sehr schnell geladen werden, anstatt dass Tausende kleiner zufälliger Lesevorgänge erforderlich sind.

Zweitens wird der Inhalt beim Start im Hintergrund geladen, da MySQL nicht warten muss, bis der Inhalt des Pufferpools geladen ist, bevor es mit der Annahme von Anforderungen beginnt (es scheint also keine Auswirkungen zu geben).

Ab MySQL 5.7.7 werden standardmäßig nur 25 % der Pufferpoolseiten in Dateien geschrieben, wenn MySQL heruntergefahren wird, aber Sie können diesen Wert steuern – verwenden Sie innodb_buffer_pool_dump_pct, 75-100 wird empfohlen.

Diese Funktion wird erst seit MySQL 5.6 unterstützt.

13. INNODB_ADAPTIVE_HASH_INDEX_PARTS

Wenn Sie einen MySQL-Server mit vielen SELECT-Abfragen betreiben (und ihn so weit wie möglich optimiert haben), dann wird der adaptive Hash-Index Ihr nächster Engpass sein. Ein adaptiver Hash-Index ist ein dynamischer Index, der intern von InnoDB verwaltet wird und die Leistung für die am häufigsten verwendeten Abfragemuster verbessert. Diese Funktion kann durch einen Neustart des Servers deaktiviert werden, ist jedoch in allen MySQL-Versionen standardmäßig aktiviert.

Diese Technik ist recht komplex, führt jedoch in den meisten Fällen bei den meisten Abfragetypen zu einer deutlichen Beschleunigung. Wenn jedoch zu viele Abfragen an die Datenbank gehen, wird ab einem bestimmten Punkt zu viel Zeit mit dem Warten auf AHI-Sperren und -Verriegelungen verbracht.

Wenn Sie MySQL 5.7 haben, besteht dieses Problem nicht – innodb_adaptive_hash_index_parts ist standardmäßig auf 8 eingestellt, sodass der adaptive Hash-Index in 8 Partitionen aufgeteilt wird, da kein globales Mutex vorhanden ist.

In Versionen vor MySQL 5.7 gibt es jedoch keine Kontrolle über die Anzahl der AHI-Partitionen. Mit anderen Worten: Es gibt ein globales Mutex zum Schutz des AHI, was dazu führen kann, dass Ihre Auswahlabfragen häufig erfolglos bleiben.

Wenn Sie also 5.1 oder 5.6 ausführen und viele ausgewählte Abfragen haben, besteht die einfachste Lösung darin, auf dieselbe Version von Percona Server zu wechseln und die AHI-Partition zu aktivieren.

14.QUERY_CACHE_TYPE

Wenn man glaubt, dass die Abfrage-Zwischenspeicherung gut funktioniert, sollte man sie auf jeden Fall verwenden. Nun ja, manchmal funktioniert es. Dies ist jedoch nur bei geringer Auslastung sinnvoll, insbesondere wenn es sich hauptsächlich um Lesevorgänge und wenige oder keine Schreibvorgänge handelt.

Wenn dies der Fall ist, setzen Sie einfach query_cache_type=ON und query_cache_size=256M. Denken Sie jedoch daran, keinen höheren Wert als 256 M festzulegen, da es sonst aufgrund von Abfrage-Cache-Fehlern zu schwerwiegenden Serverpausen kommt.

Wenn Ihr MySQL-Server stark ausgelastet ist, wird empfohlen, query_cache_size=0 und query_cache_type=OFF festzulegen und den Server neu zu starten, damit die Änderungen wirksam werden. Auf diese Weise beendet MySQL die Verwendung des Abfrage-Cache-Mutex für alle Abfragen.

15.Tabelle_öffnen_Cache_Instanzen

Ab MySQL 5.6.6 kann der Tabellencache in mehrere Partitionen aufgeteilt werden.

Der Tabellencache wird zum Speichern einer Liste der aktuell geöffneten Tabellen verwendet, und beim Öffnen oder Schließen jeder Tabelle wird ein Mutex gesperrt – auch wenn es sich um eine implizite temporäre Tabelle handelt. Durch die Verwendung mehrerer Partitionen werden mögliche Konflikte deutlich verringert.

Ab MySQL 5.7.8 ist table_open_cache_instances=16 die Standardkonfiguration.

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, er wird für jedermanns Studium hilfreich sein. Ich hoffe auch, dass jeder 123WORDPRESS.COM unterstützen wird.