Fehlerbehebung bei Master-Slave-Verzögerungsproblemen beim Upgrade von MySQL 5.6 auf 5.7

Als ich kürzlich die Zabbix-Datenbank von MySQL 5.6 auf 5.7 aktualisierte, trat ein Master-Slave-Verzögerungsproblem auf. Dieses Problem bereitete mir lange Sorgen und konnte nicht gelöst werden. Gestern wurde es endlich gelöst. Ich habe den gesamten Fehlerbehebungsprozess geklärt und ihn mit allen geteilt.

Umweltbeschreibung:

Die MySQL-Masterdatenbank ist Version 5.6 und es gibt vier Slavedatenbanken, von denen drei Version 5.6 und eine Version 5.7 sind. Die Bibliotheks- und Tabellenstrukturen aller Master und Slaves sind konsistent. Die 5.7-Slavedatenbank weist viele Verzögerungen auf, während die 5.6-Slavedatenbank keine Probleme hat. Das Geschäft wird von Zabbix überwacht und im Grunde sind alle davon Einfüge-Batchoperationen. Jedes Einfüge-SQL fügt etwa 400-1000 Datenzeilen ein.

Frage:

Die Slave-Datenbank von MySQL5.7 weist eine große Anzahl von Verzögerungen auf, das Relaylog wird normal auf die Festplatte geschrieben und die Anwendung auf die Datenbank ist langsam. Es gibt keinen Druck auf Festplatten-E/A und CPU. Es gibt keinen Unterschied, ob sync_binlog 20000 oder 0 ist, max_allowed_packet=128M, innodb_flush_log_at_trx_commit=0, bulk_insert_buffer_size = 128M, binlog_format=row, sync_relay_log=10000, parallele Replikation wird nicht verwendet, SSL ist nicht aktiviert, GDID ist nicht aktiviert und Halbsynchronisierung ist nicht aktiviert.

Fehlerbehebungsprozess:

1: Überprüfen Sie alle leistungsrelevanten Parameter und finden Sie keine Anomalien.

2: Das Überprüfen der Netzwerkkarte, der Festplatte, das Ändern des Servers und der Neustart des Datenbankservers hatten keine Auswirkungen. Die Verzögerung von 5.7 bestand immer noch, sodass Hardwareprobleme ausgeschlossen wurden.

3: 5.7 synchronisiert das Binärprotokoll der Hauptdatenbank 5.6 schnell und normal mit dem Relaylog, aber die Effizienz der Wiedergabe des Relaylogs in der 5.7-Datenbank ist äußerst gering.

4: Vergleichen Sie die Ergebnisse der Show-Engine-InnoDB-Status von 5.6- und 5.7-Slaves:

=============5,6================================
---PUFFERPOOL 1
Pufferpoolgröße 655359
Pufferpoolgröße, Bytes 10737401856
Freie Puffer 1019
Datenbankseiten 649599
Alte Datenbankseiten 239773
Geänderte Datenbankseiten 119309
Ausstehende Lesevorgänge 0
Ausstehende Schreibvorgänge: LRU 0, Flush-Liste 0, einzelne Seite 0
Seiten gemacht jung 10777670, nicht jung 181119246
13,90 Junge/n, 157,51 Erwachsene/n
Seiten gelesen 8853516, erstellt 135760152, geschrieben 784514803
20,96 Lesevorgänge/s, 58,17 Erstellungsvorgänge/s, 507,02 Schreibvorgänge/s
Pufferpool-Trefferquote 1000 / 1000, Nachwuchsquote 0 / 1000 nicht 2 / 1000
Seiten vorauslesen 0,00/s, Seiten ohne Zugriff verdrängt 0,00/s, zufälliges Vorauslesen 0,00/s
LRU-Länge: 649599, unzip_LRU-Länge: 0
I/O-Summe[209618]:aktuell[2], Entpack-Summe[0]:aktuell[0]

=============5,7===============================
---PUFFERPOOL 1
Pufferpoolgröße 819100
Pufferpoolgröße, Bytes 13420134400
Freie Puffer 1018
Datenbankseiten 722328
Alte Datenbankseiten 266620
Geänderte DB-Seiten 99073
Ausstehende Lesevorgänge 0
Ausstehende Schreibvorgänge: LRU 0, Flush-Liste 0, einzelne Seite 0
Seiten gemacht jung 37153, nicht jung 795
0,00 Junge/n, 0,00 Nicht-Junge/n
Seiten gelesen 149632, erstellt 572696, geschrieben 2706369
0,00 Lesevorgänge/s, 0,00 Erstellungsvorgänge/s, 0,00 Schreibvorgänge/s
Pufferpool-Trefferquote 1000 / 1000, Nachwuchsquote 0 / 1000 nicht 0 / 1000
Seiten vorauslesen 0,00/s, Seiten ohne Zugriff verdrängt 0,00/s, zufälliges Vorauslesen 0,00/s
LRU-Länge: 722328, unzip_LRU-Länge: 453903
I/O-Summe[98685]:aktuell[0], Entpack-Summe[882]:aktuell[6]
+++++++++++++++++++++++

Im Vergleich dazu haben wir festgestellt, dass unzip in 5.7 einen Wert hat, in 5.6 jedoch nicht. Wir vermuteten zunächst, dass die Ursache der Verzögerung mit der Komprimierung und Dekomprimierung zusammenhängt.

5: Verwenden Sie perf top -p pidof mysqld, um die 5.7-Slave-Bibliothek anzuzeigen

Es wurde festgestellt, dass libz.so.1.2.7[.]cc32 mit etwa 6 % einen höheren Anteil ausmacht als mysqld. Diese Bibliothek bezieht sich auf Komprimierung und Dekomprimierung.

6: Ändern Sie den innodb_compression_level auf 0 (d. h. aktivieren Sie die Komprimierung nicht, der Standardwert ist 6, der Bereich ist 0-9) und beachten Sie, dass keine Auswirkungen auftreten und die Verzögerung weiterhin besteht. nur

Der Anteil von libz ist gesunken, der Anteil von libc-2.17.so ist jedoch gestiegen und liegt mit etwa 9 % höher als der von mysqld. Verwenden Sie pstack, um die Warteprobleme der Dekomprimierung im Forschungsinstitut anzuzeigen.

7: Überprüfen Sie die Verlaufstabellen von Zabbix. Um Speicherplatz zu sparen, wurden diese Tabellen komprimiert:

CREATE TABLE-Trends (
itemid bigint(20) unsigned NOT NULL,
Uhr int(11) NICHT NULL STANDARD '0',
num int(11) NICHT NULL STANDARD '0',
value_min double(16,4) NICHT NULL STANDARD '0.0000',
value_avg double(16,4) NICHT NULL STANDARD '0.0000',
value_max double(16,4) NICHT NULL STANDARD '0.0000',
Primärschlüssel (Artikel-ID, Uhr),
KEY-Uhr (Uhr)
) ENGINE=InnoDB STANDARD CHARSET=utf8 ROW_FORMAT=KOMPRIMIERT KEY_BLOCK_SIZE=8

Ich vermute, dass es mit dem Komprimierungsparameter ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 zusammenhängt.

8: Erstellen Sie alle historischen Tabellen neu, entfernen Sie ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8, synchronisieren Sie erneut, reduzieren Sie die Verzögerung schrittweise und führen Sie eine Wiederherstellung durch.

Frage: Warum verursacht die gleiche Tabellenstruktur in 5.7 eine Master-Slave-Verzögerung, in 5.6 jedoch nicht? Dies kann durch die Abwärtskompatibilitätsprobleme bei der Komprimierung und Dekomprimierung in MySQL 5.7 verursacht werden. Ich habe es nicht weiter untersucht, aber ich habe den Fehler offiziell gemeldet und sie gebeten, den Quellcode zu überprüfen: http://bugs.mysql.com/100702.

Bitte verwenden Sie ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 in der Produktion mit Vorsicht. Nach Gesprächen mit mehreren Branchenexperten sagten diese, dass die Komprimierung von MySQL-Versionen vor 8.0 nicht sehr zuverlässig sei und die Verwendung von ZSTD für 8.0 besser sei.

Dies ist das Ende dieses Artikels über die Vorgehensweise zur Behebung des Master-Slave-Verzögerungsproblems beim Upgrade von MySQL 5.6 auf 5.7. Weitere Informationen zur Master-Slave-Verzögerung beim Upgrade von MySQL 5.6 auf 5.7 finden Sie in früheren Artikeln auf 123WORDPRESS.COM oder in den verwandten Artikeln weiter unten. Ich hoffe, dass jeder 123WORDPRESS.COM in Zukunft unterstützen wird!