[MySQL 4.1] "Wer ist online"-Liste verwalten

[tStein]

Hallo,

folgende Situation:
Ich habe eine Tabelle namens "users", in der die User einer Community gespeichert sind. Die Zahl der Zeilen (User) liegt im Moment bei 8000 mit stark steigender Tendenz.
Um eine "Wer ist online"-Liste zu erstellen, gibt es in der Tabelle die Spalte "lastvisit", die alle 20 Sekunden durch ein Script aktualisiert wird, das in ein IFrame geladen wird. Dieses schaut nebenbei noch nach neuen PNs für den User.

Das Problem liegt darin, dass die SQL-Abfrage zum Updaten dieser Zelle

update users set lastvisit = 1234567890 where userid = 1234 limit 1

sowie zum Auslesen der Online User

select Ein, paar, Spalten from users where lastvisit >= 1234567860 order by username asc

oft recht lang dauern. Indizes sind auf userid (unique), username und lastvisit gesetzt.

Wie könnte man nun den ganzen Vorgang beschleunigen?

Meine erste Idee ist eine zweite Tabelle, die nur die Spalten userid und lastvisit enthält, evtl. als HEAP. So muss nicht immer mit der kompletten Usertabelle gearbeitet werden, die eine recht große Datenmenge enthält. Das Problem ist, dass ich beim Anzeigen der Onlineliste trotzdem Daten wie Username, Geschlecht, Geburtstag etc. brauche und diese dann aus der Usertabelle holen muss, sei es per PHP oder per Joins.
Um dies zu umgehen könnte man in die "Lastvisit-Tabelle" ja noch die Spalten Username, Geschlecht usw. mit aufnehmen. Das Problem dabei ist, dass man ja dann redundante Daten hat und es würde wieder etwas Geschwindigkeit kosten.

Welche Methode ist eurer Meinung nach die performanteste? Oder hat jemand vielleicht einen komplett anderen Denkansatz parat?

Vielen Dank im Voraus,
Thomas

[onemorenerd]

Bei 8000 Datensätzen sollte das doch ruckzuck gehen oder ist der Server etwas schwach? Wie lange ist denn "recht lange" und liegt das nur am Updaten und Selecten der Online-User oder auch ein Stück weit an den PNs?

[tStein]

Bei nur 8000 Zeilen wundert mich das eben auch.

Ich lasse ein Logfile mit langsamen Datenbank-Abfragen erstellen und in diesem tauchen immer wieder die beiden genannten Abfragen teils mit über 0.3 Sekunden Laufzeit auf, wobei das UPDATE-Statement am meisten auftritt. Es geht also wirklich nur um diese beiden Abfragen, die Abfrage für die PNs macht keine Probleme.
Leider kann ich nicht sagen, welcher Anteil der insgesamt von dieser Sorte ausgeführten Abfragen das sind. Bei sagen wir 80 Online Usern müssten es aber im Schnitt schon 4 Stück pro Sekunde sein. In 2 Tagen wurde diese Abfrage nun ca. 1000 mal ins Logfile geschrieben, im Schnitt also etwa alle drei Minuten.

[vierteln]

>>Um eine "Wer ist online"-Liste zu erstellen, gibt es in der Tabelle die Spalte "lastvisit", die alle 20 Sekunden durch ein Script aktualisiert wird, das in ein IFrame geladen wird. Dieses schaut nebenbei noch nach neuen P

Wenn 20 users online sind, und jeder alle 20 Sekunden diese zwei Abfragen ausführt, kann es schon zu Kollisionen kommen.

[tStein]

Da hab ich mich wohl etwas unklar ausgedrückt. Das IFrame zum Updaten wird alle 20 Sekunden aktualisiert, das ist richtig.
Die Liste wird aber nur erstellt, wenn der User eine neue Seite aufruft, also nicht zwingend alle 20 Sekunden.

Trotzdem ist es sehr wahrscheinlich dass es zu Kollisionen kommt und solch sehr lange Abfragen dadurch entstehen dass auf die Freigabe der Tabelle durch (einen) andere(n) Prozess(e) gewartet werden muss.

Sind meine vorgeschlagenen Änderungen nun eures Erachtens geeignet, um das Problem einzudämmen bzw. weitestgehend zu beseitigen und welches davon dürfte aller Wahrscheinlichkeit nach schneller sein?

[onemorenerd]

Ein Schuß ins Blaue: Das UPDATE dauert vermutlich so lange, weil viele SELECTs die Tabelle blockieren.

Könntest die UPDATE-Query in ein eigenes Script ausgelagern. Aus

// ...
mysql_query('UPDATE ...');
$res = mysql_query('SELECT ...');
// ... 

würde

// ...
exec('bash -c "exec nohup setsid /path/to/php updater.php arg1=val1 arg2=val2 > /dev/null 2>&1 &"');
$res = mysql_query('SELECT ...');
// ... 

und in updater.php baust du die Kommandozeilenparameter in die UPDATE-Query ein und übergibst die Query der DB.

Der Effekt: Das eigentliche Script wartet nicht mehr, bis das UPDATE fertig ist.
Nebenwirkung: Das SELECT liefert u.U. noch die "alten" Daten, weil das UPDATE eben noch wartet.

Alle Angaben ohne Gewähr, ist nur so eine Idee ...

[tStein]

Danke für die Antwort!
Das ist zwar eine sehr gute Idee, trifft aber nicht ganz das Problem.
Wie einen Beitrag vorher bereits erwähnt, habe ich mich wohl an Anfang missverständlich ausgedrückt. Was alle 20 Sekunden durch das IFrame geladen wird ist ein Script mit dem UPDATE-Statement. Die SELECT-Anfrage wird nicht im gleichen Script durchgeführt, sondern nur wenn der User eine Seite aufruft.
Ich habe also schon die von dir vorgeschlagene Trennung in zwei Scripts, allerdings werden beide vom User aufgerufen.

Das Problem besteht auch nicht darin, dass das Script im IFrame lang zum Laden braucht, das ist ja eigentlich auch egal, weil es eh keinem auffällt. Ich mache mir vielmehr Sorgen um die Gesamtperformance, vor allem im Hinblick auf eine steigene Userzahl. Es wäre ja möglich dass sich das ganze "hochschaukelt" und ab einer gewissen Zahl an gleichzeitigen online Usern die Tabelle fast immer blockiert ist.

[onemorenerd]

Wenn ich dich jetzt richtig verstehe, ist dein Problem also die allgemeine Performance von MySQL (auf deiner Hardware)? Du hast schon alles mögliche getan, um konkurrierende UPDATEs und SELECTs zu verhindern?

Dann fällt mir nur noch http://google.com/search?q=mysql+performance+tuning ein.

[tStein]

Original geschrieben von onemorenerd
Wenn ich dich jetzt richtig verstehe, ist dein Problem also die allgemeine Performance von MySQL (auf deiner Hardware)?

Genau! Ein Problem ist es zwar noch nicht, aber ich will vermeiden, dass mit steigender Userzahl eines entsteht. Deswegen bin ich im Moment dabei, die Abfragen und Indizes zu optimieren und bin auf die hier genannten Queries aufmerksam geworden.

Kann ich also davon ausgehen, dass das Zerteilen in eine separate Tabelle für die Lastvisits, wie ich es im OP vorgeschlagen habe, keinen signifikanten Geschwindigkeitsunterschied mit sich bringen wird?
Sonst wäre ja irgendwer im Laufe der Diskussion darauf eingegangen, nehme ich an.

Du hast schon alles mögliche getan, um konkurrierende UPDATEs und SELECTs zu verhindern?

Das ist ja Webapplikationen ein bisschen schwierig, da immer (fast) alle das gleiche wollen :-)
Welche Möglichkeiten gäbe es denn da genau?

Dann fällt mir nur noch http://google.com/search?q=mysql+performance+tuning ein.

Ich habe mich schon durch eine Menge solcher Performance Tuning Guides gearbeitet und werde demnächst verschiedene Caches ein wenig anpassen. Mal sehen wie es sich entwickelt.

Da fällt mir noch eine weitere Frage ein, die nicht ganz zum Thema passt, aber ich möchte deswegen nicht extra ein neues Topic eröffnen:
Der vorherige Programmierer hat ein Paar SQL-Statements in der Form

select a.*, b.EineSpalte from a, b where a.pid = b.pid ...

hinterlassen. Ich habe einmal gelesen, dass ein expliziter JOIN in dieser Form

select a.*, b.EineSpalte from a JOIN b ON a.pid = b.pid ...

hier um einiges schneller sein soll, da nicht erst das kartesische Produkt beider Tabellen gebildet wird.
Ich kann jedoch keinen Geschwindigkeitsunterschied zwischen beiden Formen feststellen und EXPLAIN ergibt bei beiden Abfragen auch genau das gleiche.
Bezieht sich diese Aussage auf eine frühere Version, in der Statements der ersten Form nicht optimal verarbeitet wurden oder ist das auch noch in aktuellen MySQL-Versionen der Fall?

[onemorenerd]

Original geschrieben von tStein
Genau! Ein Problem ist es zwar noch nicht, aber ich will vermeiden, dass mit steigender Userzahl eines entsteht.

Frag deinen Chef nach einer Gehaltserhöhung, die meisten denken an sowas erst wenns brennt.

Kann ich also davon ausgehen, dass das Zerteilen in eine separate Tabelle für die Lastvisits, wie ich es im OP vorgeschlagen habe, keinen signifikanten Geschwindigkeitsunterschied mit sich bringen wird?

Ohne dabei ein exaktes Bild im Kopf zu haben, wie diese beiden Tabellen aussehen, behaupte ich mal, dass es nur in Sonderfällen etwas bringt. Bist du so einer? Was schwebt dir denn vor?

Ich habe mich schon durch eine Menge solcher Performance Tuning Guides gearbeitet und werde demnächst verschiedene Caches ein wenig anpassen.

Da hab ich kürzlich was beim Koehntopp gelesen ...

Der vorherige Programmierer hat ein Paar SQL-Statements in der Form

select a.*, b.EineSpalte from a, b where a.pid = b.pid ...

hinterlassen. Ich habe einmal gelesen, dass ein expliziter JOIN in dieser Form

select a.*, b.EineSpalte from a JOIN b ON a.pid = b.pid ...

hier um einiges schneller sein soll, da nicht erst das kartesische Produkt beider Tabellen gebildet wird.
Ich kann jedoch keinen Geschwindigkeitsunterschied zwischen beiden Formen feststellen und EXPLAIN ergibt bei beiden Abfragen auch genau das gleiche.
Bezieht sich diese Aussage auf eine frühere Version, in der Statements der ersten Form nicht optimal verarbeitet wurden oder ist das auch noch in aktuellen MySQL-Versionen der Fall?

Wenn EXPLAIN das gleiche ausgibt, kann man annehmen, dass der Optimizer aus beiden Queries den selben execution plan (heißt das bei MySQL so?) macht.

Randbemerkung: Sauberer Code enthält nur in begründeten Ausnahmen sowas wie a.*, da der Programmierer jederzeit sicher sein sollte, was für Spalten er abfragt.

[tStein]

Ohne dabei ein exaktes Bild im Kopf zu haben, wie diese beiden Tabellen aussehen, behaupte ich mal, dass es nur in Sonderfällen etwas bringt. Bist du so einer? Was schwebt dir denn vor?

Nun, ich denke nicht dass ich ein "Sonderfall" bin. Ist einfach eine 0815 Usertabelle wie man sich das im Allgemeinen vorstellt
Das einzige "besondere" könnten die vielen Spalten mit variabler Länge, eine davon als TEXT, sein...

Da hab ich kürzlich was beim Koehntopp gelesen ...

Interessante Seite, danke für den Hinweis!

Ich habe jetzt bei dem UPDATE-Statement ein LOW_PRIORITY eingefügt, so ist zumindest sichergestellt, dass die Tabelle nicht wegen so etwas unwichtigem wie der Online-User-Liste gelockt ist und viel wichtigere "Hauptscripte" nicht drauf zugreifen können.
Wie es sich entwickelt bleibt abzuwarten...

Randbemerkung: Sauberer Code enthält nur in begründeten Ausnahmen sowas wie a.*, da der Programmierer jederzeit sicher sein sollte, was für Spalten er abfragt.

Das ist mein nächster Optimierungsschritt