´Guten Tag,
ich arbeite als Software-Entwickler für eine Firma die Spiele herstellt. Nebenbei kümmere ich mich auch nach bestem Wissen und Gewissen um unsere Datenbank (bin leider kein SQL-Profi, aber bisher hats gereicht). In der DB werden u.A. die Highscores unserer Spieler (mehrere Millionen) gespeichert. Und hier wird seit Wochen ein Problem immer deutlicher: Je mehr Spieler wir haben, desto langsamer wird der Zugriff auf die DB d.H. unsere Spieler müssen, wenn sie ihr Highscore-Ranking abfragen wollen, immer länger warten bis sie ihr Ergebnis bekommen.
Mir ist aufgefallen, dass die CPU-Auslastung des Servers durch mySQL teilweise 99% beträgt.
Ich würde hier gerne erfahren, ob das an dem SQL-Query liegt den ich zur Berechnung verwende und ob man diesen noch optimieren kann, oder ob sich unsere Firma einen neuen Server zulegen sollte.
Hier der Query (sortiert wird nach den erreichten Scores - wenn mehrere User den selben Score haben, wird nach Upload-Datum sortiert):
SELECT COUNT(id) rank
FROM 'highscores'
WHERE (score > (
..SELECT score
..FROM 'highscores'
..WHERE name = '$username')
|| (score = (
....SELECT score
....FROM 'highscores'
....WHERE name = '$username')
....&& upload_date < (
....SELECT upload_date
....FROM 'highscores'
....WHERE name = '$username')
..)
);
Hinweis:
Indizes für score & upload_date sind gesetzt!
Tabellenformat ist MyISAM.
Zugriff auf die DB erfolgt über ein PHP-Skript.
Ich hoffe ein versierter SQL-User kann mir einen Rat geben, was ich tun kann, damit unser Server nich ganz in die Knie geht.
Eine Idee wäre, eine weitere Tabelle anzulegen, in der die User bereits ihrem Ranking nach gespeichert sind.
Vorteil: schnelle Abfrage des Rankings eines Users.
Nachteil: wenn ein User sich in seinem Ranking verändert, muss im Worst-Case der Inhalt der komplette Tabelle umsortiert werden.
Vielleicht kann mir auch jemand seine eigene Erfahrung mitteilen, der dieses Idee bereits umgesetzt hat. Ich habe nämlich die Befürchtung, auch wenn deutlich mehr User eine Rankingabfrage machen als ihre Highscores hochzuladen, dass die Zeit, die ein updaten der Tabelle benötigt, mit steigender Userzahl ebenfalls deutlich steigen wird und wir somit nichts von der Umstellung haben.
Bin für jeden Rat dankbar!
ich arbeite als Software-Entwickler für eine Firma die Spiele herstellt. Nebenbei kümmere ich mich auch nach bestem Wissen und Gewissen um unsere Datenbank (bin leider kein SQL-Profi, aber bisher hats gereicht). In der DB werden u.A. die Highscores unserer Spieler (mehrere Millionen) gespeichert. Und hier wird seit Wochen ein Problem immer deutlicher: Je mehr Spieler wir haben, desto langsamer wird der Zugriff auf die DB d.H. unsere Spieler müssen, wenn sie ihr Highscore-Ranking abfragen wollen, immer länger warten bis sie ihr Ergebnis bekommen.
Mir ist aufgefallen, dass die CPU-Auslastung des Servers durch mySQL teilweise 99% beträgt.
Ich würde hier gerne erfahren, ob das an dem SQL-Query liegt den ich zur Berechnung verwende und ob man diesen noch optimieren kann, oder ob sich unsere Firma einen neuen Server zulegen sollte.
Hier der Query (sortiert wird nach den erreichten Scores - wenn mehrere User den selben Score haben, wird nach Upload-Datum sortiert):
SELECT COUNT(id) rank
FROM 'highscores'
WHERE (score > (
..SELECT score
..FROM 'highscores'
..WHERE name = '$username')
|| (score = (
....SELECT score
....FROM 'highscores'
....WHERE name = '$username')
....&& upload_date < (
....SELECT upload_date
....FROM 'highscores'
....WHERE name = '$username')
..)
);
Hinweis:
Indizes für score & upload_date sind gesetzt!
Tabellenformat ist MyISAM.
Zugriff auf die DB erfolgt über ein PHP-Skript.
Ich hoffe ein versierter SQL-User kann mir einen Rat geben, was ich tun kann, damit unser Server nich ganz in die Knie geht.
Eine Idee wäre, eine weitere Tabelle anzulegen, in der die User bereits ihrem Ranking nach gespeichert sind.
Vorteil: schnelle Abfrage des Rankings eines Users.
Nachteil: wenn ein User sich in seinem Ranking verändert, muss im Worst-Case der Inhalt der komplette Tabelle umsortiert werden.
Vielleicht kann mir auch jemand seine eigene Erfahrung mitteilen, der dieses Idee bereits umgesetzt hat. Ich habe nämlich die Befürchtung, auch wenn deutlich mehr User eine Rankingabfrage machen als ihre Highscores hochzuladen, dass die Zeit, die ein updaten der Tabelle benötigt, mit steigender Userzahl ebenfalls deutlich steigen wird und wir somit nichts von der Umstellung haben.
Bin für jeden Rat dankbar!
Kommentar