SQL Performance-Unterschied zwischen 'UPDATE' und 'ON DUPLICATE KEY UPDATE'

[kleinesµ]

Hallo liebe CB-Gemeinde,

ein Skript muss pro Sekunde zwischen 50 bis 250 Datenbankupdates (Mysql) machen.

Bisher wurde das so gehandhabt:

INSERT INTO table SET spalte1='xxx', spalte2='yyy' ON DUPLICATE KEY UPDATE spalte1='xxx', spalte2='yyy';

Da aber geschätzte 99,99% dieser Zugriffe eh updates sind, möchte ich gerne das SQL-Statement zu einem reinen Update abändern.

UPDATE table SET spalte2='yyy' WHERE spalte1='xxx'

Was mich brennent interessieren würde, welchen Performance-Vorteil würde sich daraus ergeben? Hat da schon jemand Erfahrungen gemacht?
Im Netz finde ich nichts dazu, außer http://stackoverflow.com/questions/5036051/performance-question-on-duplicate-key-update-vs-update-mysql

Theoretisch muss ja die DB nicht mehr gucken, ob die Zeile existiert, sondern führt gleich ein Update aus.
Mich würde der prozentuale Performancegewinn interessieren.

Vielen Dank schon mal im voraus für eure Antworten

 

[volcem]

Du könntest es per PHP messen.

Also einfach die Differenz von Anfang bis Ausgabe messen.

Mehr würde mir jetzt nicht einfallen.

 

[Funart]

Wieso speicherst das nicht zwischen.
Falls object exisitiert Update ansonsten Insert.

Man muss nicht immer mit biegen und brechen alles SQL machen.

 

[kleinesµ]

@Funart

wie meinst du das mit zwischenspeichern ?

Falls object exisitiert Update ansonsten Insert.

Genau das macht doch INSERT ... ON DUPLICATE KEY UPDATE

Ergänzung (30. März 2011)

@volcem

Das Skript läuft quasi ständig im Hintergrund und schiebt Updates in die DB.
Pro Sekunde muss es 50-250 Updates schaffen, funktioniert ja auch problemlos. Ich kann da nur leider keine Zeitmessung machen, höchstens auf die Prozessorauslastung des Servers gucken, leider ist diese nur nicht so aussagekräftig.
Dachte mir vll. hat irgendwer schon Performanceerfahrungen damit gemacht.

 

[AlbertLast]

wenn du irgendwann mal vorhast schnell zu sein,
dann benutzt load data infile : http://dev.mysql.com/doc/refman/5.1/en/load-data.html

 

[kleinesµ]

@AlbertLast
Die Daten kommen als Stream, ergo nix mit load data infile. Das war auch nicht meine Fragestellung.

 

[cx01]

Also ich denke nicht, dass das einen signifikanten Unterschied macht. Vermutlich 1% oder so. Du kannst mal nach dem EXPLAIN Statement für MySQL schauen, damit kann man sich anzeigen lassen, wie eine gegebene Anfrage ausgeführt wird.

 

[ice-breaker]

Ich weiß nicht ob es noch immer so ist, aber vor einiger Zeit war "INSERT ... ON DUPLICATE KEY" noch so implementiert, dass erst ein Insert versucht wurde und bei einem Fehler dann ein Update daraus gemacht wurde, seit dem mach ich erst ein Update und dann ein Insert (da aber auf Race-Conditions prüfen).
Also da es bei dir größtenteils sowieso in UPDATE-Statements endet, würde ich dir auf jedenfall empfehlen auch den manuellen Weg zu gehen, gib MySQL nicht zuviele Möglichkeiten etwas falsch zu machen, MySQL entscheidet sich zwar mittlereile größtenteils bei den Queryevaluationsplänen richtig, es kann aber auch gehörig in die Hose gehen.

Nachtrag: meine Aussage bezieht sich nur auf diesen speziellen bei dem der Query größtenteils zu einem UPDATE wird.


@cx01: EXPLAIN funktioniert nur mit SELECT

 

[kleinesµ]

Hmm EXPLAIN geht doch nur bei selects, oder?

Ergänzung (30. März 2011)

@Eisbrecher

aber vor einiger Zeit war "INSERT ... ON DUPLICATE KEY" noch so implementiert, dass erst ein Insert versucht wurde und bei einem Fehler dann ein Update daraus gemacht wurde

So in etwa hab ich mir das auch gedacht. Müsste ja einen Performance-Vorteil von 50% bringen (theoretisch). Ich nutze Mysql Version 5.1.43

 

[cx01]

Oh, Sorry. In meiner Erinnerung ging es auch bei UPDATEs. Aber kann sein, dass das ne andere DB als MySQL war.

 

[Enigma]

Wir setzen das auf ner Tabelle mit 186M rows ein, wobei der Index rund 3 GB groß ist.

Es läuft, darum haben wir uns nie groß gedanken über die Performance gemacht

 

[AlbertLast]

wenn du den stream in csv umwandelst und dann mittels load file reinlädst hast du die maximal performance

 

[kleinesµ]

Die Daten müssen aber Realtime in die Datenbank, selbst eine Verzögerung von 10 Sekunden wäre nicht umsetzbar.
Also könnte ich nicht mal den Stream 10Sek lang in ein csv-File schreiben, und dann mit einem Schwung den Kram in die DB schicken.

Das Filesystem zu bemühen würde ja auch wieder Performance kosten. Hmm...

 

[Funart]

Zu meinem Post.
Ich rede davon die Object im Cache zu halten / Applications Logik.
Kenn jetzt die ganze Materie nicht aber unter Umständen ist es performanter per Logik zu überprüfen ob ein Insert oder update notwendig ist.
Danach einige Updates zusammenfassen und diese geblockt in einer Transaction auszuführen.
Das sollte in Summe doch etwas Performance bringen.

 

[kleinesµ]

@ Funart
Die Daten lange im Cache zu halten ist nicht so gut, da sie Realtime genutzt werden.
Aber ich könnte die Tabelle in den RAM packen http://dev.mysql.com/doc/refman/5.1/de/memory-storage-engine.html (sicherheitshalber per Cronjob alle paar Minuten auf Platte schreiben lassen).
Das könnte Performance bringen, oder?

nochmal zu deiner Idee:
Entweder werden pro Sekunde 100 einzelne Updates gemacht
oder
sagen wir mal 3 Sekunden lang gesammelt, und dann 300 Updates mit ein mal geschickt.

Die Idee kam mir ja auch, aber ich finde da ein Performance-Vorteil fraglich. Einem relativ konstanten Input steht ein Schub alle 3 Sek gegenüber.

 

[Funart]

Das hängt im großen und ganzen davon ab wie die Transaktions verwaltet werden.

Hab vor paar Wochen mal eine Wortliste von 200k Wörtern in eine DB gespeichert.
Der unterschied zwischen einem commit nach jedem Wort und erst am Ende war Enorm.
Wir reden da nicht von Sekunden sondern von Minuten.

Wenn ich du wäre würd ichs auf alle fälle einmal Testen.

 

[ice-breaker]

Aber ich könnte die Tabelle in den RAM packen http://dev.mysql.com/doc/refman/5.1/de/memory-storage-engine.html (sicherheitshalber per Cronjob alle paar Minuten auf Platte schreiben lassen).
Das könnte Performance bringen, oder?

na dann nimm lieber InnoDB mit nem großen Puffer, das vereinigt beide Ansätze in einer perfekten Mischung.
Zusammen mit "HandlerSocket" kann InnoDB dann auch so schnell wie ne NoSQL-Datenbank werden, schon klasse wenn man den SQL-Overhead los wird

Hab vor paar Wochen mal eine Wortliste von 200k Wörtern in eine DB gespeichert.
Der unterschied zwischen einem commit nach jedem Wort und erst am Ende war Enorm.
Wir reden da nicht von Sekunden sondern von Minuten.

Beides ist nicht optimal:
Je ein Commit sind unnötig viele Schreiboperationen (Daten + Log).
Ein Commit mit 200k Datensätzen erzeugt einen riesigen Overhead, da die Datenbank sicherstellen können muss, dass sie deine Transaktionen auch wieder unterbrechen kann.
Am sinnvollsten ist es die Daten in Paketen in die DB einzufügen, die optimale Paketgröße gilt es dann herauszufinden auf Basis der Größe des Recoverylogs, des Externspeichers, des Bufferpools usw.

 

[Enigma]

Die Daten müssen aber Realtime in die Datenbank, selbst eine Verzögerung von 10 Sekunden wäre nicht umsetzbar.

Der Insert dauert weniger als 2ms Das meinte ich mit "funktioniert". Das ON DUPLICATE KEY spart dir halt auf jeden fall die Zeit die du für ein 2. Query brauchst.

 

[Funart]

Ich wollte nicht empfehlen 200k einträge zu commiten.
Weis ehrlich gesagt nimmer auswendig ob da nicht ein paar zwischen commits dabei waren.
Das war auch nur ein Settup.

Wollte nur aufzeigen wie stark der Unterschied ist.
Aber gratuliere zum i-tüpferl reiten.

 

[ice-breaker]

Der Insert dauert weniger als 2ms Das meinte ich mit "funktioniert". Das ON DUPLICATE KEY spart dir halt auf jeden fall die Zeit die du für ein 2. Query brauchst.

streng genommen auch nicht
Die 2ms erreicht man nur wenn die Daten nicht persistent sind, denn MyISAM als Beispiel schreibt die Daten nur in den OS-Puffer, der die Daten dann irgendwann echt auf die Festplatte schreiben darf, genauso innodb wenn man das strikte fsync()en abstellt.
Also in echt benötigt der Schreibvorgang im Mittel die Hälfte der Seektime, also 4.5ms - wenn man eine wirkliche Datensicherheit möchte.

Immer daran denken, dass MyIsam eine absolut unsichere Engine ist, MyIsam garantiert nichtmal, dass eure Daten sicher gespeichert sind.
Zieht mal den Stecker eures Rechners/Servers während der gerade auf der MyIsam-Datenbank arbeitet, die Chance ist sehr hoch, dass die Datenbank danach korrupt ist und repariert werden muss und einige Datensätze fehlen, von denen euer Programm angenommen hat, dass sie nun wirklich gespeichert sind, wunderbar, wenn man gerade eine Bestellung in einem Onlineshop hatte

aber ich schweife wieder vom Thema ab ... ^^

Ich wollte nicht empfehlen 200k einträge zu commiten.
Weis ehrlich gesagt nimmer auswendig ob da nicht ein paar zwischen commits dabei waren.
Das war auch nur ein Settup.

Wollte nur aufzeigen wie stark der Unterschied ist.
Aber gratuliere zum i-tüpferl reiten.

ja mit ein paar Commits dazwischen hast du es doch richtig gemacht.
Ich würde das nicht als "i-tüpferl reiten" bezeichnen, man konnte aus deiner Aussage den falschen Schluss ziehen, dass alle Daten in einem Commit effizienter sind und wir wollen ja nicht, dass jemand dies wirklich denkt