Diskussion: Hol das letzte aus deiner DB!

Hallo community,

zwei Dinge vorab: ich denke, der Trend (Spielekonferrenz, Foren,..) geht momentan immer stärker auf eine Zusammenarbeit der Entwickler hinaus was ich nur dick unterstützen kann. Daher dieser thread. Zum anderen habe ich beim Verfolgen von manchen threads bemerkt, dass Informationen nur ungern preisgegeben werden (was irgendwo verständlich ist). Ich denke wenn wir alle noch enger zusammenarbeiten werden wir zumindest das gemeinsame Ziel, Browsergames bekannter und größer zu machen, erreichen. Amen

Worum es mir in diesem thread geht: ich wage mal zu behaupten dass keiner von euch gleich zu Beginn seiner Spielenntwicklung die optimale Datenbank hatte. Sprich man hat sich stufenweise verbessert. Und um neuen Kollegen diese Stufen zu ersparen würde ich gerne hier gesammelt das geballte Know-How zusammentragen, wenn ihr dem zustimmt. Ich mache mal den Anfang:

Stufe 0:
Typische MySQL MyIsam, Datentypen queerbeet. Murks.

Stufe 1:
Die Datentypen angepasst. Ich habe z.B. genau beachtet, welche Werte die einzelnen Attribute annehmen können. Aus vielen INTs mit 4 Byte wurden so UNSIGNED TINYINTS mit 1 Byte -> 75% weniger Platzverbrauch. Auf diese Art und Weise haben wir unsere DB um 23% verkleinert.

Stufe 2:
Indizes. Lange Zeit mit den Primary Keys durcheinandergeworfen bzw. gedacht, es kann nur der PK Index sein. Indiziert man eine Relation nach einem Attribut X, so wird eine Suche nach WHERE X = somevalue vehement schneller. Auf der anderen Seite wird ein INSERT oder UPDATE langsamer (weil auf den Index geschrieben werden muss), aber unseren Messungen nach sparen wir alleine in den Hintergrundcronjobs satte 65% an der Rechenzeit. Ich muss dazu sagen dass wir ein Lese / Schreibeverhältnis von etwa 8 : 1 haben.

Stufe 3:
Alles was der Spieler immer mal wieder braucht (Nickname, Userid, ...) in eine Session. Das reduziert die Datenbankanfragen.

Stufe 4:
Regelmässig die Datenbank durch REPAIR / OPTIMIZE table putzen. Gleichzeitig alle Relationen regelmässig von alten ungebrauchten Datensätzen befreien.

Soweit mal mein Wissensstand. Wir haben es nun geschafft, bei 100 gerade aktiven Spielern (also die gerade voll auf dem Server spielen) den load unter die 1 zu drücken, die Datenbank ist < 100 MB und liegt daher immer voll im RAM.

Ich mache was falsch, ihr macht was noch besser? Würde mich freuen, wenn wir hier die Infos zusammentragen können. Wenn es sowas wie hier schon gibt und ich nur zu blind bei der Suche war -> Info plz

Grüße
Temruk

Hi Temruk, schöne Sache, dieser Thread, und gleich noch ne Frage dazu

Original von Temruk

Stufe 3:
Alles was der Spieler immer mal wieder braucht (Nickname, Userid, ...) in eine Session. Das reduziert die Datenbankanfragen.

Ist das nicht ein erhöhtes Sicherheitsrisiko?
Sessions kann man doch abfangen.
Wie hoch ist dieses Risiko mit den Sessions überhaupt?
Wie funktioniert das "Session klauen"?

Den Inhalt der Session kann man nicht abfragen, nur das Cookie.
Und Session-Hikacking ist in beiden Fällen für den Account tödlich. Der Account ist übernommen, egal ob die Sessions bestimmte Werte gecached haben oder nicht.

Original von Blabbo
Wie hoch ist dieses Risiko mit den Sessions überhaupt?
Wie funktioniert das "Session klauen"?

Das würde mich dann doch auch mal brennend interessieren !
Weiß da jemand bescheid ?

@TheUndeadable
Was meinst du mit Session-Hikacking?

Das Sessoin-Cookie ändern oder index.php?session=DeineSession ?

Beides ist äquivalent und immer ein latentes Risiko bei einer Session-orientierten Verwaltung.

Daher lohnt es sich die Sessions mit weiteren Identifizierungsmerkmalen als nur einer ID auszustatten. Dies können zum Beispiel Browser-Kennung, IP oder Provider sein.

Session-Hijacking funktioniert im Regelfall durch raten oder Analyse der Session-ID-Generierung. Man erzeugt sich ein Cookie oder setzt diese ID in die URL, um dem Server vorzugaukeln, dass man eine andere Person ist.

Beispiel:

Person A loggt sich ein, erhält die ID abc.
Person B loggt sich ein, erhält die ID bcd.

Der Server ordnert die Zugehörigkeiten nur durch die ID ein. Möchte nun Person B die Authentifizierung von A haben, so braucht er nur die Session-ID in abc zu ändern. Damit hast du eine Session übernommen (Hijacking).
Ob die ID in einem Cookie, in der URL oder den ganzen Tag durch Hidden-Fields mitgeschleppt wird, macht keinen Unterschied.

Genau. Machen wir mal ein Beispiel zum Session-Hijacking:

Ich bin DAU1 und speichere einen Link in meinen Favoriten. Ergebnis:

www.someurl.de?phpsessionid=

Jetzt kommt DAU2 an deinem Rechner (z.B. im Rechnerpool an der Uni) vorbeigelaufen, du hast dich nicht abgemeldet und klickt auf den Favo. Zack ist er bei dir drin. Zugegeben, das ist etwas arg an den Haaren herbeigezogen. Einfacher für einen Hijacker wird es, wenn er mitsnifft (den tcp-traffic mitliest).

Aber um back-to-topic zu kommen: Wenn man sich nicht allzu doof anstellt, nicht die reine Session-ID anhängt und die Session alle paar Stunden ablaufen lässt sind Sessions sicher und man kann getrost sich nicht ändernde Variablen in Session speichern. Abraten würde ich von dynamischen Werten, nehmen wir mal die Anzahl Credits / Gold etc. was der Spieler gerade hat. Warum? Nun solche Werte werden von sovielen Prozessen geändert (bei uns 14), dass man da schnell in Inkonsistenzen rennt.

Könnt ihr mal wieder zum thema zurückkommen und die sessions in einem anderen thread besprechen?

Zu sessions :
- Speichert die IP vom spieler in der session und vergleicht sie bei jedem Seitenaufruf.
- Benutzt die Php session funktion, die ist eigendlich sicher genug und die session ID ist so komplex das man durch raten sicher nicht drauf kommt.
- Session sollten bei aller liebe zu den spielern doch irgendwann mal ablaufen. (5 stunden?)

Zur Datenbank :
- Klein ist fein und je kleiner die datenbank desto mehr kann man im ram behalten und umsoschneller bleibt das ganze.
- Wenn die datenbank zu groß für den speicher wird, kann man bestimmte daten in HEAP tabellen auslagern (HEAP = tabellen die nur im speicher liegen) (server aus = tabelle futsch) um sehr schnell auf sie zugreifen zu können. Das sollte aber auf keinenfall ausarten und nicht für spielrelevante daten genutzt werden.
- Grundsätzlich sollte man immer bedenken, dass man nicht übermässig in die datenbank schreibt, da dies unter umständen das lesen aus der gleichen tabelle für gewisse zeit blockiert.

so long
reg4rds
chojin

ip speichern werdet ihr freude mit aol udn arcor usern haben
die nutezn grosse proxys die während einer session schonmal wechslen können

provider wäre da schon besser nur blöd bei dne grossen wie arcor aol udn t-offline
browser auch ok aber auch wieder problem - ie und firefox

ne mischung aus provider und browser wäre schon ok - ie bei t-onlien wer hat den sowas
100%ige sicherheit gibts nicht

Klein ist fein und je kleiner die datenbank desto mehr kann man im ram behalten und umsoschneller bleibt das ganze.

Im Prinzip schon, aber man sollte auch ein paar Indexe anlegen.

Regelmässig die Datenbank durch REPAIR / OPTIMIZE table putzen. Gleichzeitig alle Relationen regelmässig von alten ungebrauchten Datensätzen befreien.
Wie oft macht da sinn? Wir lassen die z.Zt. einmal täglich als Cron drüberlaufen, nachdem die ganzen großen Crons durch sind.


Einige Erweiterungen von mir:

Das * bei SELECTs verbraucht einiges an Zeit. Lieber nur die Felder auswählen, die auch gebraucht werden. Noch ein Tipp: Die Felder in umgekehrter Reihenfolge abfragen, auch dass soll Geschwindigkeit bringen, da MySQL (und auch andere) das SQL rückwärts abarbeiten. Bei Datenbank-Fehlern habt ihr das vielleicht schon gemerkt.

Bei "Nicht-Kritischen"-Funktionen wie z.B. Loggin empfielt es sich, anstelle von Insert den INSERT DELAYED-Befehl zu verwenden. Ich verweise hier auf die MySQL-Doku: http://dev.mysql.com/doc/mysql/de/insert-delayed.html

ohne jetzt mal alles gelesen u haben: kann man das hijacken nicht unterbínden, in dem man die IP lockt die eingeliggt ist? Erst wenn die Sesion beendet ist kann sich eine andere IP einloggen

Und ruckzuck bist du auch noch alle Nutzer des AOL-Browsers los (kann man als Vorteil oder Nachteil sehen ;-) ). AOL und manch anderer Anbieter nutzen Multi-Proxies mit mehreren Ausgangs-IPs

Original von Kampfhoernchen
Wie oft macht da sinn? Wir lassen die z.Zt. einmal täglich als Cron drüberlaufen, nachdem die ganzen großen Crons durch sind.

Das ist eine gute Frage. Da bei uns recht viel Bewegung in der DB ist und so recht schnell Überhänge entstehen machen wir das momentan alle zwei Stunden. Ob das aber viel sinnvoller als einmal am Tag ist oder nicht kann ich leider nicht sagen. Viel Zeit fressen tut diese Aktion auf jeden Fall nicht.

Original von Kampfhoernchen

Die Felder in umgekehrter Reihenfolge abfragen, auch dass soll Geschwindigkeit bringen, da MySQL (und auch andere) das SQL rückwärts abarbeiten

Huch? Das ist mir neu. Kannst du da mal noch ein paar Worte dazu verlieren?

{WORTE VERLIER}
MySQL parsed zuerst das FROM. Klar, sonst weiß er nicht, welche Tabelle er überhaupt braucht. Dann entsprechend die JOINs, wobei behauptet wird, das LEFT und RIGHTs die schnellsten sein sollen (kann ich weder bestätigen noch wiederlegen).
Dann wird die WHERE-Clause abgearbeitet. Dort wird ja in 90% aller Fälle nach einer ID oder einem FK gesucht, welche bei ordentlichem DB-Design vorne stehen sollten. Deshalb werden diese (oder zumindest deren Idices schon mal in den Cache geladen.
MySQL wählt dann die treffenden Datensätze aus. Dann nimmt MySQL die liste der verlangten Argumente. Bei * muss er erst noch die namen der Spalten überprüfen, was natürlich Zeit kostet. Sprich, MySQL muss das * durch die einzelnen Spalten ersetzen, und das kostet schon wieder etwas Zeit. Beim * werden die Spalten auch rückwärtsaufgelistet.
Die einzelnen Spalten werden dann von hinten her (fragt mich nicht warum) abgearbeitet. Wenn man von hiniten anfängt, wird also der PK (der ja ganz vorne stehen sollte), zuerst gelesen. Dann der 2. Wert usw. Und wenn man von vorne her anfängt, eine Datei zu laden, geht das natürlich schneller, da man den Dateizeiger nicht zu verrücken braucht. Der Verzicht auf * und dafür eine umgekehrte Spaltenliste bringen bis zu 15%, die richtige Anordnung der Spaltenliste bringt dabei ca. 3 - 5 %. Zudem sollten die Joins untereinander und mit der Haupttabelle nicht gemischt werden (schön Tabelle für Tabelle), was zuerst kommen soll, kan n ich nicht sagen, das war bei mir ungefähr gleichschnell.

Original von Kampfhoernchen
Zeit. Beim * werden die Spalten auch rückwärtsaufgelistet. Die einzelnen Spalten werden dann von hinten her (fragt mich nicht warum) abgearbeitet. ... Und wenn man von vorne her anfängt, eine Datei zu laden, geht das natürlich schneller, da man den Dateizeiger nicht zu verrücken braucht.

Von hinten dürfte klar sein. Zuerst wird ein Lookup über sämtliche spalten durchgeführt. Diese werden in einem Stack gespeichert; welcher dann abgearbeitet wird. Alles andere würde extrem Rechenzeit benötigen.

Das Lesen ist AUCH kein Problem, da ich mal eine DB sehen möchte welche bei Operationen Innerhalb von Datensätzen irgendwas auf der HD macht Da ist eh schon mindestens der komplette Datensatz über ein Block- Read im Speicher... und im Speicher von Vorn oder von Hinten lesen ist Furz...

Die Leistungsunterschiede zwischen * und einzelnen Spalten sind nicht wirklich gross. Der einzig grosse Unterschied ist, dass ein * immer alle Daten bringt... was sich beispielsweise bei "chaotischen" Spalten wie Memos extrem auswirkt... oder auch ganz einfach die Datenmenge speichert.
->Ein * über alles dürfte schneller sein als wenn man einfach sämtliche Spalten einer Tabelle auflistet!

* hat hauptsächlich mit gutem Stil zu tun und mit Reduzierung der zu übertragenden / nachzuschlagenden Daten.

Gruss

Ich habe alle wichtigen Daten in der Session (die ich über IP mit automatik, httpreffer usw schütze) um die Abfragen schonmal zu sparen (Namen, Koordinaten, Forschungsstufen, Resourcen pro Planet usw). Ettliche "aktuelle" Daten werden dann nur aus der Session berechnet - so auch bei der aktuellen Rohstoffanzeige. Erst bei einer Operation (bauen,forschen etc.) wird in die DB geschrieben.

Weiterhin habe ich alle oft verwendeten selects als Indiz und auch die * sind bei mir Tabu.

Was aber das meiste gebracht hat war bisher die Hauptteile der Tabellen als Heap umzuarbeiten und diese über ein Script in bestimmten Intervallen als MyIsam zurück zu schreiben (stelle gerne die scripte für die Heapverwaltung zur verfügung).

Sollte der Server mal ausfallen so wird über das OS erst die Tabellen geprüft und dann in die backups aus den MyIsam in die Heap Tabellen geladen.

Der Nachteil ist das halt Daten bis zu der Zeit des Intervalls verloren sein können.

@Kampfhoernchen:
Die Auswahl der Spalten müsste doch vor den Joins stattfinden, da auch Spalten Aliases haben können und diese in den Joinbedingungen vorkommen können. Oder ersetzt MySQL die Aliases sofort durch ihre eigentlichen Bezeichner?

Ich glaube, die Aliases werden zuerst geprüft.

Sonst würde .... FROM tabelle t WHERE t.dada = 1
ja auch nicht funzen.
Das hier funzt ja dann nicht:
FROM tabelle t WHERE tabelle.dada = 1
Er kennt tabelle nicht mehr, weil das für ihn jetzt t heißt.