MySQL viele inserts, wie sieht es mit der Performance aus?

[klausschreiber]

Hallo,

in dem Projekt, an dem ich gerade arbeite, werden von Usern hochgeladene Dateien eingelesen und die Infos sollen dann in einer Datenbank gespeichert werden. Es ist durchaus möglich, dass durch einen Upload mal eben 200 Datenbankeinträge notwendig sind.
Bisher hatte ich dazu eine Tabelle und habe Prepared Statements benutzt (benutze die mysqli Erweiterung in php).

Da sich aber die Daten in bestimmten Feldern wiederholen können und ich die "zusammengehörigen" Zeilen auch in der HTML-Ausgabe gruppiert werden sollen, habe ich nun für diese Datenfelder eine extra Tabelle angelegt.

Dies läuft nun folgendermaßen ab:
Es gibt in der 2. Tabelle 3 Spalten (zuzüglich Primärschlüssel). Über diese 3 Spalten habe ich ein Unique gelegt. Der Insert für diese Tabelle sieht bisher so aus:
"INSERT INTO ...
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)
Gibt es den Eintrag noch nicht, wird er eingefügt, ansonsten wird der Primärschlüssel des vorhandenen Eintrags an "last_insert_id" weitergeleitet.

Nun wird in der anderen Tabelle der dazugehörige Eintrag erstellt mit der id von last_insert_id als Fremdschlüssel.

D.h. Wenn z.B. 200 Einträge zu machen sind, bedeutet das 400 Datenbankabfragen. Gibt es einen Weg, das möglichst performant zu machen? Bringt es etwas, wenn ich die Einträge per prepared Statement mache, oder ist das eher schlecht, 2 prepared Statements gleichzeitig "offen" zu haben?

Oder sind 400 Insert's gar kein so großes Problem (die Datenmenge ist relativ klein und es werden wohl auch nicht im Minutentakt soviele Datensätze eingefügt)? Es macht auch nichts, wenn der User 2-3 Sekunden warten muss, allerdings soll das Projekt erstmal auf einem shared Hosting Server laufen und wenn ich den ganzen Server lahmlege, sieht das der Hoster wohl nicht so gerne.

Gruß,
Klaus

 

[paddya]

Oder sind 400 Insert's gar kein so großes Problem (die Datenmenge ist relativ klein und es werden wohl auch nicht im Minutentakt soviele Datensätze eingefügt)? Es macht auch nichts, wenn der User 2-3 Sekunden warten muss, allerdings soll das Projekt erstmal auf einem shared Hosting Server laufen und wenn ich den ganzen Server lahmlege, sieht das der Hoster wohl nicht so gerne.

Gruß,
Klaus

Sollte absolut kein Problem sein. Vor allem, wenn die 400 Inserts nicht im Sekundentakt kommen. Und wenn das Projekt so stark wächst, dass der Server in die Knie geht, dann ist das doch ein gutes Zeichen und du kannst in einen eigenen Server investieren

Greetz

paddya

 

[klausschreiber]

ok, danke für die schnelle Antwort. Dann brauche ich mir ja keine Sorgen zu machen.

Gruß,
Klaus


edit: Jetzt habe ich doch noch was vergessen^^. Ist es dann trotzdem sinnvoll, das über prepared Statements zu machen, oder ändert das nicht viel? (es bleibt ja eigentlich lediglich die userID, sowie das Datum des Eintrags gleich, sodass der Datenverkehr nicht viel kleiner wird)

 

[Eziyo]

So wie ich es gehört habe, sollen prepared Statements immer sinnvoller sein, da sie viel sicherer sind.

 

[klausschreiber]

sehr sicher sind prepared Statements. Aber sind sie sicherer, als wenn man sauber mit mysql_real_escape_string arbeitet?

Gruß,
Klaus

 

[paddya]

sehr sicher sind prepared Statements. Aber sind sie sicherer, als wenn man sauber mit mysql_real_escape_string arbeitet?

Gruß,
Klaus

$result = mysql_query("SELECT * FROM users WHERE userid = " . mysql_real_escape_string($_GET['userid']));

Und jetzt übergeb mal für $_GET['userid'] folgendes:

1 OR 1 = 1

. Und da hast du deine Sicherheitslücke. Sich blind auf mysql_real_escape_string() zu verlassen ist genauso schlimm, wie es einfach wegzulassen

Greetz

paddya

 

[ice-breaker]

Es ist durchaus möglich, dass durch einen Upload mal eben 200 Datenbankeinträge notwendig sind.

das klingt für mich nach einem großen konzeptionellen Problem, also 200 Einträge pro Upload
Da entstehen ja Datenmengen die sind gewaltig.

Oder sind 400 Insert's gar kein so großes Problem (die Datenmenge ist relativ klein und es werden wohl auch nicht im Minutentakt soviele Datensätze eingefügt)?

oh das macht sehr viel aus
MySQL muss nach einem Insert möglicherweise den B-Baum "balancieren", wenn du in 400 einzelnen Querys die Daten insertest wirst du ziemlich oft den Baum neu ausrichten müssen. Ein Batch-Insert ist da eine deutlich bessere Wahl.

Es macht auch nichts, wenn der User 2-3 Sekunden warten muss

2-3 Sekunden? So geringe Anforderungen hätte ich auch gerne mal.

Sollte absolut kein Problem sein. Vor allem, wenn die 400 Inserts nicht im Sekundentakt kommen.

es geht aber doch um 400 Inserts pro Request.

Ist es dann trotzdem sinnvoll, das über prepared Statements zu machen, oder ändert das nicht viel? (es bleibt ja eigentlich lediglich die userID, sowie das Datum des Eintrags gleich, sodass der Datenverkehr nicht viel kleiner wird)

Prepared Statements funktionieren anders als du denkst
Du definierst ja eine Schablone, diese wird an MySQL gesendet, wenn du den Query nun mit den Bindings ausführst, werden nur deine Bindings neu gesendet, also deutlich weniger Datenverkehr
Nebenbei bemerkt kann MySQL die Schablone schon evaluieren und Optimierungen ausführen und sich merken, wenn du jedesmal den Query neu schickst, muss es diese Arbeit jedesmal aufs neue machen.

 

[paddya]

es geht aber doch um 400 Inserts pro Request.

pro Upload. Und seine User werden ja nicht nur uploaden

Greetz

paddya

 

[klausschreiber]

$result = mysql_query("SELECT * FROM users WHERE userid = " . mysql_real_escape_string($_GET['userid']));

Und jetzt übergeb mal für $_GET['userid'] folgendes:

1 OR 1 = 1

. Und da hast du deine Sicherheitslücke. Sich blind auf mysql_real_escape_string() zu verlassen ist genauso schlimm, wie es einfach wegzulassen

Greetz

paddya

uups, ich hätte zwar schon vorher kontrolliert, ob es ein nummerischer Wert ist, aber an diesen möglichen Schadcode habe ich nicht gedacht. Wenn ich das richtig sehe, geht das aber nur bei Integern, weil Strings ja in Hochkommata stehen? Als wäre ein mysql_real_escape_string und bei Integern eine Typenprüfung genauso sicher, wie ein prepared Statement, oder? Oder denkst du, dass ein prepared Statement selbst bei einem einzigem Insert immer zu bevorzugen ist ist vor sowas selbst "Rumgeflicktem"?

@ice-breaker:
die vielen Datenbankeinträge sind eigentlich umungehbar. Es wird ja auch nicht jeder User so große Datein uploaden. Bei manchen Uploads sinds vielleicht auch nur 20-30 Inserts, kommt halt drauf an.

Aber wenn in der Textdatei halt 200 verschiedene Einträge drinnen sind, dann sind das 200 (bzw. dann halt 400) Inserts.

Also ich speichere nicht jede Zeile extra oder so. Jeder Text ist einem bestimmten Spieler und einer Kategorie zugeordnet. Und jede Spieler-Kategorie Kombination hat ihren eigenen Datensatz. Sollte zu dieser Kombination bereits ein Datensatz vorhanden sein, erstelle ich auch keinen neuen, sondern erweitere nur den alten Datensatz.


Gruß,
Klaus

edit:
Jo, wie paddya sagt, nicht jeder Request ist ein Upload, die wenigsten Requests werden Uploads sein. Daher auch meine Meinung, dass es nichts machen würde, wenn der User 2 Sekunden warten muss. Der Upload der Datei dauert je nach Internetzugang um einiges länger, dann kann er die 2 Sekunden auch noch warten (ich kann ja einen Fortschrittsbalken einbauen). Klar, würde jeder Request solange dauern, wäre das fatal; auf so langsamen Seiten bleibe ich selber auch nicht lange^^.

 

[Snyke]

Also wenn du die last_insert id nicht im PHP benoetigst wuerde ich fuer prepared statement mit delayed inserts stimmen, damit returned jeder query call sofort, auch wenn die Daten noch nicht wirklich inserted wurden. Ausserdem kann die DB ja im hintergrund ackern waehrend das PHP frontend weiter laeuft

 

[klausschreiber]

für die zweite Abfrage ist das eine Möglichkeit, danke. Bei der ersten Abfrage brauche ich aber die last_insert_id, um die zweite Abfrage ausführen zu können.

Gruß,
Klaus

 

[Snyke]

Die kannst du doch sicher kombinieren
Ich sach nur trigger

 

[ice-breaker]

delayed inserts

dann ist aber nicht sichergestellt, dass die Daten wirklich gespeichert wurden bzw. wann, der Server kann ja ziemlich lange sehr ausgelastet sein und dann abstürzen.

Die kannst du doch sicher kombinieren
Ich sach nur trigger

Neija trigger sind in MySQL auch nicht anders implementiert als klausschreibers Lösung
Es würde nur weniger Netzwerkverkehr auftreten.

 

[klausschreiber]

@ice-breaker:
Werden delayed inserts in ihrer Priorität sehr weit zurückgestellt, sodass es wirklich sehr lange dauern kann, bis sie eingetragen werden? Wenn die Daten dann unter Umständen erst ne halbe Stunde später eintrudeln, ist das natürlich nichts.

Aber sehe ich das also richtig, dass prepared Statements für diesen Zweck auf jedenfall schonmal etwas bringen und es macht auch nichts, wenn man zwei prepared Statements gleichzeitig offen hat (außer sehr geringer Speichermehrverbrauch)?

 

[ice-breaker]

@ice-breaker:
Werden delayed inserts in ihrer Priorität sehr weit zurückgestellt, sodass es wirklich sehr lange dauern kann, bis sie eingetragen werden? Wenn die Daten dann unter Umständen erst ne halbe Stunde später eintrudeln, ist das natürlich nichts.

i.R. nicht einmal Sekunden
Sollte eine Anwendung aber eben ge-slashdoted werden, dann hat MySQL ziemlich lange ziemlich viel zu tun, weshalb es da zu solchen Problemen kommen kann.
Ich rate generell von Insert Delayed oder den Low/High-Priority-Flags ab.
Da ist es dann sinnvoller sich selbst Job-Queues zu bauen oder Software wie "gearman" zu nutzen.

Aber sehe ich das also richtig, dass prepared Statements für diesen Zweck auf jedenfall schonmal etwas bringen und es macht auch nichts, wenn man zwei prepared Statements gleichzeitig offen hat (außer sehr geringer Speichermehrverbrauch)?

ja bringt sehr viel und nein ist kein Problem.

 

[strumpel]

Jow Leute ... bitte schlagt und tretet mich nicht sollte ich was überlesen haben...

Aber ich hab schon mehr als 1 Datensatz pro INSERT gemacht.
Also 400 pro DB QRY sind machbar "in einem rutsch".
Naja vielleicht denk ich auch falsch(sorry4spam dann)

MFG
strumpel

INSERT INTO table (wert,wert1,wert3) VALUES (1,1,1),(2,2,2)

(oder so ähnlich ungetestet)

 

[klausschreiber]

ok, danke euch allen. Dann werde ich wohl normale prepared Statements nehmen.

Gruß,
Klaus

edit:
@strumpel: Das wäre zwar im Normfall eine Möglichkeit, aber ich brauche für die 2. Query die last_insert_id von der ersten Query. Dadurch muss ich immer Query 1, Query2, Query 1, Query 2, ... abschicken.

 

[theHacker]

@strumpel: Das wäre zwar im Normfall eine Möglichkeit, aber ich brauche für die 2. Query die last_insert_id von der ersten Query. Dadurch muss ich immer Query 1, Query2, Query 1, Query 2, ... abschicken.

Und wieso machst du nicht einen Schwung Query 1, danach einen Schwung Query 2?
Dann gehts mit nur 2 INSERT-Operationen.

 

[strumpel]

Ich wage mal einfach zu behaupten das du deine id noch im selben INSERT-QRY nehmen kannst und auf alles vorher aufbauen kannst. Hab ich schon gemacht(nicht mit last_ID aber vllt mal testen und lesen beim Handbuch(insert))

MFG
strumpel

EDIT: Hatte mal ne SQL Qry die 5000 Datensätze angelegt hat. Erst PHP schleife das Qry baun und dann ab dafür nach MYSQL.
Nen Update QRY brachte es mal auf 4 Seiten ^^ = 1,5sec beim Celeron 400^^

 

[klausschreiber]

@strumpel:
Meinst du mit multi Queries, weil die beiden Queries gehen ja an jeweils verschiedene Tabellen? Müsste ich mal schauen.

@theHacker:
Die zweite Query könnte ich tatsächlich in einem Schwung machen. Aber Query 1 kann ich doch nicht in einem Schwung machen, weil ich ja von jeder Query der Einserqueries die last_insert_id brauche, oder gibt es da eine Möglichkeit, die ich nicht kenne?


Gruß,
Klaus