Performance-Optimierungen in einem Forum

Beitrag von **xAaron** » 28.09.2009, 12:45

mgutt hat geschrieben:
bloddy newbie hat geschrieben:Wie schaut die Abfrage aus?
Das ist die Abfrage:
Code: Alles auswählen
SELECT *
FROM topics
WHERE moved_id = 0
AND host_id = 1
ORDER BY last_time DESC
LIMIT 10
moved_id schließt Kopien des topics aus. host_id bestimmt die Domain auf der das topic veröffentlicht wurde (Multihosting) und last_time ist eben die Sortierung nach Aktualität. Indexe liegen auf moved_id, host_id und last_time. MySQL nutzt an dieser Stelle host_id. Getestet habe ich auch mehrspaltige, aber das Problem ist hier einfach die Anzahl der Zeilen aus dem WHERE-Teil, der dann filesort richtig zum arbeiten bringt (ab 100.000 Zeilen beginnt das Problem).

Was spricht gegen einen Index auf den Spalten moved_id, host_id? Das würde schnellstmöglich alle gewünschten Zeilen liefern, die mysql, dann noch nett sortiert zurückgibt. Oder meinst du es so, dass du so viele Zeilen durch den Index zwar schnell zurückbekommst, die Sortierung aber so lange dauert?
Warum funktioniert dann ein Index über die drei Spalten nicht? Hier müssen natürlich die where-Spalten am Anfang und die order by als letzte stehen. Aber eigentlich müßte MySQL dann sich die Daten perfekt sortiert aus dem Index holen können.

von **Anzeige von ABAKUS** »

Hochwertiger Linkaufbau bei ABAKUS:

Google-konformer Linkaufbau
nachhaltiges Ranking
Linkbuilding Angebote zu fairen Preisen
internationale Backlinks

Wir bieten Beratung und Umsetzung.
Jetzt anfragen: 0511 / 300325-0

Beitrag von **mgutt** » 28.09.2009, 14:06

xAaron hat geschrieben:Was spricht gegen einen Index auf den Spalten moved_id, host_id? Das würde schnellstmöglich alle gewünschten Zeilen liefern, die mysql, dann noch nett sortiert zurückgibt. Oder meinst du es so, dass du so viele Zeilen durch den Index zwar schnell zurückbekommst, die Sortierung aber so lange dauert?

Genau. Die Menge an Ergebnissen ist das Problem. Nutze ich nur WHERE oder nur ORDER BY, dann ist die Abfrage schnell.

Warum funktioniert dann ein Index über die drei Spalten nicht? Hier müssen natürlich die where-Spalten am Anfang und die order by als letzte stehen. Aber eigentlich müßte MySQL dann sich die Daten perfekt sortiert aus dem Index holen können.

Die Frage ist, ob das nicht eher nachteilige Auswirkungen hat. Einmal dürfte das ein ziemlich großer Index werden. Und dann werden die last_time Spalte ja sehr häufig aktualisiert, so dass sich das UPDATE danach entsprechend verlangsamen dürfte.

Weiterhin funktioniert ein Index über drei Spalten nicht. Wenn ich den Index auf moved_id, host_id und last_time setze, ist die Abfrage genauso langsam wie vorher auch. Er nutzt dann host_id als Index.

Den 3-Spalten Index nutzt er nur, wenn ich zusätzlich last_time in den WHERE-Part übernehme (wie jetzt in meiner letzten Idee geäußert).

Außer ich lösche den einzelnen Index auf host_id. Dann nutzt er den 3-Spalten-Index und dann ist die Abfrage auch wieder schnell. Allerdings weiß ich leider gerade gar nicht, ob ich dadurch nicht ein neues Problem verursache, da ich ja nicht immer nach last_time vergleiche, sondern auch Sonderformen nutze (vorwärts, andere WHERE-Bedingungen, usw.).

EDIT:
Ok, ich denke ich weiß wo das Problem ist. Ich wusste nicht, dass die Reihenfolge der Spaltennamen im mehrspaltigen Index so wichtig ist. Werde das mal durchtesten.

Beitrag von **mgutt** » 28.09.2009, 15:41

Also der Tipp mit der Reihenfolge war wirklich gut. Nur durch drehen verschiedener WHERE-Parameter, konnte ich den Index überhaupt erst "aktivieren".

Trotzdem hänge ich noch an einem Problem. z.B. diese Abfrage ist schnell:

Code: Alles auswählen

SELECT *
FROM topics
WHERE host = 1
AND forum = 820
AND type IN&#40;2, 3&#41;
ORDER BY type DESC, last_time DESC

Weil sie den Index host_forum_type nutzt, der über host, forum und type definiert wurde.

Dafür ist diese wiederrum langsam:

Code: Alles auswählen

SELECT COUNT&#40;topic_id&#41; AS count_topic_id
FROM topics
WHERE host = 1
AND forum = 820
AND type NOT IN&#40;2, 3&#41;

Ich habe sowohl mit einzelnen Indexen auf forum getestet, also auch mit einem auf host und forum. Sobald ich diese setze, nutzt er diese zwar, statt host_forum_type, aber die Abfrage bleibt trotzdem lahm

Natürlich muss ich hier zusehen, dass ich von dem COUNT() wegkomme und diesen als Wert in der Tabelle "forums" hinterlege, aber trotzdem brauche ich eine Verbesserung, da ich danach die topics mit einem nahezu identischen Query auslese.

von **Anzeige von ABAKUS** »

SEO Consulting bei ABAKUS Internet Marketing
Erfahrung seit 2002

persönliche Betreuung
individuelle Beratung
kompetente Umsetzung

Jetzt anfragen: 0511 / 300325-0.

Beitrag von **xAaron** » 28.09.2009, 16:40

Wie viele Ergebnisse liefert denn die zweite Abfrage?
Welche Mysql Version setzt du ein. Ein Bug verhindert bis zur 5.0.19 die Index-Nutzung bei NOT IN: https://bugs.mysql.com/bug.php?id=19372
Damit würde zwar der Index für die ersten beiden Abfragen genutzt, aber nicht mehr für die letzte. Also verhält sich dann der Tripel-Index äquivalent wie der Doppel-Index und da es sich um viele Daten handelt ist die Abfrage entsprechend lahm.
Du brauchst keinen Doppel-Index, wenn du einen Tripel-Index mit der selben Reihenfolge hast. Die sind identisch in den ersten zwei Ebenen.

Beitrag von **mgutt** » 28.09.2009, 18:19

Wie viele Ergebnisse liefert denn die zweite Abfrage?

Um die 6.000.

Ein Bug verhindert bis zur 5.0.19 die Index-Nutzung bei NOT IN

Ich habe noch MySQL 4.1.22. Meine neuen Server sind aber bereits bestellt. Ich fahre dann mit der jeweils aktuellsten Version und dann auch DB und Filesystem getrennt.

Ich bekomme zwei XL v4 von All-Inkl.com:

AMD Phenom QuadCore
· 8 GB Arbeitsspeicher
· 750 GB Festplatte SATA
- Traffic unlimitiert
- zusätzliche Backup-Festplatte

Ich will endlich mal wissen, welcher Teil eigentlich das Problem verursacht, tippe allerdings wie immer auf MySQL

xAaron hat geschrieben:Du brauchst keinen Doppel-Index, wenn du einen Tripel-Index mit der selben Reihenfolge hast. Die sind identisch in den ersten zwei Ebenen.

Gut zu wissen.

Beitrag von **everflux** » 29.09.2009, 08:18

hast du den mysql query cache an?
wie viel ram hat der aktuelle rechner?

Beitrag von **mgutt** » 29.09.2009, 08:26

Kann ich das über PHP irgendwie in Erfahrung bringen, ansonsten müsste ich die Technik fragen.

Der aktuelle Server hat 4 GB RAM. Hier mehr Daten:
https://all-inkl.com/altetarife/alt_server_xxl_2009/

Gruß

Beitrag von **Synonym** » 29.09.2009, 08:56

Entweder im entsprechenden Bereich der my.cnf oder per MySQL unter den Servervariablen (show variables). Alternativ auch im PHPMyAdmin -> Startseite -> MySQL-System-Variablen anzeigen.

Auch möglich wäre das "MySQL Performance Tuning Primer Script" von https://www.day32.com/MySQL/ das auch gleich noch ein paar Informationen zu den sonstigen möglichen Flaschenhälsen liefert.

Beitrag von **Lord Lommel** » 29.09.2009, 09:03

Den Cache kannst du dir bei phpMyAdmin angucken. Da gibts auch ne SQL-Abfrage für, wo du dir den ganzen Konfig-Kempel anzeigen lassen kannst. Da darfst du dich aber mal selber durch die Doku wühlen.

Zum Thema persistente Verbindungen: Das kannste halten wie ein Dachdecker, nur nicht so hoch. Wenn der Apache den Thread eines PHP-Skripts beendet, dann schließt er die Verbindung sowieso. Ich weiß gar nicht, ob es überhaupt möglich ist, persistente Verbindungen so zu nutzen. Höchstwahrscheinlich müßtest du dazu über die Kommandozeile gehen. Aber es kommt wohl relativ selten vor, daß es nötig ist, aus einem PHP-Skript heraus über die Kommandozweile ein PHP-Skript anzuwerfen.

Beitrag von **xAaron** » 29.09.2009, 09:11

Lord Lommel hat geschrieben:Wenn der Apache den Thread eines PHP-Skripts beendet, dann schließt er die Verbindung sowieso. Ich weiß gar nicht, ob es überhaupt möglich ist, persistente Verbindungen so zu nutzen.

Der Apache schließt sie nur, wenn php als cgi eingebunden ist, wenn php als modul drin ist macht es Sinn:
https://php.mirror.camelnetwork.com/man ... ctions.php

Beitrag von **Lord Lommel** » 29.09.2009, 09:16

Huch, ich wußte gar nicht, daß PHP als CGI gar kein Multithreading kann. Ist ja doof.

Beitrag von **mgutt** » 29.09.2009, 13:16

Lord Lommel hat geschrieben:Huch, ich wußte gar nicht, daß PHP als CGI gar kein Multithreading kann. Ist ja doof.

Das habe ich gestern auch erst erfahren

Aber ich setze das eh als Modul ein, daher wäre das kein Problem.

Synonym hat geschrieben:Entweder im entsprechenden Bereich der my.cnf oder per MySQL unter den Servervariablen (show variables). Alternativ auch im PHPMyAdmin -> Startseite -> MySQL-System-Variablen anzeigen.

Auch möglich wäre das "MySQL Performance Tuning Primer Script" von https://www.day32.com/MySQL/ das auch gleich noch ein paar Informationen zu den sonstigen möglichen Flaschenhälsen liefert.

Sowohl das eine als auch das andere kann ich leider nicht machen. Ich vermute, dass ich in phpmyadmin nichts sehe, weil ich einen Managedserver habe und die Rechte entsprechend begrenzt wurden.

Aber wenn phpmyadmin das ausgeben kann, gibts doch bestimmt eine Variable, die ich in PHP auswerfen kann oder nicht?

Beitrag von **Lord Lommel** » 29.09.2009, 13:24

Ja, die gibts. Mußte mal lokal phpMyAdmin draufhauen. Der zeit doch immer den Query an. (Bin jetzt selber zu faul.^^)

Beitrag von **mgutt** » 29.09.2009, 13:34

Mach ich, außer jemand sagt mir direkt welche Vars das sein können. Ich kenne die:
https://www.php.net/manual/de/mysql.configuration.php

Und die:
https://de2.php.net/manual/de/mysql.constants.php

Beitrag von **everflux** » 29.09.2009, 13:40

Versuch mal sowas...
https://dev.mysql.com/doc/refman/5.0/en ... nance.html