Gründe für den plötzlichen Leistungsabfall bei MySQL

Manchmal kann es vorkommen, dass eine SQL-Anweisung normal und sehr schnell ausgeführt wird. Doch plötzlich wird die Ausführung irgendwann sehr langsam und dieses Szenario lässt sich nicht reproduzieren und kann nur zufällig gesendet werden.

Gründe, warum die SQL-Ausführung plötzlich langsamer wird

Bei der vorherigen Erläuterung des MySQL-Redo-Protokolls habe ich den WAL-Mechanismus erwähnt. Um die Geschwindigkeit von MySQL-Updates sicherzustellen, wird beim Ausführen eines Update-Vorgangs der Update-Inhalt zuerst in das Redo-Protokoll geschrieben und dann der Inhalt des Redo-Protokolls auf die Festplatte angewendet, wenn das System im Leerlauf ist.

Wenn der Inhalt der Speicherdatenseite (Redo-Log) und der Datenträgerdatenseite inkonsistent sind, wird der Speicher auch als „Dirty Page“ bezeichnet. Nachdem die Speicherdaten auf die Festplatte geschrieben wurden, sind die Daten konsistent und die Speicherseite wird als „saubere Seite“ bezeichnet.

Wenn Speicherdaten auf die Festplatte geschrieben werden, wird dieser Vorgang als Leervorgang bezeichnet. Die SQL-Ausführung wird plötzlich sehr langsam und die Leistung nimmt ab. Der Grund kann mit dem Spülvorgang zusammenhängen.

Denn während der Leerungsvorgang läuft, wartet der Aktualisierungsvorgang, bis das Redo-Protokoll geschrieben wurde.

Ursachen für den Spülvorgang

Szenario 1: Das Redo-Protokoll ist voll. Zu diesem Zeitpunkt stoppt das System den Aktualisierungsvorgang und verschiebt den Prüfpunkt nach vorne, um im Redo-Protokoll Platz zum weiteren Schreiben zu lassen.

Hier wird davon ausgegangen, dass die Lücke von CP zu CP' auf die Festplatte geschrieben wurde und dieser Teil zu einer leeren Seite wird. Zu diesem Zeitpunkt kann die Schreibposition in diesen Bereich geschrieben werden.

Szenario 2: Wenn das System nicht genügend Speicher hat und neue Speicherseiten benötigt, stellt sich heraus, dass der Speicher nicht ausreicht und einige Datenseiten gelöscht werden müssen. Wenn die Datenseite beim Löschen fehlerhaft ist, muss die fehlerhafte Seite auf die Festplatte geschrieben werden.

Zu diesem Zeitpunkt stellt sich eine Frage: Der Inhalt des benannten Redo-Protokolls wurde im Protokoll aufgezeichnet. Wenn der Speicher voll ist, können wir ihn dann nicht einfach löschen? Beim nächsten Einlesen wird der Inhalt des Redo-Logs auf die Platte übertragen.

Der Grund, warum wir uns nicht dafür entschieden haben, den Speicher direkt zu löschen, liegt in Leistungsgründen. Beim Abfragen von Daten gibt es zwei Situationen:

• Erstens befindet sich die Datenseite im Speicher, und der Speicher ist das richtige Ergebnis, das direkt zurückgegeben wird
• Es befinden sich keine Daten im Speicher, lesen Sie diese daher aus der Datendatei in den Speicher ein.

Das ist also effizienter.

Szenario 3: MySQL startet den Leerungsvorgang, wenn das System im Leerlauf ist.

Szenario 4: Wenn MySQL normal heruntergefahren wird, werden beschädigte Seiten im Speicher auf die Festplatte geschrieben.

Der Einfluss des Spülens auf die Leistung

Das dritte und vierte Szenario sind relativ normal und es besteht kein Grund zur Beachtung von Leistungsproblemen.

InnoDB versucht das erste Szenario zu vermeiden, da in diesem Fall das gesamte System keine Updates mehr akzeptiert.

Manchmal treten jedoch menschliche Konfigurationsfehler auf, wie z. B. bei einer Instanz mit 128 GB Speicher und einer innodb_io_capacity-Einstellung von 20000. Normalerweise wird empfohlen, das Redo-Protokoll auf vier 1-GB-Dateien zu beschränken. Aufgrund eines Konfigurationsfehlers wurde es jedoch auf eine 100 MB große Datei eingestellt.

Da das Redo-Log zu klein eingestellt ist, ist es schnell voll. Die Schreibposition holt den Prüfpunkt immer wieder ein. An diesem Punkt kann das System nur alle Aktualisierungen stoppen und den Prüfpunkt vorantreiben.

Die Leistung besteht darin, dass die Festplatten-E/A sehr gering ist, es jedoch zeitweise zu Leistungseinbußen kommt.

Im zweiten Szenario, wenn der Speicher nicht ausreicht, verwendet InnoDB den Pufferpool zur Speicherverwaltung.

Speicherseiten können im Pufferpool drei Zustände haben:

• Unbenutzte Datenseiten
• Ich habe es benutzt, aber es ist eine saubere Seite
• Gebrauchte, schmutzige Seite
  

Jeder Datenseitenkopf hat eine LSN, die 8 Byte groß ist und mit jeder Änderung zunimmt.

Vergleichen Sie diese LSN mit der Prüfpunkt-LSN. Die Seite, die kleiner als die Prüfpunkt-LSN ist, muss eine saubere Seite sein.

Da die Strategie von InnoDB darin besteht, so viel Speicher wie möglich zu verwenden, gibt es bei Datenbanken mit langer Laufzeit nur sehr wenige ungenutzte Seiten.

Wenn festgestellt wird, dass sich die zu lesende Datenseite nicht im Speicher befindet, müssen Sie die Datenseite aus dem Pufferpool anfordern. Und die Datenseiten, die am längsten nicht verwendet wurden, werden aus dem Speicher gelöscht

Wenn es sich um eine saubere Seite handelt, kann sie direkt freigegeben werden. Wenn es sich um eine schmutzige Seite handelt, muss sie zuerst auf die Festplatte geleert werden, um eine saubere Seite zu werden, bevor sie wiederverwendet werden kann. Wenn die schmutzige Seite in den folgenden Situationen geleert wird, wird die Leistung erheblich beeinträchtigt:

Zu viele fehlerhafte Seiten müssen gelöscht werden, was zu einer längeren Abfrageantwortzeit führt.
Das Protokoll ist voll und Updates sind blockiert.
Um dieses Problem zu lösen, verwendet InnoDB einen Mechanismus zur Kontrolle des Anteils schmutziger Seiten, um die oben genannte Situation zu vermeiden.

InooDB-Kontrollstrategie zum Leeren schmutziger Seiten

In InnoDB wird der Parameter innodb_io_capacity verwendet, um InnoDB die aktuelle Festplattenkapazität des Hosts mitzuteilen. Es wird empfohlen, diesen Wert auf die IOPS der Festplatte einzustellen.

Sie können es mit dem Fio-Tool testen:

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

Durch innodb_io_capacity verursachte Leistungsprobleme sind häufig. Beispielsweise ist manchmal der Systemdurchsatz (TPS) niedrig und Schreibvorgänge sind langsam, aber die Datenträger-E/A ist nicht hoch. Möglicherweise ist der Parameter falsch eingestellt. Beispielsweise ist der Wert von innodb_io_capacity sehr niedrig eingestellt, die verwendete Festplatte ist jedoch eine SSD. Dadurch geht InnoDB davon aus, dass die Systemkapazität sehr gering ist, sodass das Leeren der Dirty Pages sehr langsam ist. Dies führt dazu, dass sich schmutzige Seiten ansammeln und die Abfrage- und Aktualisierungsleistung beeinträchtigt wird.

InnoDB berücksichtigt beim Leeren von Datenträgern hauptsächlich zwei Faktoren:

1. Der Anteil schmutziger Seiten
   
2. Geschwindigkeit beim Redo-Log-Schreiben
   

Zunächst werden zwei Zahlen berechnet, wobei diese beiden Faktoren getrennt voneinander verwendet werden.

innodb_max_dirty_pages_pct Obergrenze für das Verhältnis schmutziger Seiten, Standard 75 %.

InnoDB berechnet basierend auf dem Anteil schmutziger Seiten (M) eine Zahl im Bereich von 0 bis 100. , der Prozess heißt F1 (M)

# M Schmutzige Seiten-Verhältnis F1(M)
{
 wenn M>=innodb_max_dirty_pages_pct dann
  gib 100 zurück;
 gibt 100*M/innodb_max_dirty_pages_pct zurück;
}

Darüber hinaus hat jedes Mal, wenn InnoDB ein Protokoll schreibt, dieses eine Sequenznummer N. Anschließend wird basierend auf N eine Zahl zwischen 0 und 100 berechnet. Dieser Berechnungsprozess wird F2(N) genannt.

N: Die Differenz zwischen der aktuell geschriebenen Sequenznummer und der dem Prüfpunkt entsprechenden Sequenznummer.

Schließlich wird basierend auf den beiden Werten von F1 (M) und F2 (N) der größere Wert als R genommen, und dann kann die Engine die Geschwindigkeit zum Leeren schmutziger Seiten gemäß innodb_io_capacity * R steuern.

Daher müssen Sie, unabhängig davon, ob Sie eine Abfrage durchführen, Daten in die Speicherdatenseite laden und die fehlerhafte Seite löschen. Unabhängig davon, ob es sich um eine Aktualisierung oder eine Datenträgerbereinigung handelt, kann es zu einer Beeinträchtigung der Leistung von MySQL kommen.

Um diese Situation zu vermeiden, sollten Sie den Wert von innodb_io_capacity sinnvoll festlegen und dem Anteil schmutziger Seiten mehr Aufmerksamkeit schenken, damit er sich nicht 75 % nähert.

Das Dirty Page-Verhältnis kann folgendermaßen ermittelt werden:

mysql> verwende performance_schema;
mysql> wähle VARIABLE_VALUE in @a aus global_status, wobei VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
Wählen Sie VARIABLE_VALUE in @b aus global_status, wobei VARIABLE_NAME = „Innodb_buffer_pool_pages_total“ ist.
wähle @a/@b;

Wenn beim ersten Mal eine Abfrage ausgeführt wird und eine schmutzige Seite gelöscht werden muss, wird der Nachbar der schmutzigen Seite zusammen gelöscht, wenn auch die Seite, die die schmutzige Seite umgibt, eine schmutzige Seite ist. Wenn sich daneben zufällig eine schmutzige Seite befindet, wird diese weiterhin gelöscht. Aus diesem Grund ist das Spülen zu langsam.

Dieses Verhalten kann durch innodb_flush_neighbors gesteuert werden. Ein Wert von 1 schaltet den obigen Mechanismus ein, ein Wert von 0 schaltet ihn aus.

Bei mechanischen Festplatten kann eine Menge zufälliger IO reduziert werden, da die IOPS mechanischer Festplatten im Allgemeinen nur einige Hundert betragen und die Reduzierung zufälliger IO eine Leistungsverbesserung bedeutet.

Wenn Sie jedoch ein Gerät mit höheren IOPS verwenden, z. B. eine SSD, stellen IOPS häufig keinen Engpass dar und Sie können es einfach ausschalten, um die Antwortzeit von SQL-Anweisungen zu verkürzen.

In 8.0 ist der Standardwert 0.

Zusammenfassen

Dieser Artikel stellt hauptsächlich vor, wie der Flush-Vorgang während WAL zu einem plötzlichen Leistungsabfall in MySQL führen kann.

Die Ursache liegt normalerweise an unzureichendem Speicher und der InnoDB-Flush-Prozess kann durch Festlegen des entsprechenden innodb_io_capacity Parameters gesteuert werden.

Oben sind die Einzelheiten zu den Gründen aufgeführt, warum die MySQL-Leistung plötzlich nachließ. Weitere Informationen zur Verschlechterung der MySQL-Leistung finden Sie in den anderen verwandten Artikeln auf 123WORDPRESS.COM!