MySQL-FAQ-Serie: So vermeiden Sie eine plötzliche Vergrößerung der ibdata1-Datei

0. Einleitung

Was ist die ibdata1-Datei?

ibdata1 ist eine Datei, die zum Erstellen des InnoDB-Systemtabellenbereichs verwendet wird. Diese Datei enthält die Metadaten, Rückgängig-Datensätze, den Änderungspuffer und den Doppelschreibpuffer der InnoDB-Tabelle. Wenn die Option „Datei pro Tabelle“ aktiviert ist, enthält die Datei nicht unbedingt Daten für alle Tabellen. Wenn die Option innodb_file_per_table aktiviert ist, werden die Daten und Indizes der neu erstellten Tabelle nicht im Systemtabellenbereich, sondern in der .ibd-Datei jeder Tabelle gespeichert.

Offensichtlich wird diese Datei immer größer. Die Option innodb_autoextend_increment gibt die Schrittweite der Datei an, die jedes Mal automatisch wächst. Der Standardwert ist 8M.

Was führt dazu, dass die ibdata1-Datei immer größer wird?

ibdata1 speichert Daten, Indizes, Caches usw. und sind die wichtigsten Daten von MySQL. Wenn die Datenbank größer wird, werden auch die Tabellen größer, was unvermeidlich ist. Mit der Zeit wird es für uns immer größer, und der Umgang mit Protokollen und Speicherplatz wird für uns nicht mehr so ​​einfach und wir wissen nicht, wo wir anfangen sollen. Als Nächstes werden wir uns mit einer solchen Situation befassen und Daten in separaten Datenbanken speichern.

Was soll ich tun, wenn die Größe der gemeinsam genutzten Tablespace-Datei ibdata1 von InnoDB dramatisch ansteigt?

1. Problemhintergrund

Freunde, die MySQL/InnoDB verwenden, haben möglicherweise auch Probleme. Aus irgendeinem Grund wird die ibdata1-Datei unerklärlicherweise größer und sie wissen nicht, wie sie sie verkleinern können, genau wie der Bauch eines Mannes nach dem 30. Lebensjahr. Zum Glück ist mein Bauch noch nicht gewachsen, hoho~

Bevor wir offiziell beginnen, müssen wir wissen, wofür die Datei ibdata1 verwendet wird.

Die ibdata1-Datei ist eine gemeinsam genutzte Tablespace-Datei der InnoDB-Speicher-Engine. Die Datei speichert hauptsächlich die folgenden Daten:

• Datenwörterbuch
• doppelter Schreibpuffer
• Puffer einfügen/Puffer ändern
• Rollback-Segmente
• Leerzeichen rückgängig machen
• Systemtabellen mit Fremdschlüsseleinschränkungen

Darüber hinaus müssen InnoDB-Tabellendaten und -Indizes in der Datei ibdata1 gespeichert werden, wenn die Option innodb_file_per_table = 0 . Die Standardgröße der ibdata1-Datei beträgt ab Version 5.6.7 12 MB, davor betrug die Standardgröße 10 MB. Die relevante Option ist innodb_data_file_path. Ich stelle sie beispielsweise normalerweise so ein:

innodb_data_file_path = ibdata1:1G:autoextend

Unabhängig davon, ob innodb_file_per_table = 1 aktiviert ist, muss die Datei ibdata1 natürlich vorhanden sein, da sie die Daten speichern muss, auf die die InnoDB-Engine angewiesen ist und die sie benötigt, insbesondere die oben fett markierten Rollback-Segmente und den Undo-Speicherplatz. Sie sind die Hauptgründe für die Vergrößerung der Datei ibdata1, die wir weiter unten ausführlich besprechen werden.

2. Ursachenanalyse

Wir wissen, dass InnoDB MVCC unterstützt. Ähnlich wie ORACLE verwendet es Undo-Log und Redo-Log, um MVCC-Funktionen zu implementieren. Wenn eine Datenzeile in einer Transaktion geändert wird, speichert InnoDB eine alte Version der Daten im Undo-Protokoll. Wenn eine andere Transaktion die Daten zu diesem Zeitpunkt ändern möchte, speichert sie die letzte sichtbare Version der Daten im Undo-Protokoll. Und so weiter. Wenn es N Transaktionen zum Ändern der Daten gibt, müssen N historische Versionen gespeichert werden (etwas anders als bei ORACLE ist das Undo-Protokoll von InnoDB kein vollständig physischer Block, sondern hauptsächlich ein logisches Protokoll. Sie können hierzu den InnoDB-Quellcode oder andere verwandte Materialien zu Rate ziehen). Diese Undo-Protokolle müssen warten, bis die Transaktion abgeschlossen ist, und dann erneut anhand der durch die Transaktionsisolationsstufe bestimmten Sichtbarkeit für andere Transaktionen beurteilt werden, um zu bestätigen, ob diese Undo-Protokolle gelöscht werden können. Diese Arbeit wird als Bereinigung bezeichnet (Bereinigungsarbeit besteht nicht nur darin, abgelaufene, nicht verwendete Undo-Protokolle zu löschen, es gibt auch andere Dinge, auf die ich später eingehen werde).

Dann stellt sich die Frage: Wenn es eine Transaktion gibt, die die historische Version einer großen Datenmenge lesen muss, und die Transaktion heute Morgen aus irgendeinem Grund nicht festgeschrieben oder zurückgesetzt werden kann, und nach dem Initiieren der Transaktion eine große Anzahl von Transaktionen die Daten ändern muss, können die von diesen neuen Transaktionen generierten Undo-Protokolle nicht gelöscht werden, was zu einer Anhäufung führt. Dies ist einer der Hauptgründe für die Vergrößerung der ibdata1-Datei. Das klassischste Szenario für diese Situation ist die Sicherung einer großen Menge an Daten. Daher empfehlen wir, die Sicherungsarbeit auf einem dedizierten Slave-Server statt auf dem Master-Server durchzuführen.

In einem anderen Fall kann die Bereinigungsarbeit von InnoDB die Undo-Protokolle, die aufgrund schlechter Datei-E/A-Leistung oder aus anderen Gründen rechtzeitig gelöscht werden können, nicht bereinigen, was zu einer Ansammlung führt. Dies ist ein weiterer wichtiger Grund für die Vergrößerung der ibdata1-Datei. Dieses Szenario tritt ein, wenn die Serverhardwarekonfiguration relativ schwach ist und nicht rechtzeitig aktualisiert wird, um mit der Geschäftsentwicklung Schritt zu halten.

Ein relativ seltenes Problem ist, dass es in der frühen MySQL-Version, die auf einem 32-Bit-System ausgeführt wird, einen Fehler gibt. Wenn die Gesamtmenge des zu löschenden Undo-Protokolls einen bestimmten Wert überschreitet, gibt der Löschthread einfach den Widerstand auf und löscht nicht mehr. Dieses Problem trat häufiger auf, als wir in den frühen Tagen die 32-Bit-Version von MySQL 5.0 verwendeten. Wir hatten einmal eine Situation, in der diese Datei auf über 100 GB anwuchs. Später haben wir viel Aufwand betrieben, um alle diese Instanzen auf 64-Bit-Systeme zu migrieren, und dieses Problem schließlich gelöst.

Der letzte Grund besteht darin, dass der Wert der Option innodb_data_file_path am Anfang nicht angepasst oder sehr klein eingestellt wurde, was zwangsläufig zur Vergrößerung der Datei ibdata1 führte. Die von Percona offiziell bereitgestellte my.cnf-Referenzdatei hat diesen Wert nie erhöht, was mich verwirrt. Soll damit absichtlich eine Geheimtür wie das xx gelassen werden, über das ich mich oft beschwere, um den Kunden eine spätere Optimierung zu ermöglichen? (Meine Gedanken sind zu düster, nicht gut~~)

Zusammenfassend sind die folgenden Gründe für die plötzliche Vergrößerung der Datei ibdata1 verantwortlich:

• Es gibt eine große Anzahl gleichzeitiger Transaktionen, die eine große Menge an Undo-Protokollen generieren.
• Es gibt alte Transaktionen, die lange Zeit nicht festgeschrieben wurden, was zu einer großen Anzahl alter Undo-Protokolle führt.
• Schlechte Datei-E/A-Leistung und langsamer Bereinigungsfortschritt;
• Die Anfangseinstellung ist zu klein und unzureichend;
• Auf 32-Bit-Systemen liegt ein Fehler vor.

Eine kleine Randbemerkung: Eine andere beliebte Datenbank, PostgreSQL, speichert die Daten jeder historischen Version zusammen mit dem ursprünglichen Datentabellenbereich, sodass das Problem in diesem Fall nicht besteht. Daher wird das Rollback von PostgreSQL-Transaktionen sehr schnell erfolgen und es müssen regelmäßig Bereinigungsarbeiten durchgeführt werden (Einzelheiten finden Sie im MVCC-Implementierungsmechanismus von PostgreSQL, ich bin möglicherweise nicht ganz korrekt).

3. Lösungsvorschläge

Nach der obigen Beschreibung der Ursachen dieser Probleme denken manche Studenten vielleicht, dass dies leicht zu lösen sei. Reduzieren Sie einfach die Größe der ibdata1-Datei und geben Sie den Tabellenspeicherplatz frei. Leider gibt es für InnoDB derzeit keine Möglichkeit, den ibdata1-Dateitabellenbereich wiederherzustellen/zu verkleinern. Sobald die ibdata1-Datei vergrößert ist, besteht die einzige Möglichkeit, die ursprüngliche Größe wiederherzustellen, darin, die Daten zu sichern, wiederherzustellen und die Instanz neu zu initialisieren oder jede unabhängige Tabellenbereichsdatei nacheinander in einer neuen Instanz zu sichern und wiederherzustellen. Es gibt keinen besseren Weg.

Natürlich lässt sich dieses Problem nicht vermeiden. Aus den oben genannten Gründen werden folgende Gegenmaßnahmen empfohlen:

• Aktualisieren Sie auf 5.6 und höher (64-Bit) und verwenden Sie einen unabhängigen Undo-Tablespace. Unabhängiger Undo-Tablespace wird seit Version 5.6 unterstützt. Sie müssen sich keine Gedanken mehr über die Vergrößerung der ibdata1-Datei machen.
• Stellen Sie während der Initialisierung die Datei ibdata1 auf mindestens 1 GB ein.
• Erhöhen Sie die Anzahl der Bereinigungsthreads, indem Sie beispielsweise innodb_purge_threads = 8 festlegen.
• Verbessern Sie die Datei-E/A-Funktionen und installieren Sie bei Bedarf SSDs.
• Reichen Sie Transaktionen rechtzeitig ein und vermeiden Sie Rückstände;
• Standardmäßig ist autocommit = 1 aktiviert, um zu vermeiden, dass das Commit einer Transaktion über einen längeren Zeitraum vergessen wird.
• Überprüfen Sie das Entwicklungsframework, um zu bestätigen, ob autocommit=0 eingestellt ist. Denken Sie daran, nach Abschluss der Transaktion explizit ein Commit oder Rollback durchzuführen.

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.