UPDATE's und INSERT's viel zu langsam

[onemorenerd]

@PHP-Desaster: Ich habe gar keine Ahnung, wie es bei MySQL intern läuft. Garantiert anders, als ich es mir vorstelle, aber sicherlich optimaler als die naive Implementierung nach Lehrbuch.

@web4free: Wenn du so viele Daten änderst, gibt es dann am Ende überhaupt noch Daten, die nicht verändert wurden? Wenn es keine* mehr gibt, hätte ich noch eine Idee: Neue Tabelle anlegen, alle Daten aus den ZIP-Files da rein, alte Tabelle löschen, neue Tabelle umbenennen und Indizes anlegen.

*) oder nur sehr wenige ... dann entsprechend vor dem Löschen der alten Tabelle diese wenigen in die neue kopieren.

[web4free]

Morgen,

Nein es sind nicht alle Daten - denn dann würde ich es in der Tat so lösen.
Es sind viele Kunden die unterschiedlich viele Datensätze über den Tag verstreut zu uns importieren.

Übrigens hat mir mein nächtliches Maintenance einen Pflock zwischen die Beine geschmissen.
Da bin ich aber selber schuld da ich hier beim DELETE "LOW_PRIORITY" eingesetzt habe - derweil soll er grad in der Nacht alles schnell über die Bühne bringen.

Wie dem auch sei, hab ich jetzt nochmals die KEY_BUFFER Variable etwas höher angesetzt, da lt. MYSQL-Handbuch hier das DELETE noch schneller gemacht werden kann.

Ich bedanke mich jedenfalls bei Euch - ich glaub ich habs im Griff!

[H2O]

Ich habe nicht die ganze Diskussion bis ins letzte Details gelesen.
Was mir aufgefallen ist, an der Struktur, sind einerseits die drei longtext-Attribute (Text ist übrigens ein reserviertes Wort). Blobs sind generell langsam und ich bezweifle, dass du tasächlich diese 2^32 (4'294'967'296) Bytes, was immerhin noch etwa 20'000 Buchseiten oder 20 dicken Büchern entspricht, für diese Felder brauchst. varchar-Attribute können seit MySQL5 auch maximal 2^16 (65'536) Bytes gross ein, und das dürfte sicher genügen.
Und dann hast du sehr viele Indizes, die Abfragen sehr beschleinigen, aber Änderungen verlangsamen. Da, wenn ich das richtig verstanden habe, du deine Änderungen als Batch-Job ausführst, ist es möglicherweise schneller, vor den Änderungen die Indizes zu löschen, dann die Updates zu machen, und dann die Indizes neu zu erstellen. Dieses Vorgehen wird in grössern DB-Installation sehr oft angewandt.

[web4free]

Hallo H2O,

Ich hab jetzt mal die Longtext in normale Text umgewandelt - sollte sich in der Tat ausgehen - wußte nicht, dass Longtext so groß wäre.

Ja, das Importscript läuft auf einen 2. Server im Dauerlauf und hat auch sonst keine weitere Aufgabe - da wird nur geschwitzt!
Klar - was noch etwas erschwerend hinzukommt ist, dass die Datenbank am 1. Server liegt - aber ich bekomme die Importe auf den 2. Server und dort beeinträchtige ich dann nicht die Produktionsmaschine.

Indizes deaktivieren, Änderungen vornehmen und dann wieder Indizes aktivieren ist mir heute schon mal durch den Kopf gegangen.
Ich hab halt nur die Befürchtung, dass das aktivieren dann wieder so lange dauert weil ja alle Indexe neu geschrieben werden - oder lieg ich da falsch??

[onemorenerd]

Kannst es auch mal mit delay_key_write=1 und pack_keys=0 versuchen.
http://dev.mysql.com/doc/refman/5.0/...ate-table.html


Mir ist übrigens aufgefallen, dass du sehr viele, oft sehr ähnliche KEYs hast. Da kann man noch einiges optimieren.


KEY Hersteller (Hersteller)
KEY Kilometer (Kilometer),
KEY Preis (Preis),
KEY Kraftstoff (Kraftstoff),
KEY Count_Fahrzeuge (fart,aktiv),

Alle unnötig, da Präfixe anderer Keys.


KEY klassifizierung (klassifizierung)

Hätte KEY klassifizierung(24) nicht bereits eine ausreichende Selektivität? Oder noch kürzer?


KEY Hersteller_3 (Hersteller,Fahrzeugtyp,aktiv,fart),
KEY Hersteller_2 (Hersteller,Fahrzeugtyp,user,aktiv,fart)

Kann man doch bestimmt durch KEY (Hersteller,Fahrzeugtyp,aktiv,fart,user) ersetzen, oder gibt es so viele Anfragen, die anhand von Hersteller, Fahrzeugtyp und user nach fart und aktiv suchen? Sicher nicht.


Der FULLTEXT Index ist vermutlich die stärkste Bremse bei deinen Massenoperationen. Den würde ich auf jeden Fall vorher wegwerfen und hinterher wieder anlegen.


Sicher steckt noch mehr Potenzial in den Indizes. Ich hab das nur überflogen und mehr macht auch keinen Sinn, wenn man weder Struktur und Häufigkeit der Anfragen noch Verteilung der Daten kennt.

[web4free]

Hallo Leute,

Ich hab mal den Hauptschuldigen ausfindig machen können!
Grund war mein nächtliches Datenbankdienstprogramm was anstelle von grad mal 20 Minuten statte 9h gedauert hat und mir damit meine Importe völlig zum Erliegen gebracht hat, da beides gemeinsam zu viel Ressourcen verbraucht hat. Man darf ja die Seitenzugriffe auch nicht vergessen und die ganzen RSS Schnittstellen die irgendwo verteilt in ganz Deutschland anzutreffen sind.

Ich hab jetzt nur meinen nächtlichen Job modifiziert und siehe da er braucht nur noch knappe 20 Minuten was in der kalkulierten Zeit ist!
Kalkulierte Zeit wären 3h, ab dann muß alles fertig sein, weil dann wieder mit den normalen Importjobs begonnen wird.
Und die sind so bemessen, dass jene die sich in der Nacht angesammelt haben bis spätestens 9h erledigt sein müssen um das normale Tagesgeschäft nicht zu beeinträchtigen.

Jetzt geht sich wieder alles schön aus und es ist jede Mange Platz für neue Kunden

Ich bedanke mich bei Euch für die Hilfe und wünsche noch schöne Feiertage!