Detaillierte Erläuterung der MySQL-Optimierung für Schreibprobleme im großen Maßstab

Zusammenfassung: Wenn über die Leistungsoptimierung von MySQL gesprochen wird, geht es immer um die Optimierung von SQL und Indizes zur Verbesserung der Abfrageleistung. Die meisten Produkte oder Websites haben mit mehr Problemen beim gleichzeitigen Lesen von Daten zu kämpfen. Doch wie lässt sich das Schreiben großer Datenmengen optimieren?

Heute werde ich Ihnen hauptsächlich die Optimierungslösung für Szenarien mit einer großen Anzahl von Schreibvorgängen vorstellen.

Im Allgemeinen wird die Schreibleistung einer MySQL-Datenbank hauptsächlich durch die Konfiguration der Datenbank selbst, die Leistung des Betriebssystems und die Leistung der Festplatten-E/A begrenzt. Zu den wichtigsten Optimierungsmethoden gehören die folgenden:

1. Datenbankparameter anpassen

(1) innodb_flush_log_at_trx_commit

Der Standardwert ist 1. Dies ist der Transaktions-Commit-Einstellungsparameter der Datenbank. Die optionalen Werte sind wie folgt:

0: Der Protokollpuffer wird einmal pro Sekunde in die Protokolldatei geschrieben und die Protokolldatei wird auf die Festplatte geleert, es wird jedoch kein Vorgang für ein Transaktions-Commit ausgeführt.

1: Wenn jede Transaktion festgeschrieben wird, wird der Protokollpuffer in die Protokolldatei geschrieben und die Protokolldatei durch Festplattenvorgänge aktualisiert.

2: Bei jedem Commit wird der Protokollpuffer in die Datei geschrieben, es werden jedoch keine Festplattenoperationen an der Protokolldatei ausgeführt. Die Protokolldatei wird jede Sekunde geleert.

Manche Leute sagen vielleicht, dass es unsicher wäre, wenn der Wert auf einen anderen Wert als 1 geändert würde? Der Sicherheitsvergleich sieht wie folgt aus:

Um die Persistenz und Konsistenz von Transaktionen sicherzustellen, wird im MySQL-Handbuch empfohlen, diesen Parameter auf 1 zu setzen. Die Werkseinstellung ist 1 und damit auch die sicherste Einstellung.

Wenn innodb_flush_log_at_trx_commit und sync_binlog beide 1 sind, ist es am sichersten. Im Falle eines Absturzes des mysqld-Dienstes oder eines Serverhosts kann das Binärprotokoll maximal eine Anweisung oder eine Transaktion verlieren.

In diesem Fall finden jedoch häufige E/A-Vorgänge statt, sodass dieser Modus auch der langsamste ist.

• Wenn innodb_flush_log_at_trx_commit auf 0 gesetzt ist, führt ein Absturz des mysqld-Prozesses in der letzten Sekunde zum Verlust aller Transaktionsdaten.
• Wenn innodb_flush_log_at_trx_commit auf 2 gesetzt ist, können alle Transaktionsdaten in der letzten Sekunde nur verloren gehen, wenn das Betriebssystem abstürzt oder das System keinen Strom mehr hat.

Für dieselbe Tabelle wird die Batcheinfügung über C#-Code gemäß dem Systemgeschäftsprozess durchgeführt. Der Leistungsvergleich sieht wie folgt aus:

• (a. Unter den gleichen Bedingungen: innodb_flush_log_at_trx_commit=0, dauert es 25,08 Sekunden, um 500.000 Datenzeilen einzufügen;
• (b. Unter den gleichen Bedingungen: innodb_flush_log_at_trx_commit=1, dauert es 17 Minuten und 21,91 Sekunden, um 500.000 Datenzeilen einzufügen;
• (c. Unter denselben Bedingungen: innodb_flush_log_at_trx_commit=2, dauert es 1 Minute und 0,35 Sekunden, um 500.000 Datenzeilen einzufügen.

Fazit: Bei der Einstellung 0 erfolgt das Schreiben von Daten am schnellsten, wodurch die Schreibleistung der Datenbank schnell verbessert werden kann. Allerdings besteht die Möglichkeit, dass Daten aus der letzten Sekunde verloren gehen.

(2) Größe der temporären Tabelle, Größe der Heap-Tabelle

Diese beiden Parameter wirken sich vor allem auf das Schreiben von Temporärtabellen und Memory Engine Tabellen aus. Werden sie zu klein eingestellt, kann es sogar zu einer Fehlermeldung „Tabelle ist voll“ kommen.

Der belegte Speicherplatz sollte entsprechend der tatsächlichen Geschäftssituation größer als die zu schreibende Datenmenge angesetzt werden.

(3) max_allowed_packet=256M, net_buffer_length=16M, setze autocommit=0

Wenn Sie diese drei Parameter beim Sichern und Wiederherstellen richtig einstellen, wird die Geschwindigkeit Ihrer Sicherung und Wiederherstellung enorm steigen!

(4) innodb_data_file_path=ibdata1:1G;ibdata2:64M:autoextend

Natürlich dient die automatische Erweiterung nach dem Tablespace dazu, den Tablespace automatisch zu erweitern, standardmäßig beträgt er jedoch nur 10 M. Beim Schreiben großer Datenmengen möchten Sie diesen Parameter möglicherweise erhöhen.

Weisen Sie so viel Tabellenspeicherplatz wie möglich zu, wenn der Tabellenspeicherplatz wächst, und vermeiden Sie häufige Dateierweiterungen beim Schreiben in großen Stapeln

(5) innodb_log_dateigröße, innodb_log_dateien_in_gruppe, innodb_log_puffergröße

Legen Sie die Größe des Transaktionsprotokolls, die Anzahl der Protokollgruppen und den Protokollpuffer fest. Der Standardwert ist sehr klein. Der Standardwert von innodb_log_file_size beträgt nur einige zehn MB und der Standardwert von innodb_log_files_in_group ist 2.

In InnoDB werden Daten jedoch normalerweise zuerst in den Cache, dann in das Transaktionsprotokoll und dann in die Datendatei geschrieben. Wenn der Wert zu klein eingestellt ist, führt dies in Szenarien, in denen große Datenmengen geschrieben werden, zwangsläufig dazu, dass häufig Datenbankprüfpunkte ausgelöst werden, um Daten im Protokoll in Datendateien auf der Festplatte zu schreiben. Häufige Pufferaktualisierungen und Protokollwechsel führen zu einer Leistungseinbuße beim Schreiben großer Datenmengen.

Natürlich sollte dieser nicht zu groß eingestellt werden. Wenn die Größe zu groß ist, kann die Datenbank abnormal abstürzen. Wenn die Datenbank neu gestartet wird, liest sie die fehlerhaften Daten im Protokoll, die nicht in die Datendatei geschrieben wurden, führt einen Wiederherstellungsvorgang aus und stellt die Datenbank wieder her. Wenn die Größe zu groß ist, wird die Wiederherstellungszeit länger. Wenn die Wiederherstellungszeit die erwartete Wiederherstellungszeit des Benutzers bei weitem überschreitet, führt dies zwangsläufig zu Benutzerbeschwerden.

Für diese Einstellung können Sie sich auf die Standardeinstellungen der Huawei Cloud-Datenbank beziehen. In der 2-Core-4G-Umgebung von Huawei Cloud scheint die Standardkonfiguration Puffer: 16 M, log_file_size: 1 G zu sein, was fast 25 % des von MySQL empfohlenen Gesamtspeichers entspricht; und die Protokollgruppe files_in_group ist auf 4 Gruppen eingestellt.

Mit einer so niedrigen Hardwarekonfiguration von 2 Kernen und 4G kann es aufgrund der angemessenen Parametereinstellungen Tausende von Lese- und Schreibanforderungen pro Sekunde und mehr als 80.000 Lese- und Schreibanforderungen pro Minute standhalten.

Wenn die Menge der geschriebenen Daten viel größer ist als die Menge der gelesenen Daten oder wenn Sie die Parameter nach Belieben ändern möchten, können Sie große Datenmengen importieren und dann die log_file_size auf einen größeren Wert anpassen, der 25 % bis 100 % der innodb_buffer_pool_size erreichen kann.

(6) innodb_buffer_pool_size legt die verfügbare Cachegröße von MySQL Innodb fest. Theoretisch liegt der maximal einstellbare Wert bei 80 % des gesamten Serverspeichers.

Natürlich führt das Festlegen eines größeren Werts zu einer besseren Schreibleistung als das Festlegen eines kleineren Werts. Beispielsweise wird der obige Parameter innodb_log_file_size in Bezug auf die Größe von innodb_buffer_pool_size festgelegt.

(7) innodb_thread_concurency=16

Wie der Name schon sagt, steuert es die Anzahl gleichzeitiger Threads. Theoretisch gilt: Je mehr Threads vorhanden sind, desto schneller ist das Schreiben. Natürlich darf er nicht zu groß eingestellt werden. Die offizielle Empfehlung liegt bei etwa der doppelten Anzahl der CPU-Kerne.

(8) Schreibpuffergröße

Steuert die Cachegröße für einen einzelnen Schreibvorgang in einer Sitzung. Der Standardwert liegt bei etwa 4 KB und muss im Allgemeinen nicht angepasst werden. In Szenarien, in denen häufig große Datenmengen geschrieben werden, können Sie jedoch versuchen, den Wert auf 2 M anzupassen. Sie werden feststellen, dass sich die Schreibgeschwindigkeit bis zu einem gewissen Grad verbessert.

(9) innodb_buffer_pool_instance

Der Standardwert ist 1, wodurch hauptsächlich die Anzahl der Speicherpufferpools festgelegt wird. Einfach ausgedrückt steuert er die Anzahl gleichzeitiger Lese- und Schreibvorgänge von innodb_buffer_pool.

In Szenarien mit umfangreichen Schreibvorgängen können Sie diesen Parameter auch erhöhen, was ebenfalls zu erheblichen Leistungsverbesserungen führt.

(10) bin_log

Binärprotokolle zeichnen normalerweise alle Hinzufügungs-, Lösch- und Änderungsvorgänge der Datenbank auf. Beim Importieren großer Datenmengen, etwa beim Wiederherstellen einer Datenbank, möchten Sie jedoch möglicherweise bin_log vorübergehend schließen, das Schreiben in das Binärprotokoll deaktivieren, Daten nur in die Datendatei schreiben, die Datenwiederherstellung schnell abschließen und sie anschließend erneut öffnen.

2. Reduzieren Sie die Festplatten-E/A und verbessern Sie die Lese- und Schreibeffizienz der Festplatte

Folgende Methoden sind enthalten:

(1): Optimierung der Datenbanksystemarchitektur

a: Führen Sie eine Master-Slave-Replikation durch;

Stellen Sie beispielsweise einen Dual-Master-Slave-Modus bereit. Der Dual-Master-Slave-Modus wird bereitgestellt, um sich gegenseitig zu sichern und die Datensicherheit zu gewährleisten. Verschiedene Geschäftssysteme sind mit verschiedenen Datenbankservern verbunden, und die automatische Umschaltfunktion von ngnix oder Keepalive wird kombiniert, um einen Lastausgleich und eine automatische Umschaltung im Fehlerfall zu erreichen.

Durch diese Architekturoptimierung werden die gleichzeitigen Lese- und Schreib-E/A des verteilten Geschäftssystems von einem Server auf mehrere Server verschoben, wodurch auch die Schreibgeschwindigkeit einer einzelnen Datenbank verbessert werden kann.

b: Trennung von Lesen und Schreiben durchführen

Ähnlich wie bei den in 1 zu berücksichtigenden Problemen kann die Festplatten-E/A eines einzelnen Servers reduziert und die Sicherungsvorgänge auf dem Server auf den Standby-Server verschoben werden, um den E/A-Druck auf dem primären Server zu reduzieren und so die Schreibleistung zu verbessern.

(2): Hardware-Optimierung

a: Wenn die Ressourcen begrenzt sind, sollte das Betriebssystem während der Installation und Bereitstellung mehrere Festplatten haben. Anwendungen, Datenbankdateien, Protokolldateien usw. sollten zur Speicherung auf verschiedene Festplatten verteilt werden, um die E/A jeder Festplatte zu reduzieren und so die Schreibleistung einer einzelnen Festplatte zu verbessern.

b: Verwenden Sie ein Solid-State-Laufwerk (SSD).

Wenn die Ressourcen ausreichend sind, kann SSD-Speicher verwendet werden. SSD verfügt über die Eigenschaften des Hochgeschwindigkeitsschreibens und kann auch alle Festplatten-E/A-Vorgänge erheblich verbessern.

Natürlich gibt es noch weitere Hard- oder Software-Optimierungsmethoden, die hier nicht einzeln aufgeführt sind.

Dies ist das Ende dieses Artikels über die detaillierte Optimierung von MySQL-Massenschreibproblemen. Weitere relevante MySQL-Massenschreibinhalte 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!