Eine kurze Diskussion darüber, ob zu viele MySQL-Datenabfragen OOM verursachen

Mein Host verfügt nur über 100 GB Speicher. Wenn ich einen vollständigen Tabellenscan für eine 200 GB große Tabelle durchführen möchte, wird dann der Speicher des DB-Hosts aufgebraucht?

Wird beim Durchführen einer logischen Sicherung nicht lediglich die gesamte Datenbank gescannt? Wenn dies passieren würde, wäre der gesamte Speicher verbraucht und die logische Sicherung wäre schon vor langer Zeit fehlgeschlagen, nicht wahr?
Es scheint also, dass bei einem vollständigen Tabellenscan der großen Tabelle kein Problem auftreten sollte. Warum ist das so?

Auswirkungen eines vollständigen Tabellenscans auf die Serverebene

Angenommen, wir möchten jetzt einen vollständigen Tabellenscan einer 200G InnoDB-Tabelle db1.t durchführen. Wenn Sie die Scan-Ergebnisse auf dem Client speichern möchten, verwenden Sie natürlich einen Befehl wie diesen:

mysql -h$host -P$port -u$benutzer -p$pwd -e 
	"Wählen Sie * aus db1.t" > $Zieldatei

InnoDB-Daten werden im Primärschlüsselindex gespeichert, sodass ein vollständiger Tabellenscan tatsächlich direkt den Primärschlüsselindex der Tabelle t scannt. Da diese Abfrageanweisung keine weiteren Beurteilungsbedingungen hat, kann jede gefundene Zeile direkt in den Ergebnissatz eingefügt und dann an den Client zurückgegeben werden.

Also, wo existiert dieser „Ergebnissatz“?
Der Server muss keinen vollständigen Ergebnissatz speichern. Der Vorgang zum Abrufen und Senden von Daten läuft wie folgt ab:

• Holen Sie sich eine Zeile und schreiben Sie sie in net_buffer. Die Größe dieses Speichers wird durch den Parameter net_buffer_length definiert, der Standardwert ist 16k
• Holen Sie wiederholt Zeilen ab, bis der Net_Buffer voll ist, und rufen Sie dann die Netzwerkschnittstelle auf, um sie auszusenden
• Wenn die Übertragung erfolgreich ist, wird der Net_Buffer gelöscht und anschließend die nächste Zeile entnommen und in den Net_Buffer geschrieben.
• Wenn die Sendefunktion EAGAIN oder WSAEWOULDBLOCK zurückgibt, bedeutet dies, dass der lokale Netzwerkstapel (Socket-Sendepuffer) voll ist und in den Wartemodus wechselt. Warten Sie, bis der Netzwerkstapel wieder beschreibbar ist, bevor Sie mit dem Senden fortfahren
  

Abfrageergebnis-Sendeprozess

sichtbar:

• Wenn eine Abfrage gesendet wird, beträgt die maximale Speichermenge, die von MySQL belegt wird, net_buffer_length und erreicht somit nicht 200 G.
• Der Socket-Sendepuffer kann nicht 200 G erreichen (Standarddefinition /proc/sys/net/core/wmem_default). Wenn der Socket-Sendepuffer voll ist, wird der Datenlesevorgang angehalten.

MySQL ist also tatsächlich ein „Lesen und Senden“. Dies bedeutet, dass der MySQL-Server die Ergebnisse nicht senden kann, wenn der Client die Daten langsam empfängt, und die Ausführungszeit der Transaktion länger ist.

Der folgende Status ist beispielsweise das Ergebnis, das von „show processlist“ auf dem Server angezeigt wird, wenn der Client den Inhalt des Socket-Empfangspuffers nicht liest.

Server sendet blockiert

Wenn Sie sehen, dass der Status immer „An Client senden“ lautet, bedeutet dies, dass der Netzwerkstapel auf dem Server voll ist.

Wenn der Client den Parameter –quick verwendet, wird die Methode mysql_use_result verwendet: Lesen Sie eine Zeile und verarbeiten Sie sie zeilenweise. Angenommen, die Logik eines bestimmten Geschäfts ist relativ komplex. Wenn die nach dem Lesen jeder Datenzeile zu verarbeitende Logik sehr langsam ist, dauert es lange, bis der Client die nächste Datenzeile abruft. Dies kann zu dem in der obigen Abbildung gezeigten Ergebnis führen.

Wenn eine Abfrage im normalen Online-Geschäft nur wenige Ergebnisse zurückgibt, wird daher empfohlen, die Schnittstelle mysql_store_result zu verwenden, um die Abfrageergebnisse direkt im lokalen Speicher zu speichern.

Voraussetzung ist natürlich, dass die Abfrage nur wenige Ergebnisse zurückgibt. Wenn es zu viele sind, belegt der Client fast 20 GB Speicher, weil eine große Abfrage ausgeführt wird. In diesem Fall müssen Sie stattdessen die Schnittstelle mysql_use_result verwenden.

Wenn Sie in der MySQL-Datenbank, für deren Verwaltung Sie verantwortlich sind, viele Threads im Status „An Client senden“ sehen, bedeutet das, dass Sie Ihre Kollegen aus der Geschäftsentwicklung bitten sollten, die Abfrageergebnisse zu optimieren und zu beurteilen, ob so viele zurückgegebene Ergebnisse sinnvoll sind.

Wenn Sie die Anzahl der Threads in diesem Zustand schnell reduzieren möchten, können Sie net_buffer_length auf einen größeren Wert setzen.

Manchmal sehe ich auf der Instanz viele Abfrageanweisungen mit dem Status „Daten werden gesendet“, aber es liegen keine Netzwerkprobleme vor. Warum dauert das Senden von Daten so lange?
Die Statusänderungen einer Abfrageanweisung sind wie folgt:

• Nachdem die MySQL-Abfrageanweisung in die Ausführungsphase eintritt, setzen Sie zunächst den Status auf Daten senden
• Senden Sie dann die spaltenbezogenen Informationen (Metadaten) des Ausführungsergebnisses an den Client
• Fahren Sie mit der Ausführung des Anweisungsflusses fort.
• Nachdem die Ausführung abgeschlossen ist, setzen Sie den Status auf eine leere Zeichenfolge.

Das heißt, „Daten senden“ bedeutet nicht notwendigerweise „Daten senden“, sondern kann jede Phase im Ausführungsprozess bedeuten. Sie können beispielsweise ein Szenario zum Warten auf eine Sperre erstellen und den Status des Datenversands anzeigen.

Das Lesen der gesamten Tabelle ist gesperrt:

Status der Datenübermittlung

Es ist ersichtlich, dass Sitzung2 auf die Sperre wartet und der Status als „Daten werden gesendet“ angezeigt wird.

• „An Client senden“ wird nur angezeigt, wenn sich ein Thread im Status „Warten auf den Empfang des Ergebnisses durch den Client“ befindet.
• Wenn dort "Daten senden" steht, bedeutet das nur "Ausführen"

Daher werden die Abfrageergebnisse segmentweise an den Client gesendet, sodass das Scannen der gesamten Tabelle und die Rückgabe einer großen Datenmenge nicht zu einer Speicherexplosion führt.

Das Obige ist die Verarbeitungslogik der Serverebene. Wie wird sie in der InnoDB-Engine gehandhabt?

Auswirkungen eines vollständigen Tabellenscans auf InnoDB

Eine der Funktionen des InnoDB-Speichers besteht darin, Aktualisierungsergebnisse zu speichern und mit dem Redo-Log zusammenzuarbeiten, um zufällige Schreibvorgänge auf die Festplatte zu vermeiden.

Die Datenseiten im Speicher werden im Pufferpool (kurz BP) verwaltet. In WAL übernimmt BP die Rolle der Aktualisierungsbeschleunigung.
BP kann Abfragen auch beschleunigen.

Aufgrund von WAL ist die Datenseite auf der Festplatte alt, wenn eine Transaktion festgeschrieben wird. Wenn eine Abfrage zum sofortigen Lesen der Datenseite vorliegt, sollte das Redo-Protokoll dann sofort auf die Datenseite angewendet werden?

unnötig. Da zu diesem Zeitpunkt das Ergebnis der Speicherdatenseite am aktuellsten ist, können Sie die Speicherseite direkt lesen. Zu diesem Zeitpunkt muss die Abfrage die Festplatte nicht lesen und die Ergebnisse werden direkt aus dem Speicher abgerufen, was sehr schnell ist. Daher kann Buffer Pool Abfragen beschleunigen.

Der Beschleunigungseffekt von BP auf Abfragen hängt von einem wichtigen Indikator ab, nämlich der Speichertrefferrate.
Sie können die aktuelle BP-Trefferquote eines Systems in den InnoDB-Statusergebnissen der Show Engine anzeigen. Generell gilt: Damit ein Online-System mit stabilen Diensten eine den Anforderungen entsprechende Reaktionszeit gewährleisten kann, muss die Speichertrefferrate über 99 % liegen.

Führen Sie „show engine innodb status“ aus. Anschließend werden die Worte „Buffer pool hit rate“ angezeigt, die die aktuelle Trefferquote zeigen. Beispielsweise beträgt die Trefferquote im Bild unten 100 %.

Im besten Fall können alle von der Abfrage benötigten Datenseiten direkt aus dem Speicher geholt werden, das entspricht einer Trefferquote von 100%.

Die Größe des InnoDB-Pufferpools wird durch den Parameter innodb_buffer_pool_size bestimmt. Es wird im Allgemeinen empfohlen, ihn auf 60 % bis 80 % des verfügbaren physischen Speichers einzustellen.

Vor etwa zehn Jahren betrug die Datenmenge auf einer einzelnen Maschine Hunderte von GB, während der physische Speicher mehrere GB groß war. Heute hat die Datenmenge auf einer einzelnen Maschine das T-Niveau erreicht, obwohl viele Server über 128 GB oder sogar mehr Speicher verfügen.

Daher ist innodb_buffer_pool_size häufig kleiner als die Datenmenge auf der Festplatte. Wenn ein Pufferpool voll ist und eine Datenseite von der Festplatte gelesen werden muss, muss eine alte Datenseite gelöscht werden.

InnoDB-Speicherverwaltung

Der Least Recently Used (LRU)-Algorithmus wird verwendet, um die am längsten ungenutzten Daten zu eliminieren.

• Grundlegender LRU-Algorithmus

ZU TUN

• InnoDB verwaltet den LRU-Algorithmus von BP, der mithilfe einer verknüpften Liste implementiert wird:
• state1, der Kopf der verknüpften Liste ist P1, was bedeutet, dass P1 die Datenseite ist, auf die zuletzt zugegriffen wurde
• Zu diesem Zeitpunkt greift eine Leseanforderung auf P3 zu, sodass dieser in den Zustand 2 wechselt und P3 nach vorne verschoben wird.
• Zustand 3 bedeutet, dass die aufgerufene Datenseite nicht in der verknüpften Liste vorhanden ist. Daher muss in BP eine neue Datenseite Px beantragt und am Kopf der verknüpften Liste hinzugefügt werden. Da der Speicher jedoch voll ist, kann kein neuer Speicher angefordert werden. Löschen Sie also den Pm-Datenseitenspeicher am Ende der verknüpften Liste, speichern Sie den Inhalt von Px und platzieren Sie ihn am Anfang der verknüpften Liste

Abschließend wird die Datenseite Pm gelöscht, auf die am längsten nicht zugegriffen wurde.
Was passiert, wenn zu diesem Zeitpunkt ein vollständiger Tabellenscan durchgeführt wird? Wenn Sie eine 200-G-Tabelle scannen möchten und es sich bei dieser Tabelle um eine Tabelle mit historischen Daten handelt, greift normalerweise kein Unternehmen darauf zu.

Anschließend werden durch das Scannen gemäß diesem Algorithmus alle Daten im aktuellen BP gelöscht und die Inhalte der während des Scanvorgangs aufgerufenen Datenseiten gespeichert. Mit anderen Worten enthalten die Daten in BP hauptsächlich die Daten in dieser historischen Datentabelle.

Für eine Bibliothek, die geschäftliche Dienste anbietet, ist dies nicht akzeptabel. Sie werden feststellen, dass die Trefferquote des BP-Speichers stark abfällt, der Festplattendruck zunimmt und die Antworten auf SQL-Anweisungen langsamer werden.

Daher kann InnoDB das ursprüngliche LRU nicht direkt verwenden. InnoDB optimiert es.

Verbesserter LRU-Algorithmus

InnoDB teilt die verknüpfte Liste im Verhältnis 5:3 in den neuen und den alten Bereich auf. In der Abbildung zeigt LRU_old auf die erste Position des alten Bereichs, der 5/8 der gesamten verknüpften Liste ausmacht. Das bedeutet, dass 5/8 am Anfang der verknüpften Liste der neue Bereich ist und 3/8 am Ende der verknüpften Liste der alte Bereich ist.

Verbesserter Ausführungsprozess des LRU-Algorithmus:

1. Status 1, um auf P3 zuzugreifen. Da sich P3 im neuen Bereich befindet, verschieben Sie es genau wie LRU vor der Optimierung an den Anfang der verknüpften Liste => Status 2
2. Wenn Sie dann auf eine neue Datenseite zugreifen möchten, die in der aktuellen verknüpften Liste nicht vorhanden ist, wird die Datenseite Pm dennoch eliminiert, die neu eingefügte Datenseite Px jedoch bei LRU_old platziert.
3. Bei Datenseiten im alten Bereich muss bei jedem Zugriff folgende Beurteilung vorgenommen werden:

• Wenn die Datenseite länger als 1 Sekunde in der LRU-Liste vorhanden ist, verschieben Sie sie an den Anfang der Liste.
• Wenn die Datenseite weniger als 1 Sekunde in der LRU-Liste vorhanden ist, bleibt ihre Position unverändert. 1 s wird durch den Parameter innodb_old_blocks_time gesteuert, mit einem Standardwert von 1000 und einer Einheit von ms.
  

Diese Strategie ist auf die Handhabung von Vorgängen wie vollständigen Tabellenscans zugeschnitten. Oder scannen Sie die 200G-Historische Datentabelle:
4. Beim Scanvorgang werden alle neu eingefügten Datenseiten in den alten Bereich gelegt
5. Eine Datenseite enthält mehrere Datensätze. Auf diese Datenseite wird mehrmals zugegriffen. Aufgrund der sequentiellen Abtastung beträgt das Zeitintervall zwischen dem ersten und dem letzten Zugriff auf diese Datenseite jedoch nicht mehr als 1 Sekunde, sodass sie weiterhin im alten Bereich gespeichert bleibt.
6. Fahren Sie mit dem Scannen nachfolgender Daten fort. Auf die vorherige Datenseite wird nicht erneut zugegriffen, sodass sie nie die Möglichkeit hat, an den Anfang der verknüpften Liste (Neuer Bereich) zu gelangen, und bald gelöscht wird.

Es ist ersichtlich, dass der größte Vorteil dieser Strategie darin besteht, dass BP zwar auch beim Scannen dieser großen Tabelle verwendet wird, jedoch keine Auswirkungen auf den jungen Bereich hat und somit die Abfragetrefferquote des Pufferpools als Reaktion auf das normale Geschäft sichergestellt wird.

Zusammenfassung

MySQL verwendet die Logik des gleichzeitigen Berechnens und Sendens, sodass bei Abfrageergebnissen mit einer großen Datenmenge nicht der vollständige Ergebnissatz auf der Serverseite gespeichert wird. Wenn der Client die Ergebnisse nicht rechtzeitig liest, blockiert er daher den MySQL-Abfrageprozess, führt jedoch nicht zu einer Speicherexplosion.

Bei der InnoDB-Engine kommt es aufgrund der Eliminierungsstrategie bei großen Abfragen nicht zu einem Anstieg der Speichernutzung. Da InnoDB außerdem den LRU-Algorithmus verbessert hat, können die Auswirkungen vollständiger Tabellenscans kalter Daten auf den Pufferpool kontrolliert werden.

Vollständige Tabellenscans sind immer noch relativ IO-intensiv, sodass während der Geschäftsspitzenzeiten keine vollständigen Tabellenscans direkt auf der Online-Masterdatenbank durchgeführt werden können.

Dies ist das Ende dieses Artikels darüber, ob zu viele MySQL-Datenabfragen OOM verursachen. Weitere relevante OOM-Inhalte zu MySQL-Datenabfragen finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder durchsuchen Sie die folgenden verwandten Artikel weiter. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!