MySQL-Optimierung: Cache-Optimierung

Ich freue mich, dass einige Blogger meinen Artikel markiert haben. Nachdem ich Mark kennengelernt habe, folge ich ihm nur noch selten. Aber es zeigt indirekt, dass der Blogger, wenn er auf den Blog klickt, das Gefühl hat, dass dieser Blog wertvoll ist und seinen Wissensmangel ausgleichen kann. Das Wichtigste an einem Blog ist, dass es Ihnen selbst von Nutzen ist. Wenn es auch anderen von Nutzen ist, ist das das beste Ergebnis. Der Grund, warum ich darauf bestehe, einen Blog zu schreiben, besteht darin, so schnell wie möglich eine zuverlässige Lösung zu finden, wenn ich einige Wissenspunkte vergesse. Wenn Sie sich an das zusammengefasste Wissen erinnern, wird es langsamer vergessen. Nach langer Zeit wird dieses Wissen schließlich zu den Worten, die Sie fließend aussprechen können, und Sie werden keine Angst mehr haben, es zu vergessen. In diesem Blog geht es weiter um MySQL. Hier geht es um Cache-Optimierung. Der Gesprächsprozess ist gleichzeitig auch mein Lernprozess.

Werfen wir zunächst einen Blick auf unsere MySQL-Version. Auf meinem Mac ist die Version 5.7 installiert, und viele Inhalte haben sich geändert. Die hier besprochene Hauptversion ist 5.6.

[root@roverliang ~]# mysql --version
mysql Ver 14.14 Distrib 5.6.24, für Linux (x86_64) mit EditLine-Wrapper

1. MySQL-Cache-Klassifizierung

MySQL-Optimierung bezieht sich auf ein sehr großes System. Während des Interviews habe ich über SQL-Anweisungsoptimierung gesprochen. Diese Art der Optimierung ist ebenfalls effektiv, aber es handelt sich um eine Optimierung aus logischer Sicht. Aber warum kann MySQL einer hohen Parallelität nicht standhalten, wenn alle logischen Aspekte optimiert, alle Indizes hinzugefügt und die Tabellenstruktur sinnvoll entworfen wurde? Natürlich können Sie MySQL auch auf andere Weise entlasten, aber darauf gehen wir hier nicht ein. Bei MySQL müssen wir die Leistung der Maschine maximal ausnutzen, damit keine Rechenressourcen verschwendet werden und uns zur Verfügung stehen. MySQL läuft auf einem Server, insbesondere einem Linux-Server. Dann wirken sich Festplatte, CPU, Speicher und Netzwerk des Servers alle auf die Leistung von MySQL aus. MySQl ist sehr speicherintensiv. Der MySQL-Speicher eines Online-Servers beträgt ca. 80%. Ist der Speicher zu klein, bleibt eigentlich nur wenig Spielraum für andere Optimierungen.

Darüber hinaus ist auch die Verbindung ein wichtiger Aspekt, der sich auf die MySQL-Leistung auswirkt. Die Verbindung zwischen dem MySQL-Client und dem MySQL-Server ist das Ergebnis wiederholter Handshakes zwischen dem MySQL-Client und dem MySQL-Server. Jeder „Handshake“ umfasst Identitätsprüfung, Berechtigungsprüfung und andere Links. Der Handshake erfordert eine bestimmte Menge an Netzwerkressourcen und MySQL-Serverspeicherressourcen.

Eine Sache, die erwähnt werden muss, sind Sperrkonflikte. Bei Datenbanken mit hohen Anforderungen an die gleichzeitige Leistung haben heftige Sperrkonflikte enorme Auswirkungen auf die Datenbankleistung. Sperrkonflikte können den Aufwand für das Umschalten des Thread-Kontexts erheblich erhöhen, was jedoch nichts mit dem erwarteten Bedarf zu tun hat.

2. Status anzeigen und Variablen anzeigen

In den vorherigen Blogs der MySQL-Reihe sind uns diese Befehle häufig begegnet. Schauen wir uns also einmal an, welche Informationen diese beiden Befehle dem MySQL-Systemadministrator zeigen:

Status anzeigen

Wenn der MySQL-Dienst ausgeführt wird, sind die Statusinformationen der MySQL-Dienstinstanz dynamisch. Mit diesem Befehl können die Informationen zur Sitzungsstatusvariable der aktuellen MySQL-Serververbindung angezeigt werden. Standardmäßig beginnen Variablennamen mit einem Großbuchstaben.

Variablen anzeigen

Mit show variables werden verschiedene Systemvariablen der MySQL-Dienstinstanz angezeigt (z. B. globale Systemvariablen, Sitzungssystemvariablen, statische Variablen). Diese Variablen enthalten die Standardwerte der MySQL-Kompilierungszeitparameter oder die in my.cnf festgelegten Parameterwerte. Systemvariablen oder Parameter sind ein statisches Konzept. Standardmäßig bestehen Systemvariablennamen alle aus Kleinbuchstaben.

Verwenden Sie den MySQL-Befehl „show status“ oder „show session status“, um die Sitzungsvariableninformationen der aktuellen MySQL-Serververbindung anzuzeigen. Die Variablenwerte des Sitzungsstatus sind für den aktuellen MySQL-Client gültig, z. B.: Statusvariablen „Opened_tables“, „Opened_table_definitions“.

Cache-Mechanismus

Der Grund für die Effektivität des Caches liegt hauptsächlich darin, dass der Zugriff auf den Arbeitsspeicher oder den externen Speicher während der Programmausführung lokal erfolgt und die lokalen Merkmale räumliche und zeitliche Lokalität sind. Zeitliche Lokalität bedeutet, dass auf Daten, auf die gerade zugegriffen wurde, in naher Zukunft möglicherweise erneut zugegriffen wird, und räumliche Lokalität bedeutet, dass nach dem Zugriff auf einen bestimmten Standort wahrscheinlich auch auf die Daten an den benachbarten Standorten zugegriffen wird. Der MySQL-Cache-Mechanismus besteht darin, die Daten, auf die gerade zugegriffen wurde (zeitliche Lokalität), und die Daten, auf die in Zukunft zugegriffen wird (räumliche Lokalität), im Cache oder sogar im Hochgeschwindigkeitscache zu speichern. Dadurch wird die E/A-Effizienz verbessert.

Entsprechend den unterschiedlichen Cache-Lese- und Schreibfunktionen unterteilt MySQL den Cache in Puffer-Cache und Cache-Cache.

Puffercache. Da die Schreibgeschwindigkeit der Festplatte zu langsam oder die E/A zu häufig ist, wird die Effizienz der Festplatte enorm verschwendet. Anschließend können Sie warten, bis eine bestimmte Datenmenge im Cache gespeichert ist und diese dann auf einmal auf die Festplatte schreiben. Der Puffercache wird hauptsächlich zum Schreiben von Daten und zur Verbesserung der E/A-Leistung verwendet.

Cache Cache. Der Cache enthält im Allgemeinen Daten, auf die häufig zugegriffen wird, die aber selten geändert werden. Wenn der Cache voll ist, wird der LRU-Algorithmus aktiviert, um Daten zu löschen. Eliminieren Sie die am wenigsten genutzten Daten, um neuen Speicherplatz zu schaffen. Bei sehr großen Websites ist es jedoch schwierig, mit dieser Strategie hochfrequente Leseanforderungen zu reduzieren. Im Allgemeinen werden die Daten, auf die sehr häufig zugegriffen wird, statisch dargestellt und von nginx direkt an den Benutzer zurückgegeben. Je weniger Ihr Programm mit den Datenbank-E/A-Geräten interagiert, desto höher ist die Effizienz.

MySQL-Zeitüberschreitung

Bei der Verwendung von MySQL können verschiedene Timeout-Ausnahmen auftreten, normalerweise Verbindungstimeouts, Wartezeiten bei Sperren usw.

Überprüfen Sie die Timeout-Typen:

mysql> Variablen wie „%timeout%“ anzeigen;
+-----------------------------+----------+
| Variablenname | Wert |
+-----------------------------+----------+
| Verbindungstimeout | 10 |
| verzögertes_Einfügetimeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | AUS |
| interaktives_Timeout | 28800 |
| Wartezeit_für_Sperre | 31536000 |
| Zeitüberschreitung beim Lesen des Netzes | 30 |
| Zeitüberschreitung beim Schreiben von Nachrichten | 60 |
| rpl_stop_slave_timeout | 31536000 |
| Slave_Net_Timeout | 3600 |
| Wartezeitüberschreitung | 28800 |
+-----------------------------+----------+

1. Verbindungs-Timeout (connect_timeout)

Der Standardwert für connect_timeout beträgt 10 Sekunden. Das Herstellen einer MySQL-Verbindung ist das Ergebnis eines Handshakes zwischen Client und Server und ist das Ergebnis mehrerer Handshakes. Bei jedem Handshake muss neben der Überprüfung des Kontonamens und der Identitätsinformationen auch die Auflösung des Host- und Domänennamens überprüft werden. Wenn zwischen dem Client und dem Server ein Netzwerkfehler auftritt, kann der Parameter connect_timeout festgelegt werden, um wiederholte Handshakes zwischen ihnen zu verhindern.

interactive_timeout bezieht sich auf das interaktive Terminal, das in der Befehlszeile eingegeben wird. Wenn der Standardwert überschritten wird, wird die Verbindung getrennt.

wait_timeout bezeichnet ein nicht interaktives Terminal, wie beispielsweise eine von PHP instantiierte Mysql-Verbindung, die immer belegt ist und automatisch getrennt wird, wenn der durch diesen Parameter festgelegte Wert überschritten wird.

net_write_timeout Der MySQL-Server generiert einen großen Datensatz. Wenn der MySQL-Client diesen nicht innerhalb der durch diesen Wert festgelegten Zeit empfangen kann, wird die Verbindung getrennt.

net_read_timeout Wenn der MySQL-Client eine große Datenmenge liest und das Lesen nicht innerhalb des eingestellten Werts beenden kann, wird die Verbindung automatisch getrennt.

Wartezeit für InnoDB-Sperre

mysql> Variablen wie „innodb_lock_wait_timeout“ anzeigen;
+--------------------------+----------+
| Variablenname | Wert |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+----------+

Die Standardwartezeit für Sperren bei InnoDB beträgt 50 Sekunden. Legen Sie den Wartewert für Sperren auf Zeilenebene fest. Wenn bei einer Sperre die Wartezeit diesen Wert überschreitet, wird das SQL für die Sperre zurückgesetzt (nicht die gesamte Transaktion). Wenn Sie möchten, dass die gesamte Transaktion zurückgesetzt wird, müssen Sie den Parameter innodb_rollback_on_timeout aktivieren.

mysql> Variablen wie „%rollback%“ anzeigen;
+----------------------------+----------+
| Variablenname | Wert |
+----------------------------+----------+
| innodb_rollback_on_timeout | AUS |
| innodb_rollback_segments | 128 |
+----------------------------+----------+

Wenn innodb_rollback_on_timeout auf „true“ gesetzt ist, wird die gesamte Transaktion zurückgesetzt, wenn das Zeitlimit einer Transaktion überschritten wird.

Timeout der Replikationsverbindung

Wenn bei der Master-Slave-Konfiguration das Lesen des Binärprotokolls vom Master-Server (Master) durch den Slave-Server (Slave) fehlschlägt, wartet der Slave-Server auf „slave_net_timeout“ und ruft dann das Binärprotokoll erneut vom Master ab. Kann auf 10 s eingestellt werden.

mysql> Variablen wie „slave_net_timeout“ anzeigen;
+---------------------+------+
| Variablenname | Wert |
+---------------------+------+
| Slave_Net_Timeout | 3600 |
+---------------------+------+

Dieser Teil der Zusammenfassung hätte schon am Sonntagabend zusammengestellt werden sollen, es hat sich jedoch bis heute verzögert. Meine Zukunftspläne müssen erneut verschoben werden. Mein Aufschiebeproblem ist wirklich ernst.