Performance-Optimierungen in einem Forum

[mgutt]

SQL-Abfrageergebnis
Erstellungszeit: 29. September 2009 um 14:43
SQL-Befehl: SHOW VARIABLES LIKE 'have_query_cache';
Zeilen: 1
Variable_name Value
have_query_cache YES

SQL-Abfrageergebnis
Erstellungszeit: 29. September 2009 um 14:44
SQL-Befehl: SHOW STATUS LIKE 'Qcache%';
Zeilen: 8

Variable_name Value
Qcache_free_blocks 3510
Qcache_free_memory 125707792
Qcache_hits 166778823
Qcache_inserts 371275622
Qcache_lowmem_prunes 0
Qcache_not_cached 2145892
Qcache_queries_in_cache 6854
Qcache_total_blocks 17268

[bloddy newbie]

AND type NOT IN(2, 3)

statt dessen könntest du auch

Code: Alles auswählen

AND type != 2 AND type != 3

schreiben. Ich mag persönlich IN und NOT IN nicht besonders, weil ich bzgl. der Performance einige male negative Erfahrungen gesammelt habe. Insbesondere folgendes Beispiel lief sagenhaft schlecht:

Code: Alles auswählen

 AND 3 NOT IN (stadt_id, stadt_id_2)

Besser lief hingegen:

Code: Alles auswählen

 AND stadt_id != 3 AND stadt_id_2 != 3

Verstanden habe ich das nicht, da der Query Optimierer leztenendes nichts anderes machen sollte...

[mgutt]

Vielleicht wegen dem oben genannten Bug?

Also bei mir ändert das leider nichts, wobei ich glaube auch mal so eine Beobachtung mit <> und != gemacht zu haben.

Kann aber auch Zufall gewesen sein.

Die oben genannte Abfrage braucht übrigens manchmal nur 0.0001s und dann wieder bis zu einer Sekunde. Aber sobald ein wenig mehr Last auf dem Server ist, ist sie eigentlich immer langsam.

Was ich bei mir in jedem Fall noch machen werde, ist eine Umstellung der Sortierreihenfolge der Themen. Ein Forum ist immer so aufgebaut, dass die neuesten Themen in den Foren nach vorne rutschen. Auf die Art ist es nicht möglich die Unterseiten zu cachen (im Gegensatz zu den Themen, wo der älteste Beitrag vorne steht), da mit einem neuen Thema alle Unterseiten betroffen sind. Daher werde ich das bald so machen, dass ich nur noch die ersten drei Seiten eines Forums nach Aktualität ausgebe, während ich die hinteren Seiten als "Archiv" nach Themen-ID aufliste. Die Seitenzahlen mache ich dann wieder umgekehrt, so dass es wieder passt. Auf die Art kann man alle Unterseiten cachen und muss den Cache erst zurücksetzen, wenn man ein Thema verschiebt.

Mehr als die ersten 3 Seiten guckt sich sowieso keiner an (wenn überhaupt).

Irgendwann sollen bei mir die Seitenzahlen so aussehen:

Aktuelle Themen Seite 1, 2, 3 - Kategorie Bilder, Projekte, Videos, News, Anleitungen, FAQ Archiv Seite 21, 20, 19 ... 3, 2, 1

[everflux]

Die Query selber kennst du aber, die das ganze verursacht?
Dann mach doch mal ein
EXPLAIN <QUERY>

Wenn es manchmal so lange dauert ist meine hypothese, dass er eine temporäre Tabelle anlegt und die nicht im Speicher anlegen kann, sondern auf Disk machen muss. Dann bist Du plötzlich IO bound und das könnte das ganze gut erklären.

[Lord Lommel]

Wenn die eigentlich schnell ist, aber dann plötzlich sau langsam, würd ich mal drauf tippen, daß irgendwas anderes eine betroffene Tabelle sperrt. Vielleicht hängt sich ja eine Transaktion auf.

[mgutt]

Die Query selber kennst du aber, die das ganze verursacht?
Dann mach doch mal ein
EXPLAIN <QUERY>

Wenn es manchmal so lange dauert ist meine hypothese, dass er eine temporäre Tabelle anlegt und die nicht im Speicher anlegen kann, sondern auf Disk machen muss. Dann bist Du plötzlich IO bound und das könnte das ganze gut erklären.

Die hatte ich hier schon erwähnt:
https://www.abakus-internet-marketing.d ... tml#640972

Die Explains sehe ich ständig. Ich werfe mir alle Queries im Footer als Explain aus. Das sieht dann so aus:

db: 0.0471s
Select Type: SIMPLE
Type: Range
Possible Keys: topic_type,host_forum_type
Used Key: host_forum_type
Key length: 6
Rows: 6445
Commen: Using where

Die zweite ohne COUNT, die dann die topics wirklich ausliest ist maginal langsamer db: 0.0673s und nutzt zusätzlich filesort.

Die Abfrage ist wie gesagt entweder sau schnell (0.0001s), durchschnittlich (0.0500s) oder langsam (bis zu 1s).

Wenn die eigentlich schnell ist, aber dann plötzlich sau langsam, würd ich mal drauf tippen, daß irgendwas anderes eine betroffene Tabelle sperrt. Vielleicht hängt sich ja eine Transaktion auf.

Nein, da diese direkt davor ausgeführt wird und immer schnell ist:

SELECT *
FROM topics
WHERE host = 1
AND forum = 820
AND type IN(2, 3)
ORDER BY type DESC, last_time DESC

Allerdings resultiert die auch nur 3 rows. Ich denke es hängt simpel mit der Anzahl der Reihen zusammen und vielleicht auch mit dem zuvor genannten Bug.

[Lord Lommel]

Ich sag doch, da hängt was.

[everflux]

Ich würde erstmal mit nem aktuellen Mysql schauen, ob sich das dann noch immer so verhält. Die andere Möglichkeit ist natürlich, dass auf dem Server noch irgendwas anderes läuft. (Sagtest Du nicht was von shared Server oder so?)
Bei 6000 Rows sollte er das eh alles in Memory können, da dürfte eigentlich nie was klemmen.

[mgutt]

Ich sag doch, da hängt was.

Ja und was?

Also ein separat laufende Transaktion z.B. ein UPDATE ist auszuschließen, da ja die anderen dann auch langsam sein müssten (es laufen immerhin 7-10 Abfragen auf die topics-Tabelle und es sind welche davor als auch danach und ja ich habe auch schon ausprobiert und die anderen Abfragen deaktiviert bzw. die einzelne "schlechte" in phpmyadmin separat ausgeführt ).

Übrigens habe ich die Abfrage geändert in (da ich vergessen habe, dass es eh nur 4 Typen gibt):

SELECT COUNT(topic) AS count_topic
FROM topics
WHERE host = 1
AND forum = 518
AND type IN(0,1)

Das macht keinen Unterschied, also sollte der Bug auszuschließen sein.

Es geht tatsächlich nur um die Anzahl der rows. Die Ausführungszeit ist übrigens proportional zu diesen. Suche ich mir eine Seite mit weniger resultierenden rows wird die Ausführung entsprechend schneller.

Ich würde erstmal mit nem aktuellen Mysql schauen, ob sich das dann noch immer so verhält. Die andere Möglichkeit ist natürlich, dass auf dem Server noch irgendwas anderes läuft. (Sagtest Du nicht was von shared Server oder so?)

Ne bestimmt nicht

Das ist ein Multihosting-Projekt, aber basierend auf einer DB und einem Filesystem.

Bei 6000 Rows sollte er das eh alles in Memory können, da dürfte eigentlich nie was klemmen.

Wundert mich ja auch, da diese Abfrage immer schnell ist und über 100k rows resultiert:

SELECT *
FROM topics
WHERE moved = 0
AND host = 1
ORDER BY last_time DESC
LIMIT 10

Die nutzt allerdings den Index moved_host_last_time.

[xAaron]

Die Abfrage ist wie gesagt entweder sau schnell (0.0001s), durchschnittlich (0.0500s) oder langsam (bis zu 1s).

Ok, das heißt aber, dass der Index genutzt wird. Denn ohne Index kannst du kein 0.0001 s Ergebnis haben. Also liegt da nicht mehr das Problem.

Meine These wäre: der Abfragen-Optimierer stellt sich vor jeder Ausführung die Frage, wie schaff ich die Verarbeitung am schnellsten und diese Frage wird unterschiedlich beantwortet, tippe ich mal, aber bei mysql weiß ich es nicht, je nachdem, ob ein Index im Arbeitsspeicher liegt oder nicht und wie viel Platz noch frei ist. Also wird der Index benutzt, wenn er im Arbeitsspeicher liegt mit dem wunderbaren Ergebnis und sonst wird er entweder in den Arbeitsspeicher geladen mit dem mittleren Ergebnis oder aber der Arbeitsspeicher reicht nicht, bei hoher Last, und entsprechend wird die Anfrage anders ausgeführt, mit entsprechend schlechtem Ergebnis.
So oder so ähnlich wird die Begründung sein.

Führ die Abfrage mehrmals zwei Mal hintereinander aus, wenn die zweite dann in der Regel ein 0.0001s Ergebnis ist, dann liegt die Ursache wirklich in zuwenig Arbeitsspeicher.

[mgutt]

Ich denke das könnte es sein, außer es gibt eine Art Cache. Ich habe die Abfrage einfach drei mal hintereinander ausführen lassen. Dann passiert das:

häufig:
1. schnell
2. schnell
3. schnell

selten:
1. langsam
2. schnell
3. schnell

sehr selten:
1. langsam
2. langsam
3. schnell

Wenn ich allerdings die Forum-IDs so wechsle, dass drei verschiedene Queries resultieren, passiert das:

häufig:
1. schnell
2. schnell
3. schnell

selten:
1. langsam
2. schnell
3. schnell

sehr selten:
1. langsam
2. langsam
3. schnell

sehr sehr selten:
1. langsam
2. langsam
3. langsam

Aber auch da kommt es nicht vor, dass die erste schnell ist und die darauf folgenden langsam, was Deine Theorie ja stützen würde.

[xAaron]

ok, das ist eindeutig. Damit haben wir die Ursache. Denn das ist genau das Phänomen: der Index wird mit der ersten Abfrage, wenn er passt, in den Arbeitsspeicher geladen und alles weitere ist dann rasend schnell.

Deshalb ist es auch viel sinnvoller, anstatt einen neuen größeren Server zu bestellen, der dann mit noch mehr Prozessoren oder Gigaherz die daten verarbeitet, die lahm von der Platte gelesen werden, sich intensiv mit Indizes zu beschäftigen, die Seite dahingehend zu optimieren und einfach mehr Arbeitsspeicher zu nehmen. Das Ergebnis ist viel besser und kostengünstiger.
Denn wenn du die Abfragen größtenteils durch Indizes abgedeckt bekommst, dann ist es besser mit einem 486 und massig Arbeitsspeicher zu arbeiten, als mit dem neusten Mehrprozessor-System und wenig Arbeitsspeicher. Das siehst du ja an den Ergebnissen: ohne Index und von der Platte brauchst du 10000 Mal so lang wie mit Index und aus dem Arbeitsspeicher. Da bringt dann auch der Prozessor nichts mehr, da der Flaschenhals die Festplatte ist.

[mgutt]

Bleibt nur noch die Frage, warum die Abfrage, die direkt vor der problematisch Abfrage abgesetzt wird, immer schnell ist und den gleichen Index benutzt nie langsam ist:

SELECT *
FROM topics
WHERE host = 1
AND forum = 819
AND type IN(2,3)
ORDER BY type DESC, last_time DESC

Ist die eigentlich auch langsam, nur ich sehe es nicht, weil sie viel weniger Arbeit verursacht als die danach?

Oder werden Indexe je nach Queryart unterschiedlich hinterlegt?

Wo kann ich denn die Indexgrößen sehen bzw. wie kann ich es steuern, dass immer alle Indexe im Arbeitsspeicher zur Verfügung stehen?

[bfs]

Das kommt drauf an ob du myisam oder z.b. innodb verwendest. myisam skaliert überhaupt nicht gut mit speicher - und kann auch nur gescheit für indizes verwendet werden. Mehr als 2GB keycache bei myisam ist aber nicht sinnvoll.

Innodb Tabellen skalieren dagegen beliebig mit dem Arbeitspeicher.

[mgutt]

Aktuell verwende ich myisam, da ich fulltext für die Texte brauche. Ich habe aber schon mal darüber nachgedacht alles bis auf die Texte auf innodb umzustellen, da innodb ja auch schneller sein soll.

Inbesondere vermeide ich damit die Probleme von früher, wo UPDATE-Prozesse in der Warteschlange hängen geblieben sind, da zu viele SELECTs ausgeführt wurden.