Lassen Sie uns darüber sprechen, ob MySQL OOM verursacht, wenn zu viele Datenabfragen vorhanden sind
- WBOYnach vorne
- 2022-01-07 18:14:412264Durchsuche
Dieser Artikel vermittelt Ihnen relevantes Wissen darüber, ob MySQL OOM verursacht, wenn zu viele Datenabfragen vorhanden sind. Ich hoffe, dass er Ihnen hilfreich sein wird.
Der Hostspeicher beträgt nur 100G. Jetzt müssen wir die gesamte Tabelle einer 200G großen Tabelle scannen. Wird der Speicher des DB-Hosts aufgebraucht sein?
Handt es bei einer logischen Sicherung nicht nur um einen Scan der gesamten Datenbank? Wenn dies den gesamten Speicher verschlingen würde, wäre das logische Backup dann nicht schon längst ausgefallen?
Es scheint also kein Problem mit einem vollständigen Tabellenscan des großen Tisches zu geben. Warum ist das so?
Die Auswirkungen des vollständigen Tabellenscans auf die Serverschicht
Angenommen, wir möchten jetzt einen vollständigen Tabellenscan für eine 200G-InnoDB-Tabelle db1.t durchführen. Wenn Sie die Scanergebnisse auf dem Client speichern möchten, verwenden Sie natürlich einen Befehl wie diesen:
mysql -h$host -P$port -u$user -p$pwd -e "select * from db1.t" > $target_file
InnoDB-Daten werden im Primärschlüsselindex gespeichert, sodass der vollständige Tabellenscan tatsächlich direkt den Primärschlüsselindex der Tabelle scannt T. Da diese Abfrageanweisung keine anderen Beurteilungsbedingungen hat, kann jede gefundene Zeile direkt in die Ergebnismenge eingefügt und dann an den Client zurückgegeben werden.
Wo existiert diese „Ergebnismenge“?
Der Server muss keinen vollständigen Ergebnissatz speichern. Der Prozess zum Abrufen und Senden von Daten ist wie folgt:
Erhalten Sie 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 16 KB Erfolgreich, löschen Sie es** „net_buffer“, fahren Sie dann mit der nächsten Zeile fort und schreiben Sie sie in „net_buffer“**
Wenn die Sendefunktion **„EAGAIN“ oder „WSAEWOULDBLOCK“** zurückgibt, bedeutet dies, dass die lokale Wenn der Netzwerkstapel (Socket-Sendepuffer) voll ist, geben Sie „wait“ ein. Bis der Netzwerkstapel wieder beschreibbar ist, senden Sie weiter
Sendevorgang für Abfrageergebnisse
Sichtbar:
Während des Sendevorgangs einer Abfrage beträgt der von MySQL maximal belegte interne Speicher ** „net_buffer_length“ ** So groß wird der 200G-Socket-Sendepuffer nicht erreicht und es ist unmöglich, 200G zu erreichen (Standarddefinition /proc/sys/net/core/wmem_default), wenn der Socket-Sendepuffer voll ist Daten werden ausgesetzt
MySQL ist also tatsächlich „lesend und sendend“. Dies bedeutet, dass der MySQL-Server die Ergebnisse nicht senden kann, wenn der Client langsam empfängt, und die Ausführungszeit der Transaktion länger wird.
Zum Beispiel ist der folgende Status das Ergebnis, das von show Processlist auf dem Server angezeigt wird, wenn der Client den Inhalt von **„Socket-Empfangspuffer“** nicht liest. Serverseitiges Senden ist blockiert
Die Voraussetzung ist natürlich, dass die Abfrage nicht viele Ergebnisse zurückgibt. Wenn zu viele vorhanden sind, belegt der Client fast 20 GB Speicher, da eine große Abfrage ausgeführt wird. In diesem Fall müssen Sie stattdessen die Schnittstelle „mysql_use_result“ verwenden.
Wenn Sie in MySQL, für dessen Pflege Sie verantwortlich sind, viele Threads unter „Senden an Client“ sehen, bedeutet das, dass Sie möchten, dass Ihre Geschäftsentwicklungsstudenten die Abfrageergebnisse optimieren und bewerten, ob so viele zurückgegebene Ergebnisse angemessen 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 festlegen.
Manchmal lautet der Status vieler Abfrageanweisungen auf der Instanz „Daten werden gesendet“, aber bei der Überprüfung des Netzwerks gibt es kein Problem. Warum dauert das Senden von Daten so lange?
Die Statusänderung einer Abfrageanweisung sieht folgendermaßen aus:
Nachdem die MySQL-Abfrageanweisung in die Ausführungsphase eintritt, setzt sie zunächst den Status auf „Daten werden gesendet“
Dann sendet sie die spaltenbezogenen Informationen Senden Sie das Ausführungsergebnis (Metadaten) an den Client
und setzen Sie dann den Prozess der Ausführung der Anweisung fort
Nachdem die Ausführung abgeschlossen ist, setzen Sie den Status auf eine leere Zeichenfolge.
Das heißt, „Senden von Daten“ bedeutet nicht unbedingt „Senden von Daten“, sondern kann in jeder Phase des Ausführungsprozesses erfolgen. Sie können beispielsweise ein Sperrwarte-Szenario erstellen und den Status „Daten werden gesendet“ anzeigen.
Das Lesen der gesamten Tabelle ist gesperrt:
Es ist ersichtlich, dass Sitzung2 auf die Sperre wartet und der Status als „Daten werden gesendet“ angezeigt wird. 
Nur wenn sich ein Thread im Status „Warten auf den Empfang von Ergebnissen durch den Client“ befindet, wird „An Client senden“ angezeigt.
Wenn er als „Daten werden gesendet“ angezeigt wird, bedeutet dies lediglich „Ausführen“.
Daher werden die Ergebnisse der Abfrage in Segmenten an den Client gesendet, sodass die gesamte Tabelle gescannt wird und die Abfrage eine große Datenmenge zurückgibt, ohne den Speicher zu sprengen.
Das Obige ist die Verarbeitungslogik der Serverschicht. Wie wird sie in der InnoDB-Engine verarbeitet?
Die Auswirkungen des vollständigen Tabellenscans auf InnoDB
Eine der Funktionen des InnoDB-Speichers besteht darin, die aktualisierten Ergebnisse zu speichern und mit dem Redo-Log zusammenzuarbeiten, um zufällige Schreibvorgänge auf die Festplatte zu vermeiden.
Die Datenseiten des Speichers werden im Pufferpool (als BP bezeichnet) verwaltet, und BP spielt eine Rolle bei der Beschleunigung von Aktualisierungen in WAL.
BP kann auch Abfragen beschleunigen.
Wenn die Transaktion festgeschrieben wird, ist die Datenseite auf der Festplatte aufgrund von WAL alt. Wenn eine Abfrage zum sofortigen Lesen der Datenseite vorliegt, sollte das Redo-Protokoll sofort auf die Datenseite angewendet werden?
Keine Notwendigkeit. Da zu diesem Zeitpunkt das Ergebnis der Speicherdatenseite das neueste ist, lesen Sie einfach die Speicherseite direkt. 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 der Pufferpool 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 Statusergebnissen der Show Engine innodb überprüfen. Im Allgemeinen muss für ein Online-System mit stabilem Service die Speichertrefferrate über 99 % liegen, um sicherzustellen, dass die Antwortzeit den Anforderungen entspricht.
Ausführen zeigt den Innodb-Status der Engine an. Sie sehen die Worte „Pufferpool-Trefferrate“, die die aktuelle Trefferrate anzeigt. Die Trefferquote im Bild unten beträgt beispielsweise 100 %.
Wenn alle für die Abfrage erforderlichen Datenseiten direkt aus dem Speicher abgerufen werden können, ist dies das Beste und die entsprechende Trefferquote beträgt 100 %.
Die Größe des InnoDB-Pufferpools wird durch den Parameter **"innodb_buffer_pool_size"** bestimmt. Es wird allgemein empfohlen, ihn auf 60 % ~ 80 % des verfügbaren physischen Speichers festzulegen.
Vor etwa zehn Jahren betrug das Datenvolumen einer einzelnen Maschine Hunderte von Gigabyte und der physische Speicher mehrere Gigabyte. Obwohl viele Server über 128 GB oder mehr Speicher verfügen können, hat das Datenvolumen einer einzelnen Maschine inzwischen das T-Niveau erreicht . .
So kommt es häufig vor, dass **"innodb_buffer_pool_size"** kleiner als die Festplattendatengröße ist. Wenn ein Pufferpool voll ist und eine Datenseite von der Festplatte gelesen werden muss, muss eine alte Datenseite entfernt werden.
InnoDB-Speicherverwaltung
verwendet den LRU-Algorithmus (Least Recent Used), um die Daten zu entfernen, die am längsten nicht verwendet wurden.
Grundlegender LRU-Algorithmus
Der LRU-Algorithmus von InnoDB zur Verwaltung von BP wird mithilfe einer verknüpften Liste implementiert:
state1, was bedeutet, dass P1 die zuletzt aufgerufene Datenseite ist
At Diesmal greift eine Leseanforderung auf P3 zu, sodass es zu Status 2 wird und P3 nach vorne verschoben wird
Status 3 bedeutet, dass die Datenseite, auf die dieses Mal zugegriffen wurde, nicht in der verknüpften Liste vorhanden ist, also eine neue Datenseite Px muss in BP beantragt und zur Kopfzeile der verknüpften Liste hinzugefügt werden. Da der Speicher jedoch voll ist, kann kein neuer Speicher angefordert werden. Daher wird der Speicher der Pm-Datenseite am Ende der verknüpften Liste gelöscht, der Inhalt von Px gespeichert und an der Spitze der verknüpften Liste platziert
Am Ende wird die Datenseite Pm gelöscht, die noch nicht vorhanden war Zugriffe, auf die am längsten zugegriffen wurde, werden eliminiert.
Was passiert, wenn wir zu diesem Zeitpunkt einen vollständigen Tabellenscan durchführen möchten? Sie möchten eine 200G-Tabelle scannen, bei der es sich um eine historische Datentabelle handelt, auf die normalerweise kein Unternehmen zugreift.
Dann werden beim Scannen nach diesem Algorithmus alle Daten im aktuellen BP gelöscht und der Inhalt der Datenseite gespeichert, auf die während des Scanvorgangs zugegriffen wurde. Mit anderen Worten: BP speichert hauptsächlich Daten aus dieser historischen Datentabelle.
Für eine Bibliothek, die Unternehmensdienstleistungen anbietet, ist dies nicht möglich. Sie werden feststellen, dass die Trefferquote des BP-Speichers stark abnimmt, der Festplattendruck zunimmt und die Antwort auf die SQL-Anweisung langsamer wird.
Daher kann InnoDB rohes LRU nicht direkt verwenden. InnoDB optimiert es.
Verbesserter LRU-Algorithmus
InnoDB unterteilt die verknüpfte Liste in einen neuen Bereich und einen alten Bereich im Verhältnis 5:3. In der Abbildung zeigt LRU_old auf die erste Position des alten Bereichs, der 5/8 der gesamten verknüpften Liste ausmacht. Das heißt, 5/8 in der Nähe des Kopfes der verknüpften Liste ist der neue Bereich und 3/8 in der Nähe des Endes der verknüpften Liste ist der alte Bereich.
Verbesserter Ausführungsprozess des LRU-Algorithmus:
Zustand 1, Sie müssen auf P3 zugreifen. Da sich P3 im Bereich „Neu“ befindet, ist es mit LRU vor der Optimierung identisch. Verschieben Sie es an den Anfang der verknüpften Liste => Nach Zustand 2
Sie müssen auf eine neue Seite zugreifen. Für Datenseiten, die in der aktuellen verknüpften Liste nicht vorhanden sind, wird die Datenseite Pm zu diesem Zeitpunkt noch entfernt, aber die neu eingefügte Datenseite Px wird unter **"LRU_old"* platziert. *
Die Datenseite im alten Bereich wird jedes Mal gelöscht. Beim Zugriff müssen Sie Folgendes beurteilen:
Wenn die Datenseite länger als 1 Sekunde in der verknüpften LRU-Liste vorhanden ist, verschieben Sie sie an den Kopf der verknüpften Liste
Wenn die Datenseite weniger als 1 Sekunde in der verknüpften LRU-Liste vorhanden ist, bleibt der Speicherort unverändert. 1s wird durch den Parameter „innodb_old_blocks_time“ gesteuert, der Standardwert ist 1000, Einheit ms.
Diese Strategie ist auf Vorgänge wie vollständige Tabellenscans zugeschnitten. Oder scannen Sie die 200G-Verlaufsdatentabelle:
4. Während des Scanvorgangs werden die Datenseiten, die neu eingefügt werden müssen, im alten Bereich platziert
5. Auf einer Datenseite befinden sich mehrere Datensätze Es kann jedoch mehrmals darauf zugegriffen werden, aber aufgrund des sequentiellen Scannens wird das Zeitintervall zwischen dem ersten Zugriff und dem letzten Zugriff auf diese Datenseite nicht länger als 1 Sekunde sein, sodass sie weiterhin im alten Bereich erhalten bleibt
6 Nachfolgende Daten, die vorherige Auf diese Datenseite wird in Zukunft nicht mehr zugegriffen, daher besteht nie die Möglichkeit, an den Kopf der verknüpften Liste (Neuer Bereich) zu gelangen, und sie wird bald entfernt.
Es ist ersichtlich, dass der größte Vorteil dieser Strategie darin besteht, dass beim Scannen dieser großen Tabelle, obwohl auch BP verwendet wird, dies überhaupt keine Auswirkungen auf den jungen Bereich hat, wodurch die Abfragetrefferrate des Puffers sichergestellt wird Pool als Reaktion auf das normale Geschäft.
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, platzt jedoch nicht der Speicher.
Innerhalb der InnoDB-Engine führen große Abfragen aufgrund der Eliminierungsstrategie nicht zu einer Speicherexplosion. Da InnoDB außerdem den LRU-Algorithmus verbessert hat, können auch die Auswirkungen des vollständigen Tabellenscans kalter Daten auf den Pufferpool gesteuert werden.
Der vollständige Tabellenscan verbraucht immer noch E/A-Ressourcen, sodass es in Spitzengeschäftszeiten immer noch nicht möglich ist, einen vollständigen Tabellenscan direkt online in der Hauptdatenbank durchzuführen.
Empfohlenes Lernen: MySQL-Video-Tutorial
Das obige ist der detaillierte Inhalt vonLassen Sie uns darüber sprechen, ob MySQL OOM verursacht, wenn zu viele Datenabfragen vorhanden sind. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!