Gelegentlich kommt es vor, dass man große Dumps von Datenbanken importieren muss. Die Probleme sind teilweise erheblich, da dies einiges an Gehirnschmalz kostet, um die Produktionsumgebung nicht lahm zu legen. Die nachfolgenden Tipps beziehen sich auf einen Import eines 22 GB großen Dumps in eine Datenbank vom Typ InnoDB. Es werden keine anderen Typen wie MyISAM verwendet.
Für einen Import gibt es in der Regel drei Ansatzpunkte:
- die Tools
- der Server selbst (Konfiguration)
- den Skripten (Dumps)
Die Tools
Welche Tools zum Einsatz kommen, hängt stark vom Setup ab. Die meisten haben keinen direkten DB-Zugriff und verwenden daher notgedrungen Tools, wie BigDump.
BigDump Skript
Dieses Tools Teilen die Abfrage in kleine Happen und führt sie daher nacheinander aus. Das erzeugt einen ziemlichen Overhead, da verwendete Indezies nach jedem Update/Insert aktualisiert werden. Für die im meinen Setup verwendete Konfiguration war dies zumindestens teilweise erfolgreich. Teilweise daher, da ich ca. 30 % importieren konnte, dann aber mit Dublettenfehler „rausgeworfen“ wurde. Das liegt daran, das die Inserts länger laufen als die maximale Laufzeit von PHP und daher nicht korrekt der aktuelle Stand vermerkt wird. Beim Aufruf des nächsten Happens verschluckt sich das Skript.
Kommandozeile
Kommandozeile steht natürlich nur zur Verfügung, wenn ein direkter Zugriff auf den Server vorhanden ist.
mysql.exe -u root –pPASSWORD DATENBANK --default_character_set=binary > DUMP_FILE
Leider ist für MySQL keine Vorschrittsanzeige des Imports vorhanden. Für Linux und Leute, die mit cygwin arbeiten aber schon.(siehe hier)
pv sqlfile.sql | mysql -uxxx -pxxxx dbname
Für die Leute ohne bleibt nur der Aufruf der Prozessliste, um den Fortschritt zu erfassen.
Der Server
Für die Performance ist es immer von Vorteil, wenn man bei Datenbanken viel Speicher hat. Daher allen anderen Kram beenden und die Konfiguration auf den maximalen Speicher anpassen (siehe Empfehlungen).
Was ein weiteres Problem ist, ist der massive IO. Die Platte lernt „fliegen“. Daher ist es Ziel, zu mindestens für die Zeit des Importes (sofern für die Produktion möglich) einige Parameter zu ändern, die diese Reduktion herbeiführen.
innodb_log_buffer_size = 64M
Die Skripte
Bei Import von großen Datenmengen kann viel Zeit gespart werden, wenn einige Prüfungen entweder deaktiviert bzw. verzögert werden. Dies gilt generell für die Indizes. Primärschlüsselfelder (PK bzw. UK) sollten jedoch nicht deaktiviert werden. Sehr wohl aber Foreign Keys (FK). Voraussetzung ist, dass die Dumps konsistent sind. Anderenfalls ist es Zeitverschwendung. Das gilt auch für die InnoDB, leider kann man die Indizes nicht deaktivieren, sondern nur löschen.
ALTER TABLE Tabelle DROP INDEX <code>Spalte</code>;ALTER TABLE Tabelle Add INDEX <code>Spalte</code>;
Das Deaktivieren/Löschen der FK, IX etc. während des Imports verhindert, dass diese nach jedem UpSert neu bearbeitet werden müssen. Das erfolgt daher erst am Ende auf einen Schlag und verhindert auch eine Fragmentierung des Indizes (siehe hier).