Schlagwort-Archive: PreparedStatements

MySQL-Import und pdflush

Computer, , , , ,

Vermutlich kommt jeder in gewissen Abständen an einen Punkt, an dem man absolutes Neuland betritt, da man mit seinem bisherigen Wissen nicht weiter kommt.

Das Problem

war, dass viele Datensätze (6 Mio) per PreparedStatements in eine Datenbank importiert werden müssen. An sich kann es kein Problem sein, 6 Mio Datensätze zu importieren, aber unser Server machte da nicht mit

  1. war das Wiki kurz nach Beginn des Datenimports nicht mehr erreichbar
  2. einige Zeit später machte der komplette Server die Grätsche

Wir mutmaßten, dass die Wiki-Datenbank auf irgendeine Weise mit dem Importvorgang in einen Konflikt gerät, da diese ja als erstes ausfiel. Dieser Verdacht war falsch, da lediglich die InnoDB der Wiki-Datenbank zum Ausfall führte. Nachdem das Wiki auf MyISAM umgestellt war, funktionierte das Wiki auch während des Imports problemlos.

Das zweite Problem blieb aber bestehen. Nach einigen Versuchen und der Installation von Diagnosetools stand fest, dass die Festplatte durch den pdflush Daemon so stark beansprucht wurde, dass es für andere Prozesse nicht mehr möglich war auf die Festplatte zuzugreifen.

Wir überlegten, die Einstellungen für den pdflush Daemon zu ändern, mir fiel aber vorher auf, dass mir bei der Programmierung des Imports ein Fehler unterlaufen war: Anstatt vor dem Import die Schlüssel zu deaktivieren

[code lang=’sql‘]ALTER TABLE … DISABLE KEYS[/code]

und nach dem Import die Schlüssel wieder zu aktivieren

[code lang=’sql‘]ALTER TABLE … ENABLE KEYS[/code]

waren durch einen Zeilenverrutscher die Schlüssel für eine Tabelle schon vor dem Import wieder aktiviert worden und genau das verursachte das Zusammenbrechen des Servers, weil die Indizes während des Imports neu berechnet werden mussten.

Performancevergleich: Enzelne SQL-Statements vs. MySQL LOAD DATA INFILE vs. Prepared Statements

Java, , , , ,

Performance: Einzelne SQL-Statements

Was macht das Programm?

  1. Aus einer großen XML-Datei werden per SAXParser Datensätze eingelesen. Insgesamt gibt es 1.109.270 „Datensätze“ in der XML-Datei.
  2. Aus jedem Datensatz werden 2 SQL-Queries erzeugt: Ein REPLACE-Statement zum Einfügen/Aktualsieren von Stammdaten und ein INSERT-Statement zum Einfügen von Bewegungsdaten

Es werden also kontinuierlich SQL-Statements abgesetzt.

Laufzeit des Programms

41 Minuten und 36 Sekunden

Performance: Generierung der CSV-Datei & LOAD DATA INFILE

Was macht das Programm?

  1. (identisch mit vorherigem Programm:) Aus einer großen XML-Datei werden per SAXParser Datensätze eingelesen. Insgesamt gibt es 1.109.270 „Datensätze“ in der XML-Datei.
  2. Aus jedem Datensatz werden 2 CSV-Dateien um einen Datensatz erweitert. Die erste Datei enthält die Daten für die Stammdaten-Tabelle, die zweite Datei enthält die Bewegungsdaten.
  3. Zwei LOAD DATA INFILE Befehle zum Einlesen der CSV-Dateien.

Das Programm erzeugt also zuerst 2 Dateien und greift erst zum Schluss auf die Datenbank zu.

Laufzeit des Programms

30 Minuten und 9 Sekunden

Performance: Nur LOAD DATA INFILE

Wenn wir annehmen, dass die 2 CSV-Dateien schon vorhanden sind und nur noch die LOAD DATA INFILE-Befehle ausgeführt werden müssen, so sind die immerhin 2.218.540 Datensätze innerhalb von

2 Minuten und 23 Sekunden

importiert.

Performance: Prepared Statements

Prepared Statements sollten nicht nur aus sicherheitsrelevanten Überlegungen eingesetzt werden, sondern bringen – richtig implementiert – auch einen Performance-Vorteil. Dieser Vorteil lässt sich so erklären, dass 1x das SQL-Statement mit „?“ als Platzhalter an das DBMS übertragen wird und danach werden für jeden Datensatz nur noch die Parameter übertragen, die in die entsprechenden Platzhalter durch das DBMS eingefügt werden. Es fallen also weniger zu übertragene Daten an, aber vor allem können die Daten so übertragen und verarbeitet werden, dass sie nicht mehr durch das DB-System interpretiert bzw. konvertiert werden müssen. Das Ergebnis kann sich sehen lassen:

Das Programm ist identisch mit dem Programm, welches kontinuierlich, einzelne SQL-Statements absetzt. Allerdings ist es so programmiert, dass es die Performance-Vorteile der Prepared Statements nutzt.

Laufzeit des Programms

28 Minuten und 30 Sekunden

Fazit

Bei so großen Datenmengen, die sequentiell eingelesen werden, kommt es auf die richtige Wahl der Methode an:

Vorteile Prepared Statements:

  • sind einfach zu implementieren
  • sind  im Performance-Vergleich noch ein Stück weit schneller als CSV-Dateien zu erstellen und per LOAD DATA INFILE einzulesen

Nachteile Prepared Statements:

  • die Datenbank wird über die gesamte Zeit stark belastet

Vorteile Generierung von CSV & LOAD DATA INFILE:

  • die Datenbank wird nur für kurze Zeit beansprucht
  • die CSV-Datei kann nach dem Generieren gespeichert bleiben und zu Diagnosezwecke bzw. wiederholten Datenimport verwendet werden

Nachteile Generierung von CSV & LOAD DATA INFILE:

  • kompliziertere Implementierung
  • zusätzlicher Festplattenverbrauch durch CSV-Dateien
  • geringfügig langsamer als Prepared Statements