Performance-Optimierungen in einem Forum

Beitrag von **Lord Lommel** » 12.10.2009, 16:21

https://dev.mysql.com/doc/refman/5.0/en ... tines.html

Sowas meine ich.

von **Anzeige von ABAKUS** »

Beitrag von **mgutt** » 12.10.2009, 16:43

Ziemlich wenig Infos auf der Seite. Keine Beispielcodes.. nichts. Gibts da irgendwo bessere Informationen zu?

Beitrag von **Lord Lommel** » 12.10.2009, 19:02

Na du schreibst quasi ein Unterprogramm in SQL. Mußt du mal allgemein nach Stored Procedures suchen. Die sind relativ praktisch, weil du alles direkt auf der Datenbank machst und nicht mit dem lahmen PHP rumhantieren mußt. Schau dir auch bei der Gelegenheit gleich Views und Trigger an. Sowas kann man auch gut benutzen, auch wenn SQL-Puristen Trigger nicht so gerne sehen. Besonders in Verbindung mit Fremdschlüsseln und Constraints sind die recht praktisch.

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 **mgutt** » 12.10.2009, 19:33

Also eins habe ich in den letzten Jahren gelernt. Wenn was langsam ist, dann SQL und nicht PHP

Allerdings habe ich von SP keine Ahnung. Und vom direkten Hantieren auf der Datenbank: Vermutlich geht das gar nicht, weil ich Managed hoste.

Mal ne Grundlagenfrage. Ich hab diese Abfrage:

Code: Alles auswählen

EXPLAIN SELECT DISTINCT topic
FROM posts
WHERE poster = 2
AND forum NOT IN &#40;0&#41;

Ich habe testweise die Indexe gesetzt:
poster_forum
poster_forum_topic
poster_topic

Genutzt wird der Index poster_forum_topic, allerdings mit diesen Extras:
Using where; Using index; Using temporary

In diesem Fall:

Code: Alles auswählen

EXPLAIN SELECT DISTINCT topic
FROM posts FORCE INDEX&#40;poster_topic&#41;
WHERE poster = 2
AND forum NOT IN&#40;0&#41;

Extras:
Using where

Ich gehe jetzt recht in der Annahme, dass letztere Variante die bessere ist. Allerdings verstehe ich nicht, warum die erste sogar einen temporay nutzt.

Als SUB-Select ist allerdings wieder das Extra tempory aktiv. Aber ich denke mal, dass das normal ist, weil der ja das Ergebnis danach noch sortieren muss?!

Beitrag von **Lord Lommel** » 12.10.2009, 19:40

mgutt hat geschrieben:Also eins habe ich in den letzten Jahren gelernt. Wenn was langsam ist, dann SQL und nicht PHP

Wo haste denn das her ? SQL ist so schnell wie du es programmierst. Das langsamste ist die Verbindung zwischen MySQL und PHP. Deshalb schiebt man da so wenig wie möglich durch. Außerdem ist PHP um Größenordnungen langsamer als SQL.

mgutt hat geschrieben:Allerdings habe ich von SP keine Ahnung. Und vom direkten Hantieren auf der Datenbank: Vermutlich geht das gar nicht, weil ich Managed hoste.

Das ist eigentlich ganz einfach. Die Prozedur kannst du wie einen SQL-Befehlt über die Schnittstelle an die Datenbank geben und dann liegt sie da rum und kann benutzt werden. Das geht sogar bei Freehostern. Ich weiß gar nicht, ob man das explizit abstellen kann.

Beitrag von **mgutt** » 12.10.2009, 19:44

Lord Lommel hat geschrieben:
mgutt hat geschrieben:Also eins habe ich in den letzten Jahren gelernt. Wenn was langsam ist, dann SQL und nicht PHP
Wo haste denn das her ? SQL ist so schnell wie du es programmierst. Das langsamste ist die Verbindung zwischen MySQL und PHP. Deshalb schiebt man da so wenig wie möglich durch. Außerdem ist PHP um Größenordnungen langsamer als SQL.

SQL ist ein Flaschenhals, daher kann es so schnell sein wie es will. Wenn der Prozessor belastet wird, laufen die Abfragen gerne mal auf und dann ist Schluss mit lustig. Daher behaupte ich, dass SQL langsamer ist. Zumindest hatte ich noch nie Probleme in PHP und das schon seit 5 Jahren Wachstum.

Wer dann noch hingeht und mit IF oder Berechnungen in SQL arbeitet, hat meiner Ansicht nach zu viele Server auf Lager

Beitrag von **Lord Lommel** » 12.10.2009, 20:24

Ich halt mich mal jetzt lieber zurück...

Beitrag von **xAaron** » 12.10.2009, 22:03

mgutt hat geschrieben: SQL ist ein Flaschenhals, daher kann es so schnell sein wie es will. Wenn der Prozessor belastet wird, laufen die Abfragen gerne mal auf und dann ist Schluss mit lustig. Daher behaupte ich, dass SQL langsamer ist. Zumindest hatte ich noch nie Probleme in PHP und das schon seit 5 Jahren Wachstum.

Das Gegenteil ist der Fall. Du musst dir nur klar machen, was du da eigentlich mit SQL tust: Du willst aus Gigabyte einzelne Daten haben und das wenn möglich in Nanosekunden. Probier doch mal die Datenhaltung und das entsprechende Abfragen in PHP zu implementieren. Und dann wirst du vor SQL auf die Kniee fallen.

Wenn du noch zur Ausgangsfrage einen Hinweis willst, dann brauch ich die Ergebnisse von
Select count(*) from.. where p.topic=t.topic and user=mgutt,
Select count(*) from.. where moved=0 and user=mgutt,
...und das für jede where Bedingung.
Sonst ist das Stochern im Nebel.

Beitrag von **bfs** » 12.10.2009, 22:23

PHP vs. MySQL ist wie ein Vergleich von Äpfel und Birnen, und das auch noch zu verschiedenen Jahreszeiten.

Was deine Frage zu den Indizes angeht, so würde ich sagen poster_forum_topic ist dafür maßgeschneidert und eindeutig die beste Wahl. Das sagt ja auch das "using index" aus: mysql kann alle Teile des Queries aus dem Index bedienen.

Nebenbei: der Index "poster_forum" ist natürlich überflüssig, da er ja schon in "poster_forum_topic" enthalten ist. poster_topic kannst du dir auch sparen und stattdessen in deine queries an der 2. relevanten Position entsprechend "immerwahre" Abfrage von "Forum" einbauen wie "AND forum >= 0", dann können diese Queries auch poster_forum_topic nutzen.

Das er für poster_topic kein "using temporary" ermittelt würde ich auf die Ungenauigkeiten von explain in Kombination mit DISTINCT schieben, da dieses eine effiziente Indexnutzung verhindert. Was sagt denn explain ohne "DISTINCT", ist das "using temporary" dann weg?

Um sich DISTINCTe Werte "zu merken", muss ich mysql nämlich fast immer eine temporary anlegen, daher ist es bei grösseren Tabellen oft sehr langsam. Wenn es mengenmässig machbar ist, dann ist es oft schneller sich ALLE Werte geben zu lassen und die Duplikate durch die Anwendung zu eliminieren.

Beitrag von **mgutt** » 15.10.2009, 11:36

bfs hat geschrieben:Was deine Frage zu den Indizes angeht, so würde ich sagen poster_forum_topic ist dafür maßgeschneidert und eindeutig die beste Wahl. Das sagt ja auch das "using index" aus: mysql kann alle Teile des Queries aus dem Index bedienen.

Ja allerdings erzeugt die Abfrage ja einen temporary table und das ist wohl auch der Grund, warum der index poster_forum_topic langsamere Ergebnisse resultiert als poster_forum.

Das er für poster_topic kein "using temporary" ermittelt würde ich auf die Ungenauigkeiten von explain in Kombination mit DISTINCT schieben, da dieses eine effiziente Indexnutzung verhindert. Was sagt denn explain ohne "DISTINCT", ist das "using temporary" dann weg?

Ja, liegt natürlich an DISTINCT. Warum beim anderen Index temporary nicht auftaucht, verstehe ich allerdings nicht.

Wenn es mengenmässig machbar ist, dann ist es oft schneller sich ALLE Werte geben zu lassen und die Duplikate durch die Anwendung zu eliminieren.

Das ist z.B. ein Grund, warum PHP schneller als MySQL ist, auch wenn es Äpfel und Birnen sind. Tatsächlich habe ich meine Abfrage jetzt erstmal so angepasst, dass ich mit zwei SELECTs arbeite. Im ersten Fall lese ich alle topics aus, nutze array_unique() und lege sie dann wieder in die zweite Abfrage mit IN(). Das mache ich aber mit einem Filter. Und zwar setze ich voraus, dass ich nur die Themen ausgebe, in denen der letzte Beitrag eines Nutzers maximal 90 Tage alt ist. Auf die Art fallen die Themen raus, in denen er sich sowieso nicht mehr beteiligt und das Ergebnis ist maximal 1000 Zeilen stark. Allerdings gab es da bereits die erste Beschwerde, weshalb ich da mit Optionen arbeiten musste.

D.h. ich habe folgendes gemacht:
A) wenn mindestens 5 Topics mit der 90 Tage-Regel gefunden wurden, bleibt es bei dem array_unique() / IN-Konstrukt.
B) wenn weniger als 5 Topics gefunden wurden, greift die alte Abfrage, die alle Topics ausliest.

Der Nutzer kann zusätzlich A) und B) über ein Dropdown auslösen (alle Themen anzeigen, in denen Du in den letzten 90 Tagen geschrieben hast / alle Themen seit Beginn anzeigen).

Damit habe ich erstmal die große Welle an Abfragen reduzieren können.

Die Abfrage A):

Code: Alles auswählen

SELECT topic
FROM posts FORCE INDEX&#40;poster_time&#41;
WHERE poster = 2
AND time > ' . &#40;time&#40;&#41; - 7776000&#41;
$topics = array_unique&#40;$topics&#41;;
$total = count&#40;$topics&#41;;
if &#40;$total > 4&#41; &#123;
	SELECT t.*, h.*
	FROM topics t FORCE INDEX&#40;PRIMARY&#41;, hosts h FORCE INDEX&#40;PRIMARY&#41;
	WHERE t.moved = 0
	AND t.host = h.host
	AND t.forum NOT IN&#40;0&#41;
	AND t.topic IN&#40;' . implode&#40;', ', $topic_ids&#41; . '&#41;
	ORDER BY t.last_time DESC
	LIMIT 300
&#125;

Gesamtzeit ca. 0.025s

Abfrage B)

Code: Alles auswählen

else &#123;
	SELECT t.*, h.*
	FROM topics t FORCE INDEX&#40;PRIMARY&#41;, posts p FORCE INDEX&#40;poster_topic&#41;, hosts h FORCE INDEX&#40;PRIMARY&#41;
	WHERE t.moved = 0
	AND t.host = h.host
	AND t.forum NOT IN&#40;0&#41;
	AND t.topic = p.topic
	AND p.poster = 2
	GROUP BY p.topic
	ORDER BY t.last_time DESC
	LIMIT 300
&#125;

Gesamtzeit: 0.14 - 0.25s
Mit DISTINCT statt GROUP BY: 0.5 - 0.7s
Mit SUBSELECT: Absturz
Mit mehrspaltigen Indexen: 0.5 - 0.9s

Wie man sieht, hat der umfangreiche Test ergeben, dass PRIMARY bei t der schnellste Index war, auch schneller als moved_host_forum, moved_host_forum_topic, etc.

Nach meinem Empfinden hat FORCE INDEX die Abfragen auch beschleunigt. Vermutlich weil MySQL nicht mehr selber suchen muss, welcher Index sind macht.

Beitrag von **everflux** » 15.10.2009, 11:52

Ist das jetzt bei einer aktuellen MySQL Version? Sonst könnte es sein, dass Du Dir viel zu viel Arbeit machst, die Du mit einem Update sparen könntest

Beitrag von **mgutt** » 15.10.2009, 12:42

Ja ist mit der aktuellen DB-Version. Ich habe bereits den DB-Server im Einsatz. Rennt wie Sau

Aber das Erstellen der Indexe und der Subselect führte zu leichten Schwitzattacken