Überlegungen zum Importieren von Daten für MySQL

Der folgende Inhalt enthält technische Informationen zum Laden von Daten in MySQL. Dieser Inhalt richtet sich an Benutzer, die mit der MySQL-Serverarchitektur vertraut sind.

Binärprotokollierung

Durch die Aktivierung der Binärprotokollierung wird die Leistung beim Laden von Daten reduziert und bis zu viermal mehr zusätzlicher Speicherplatz als bei deaktivierter Protokollierung benötigt. Die zum Laden der Daten verwendete Transaktionsgröße wirkt sich direkt auf die Systemleistung und den Speicherplatzbedarf aus. Größere Transaktionen erfordern mehr Ressourcen.

Transaktionsgröße

Die Transaktionsgröße beeinflusst die folgenden Aspekte bei MySQL-Datenladevorgängen:

• Ressourcennutzung

• Auslastung des Festplattenspeichers

• Fortsetzen des Vorgangs

• Zeit für die Wiederherstellung

• Eingabeformat (Flatfiles oder SQL)

In diesem Abschnitt wird beschrieben, wie sich die Transaktionsgröße auf die binäre Protokollierung auswirkt, und verdeutlicht, welche Vorteile das Deaktivieren der binären Protokollierung bei umfangreichen Datenladevorgängen mit sich bringen kann. Die binäre Protokollierung kann durch die Festlegung des automatischen Aufbewahrungszeitraums für Backups in Amazon RDS aktiviert und deaktiviert werden. Nicht-Null-Werte aktivieren die binäre Protokollierung und Null deaktiviert diese. Weitere Informationen finden Sie unter Aufbewahrungszeitraum für Backups.

In diesem Abschnitt wird auch die Auswirkung von großen Transaktionen auf InnoDB beschrieben und warum es so wichtig ist, die Transaktionsgröße klein zu halten.

Kleine Transaktionen

Bei kleinen Transaktionen sorgt die binäre Protokollierung für eine Verdopplung der Schreibvorgänge auf der Festplatte, die für das Laden der Daten erforderlich sind. Dieser Effekt kann die Leistung für andere Datenbanksitzungen signifikant herabsetzen und die zum Laden der Daten erforderliche Zeit erhöhen. Wie stark die Leistungseinbuße ist, hängt teilweise von folgenden Faktoren ab:

• Upload-Rate

• Andere Datenbankaktivitäten während des Ladevorgangs

• Kapazität der Amazon-RDS-DB-Instance

Die binären Protokolle benötigen zudem fast genau soviel Speicherplatz wie die Datenladevorgänge, bis sie gesichert und entfernt wurden. Amazon RDS minimiert dies durch häufige Backup-Vorgänge und Löschen von Binärprotokollen.

Große Transaktionen

Bei großen Transaktionen verdreifacht die binäre Protokollierung die IOPS (Ein-/Ausgabe pro Sekunde) und die Festplattennutzung aus den folgenden Gründen:

• Der Binärprotokoll-Cache speichert Transaktionsdaten vorübergehend auf der Festplatte.

• Dieser Cache wächst mit der Transaktionsgröße an, wodurch Speicherplatz belegt wird.

• Wenn die Transaktion (Commit oder Rollback) abgeschlossen ist, wird der Cache vom System in das Binärprotokoll kopiert.

Bei diesem Vorgang werden drei Kopien von Daten erstellt:

• Originaldaten

• Cache auf der Festplatte

• Letzter binärer Protokolleintrag

Jeder Schreibvorgang verursacht zusätzliche E/A-Vorgänge, was sich weiter auf die Leistung auswirkt.

Aus diesem Grund benötigt die binäre Protokollierung dreimal so viel Speicherplatz wie die deaktivierte Protokollierung. Wenn Sie beispielsweise 10 GiB Daten als eine einzelne Transaktion laden, werden drei Kopien erstellt:

• 10 GiB für die Tabellendaten

• 10 GiB für den Binärprotokoll-Cache

• 10 GiB für die Binärprotokolldatei

Der gesamte benötigte temporäre Speicherplatz beträgt 30 GiB.

Wichtige Überlegungen zum Speicherplatz:

• Die Cache-Datei wird so lange beibehalten, bis die Sitzung endet oder durch eine neue Transaktion ein weiterer Cache erstellt wird.

• Das Binärprotokoll wird so lange beibehalten, bis es gesichert wurde, und kann über einen längeren Zeitraum möglicherweise 20 GiB (Cache und Protokoll) enthalten.

Wenn Sie LOAD DATA LOCAL INFILE zum Laden der Daten verwenden, wird bei der Datenwiederherstellung eine vierte Kopie für den Fall erstellt, dass die Datenbank aus einem vor dem Ladevorgang durchgeführten Backup wiederhergestellt werden muss. Während der Wiederherstellung extrahiert MySQL die Daten aus dem Binärprotokoll in eine Flat-File. MySQL führt dann LOAD DATA LOCAL INFILE aus. Basierend auf dem vorherigen Beispiel benötigt diese Wiederherstellung insgesamt einen temporären Speicherplatz von 40 GiB bzw. jeweils 10 GiB für Tabellen, Caches, Protokolle und lokale Dateien. Wenn nicht mindestens 40 GiB freier Speicherplatz zur Verfügung steht, schlägt die Wiederherstellung fehl.

Optimieren von großen Datenladevorgängen

Deaktivieren Sie die Binärprotokollierung bei großen Datenladevorgängen, um den Aufwand und Speicherplatzbedarf zu reduzieren. Sie können die Binärprotokollierung deaktivieren, indem Sie den Aufbewahrungszeitraum für Backups auf Null festlegen. Setzen Sie den Wert für den Aufbewahrungszeitraum für Backups auf den entsprechenden Nicht-Null-Wert zurück, nachdem der Ladevorgang abgeschlossen wurde. Weitere Informationen finden Sie unter Ändern einer Amazon-RDS-DB-Instance und Aufbewahrungszeitraum für Backups in der Einstellungstabelle.

Anmerkung

Sie können den Aufbewahrungszeitraum für Backups nicht auf Null festlegen, wenn es sich bei der DB-Instance um eine Quell-DB-Instance für Lesereplikate handelt.

Vor dem Laden von Daten empfehlen wir Ihnen, einen DB-Snapshot zu erstellen. Weitere Informationen finden Sie unter Verwalten manueller Backups.

InnoDB

Die folgenden Informationen zu der Undo-Protokollierung und den Wiederherstellungsoptionen helfen Ihnen dabei, InnoDB-Transaktionen klein zu halten, um die Datenbankleistung zu optimieren.

Grundlagen zur Undo-Protokollierung in InnoDB

Undo ist ein Protokollierungsmechanismus, der die Rücksetzung von Transaktionen ermöglicht und MVCC (Multi-Version Concurrency Control, Parallelitätskontrolle für mehrere Versionen) unterstützt.

Bei MySQL 5.7 und älteren Versionen werden Undo-Protokolle im InnoDB-System-Tablespace (normalerweise ibdata1) gespeichert und so lange beibehalten, bis sie vom Bereinigungs-Thread entfernt werden. Daher können große Datenladetransaktionen dazu führen, dass der System-Tablespace sehr groß wird und Speicherplatz belegt, der erst zurückgewonnen werden kann, wenn die Datenbank neu erstellt wird.

Bei allen MySQL-Versionen muss der Bereinigungs-Thread auf das Löschen der Undo-Protokolle warten, bis die älteste aktive Transaktion entweder festgeschrieben oder zurückgesetzt wurde. Wenn die Datenbank während des Ladevorgangs andere Transaktionen verarbeitet, sammeln sich auch ihre Undo-Protokolle an, die nicht entfernt werden können, selbst wenn die Transaktionen festgeschrieben wurden und keine andere Transaktion die Undo-Protokolle für MVCC benötigt. In dieser Situation werden alle Transaktionen langsamer, auch schreibgeschützte Transaktionen. Diese Verlangsamung ist darauf zurückzuführen, dass alle Transaktionen auf alle Zeilen zugreifen, die von jeder Transaktion und nicht nur von der Ladetransaktion geändert werden. Tatsächlich müssen Transaktionen Undo-Protokolle durchsuchen, deren Löschung durch lang andauernde Ladetransaktionen während einer Bereinigung von Undo-Protokollen verhindert wurde. Dies beeinträchtigt die Leistung aller Vorgänge, die auf geänderte Zeilen zugreifen.

Optionen zur Wiederherstellung von Transaktionen in InnoDB

Obwohl InnoDB Commit-Vorgänge optimiert, sind große Transaktions-Rollbacks langsam. Führen Sie für eine schnellere Wiederherstellung eine zeitpunktbezogene Wiederherstellung durch oder stellen Sie einen DB-Snapshot wieder her. Weitere Informationen erhalten Sie unter Point-in-time Erholung und Wiederherstellen auf eine DB-Instance.

Datenimportformate

MySQL unterstützt zwei Datenimportformate: Flatfiles und SQL. Lesen Sie die Informationen zum jeweiligen Format, um die beste Option für Ihre Anforderungen zu ermitteln.

Flache Dateien

Laden Sie Flatfiles für kleine Transaktionen mithilfe von LOAD DATA LOCAL INFILE. Dieses Datenimportformat bietet die folgenden Vorteile gegenüber der Verwendung von SQL:

• Weniger Netzwerkverkehr

• Niedrigere Datenübertragungskosten

• Geringerer Aufwand für die Verarbeitung von Datenbanken

• Schnellere Verarbeitung

LOAD DATA LOCAL INFILE lädt die gesamte Flatfile als eine Transaktion. Halten Sie die Größe der einzelnen Dateien klein, um die folgenden Vorteile zu erzielen:

• Fortgesetzte Leistungsfähigkeit: Sie können nachverfolgen, welche Dateien geladen wurden. Wenn während des Ladevorgangs ein Problem auftritt, können Sie dort fortfahren, wo Sie aufgehört haben. Einige Daten müssen eventuell erneut an Amazon RDS übertragen werden. Bei kleinen Dateien ist die Menge der erneut zu übertragenden Daten allerdings minimal.

• Paralleles Laden der Daten: Wenn Sie genügend IOPS und die erforderliche Netzwerkbandbreite für das Laden einer einzelnen Datei haben, können parallele Ladevorgänge Zeit sparen.

• Steuerung der Laderate: Wenn sich der Datenladevorgang negativ auf andere Prozesse auswirkt, können Sie die Laderate steuern, indem Sie das Intervall zwischen den Dateien verlängern.

Große Transaktionen verringern die Vorteile, die sich aus der Verwendung von LOAD DATA LOCAL INFILE zum Importieren der Daten ergeben. Wenn Sie eine große Datenmenge nicht in kleinere Dateien aufteilen können, sollten Sie SQL verwenden.

SQL

SQL hat einen Hauptvorteil gegenüber Flatfiles: Damit ist es einfach, Transaktionen klein zu halten. Jedoch kann ein Ladevorgang mit SQL deutlich länger dauern als mit Flatfiles. Außerdem kann es nach einem Fehler schwierig sein, zu ermitteln, wo der Ladevorgang fortgesetzt werden soll, da mysqldump-Dateien nicht neu gestartet werden können. Wenn während des Ladens einer mysqldump-Datei ein Fehler auftritt, müssen Sie die Datei ändern oder ersetzen, damit der Ladevorgang fortgesetzt werden kann. Alternativ können Sie auch nach dem Beheben der Fehlerursache eine Wiederherstellung auf einen Zeitpunkt vor dem Ladevorgang durchführen und die Datei erneut senden. Weitere Informationen finden Sie unter Point-in-time Erholung.

Verwenden von Amazon-RDS-DB-Snapshots für Datenbankprüfpunkte

Wenn Sie Daten über lange Zeiträume, z. B Stunden oder Tage, ohne die Binärprotokollierung laden, verwenden Sie DB-Snapshots, um regelmäßige Prüfpunkte für die Datensicherheit bereitzustellen. Jeder DB-Snapshot erstellt eine konsistente Kopie Ihrer Datenbank-Instance, die bei Systemausfällen oder Datenbeschädigungen als Wiederherstellungspunkt dient. Da DB-Snapshots schnell erstellt werden, hat das Festlegen häufiger Prüfpunkte nur minimale Auswirkungen auf die Ladeleistung. Sie können vorherige DB-Snapshots löschen, ohne die Dauerhaftigkeit der Datenbank oder die Wiederherstellungsfunktionen zu beeinträchtigen. Weitere Informationen zu DB-Snapshots finden Sie unter Verwalten manueller Backups.

Verkürzen der Ladezeit von Datenbanken

Im Folgenden sind zusätzliche Tipps zum Verringern der Ladezeit aufgeführt:

• Erstellen Sie alle sekundären Indizes, bevor Sie Daten in MySQL-Datenbanken laden. Im Gegensatz zu anderen Datenbanksystemen erstellt MySQL die gesamte Tabelle neu, wenn sekundäre Indizes hinzugefügt oder geändert werden. Dieser Prozess erstellt eine neue Tabelle mit Indexänderungen, kopiert alle Daten und löscht die Originaltabelle.

• Laden Sie Daten in der Reihenfolge der Primärschlüssel. Für InnoDB-Tabellen kann hierdurch die Ladezeit um 75 bis 80 % und die Datendateigröße um 50 % reduziert werden.

• Deaktivieren Sie Fremdschlüsselbeschränkungen, indem Sie foreign_key_checks auf 0 festlegen. Dies ist häufig für Flatfiles erforderlich, die mit LOAD DATA LOCAL INFILE geladen werden. Durch Deaktivieren von Fremdschlüsselprüfungen werden jegliche Datenladevorgänge beschleunigt. Aktivieren Sie nach Abschluss des Ladevorgangs die Einschränkungen erneut, indem Sie foreign_key_checks auf 1 festlegen, und überprüfen Sie die Daten.

• Laden Sie Daten parallel, es sei denn, Sie nähern sich einer Ressourcenbegrenzung. Verwenden Sie gegebenenfalls partitionierte Tabellen, um gleichzeitige Ladevorgänge über mehrere Tabellensegmente hinweg zu ermöglichen.

• Um den Aufwand bei der SQL-Ausführung zu reduzieren, kombinieren Sie mehrere INSERT-Anweisungen in einzelne INSERT-Vorgänge mit mehreren Werten. mysqldumpimplementiert diese Optimierung automatisch.

• Verringern Sie InnoDB-Protokoll-E/A-Vorgänge, indem Sie innodb_flush_log_at_trx_commit auf 0 festlegen. Setzen Sie innodb_flush_log_at_trx_commit nach Abschluss des Ladevorgangs wieder auf 1 zurück.

  Warnung

  Wenn innodb_flush_log_at_trx_commit auf 0 festgelegt wird, führt dies dazu, dass die Protokolle in InnoDB jede Sekunde anstatt bei jedem Commit bereinigt werden. Diese Einstellung erhöht zwar die Leistung, kann jedoch bei Systemausfällen zu Transaktionsverlusten führen.

• Wenn Sie Daten in eine DB-Instance laden, in der keine Lesereplikate vorhanden sind, legen Sie sync_binlog auf 0 fest. Setzen Sie sync_binlog parameter nach Abschluss des Ladevorgangs wieder auf 1 zurück.

• Laden Sie die Daten in eine Single-AZ-DB-Instance, bevor Sie die DB-Instance in eine Multi-AZ-Bereitstellung konvertieren. Wenn die DB-Instance bereits eine Multi-AZ-Bereitstellung verwendet, empfehlen wir Ihnen nicht, zwecks Laden von Daten zu einer Single-AZ-Bereitstellung zu wechseln. Dies führt nur zu geringfügigen Verbesserungen.