Update-Performance bei Tabellen mit vielen Einträgen

[Niko310391]

Hallo,

im Rahmen eines Codes bilde ich bei mir zwei "Update-Arrays", die jeweils rund 20.000 Einträge nach folgendem Schema besitzen:

$array1[$id]["wert1"] = 100
$array1[$id]["wert2"] = 100

$array2[$id][$id2]["wert"] = 100
$array2[$id][$id3]["wert"] = 100

Für diese Arrays habe ich am Ende meines Codes einen Update-Befehl in meine MySQL Datenbank, welcher aber extrem lange dauert (ca. 20 Sekunden bestimmt). Das Ganze läuft hier über eine foreach-Schleife mit mysqli-Update Befehlen:

foreach($arUpdatePlayers AS $playerid => $key):
    mysqli_query($connection,"UPDATE players SET fitness = '".$key["fitness"]."', freshness = '".$key["freshness"]."' WHERE playerid = '$playerid'");
endforeach;

foreach($arUpdatePlayerabilities AS $playerid => $value):
    foreach($value AS $abilityid => $key):
        mysqli_query($connection,"UPDATE playerabilities SET value = '".$key["value"]."', training_level = '".$key["training_level"]."' WHERE player_id = '$playerid' AND ability_id = '$abilityid'");
    endforeach;
endforeach; 

Diese lange Update-Zeit ist nicht wünschenswert und auch nicht tragbar. Gibt es eine Möglichkeit die Performance des Updates zu verbessern?

Infos zur Datenbank

Meine Datenbank-Tabellen haben als Engine InnoDB.
In der Tabelle players gibt es den Primärschlüssel "playerid" und einen Index auf der Spalte "position_id"..
In der Tabelle playerabilities gibt es den Primärschlüssel "playerabilityid" und einen Index auf den Spalten "player_id" und "ability_id".

[jspit]

Ich hatte schonmal die Idee, zum Spielstart / beim Laden eines Spielstandes alle Daten einmalig in Arrays zu schreiben und dann während des Spiels immer nur die Werte aus dem Array zu aktualisieren und nur, wenn der User auf Speichern drückt, die Arrays wieder in die Datenbank zu schreiben. Geht sowas überhaupt? Wenn ja, kann mir jemand sagen, wie?

Wenn du ein Array genau so wieder brauchst wie es beim Speichern war dann brauchst du dieses auch nicht auseinander zu nehmen, sonder kannst es in einem Rutsch speichern. Das ist von Grundsatz mit so einer Klasse wie SQLiteObjectStore machbar. Die Klasse selbst habe ich jedoch nicht mit solch großen Arrays wie du sie hast getestet.
Edit: Wenn die Anzahl der Arrays überschaubar bleibt, können die serialisierten Daten auch direkt in Dateien geschrieben bzw. aus Dateien gelesen werden.

Welchen Vorteil bietet denn die Verwendung einer Memory Engine statt der InnoDB Engine? Sind die Zugriffe hier schneller bzw. auch die schreibenden Aktionen?

Die Frage wurde schon #47 beantwortet.

Mit 25.000 Datensätzen benötige ich ca. 900 ms für ein Update aller Datensätze nach dem Schema #38 auf einem (alten!) Entwicklungssystem.
Mit ENGINE=MEMORY für die Updatetabelle reduziert sich die Zeit auf ca. 600 ms.

[protestix]

Du stellst jetzt hier nicht so einfache Grundsatzfragen, oder?
Wie wäre es mit Nachdenken und evtl Bing, Google?

[Niko310391]

Welchen Vorteil bietet denn die Verwendung einer Memory Engine statt der InnoDB Engine? Sind die Zugriffe hier schneller bzw. auch die schreibenden Aktionen?
Was ist denn mit meiner letzten Frage? Lässt sich sowas so in der Art auch umsetzen? Das würde das ganze ja um einiges schneller machen, da ich dann sogesehen keine Datenbank-Aktionen mehr habe außer beim Spielstart / Spielende.

[protestix]

Du willst ein Spiel programmieren, was meiner Meinung nach schon höchster Programmierlevel ist und fragst uns wie?
Ein Mysql Tabelle im Ram anzulegen geht genauso wie sonst auch, lediglich die engine ist dann halt nicht innoDB sondern Memory. Lies im Handbuch den Rest nach.

Du bleibst also bei Datanebanken hast halt nur 2. eine auf der Festplatte als "Archiv" und Sicherung und die memory für die Arbeitsumgebung.

Arrays sind nicht persistent im Speicher. Die Memory DB aber schon.

[Niko310391]

Dann bitte ich dich, mir mal zu erklären, wie das klappen soll. Da bin ich leider überfragt.

Es ist ja nunmal so, dass ich in jedem Menü auf Werte aus der Datenbank zugreife. Diese müssen dementsprechend auch aktuell bleiben. Also müssen auch die ganzen Berechnungen da reinlaufen, oder gibt es eine andere Möglichkeit?
Vielleicht gibt es ja auch eine viel simplere Methode?!

Ich hatte schonmal die Idee, zum Spielstart / beim Laden eines Spielstandes alle Daten einmalig in Arrays zu schreiben und dann während des Spiels immer nur die Werte aus dem Array zu aktualisieren und nur, wenn der User auf Speichern drückt, die Arrays wieder in die Datenbank zu schreiben. Geht sowas überhaupt? Wenn ja, kann mir jemand sagen, wie?

[protestix]

Dann hat dein Spiel ein Desingproblem.
Bei vielen Spielen, wird halt alles im RAM gehalten und erst nach einer entweder vorab eingestellten Zeit oder beim verlassen des Spiels der Spielstand gespeichert.
Bricht man die Spiele ab sind meist auch die Spielstände verloren. Das Betrifft insbesondere Wirtschaftssimulationen wo oft viele Berechnungen durchgeführt werden müssen. Ich beziehe mich jetzt auf Spiele die nicht online gespielt werden müssen, und shcon da ist das Problem offensichlich, den beim Speichern kann schon mal mehr als eine Sekunde vergehen.

Du kannst dein Spiel ja im Hintergrund alle 10 Minuten per Cronjob speichern. Und hältst den Rest im RAM, dazu kannst du bei Mysql die Tabelle als Memory anlegen und überträgst dann nur von dieser Tabelle die Daten auf die Tabellle in der Festplatte.

Du musst dir halt was überlegen.

Eins steht aber ausser Frage das Schreiboperationen in eine DB teuer sind, das betrifft updates ebenso wie inserts. Kommen noch Indexe hinzu kann das richtig langsam werden, da ja bei jedem Insert oder update die ganzen Index-Tabellen auch aktualisiert werden müssen.
Darüber solltest du dir im Klaren sein.

[Niko310391]

Also es liegt wider Erwarten am SQL Statement:
Calculation: 1,13715195656 sec
SQL: 3,68900108337 sec

In den Bereich Calculation fällt die Ermittlung der ganzen Effekte des Trainingstages und der Bereich SQL wertet die Arrays aus, schreibt sie per MultiInsert in eine temporäre Datenbank und aktualisiert die entsprechenden Tabellen dann auf Basis eben dieser.
Daher bräuchte ich wirklich Hilfe im Bereich der SQL Optimierung.


Die Frage von VPh habe ich schon verstanden. Aber ja, bei jedem vom User ausgelösten Tagesfortschritt (diesen kann er jederzeit in seinem Spielstand auslösen) müssen die Berechnungen neu ausgeführt werden.

[jspit]

Habe nochmal einige Test's gemacht.
Mit 25.000 Datensätzen benötige ich ca. 900 ms für ein Update aller Datensätze nach dem Schema #38 auf einem (alten!) Entwicklungssystem.
Mit ENGINE=MEMORY für die Updatetabelle reduziert sich die Zeit auf ca. 600 ms. Nun sind diese Ergebnisse nicht mit deiner (@Niko310391) Datenbankstruktur vergleichbar, eine genaue Analyse wo bei dir die Zeit bleibt dürfte sich trotzdem lohnen.

Die Frage von VPh hätte ich so formuliert:
Müssen diese Berechnungen denn immer ausgeführt werden, wenn ein User aktiv eine Seite aufruft? Aber wir kennen ja deine Abläufe nicht.

[Niko310391]

VPh Ja, müssen sie. Denn der Tagesablauf im Spiel spiegelt nicht den realen Tagesablauf dar. Und jeder simulierte Tag im Spiel bedarf dieser Berechnungen.
Somit bringt mir ein Cronjob an dieser Stelle gar nichts.

[VPh]

Ich möchte einfach, rein aus Interesse nochmal fragen...

Müssen diese Berechnungen denn ausgeführt werden, wenn ein User aktiv eine Seite aufruft?
Auf dem ersten Blick wäre meine Vermutung, dass das einfach ein Trainingsscript ist, dass auch regemäßig um 00:01 Uhr per CronJob aufgerufen werden kann und dann im Hintergrund seinen Job erledigt.

[Niko310391]

Die genannte Zeit von 4 Sekunden ist doch gar nicht so schlecht zu den gefühlten 30 Sekunden im Startbeitrag. Für die Feinoptimierung setzte doch erstmal Zeitmarken (microtime(true) -> array) um zu ergründen wo genau die Zeit bleibt. Kannst dich dafür auch gerne der Klassen debug und oder phpcheck bedienen.

Das werde ich mal versuchen. Aber rein vom Gefühl her würde ich behaupten, dass die Zeit bei der Ermittlung der Effekte draufgeht.

[jspit]

Das erstaunt mich, bei 30.000 kann man ja noch nicht wirklich von "viel" reden...

Das ist genau die 1 MB Grenze von der protestix #39 gesprochen hat. 25.000 Datensätze für ein Insert mit ca. 960KByte Umfang passt bei mir grad noch.

Edit: Dich hat "MySQL server has gone away" erstaunt. Der MySQL-Server ist da nicht down, nur die Verbindung zum Client.

[kaminbausatz]

Ja, bei mir kommt da schon beim Erstellen von 30.000 Testdaten ein "MySQL server has gone away". Der Speicherbedarf des Test ist auch nicht ohne: Memory: 17.5M (128M).

Das erstaunt mich, bei 30.000 kann man ja noch nicht wirklich von "viel" reden...

[jspit]

Der Kritische Punkt ist nicht das Multi Insert, dieses ist unlimitiert, Grenzen gibt e aber beim Speicher(RAM) und paket size(bei MySQL 1 MByte). Paket Size kann man aber hoch setzen siehe https://dev.mysql.com/doc/refman/8.0...too-large.html

Ja, bei mir kommt da schon beim Erstellen von 30.000 Testdaten ein "MySQL server has gone away". Der Speicherbedarf des Test ist auch nicht ohne: Memory: 17.5M (128M).

Niko310391 : Die genannte Zeit von 4 Sekunden ist doch gar nicht so schlecht zu den gefühlten 30 Sekunden im Startbeitrag. Für die Feinoptimierung setzte doch erstmal Zeitmarken (microtime(true) -> array) um zu ergründen wo genau die Zeit bleibt. Kannst dich dafür auch gerne der Klassen debug und oder phpcheck bedienen.
Ich zähle mich nicht zu den DB-Spezis und habe zudem von MySQLi null Ahnung (Nutze nur PDO).