Performance-Optimierungen in einem Forum

Beitrag von **nerd** » 01.10.2009, 22:55

nachtrag zur storage engine: https://www.softwareprojects.com/resour ... -1470.html

von **Anzeige von ABAKUS** »

Beitrag von **mgutt** » 02.10.2009, 08:19

Toll jetzt bin ich hin- und hergerissen

Dann bleibe ich wohl doch bei MyISAM, nur dass ich mir für die Volltextsuche eine alternative suche. Aber da warte ich erstmal wie schnell die aktuellste MySQL-Version mittlerweile damit umgehen kann. Dann entscheide ich weiteres.

Beitrag von **Synonym** » 02.10.2009, 09:03

Bei 1 Millionen Beiträgen mit je 20 Wörtern hat man schon ca. 10 Millionen Zeilen im word table. Die müssen erstmal durchsucht werden.

Naja, nicht unbedingt. Bzw. kannst Du das mal näher erklären?

10 Millionen Wörter finde ich heftig. Gibt es so viele überhaupt? Mein System ist etwa in der Art und ja, weil Du es sagst, ist auf Basis des phpbb entstanden.

Bei mir kommen die Wörter alle in eine wort_table mit unique_index auf "wort". Dazu natürlich eine wort_id. Da kam ich bisher maximal auf etwa 85.000 Wörter in den letzten 3 Jahren.

Dann gibt es als Verknüpfung die treffer_table, in der die wort_id und die artikel_id ist. Index liegt dort über beide Spalten. Derzeit ca. 1.150.000 Datensätze.

Kommt nun eine Suche, dann wird erst die wort_db befragt ob es das Wort gibt. Also eine Suche innerhalb von 85.000 Datensätzen anhand dem unique_index. Wenn es dort Treffer gibt, dann wird die wort_id ausgelesen. Danach wird dann in der treffer_table gesucht. Suche nach INT anhand eines Index.

Also bei mir geht das sehr schnell.

Auch die Inserts benötigen nicht für jedes Wort eine Abfrage. Man kann auch alle abfragen die im jeweiligen Artikel sind. Für die, bei denen es keinen Treffer in der wort_table gibt, wird ein Insert (einer für alle) durchgeführt.

Anschließend werden genau die eben neu eingetragenen abgefragt um die wort_id zu bekommen (ein Select).

Dann frage ich die treffer_table ab zu den Wörtern die schon da waren und die entsprechende artikel_id besitzen (Ein Select). Auch hier werden danach die verwendet die keinen Treffer liefern.

Die wort_id der neuen Wörter sowie die wort_id ohne Treffer bei dem Artikel werden dann mit der jeweiligen artikel_id in die treffer_table geschrieben.

Oder denke ich da verkehrt und es läuft bei mir nur zufällig und der Tod steht bevor?

von **Anzeige von ABAKUS** »

Beitrag von **bfs** » 02.10.2009, 09:14

Vorsicht, das ist ein absoluter Cleanroomvergleich, der das unterschiedliche Skalierungsverhalten bezüglich Größe der Datenbank und des verfügbaren Hauptspeichers unberücksichtig lässt. Und ganz offensichtlich wurde dieser Vergleich im Memorybereich < 2GB durchgeführt, in der InnoDB seine Skalierungsvorteile noch garnicht auspielen kann. Man beachte folgenden Absatz:

8. To maximize InnoDB MySQL database performance, start with these my.cnf settings:
[...]
innodb_buffer_pool_size = 250M
[...]

Das ist als "Perfomancetuning-Empfehlung" geradezu hahnebüchen. Dieser Wert beweist nicht nur, das hier unter Low Memory Conditions getestet wurde sondern schränkt die InnoDB Perfomance bereits auf Servern mit heutzutage üblichen Speichergrößen von 4-8 GB völlig unnötig ein.

Beitrag von **lunetics** » 02.10.2009, 10:12

mgutt hat geschrieben:Toll jetzt bin ich hin- und hergerissen

Dann bleibe ich wohl doch bei MyISAM, nur dass ich mir für die Volltextsuche eine alternative suche. Aber da warte ich erstmal wie schnell die aktuellste MySQL-Version mittlerweile damit umgehen kann. Dann entscheide ich weiteres.

Ich würde empfehlen du postest mal die Server Config, deine DB Größe etc.pp.

Am besten natürlich mal das performance script von day32 drüberlaufen lassen...

Volltextsuche würde ich SOLR empfehlen, was auch auf Lucene basiert. Falls dir volltextanfragen mit ergebniszeiten im millisekundenbereich wichtig sind. Du darfst nicht vergessen dass dies auch die Datenbank enorm entlasten wenn du viele Suchen hast.

Im slow query log können je nach config auch Queries drinstehen die zwar relativ schnell ausgeführt werden, jedoch keinen Index benutzen. Je nachdem wie OFT die ausgeführt werden, kann das viel bringen.

Ich empfehle dir mal dass du nen shell account bekommst und entsprechende tools laufen lässt (maatkit / mk-query-digest) um nach und nach die queries zu optimieren. Immer schön an der Wurzel anfangen

Beitrag von **mgutt** » 02.10.2009, 10:39

Synonym hat geschrieben:
Bei 1 Millionen Beiträgen mit je 20 Wörtern hat man schon ca. 10 Millionen Zeilen im word table. Die müssen erstmal durchsucht werden.
Naja, nicht unbedingt. Bzw. kannst Du das mal näher erklären?

10 Millionen Wörter finde ich heftig. Gibt es so viele überhaupt? Mein System ist etwa in der Art und ja, weil Du es sagst, ist auf Basis des phpbb entstanden.

Ok das war wohl reichlich übertrieben von mir:
https://de.wikipedia.org/wiki/Wortschatz

Es gibt um die 75.000 - 500.000 Wörter im Deutschen. Allerdings schließt das keine Phantasiebegriffe wie Marken, etc. mit ein. Daher bist Du auch schon bei 85.000 Wörtern, obwohl Du vermutlich nicht mal ansatzweise alle Fremdwörter in der Datenbank stehen hast.

Trotzdem hast Du nur eine simple "Wort ist da? Ja / Nein" Suche. Von Relevanz kann da nicht die Rede sein (was z.B. MyISAM Fulltext bietet) und von Wortpositionen erst recht nicht (was z.B. Sphinx bietet).

Bei mir ist das System irgendwann zusammengebrochen. Das Absenden von neuen Beiträgen entwickelte sich mit der Zeit zu einer Geduldsprobe (ca. 500.000 Beiträge hatte ich zu dieser Zeit, aber auch einen schlechteren Server als heute). Und umso größer die Beiträge waren, umso schlimmer wurde es und zuletzt kamen immer mehr Doppelposts dazu, weil die Leute dachten, dass der Server nicht mehr antworten würde.

Mich interessiert vor allen Dingen Relevanz. Danach kommt die Geschwindigkeit. Beides zusammen wäre natürlich perfekt.

z.B. hat Sphinx etwas, was mich sehr interessiert:
"Ich bin eine Suchphrase" [Suche nach kompletter Phrase] << ("Ich bin" | "bin eine" | "eine Suchphrase") [Suche nach 2er Paketen] << Ich bin eine Suchphrase [AND-Suche]

Sphinx kann also von links nach rechts alles durcharbeiten und die besten Ergebnisse auf diese Art zurückgeben. Das kann MyISAM nicht.

Allerdings habe ich noch nichts gefunden, was mit unterschiedlichen Wortformen umgehen kann, wie z.B. Ein- und Mehrzahl.

lunetics hat geschrieben:Ich empfehle dir mal dass du nen shell account bekommst und entsprechende tools laufen lässt (maatkit / mk-query-digest) um nach und nach die queries zu optimieren. Immer schön an der Wurzel anfangen

Ich verstehe nicht ganz, was mir solche Tools bringen sollen, die ja auch nur wieder Last resultieren. Hört sich zwar komisch an, aber ich kenne fast alle meine Queries aus dem Kopf. Es sind ja nicht irgendwelchen Unbekannten, die ein Problem verursachen, sondern das Problem ist, dass Änderungen an einem Table dazu führen, dass der Index neu aufgebaut wird und sobald das geschieht, werden die parallel laufenden Zugriffe wieder langsam.

Weiterhin werfen mir diese Logs haufenweise Queries aus, wo ich weiß, dass sie langsam sind, aber ich auch weiß, dass sie zwischengespeichert werden. D.h. die Last ist im Verhältnis zu schnellen Abfragen viel geringer, weil der "schwere" Query nur einmal am Tag ausgeführt wird, während andere bei jeder Impression ausgelöst werden.

Die Indexe / Abfragen, die ich dank diesem Thread verbessern konnte, haben schon gut geholfen. Jetzt bin ich mal wieder bei der lästigsten Abfrage und das ist die Fulltext-Abfrage.

Übrigens sehe ich die Wurzel in einer Abfrage als solche. Wenn ich könnte, würde ich völlig auf die DB verzichten

Ich habe noch mal meine Stats gecheckt. Also pro Tag kommen z.B. ca. 1.500 Beiträge hinzu. Der Hauptanteil natürlich abends, so dass zu der Zeit jede Minute ein neuer Beitrag hinzugefügt wird. Aus InnoDB-Sicht dürfte damit die Postingtabelle die Voraussetzungen für InnoDB erfüllen. Allerdings "nur" die mit den IDs, also nicht die mit den Texten. Die ist denke ich aber auch nicht so wichtig. Schließlich wird bei jedem neuen Beitrag auch die topic-Tabelle aktualisiert, so dass ich auch dort gut InnoDB nutzen könnte. Die Frage ist, was sind "viele Writes" im Verhältnis zu den Reads? Was ja die Voraussetzung für InnoDB ist.

Aktuell überlege ich noch, ob es so schlau war ein Session-System auf der Datenbank aufgebaut zu haben. Vielleicht bringt mir das mehr, wenn ich PHPs-Sessionsystem nutze oder selbst ein Filebasiertes umsetze. Hat dazu mal jemand Benchmarks gesehen?

Dieses Benchmark ist insofern falsch, als das es innerhalb der Schleife immer wieder neu zur Datenbank konnektiert:
https://board.artfiles.de/packages/inde ... #post20941

Da man ja in der Regel eh schon eine Verbindung hat, macht es wenig Sinn das mit in den Benchmark einfließen zu lassen.

Beitrag von **mgutt** » 12.10.2009, 11:32

Hat jemand eine Idee, wie ich diese Abfrage beschleunigen könnte?

Code: Alles auswählen

SELECT t.*, h.domain, h.www
FROM topics t, posts p, hosts h
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 t.topic
ORDER BY t.last_time DESC
LIMIT 300

Das Hauptproblem ist denke ich mal posts. Da hatte ich einen Index auf topic und einen auf poster. Jetzt habe ich einen mehrspaltigen auf poster_topic gelegt, damit ist die Abfrage schon mal 50% schneller. Das funktioniert schon mal soweit. Allerdings habe ich jetzt bei Explain folgendes stehen:

Using where; Using index; Using temporary; Using filesort

Das ganze Paket also

Von hosts nimmt er übrigens PRIMARY host und von topics PRIMARY topic. Ich habe in topic noch mehrspaltige Indexe auf host_forum_type und auf moved_host_lasttime, die nimmt er allerdings nicht. Soll ich die beiden Indexe kombinieren oder noch einen dritten auf moved_host_forum_topic_lasttime setzen?

Ich habe aktuell Schiss, dass ein solcher Index dann wieder die anderen Abfragen langsam macht oder greift der dann auch bei den anderen optimal?

Ich verstehe auch nicht, warum bei phpmyadmin immer davor gewarnt wird doppelte Indexe anzulegen. Ist das weil MySQL zu lange suchen muss? Dann könnte man doch USE INDEX() nutzen oder?

Beitrag von **xAaron** » 12.10.2009, 13:07

Könntest du mal kurz auflisten für jede der Tabellen: wie viele Einträge sie insgesamt hat und wie viele Daten du daraus selektierst?! Denn davon hängt es ab, wo man am besten ansetzen sollte.

Beitrag von **mgutt** » 12.10.2009, 13:28

topics 500k
posts 6M
hosts 1k

Die Abfrage hängt von der Anzahl der Beiträge des jeweiligen Mitglieds ab. Entsprechend schnell ist sie bei einem neuen User und entsprechend langsam ist sie bei mir. Bei mir sind es ca. 20k Ergebnisse:
https://www.maxrev.de/files/2009/10/ego_abfrage.png

Beitrag von **Lord Lommel** » 12.10.2009, 13:45

Schmeiß doch die Posts mit poster = 2 erstmal in eine temporäre Tabelle. Dann verjoine damit.

Beitrag von **xAaron** » 12.10.2009, 14:07

Das schlimme sind die joins. Gerade der zwischen p und t ist das personifizierte Böse. Ich bin ja leider kein mysql Experte, in Oracle könntest du jetzt einfach einen join index nutzen, aber in mysql geht das wohl nicht so einfach.

Könntest du mir bitte noch mal kurz genauer schreiben, wie viel die einzelnen Bedingungen zurückgeben, also wie viele Zeilen moved=0, t.host=h.host, p.topic=t.topic,... jeweils für einen lange existierenden, also z.B. deinen, account zurückgeben?
Weil erst daraus kann man schließen, wie mysql vorgeht bei der Abarbeitung.

Beitrag von **mgutt** » 12.10.2009, 14:26

Lord Lommel hat geschrieben:Schmeiß doch die Posts mit poster = 2 erstmal in eine temporäre Tabelle. Dann verjoine damit.

Ich hab das mit einem Subselect versucht:

Code: Alles auswählen

SELECT t.*, h.domain, h.www 
FROM topics t, hosts h 
WHERE t.moved = 0 
AND t.host = h.host 
AND t.forum NOT IN&#40;0&#41; 
AND t.topic IN&#40;SELECT DISTINCT topic FROM posts WHERE poster_id = 2&#41;  
AND p.poster = 2 
ORDER BY t.last_time DESC 
LIMIT 300

Allerdings ist das noch langsamer

Liegt vermutlich am "fehlenden" Index und der Anzahl der resultierenden Reihen:
https://www.maxrev.de/files/2009/10/ego ... select.png

Er nutzt wie man sieht moved_host_lasttime als Index. Ich denke ich müsste entweder diesen erweitern oder einen weiteren über noch mehr Spalten anlegen. Langsam wird das zu einem "leg Index über alles"-Index ^^

EDIT:
Ok, ich scheine wieder das gleiche Problem wie vorher zu haben. Nämlich dann wenn die tables gelockt sind oder der CACHE noch nicht verfügbar ist, verzögert sich der SELECT. Ich habe den mhlt mal erweitert auf mhflt, also moved_host_forum_lasttime und der funktioniert überall, allerdings kann der mal 0.0009s dauern, aber auch mal 6s

Beitrag von **Lord Lommel** » 12.10.2009, 14:43

Ich dachte eigentlich eher in Richtung Stored Procedure. Dann muß er nicht das Riesengefelcht verjoinen, zumal du den Join ja eh nur als Filter nutzt.

Beitrag von **mgutt** » 12.10.2009, 14:52

Prepared Statements? Dazu habe ich vor kurzem ein Benchmark gemacht:
https://www.programmierer-forum.de/benc ... 116938.htm

Ich konnte dabei keinen Vorteil ermitteln. Im Gegenteil, die Abfragen waren alle langsamer. Erst wenn man PS ohne WHERE einsetzt, erscheint mir das Ergebnis schnell zu sein, wenn ich es mit diesem Test vergleiche:
https://www.mysqlperformanceblog.com/20 ... s-of-data/

Allerdings wechselt die Abfrage ja bei jedem User und damit wird ja automatisch bei jedem eine neue WHERE-Konstellation fällig.

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

mgutt hat geschrieben:
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

Neuer Datenbankserver, neue Werte:

Variable_name Value
Qcache_free_blocks 510
Qcache_free_memory 31405984
Qcache_hits 21844
Qcache_inserts 85499
Qcache_lowmem_prunes 0
Qcache_not_cached 3220
Qcache_queries_in_cache 1291
Qcache_total_blocks 3137

Der DB-Server ist jetzt nur noch für die DB da und hat eigens 8 GB RAM. Den RAM kann ich aber denke ich erst ausnutzen, wenn ich teilweise auf InnoDB umgestellt habe.