Analyse langsamer Einfügefälle aufgrund großer Transaktionen in MySQL

【Frage】

Die INSERT-Anweisung ist eine der am häufigsten verwendeten SQL-Anweisungen. In letzter Zeit meldet ein MySQL-Server von Zeit zu Zeit Warnungen über gleichzeitig ausgeführte Threads. Aus den Protokollinformationen geht hervor, dass eine große Anzahl langsamer Insert-Abfragen Dutzende von Sekunden zur Ausführung benötigt, auf das Leeren der Protokolle wartet und sich im Abfrage-Endzustand befindet.

[Vorläufige Analyse]

Aus der Perspektive des Wartens auf Ressourcen wird die meiste Zeit in der Phase innodb_log_file verbracht. Es wird vermutet, dass dies durch Festplattenprobleme verursacht werden kann. Nach der Untersuchung wurden im Server selbst keine Hardwareprobleme gefunden.

Später, wenn der Thread zunimmt, wird die automatische Sammlung von pstack aktiviert, um die Position zu lokalisieren, an der der MySQL-Thread wartet.

【Analyseprozess】

Nach der Bereitstellung des automatischen Crawlings von pstack gab es 6 Thread-Parallelitäten >= 50 Alarme (bei jeder Ausgabe eines Alarms wurde eine große Anzahl langsamer Abfragen generiert) und 3 davon wurden sofort abgefangen.

Wenn die Anzahl gleichzeitiger Threads zunimmt, bleiben mehr als 50 Threads in Stage_manager::enroll_for in der Gruppen-Commit-Phase hängen

Die SQL-Anweisung zum Thread 0x519c5940 lautet wie folgt und wurde 18 Sekunden lang ausgeführt

Der Zweck Stage_manager::enroll_for besteht darin, die Warteschlangenbildung mehrerer Threads in der Phase flush_stage zu implementieren. Einfach ausgedrückt wird eine gruppierte Transaktion vom führenden Thread übermittelt, und die anderen Threads befinden sich in einem Warteschlangenwartezustand und warten darauf, dass der führende Thread die Transaktion des Threads übermittelt.

Wenn die Ausführung des ersten Threads langsam ist, befinden sich die nachfolgenden Threads in einem Wartezustand und die gesamte Gruppe von Transaktionen kann nicht festgeschrieben werden.

Der Vorgang kann auch wie folgt verstanden werden:

Sitzung A COMMIT-->Sperre abrufen-->Binlog schreiben-->Commit abgeschlossen

Sitzung B COMMIT-->Auf Sperre warten-------------------------->Sperre erhalten-->Binlog schreiben-->Commit abgeschlossen

Warum wird der erste Thread so langsam ausgeführt? Wir haben die Protokolldateien während des Alarmzeitraums analysiert und festgestellt, dass die Protokolldateien zwei große Transaktionen von 15 M und 20 M enthielten.

Überprüfen Sie die Protokolldetails. Es gibt eine große Transaktionslöschanweisung namens „delete from“, die etwa 230.000 Datensätze enthält. Das Löschen von 230.000 Datensätzen im ROW-Modus erzeugt eine Protokolldatei von etwa 20 MB. Die Datenträgerleerung dauert lange, wodurch die Übermittlung anderer Transaktionen in derselben Gruppe blockiert wird.

Die Startzeit der Transaktion entspricht der Alarmzeit

Die zurückgebliebenen gruppierten Transaktionen werden konzentriert auf die Festplatte geschrieben. Dies spiegelt sich im Festplattenindex wider. Der Index disk_write_kbytes zeigt während des Problemzeitraums einen deutlichen Anstieg.

【Optimierungsplan】

1. Entwicklern wird empfohlen, die Verwendung der Anweisung „delete from“ zum Löschen einer großen Transaktion der gesamten Tabelle zu vermeiden.

[Andere Problemumgehungen]

2. Binlog-Aufzeichnungen im ROW-Modus erzeugen eine große Anzahl von Protokollen. Der Wechsel in den MIXED-Modus kann das Problem theoretisch lösen.

3. Ersetzen Sie die Festplatte durch eine mit besserer Leistung

Zusammenfassen

Das Obige ist der vollständige Inhalt dieses Artikels. Ich hoffe, dass der Inhalt dieses Artikels einen gewissen Lernwert für Ihr Studium oder Ihre Arbeit hat. Wenn Sie Fragen haben, können Sie eine Nachricht hinterlassen. Vielen Dank für Ihre Unterstützung von 123WORDPRESS.COM.