MySQL Datenimport über Shell & Performance

[holzchines]

Hallo Zusammen,

ich bereibe eine geschlossene Shopanwednung mit eigenen Preisen für jeden Kunden. Um die Preise aktuell zu halten, werden sql-Dateien auf der Shell (Cent-OS) in die mySQL-DB importiert:

$ mysql < datei.sql

Die Dateien beinhalten etwa 13000 Inserts und vorne weg einen Delete. Von diesen Updates laufen jede Nacht ca. 120-200.
Der Server ist virtuell (XEN).

Nun zu meinem Problem, dem ich mich seit Wochen zu nähern versuche:
Die Updates dauern zwischen 30 sec und !!13 min!!.
13 Minuten ist viel zu lange. In schlechten Nächten (= viele langsame Imports - wenig schnelle) können nicht alle Updates abgearbeitet werden. Ich muss also herausfinden warum zwei ähnliche Updates der Maßen unterschiedlich Lange für den Import brauchen. Am Datei-Inhalt kann es nicht liegen, da inhaltlich identische Updates mal schnell - mal langsam eingelesen werden.
Ich habe auch schon den "top" befragt. Der sagt mir, dass die Platte (wait io) während des Updates überdurchschnittlich hoch ist. Das ist aber eigentlich nicht soo verwunderlich und erklärt eigentlich nichts.

Was kann ich testen, wo könnte ich debuggings einbauen? Ich habe irgendwie keinen Ansatz, wie ich mich dem Problem nähern könnte....

Vielleicht habt ihr ja Ideen zur Vorgehensweise oder zum Problem ...?

Danke schon mal...
holzchines.

[Exterminans]

Bitte beschreibe die genaue Datenstruktur der vom Update betroffenen Tabellen.

Sollte es sich nur um eine einzelne Tabelle handeln, so greif zu einem Trick. Mit "ALTER TABLE `table` DISABLE KEYS;" kannst du die Indizes auf der Tabelle temporär deaktivieren was das Einfügen der Datensätze um den Faktor 100-200x beschleunigt. Anschließend nach dem durchführen aller Inserts mit "ALTER TABLE `table` ENABLE KEYS;" die Indizes wieder aktivieren, dies geht bei so wenigen Datensätzen innerhalb von maximal 5 Sekunden. Dieser Trick funktioniert NUR bei INSERTS, bei UPDATES funktioniert das nicht!

Innerhalb des INSERTS solltest du jeweils so viele Datensätze wie möglich in einem einzigen INSERT zuammen fassen (mehrere Datensätze mit einem Befehl einfügen), damit wird der Overhead massiv reduziert und die Geschwindigkeit weiter gesteigert.


Die Geschwindigkeit der Inserts wird dabei massiv von der Fragmentierung der Tabelle beeinflusst, je stärker die Daten innerhalb der Tabelle fragmentiert sind, desto größer sind die Indizes. Sobald die Indizes die Größe des CPU-Caches überschreiten bricht die Performance der Datenbank SOFORT auf einen Bruchteil zusammen. Außerdem führt die Fragmentierung der Tabelle zu einem erhöhten Maß an nicht-sequentiellen Zugriffen auf die Festplatte, dies lässt die Performance ebenfalls kollabieren.

Aus diesem Grund solltest du, falls du eh einen Großteil der Daten ersetzt, die Tabelle vor dem Update per TRUNCATE entweder leeren oder direkt löschen und anschließend neu erzeugen, letzteres geht i.d.R. schneller da TRUNCATE nicht sonderlich effizient arbeitet. Beim Einfügen der Daten dann wieder mit dem bereits beschriebenen Deaktivieren der Indizes arbeiten.

In deinem Fall musst du noch bedenken dass zusätzlich auch noch andere User während dieser Zeit IO-lastige Anwendungen am laufen haben (z.B. Backups) und deine Datenbank dadurch zusätzlich ausgebremst wird. Aus diesem Grund sollten diese Optimierungen dir gut helfen können, zumindest einen sequentiellen Zugriff auf die Festplatte aus zu lösen welcher selbst bei erhöhter Grundlast noch wesentlich schneller ausgeführt werden als zufällig verstreute Zugriffe.

[holzchines]

wow. Danke für die Fülle an Informationen. Ich werde das der Reihe nach abarbeiten.
Ausschließen kann ich lediglich, dass andere Prozesse dazwischenfunken oder den Rechner parallel beanspruchen. Das habe ich gut im Blickfeld.

An Fragmentierungen habe ich auch schon gedacht.
Ich werde über meine Ergebnisse berichten...

[Exterminans]

Ausschließen kann ich lediglich, dass andere Prozesse dazwischenfunken oder den Rechner parallel beanspruchen. Das habe ich gut im Blickfeld.

Ist es ein eigener Root-Server? Nein? Dann kannst du das eben nicht ausschließen. Du bist in jedem Fall IO-limitiert und da hast du keine garantierten Prioritäten.

[r4wi]

log into mysql

#mysql > use mydatabasename;
#mysql > ./mydump.sql;

feddisch
und echt schnell

[Kugelfisch23]

r4wi, was versuchst du mit diesen Kommandozeilen zu erreichen? Die erste Zeile bewirkt lediglich, dass mysql (d.h. der Kommandozeilen-Client von MySQL) gestartet und die Ausgabe in eine Datei namens use umgeleitet wird, die Letztere leitet die Ausgabe in mydump.sql um. Mangels weiterer Parameter werden dadurch lediglich leere Dateien erstellt bzw. bereits vorhandene Dateien mit diesen Namen geleert. Das ist kaum sinnvoll.

Was du wohl meintest, ist das importieren eines Dumps, wie von holzchines erwähnt. Dazu wäre jedoch die stdio-Umleitung `< mydump.sql` passend - abgesehen davon, dass diese Möglichkeit holzchines gemäss Startbeitrag bereits bekannt ist. Es geht ihm lediglich um die Performance des Imports, die aus offenbar noch ungeklärten Gründen stark schwankt.


Den Tipps von Exterminans kann ich mich allerdings nur anschliessen. Abgesehen davon scheint mir durchaus auch zu hinterfragen, ob eine Aktualisierung des kompletten(?) Datenbestandes jede Nacht tatsächlich die sinnvollste Lösung ist. Woher stammen die zu aktualisierenden Daten? Wie stark ändern sie sich üblicherweise innerhalb eines Tages? Wäre eventuell MySQL-Replikation oder ein direkter Zugriff auf die Datenquelle eine Alternative? Das hängt stark vom konkreten Einzelfall ab und lässt sich daher kaum pauschal beantworten.