Performance-Optimierungen in einem Forum

[mgutt]

Ich habe bereits:
- Diverse Zwischenspeicher für DB-Abfragen (vorhalten der Daten, Aktualisierung erst bei Änderung des Datensatzes)
- Struktur der Zwischenspeicher ist aufgeteilt, so dass pro Verzeichnis max. X Dateien enthalten sind
- Template-Caching

Weitere Performance-Optimierungen interessieren mich, daher hier meine Antwort auf diesen Beitrag:

Prepared Statements

Da lese ich mich aktuell ein, aber wirklich viel hilft PS auch nicht, wie mir scheint. Ich meine wie oft nutzt man die gleiche Abfrage, nur mit unterschiedlichen Parametern? Ich habe glaube ich zwei Stellen, wo ich mit einer Schleife einen INSERT absetze. Schade, dass PS nicht global funktioniert. Zumindest konnte ich dazu nichts finden.

persistente Verbindungen

Da gehen die Meinungen bei MySQL auseinander. Viele sagen, dass es völlig egal wäre und keinen Vorteil bringt.

DB-Partitionierung

Wenn das in MySQL 5 mal irgendwann funktioniert, dann vielleicht ja

Ich für meinen Teil spiele ungern mit neuen Sachen herum, die nicht ausreichend getestet wurden. Da gibt es immer wieder gerne mal Sicherheitslecks.

gut aufgebaute Indizes

Das ist denke ich Standard. Ohne anständige Indizes, kackt ja eigentlich jede DB ziemlich schnell ab.

materialisierte Sichten

Gibts in MySQL (leider) nicht. Hört sich für mich aber ähnlich wie PS an. Oder ist es das "globale" Ding was ich bei PS vermisse?

FullTableScan

Sagte mir erst nichts, aber dadurch bin ich überhaupt darauf gekommen, dass es mysql.trace_mode gibt. Da werde ich doch mal schauen, ob es noch was zu optimieren gibt

[xAaron]

Prepared Statements

Da lese ich mich aktuell ein, aber wirklich viel hilft PS auch nicht, wie mir scheint. Ich meine wie oft nutzt man die gleiche Abfrage, nur mit unterschiedlichen Parametern?

Da hast du recht. Aus Optimierungssicht macht das nur in Spezialfällen und bei bestimmten DBMS, z.B. nicht bei MySQL, Sinn.

persistente Verbindungen

Da gehen die Meinungen bei MySQL auseinander. Viele sagen, dass es völlig egal wäre und keinen Vorteil bringt.

Ok, ich bin bei MySQL nicht so firm, aber das soll keinen Sinn machen? Es ist in etwa gleich effizient 1000 Mal eine Verbindung zum Server aufzubauen, wie sie ein Mal aufzubauen? Das glaub ich nicht. Grundvorraussetzung ist natürlich, dass PHP als Modul drin ist, sonst macht das natürlich keinen Sinn.
Allein der overhead eine Verbindung zu öffnen macht schon einiges an Zeit und Last für den Server aus.
An deiner Stelle würde ich das mal testen, ob nicht gerade hier die Performance-Bremse bei deinen Projekten liegt.

DB-Partitionierung

Wenn das in MySQL 5 mal irgendwann funktioniert, dann vielleicht ja

Ich für meinen Teil spiele ungern mit neuen Sachen herum, die nicht ausreichend getestet wurden. Da gibt es immer wieder gerne mal Sicherheitslecks.

Sicherheitslecks musst du dabei nicht befürchten. Es macht halt gerade für ein Forum, wo im großen und ganzen nur die aktuelleren Daten gebraucht werden schon Sinn, da dann die Partition der aktuellen Daten gut im Arbeitsspeicher liegen kann.

gut aufgebaute Indizes

Das ist denke ich Standard. Ohne anständige Indizes, kackt ja eigentlich jede DB ziemlich schnell ab.

Ja, aber index ist nicht gleich index. Nicht umsonst kann man als reiner DB-Designer ganz gut leben. So können Indizes so komponiert sein, dass sie gut aufeinander aufbauen usw. Das ist bei komplexen Abfragen eine Kunst für sich.

materialisierte Sichten

Gibts in MySQL (leider) nicht. Hört sich für mich aber ähnlich wie PS an. Oder ist es das "globale" Ding was ich bei PS vermisse?

Der Unterschied ist himmeldweit: Bei PS wird die Abfrage gespeichert und eventuell die optimierte Variante. Das bringt nur selten was. Materialisierte Sichten speichern ein Abfrageergebnis. MS ist also eine neue virtuelle Tabelle.
Beispiel: In der Tabelle sind alle Käufe von Autos der letzten 10 Jahre. Jetzt brauchst du häufig die Anzahl der Porsche-Käufe in einem Monat des letzten Jahres. Also für Januar oder für März oder einen anderen Monat. D.h. du hast hoffentlich einen passenden Index der Tabelle, der in den Hauptspeicher passt, sonst hast du ein Problem.
Du könntest jetzt eine Materialiserte Sicht erstellen, die dir die Porsche-Verkäufe des letzten Jahres gibt. Dann hast du eine Tabelle, die nur die Porsche-Käufe und nur des letzten Jahres enthält, was schon eine riesige Einsparung ist. Gleichzeitig wird die Tabelle synchron zur Muttertabelle gehalten. Jetzt wird das DBMS für jede Anfrage, die nach dem Porsche-Käufen in einem der letzten Monate fragt nur unter Nutzung der MS beantworten und das ist natürlich viel schneller, da auf der MS natürlich auch wieder ein Index sein kann, als eine Abfrage an die große Tabelle.

FullTableScan

Sagte mir erst nichts, aber dadurch bin ich überhaupt darauf gekommen, dass es mysql.trace_mode gibt. Da werde ich doch mal schauen, ob es noch was zu optimieren gibt

Ja, Full Table Scan ist Quasi der Albtraum schlechthin. Wenn du es mit Indizes schaffst den zu verhindern hast du schon 90% der Optimierungsarbeit erledigt.

[xAaron]

Noch als Nachtrag, auch wenn wir jetzt weit weg vom Thema sind: Besser als Datenbank-Optimierung ist natürlich die fertige html-Ausgabe gecached zu haben und die einfach wieder rauszuschicken, solange bis sich in der DB entsprechende Einträge ändern. Das würde die Performance am meisten steigern. Bei einem Chat mag das nur begrenzt gehen, aber bei Foren wäre das ein unheimlicher Performance-Gewinn.

[mgutt]

Jo mache ich bereits. Geht aber nur zum Teil, weil gewisse Inhalte auch auf Topic-Seiten dynamisch sind (Beitragszähler, Avatar, Signaturen, etc.). Wenn man die dynamischen Inhalte allerdings entfernt oder bei Änderung dieser entsprechenden Punkt die jeweiligen Caches zurücksetzt, könnte man auch eine 100%-tige HTML-Seite ausliefern.

Was allerdings ein bisschen dämlich ist, dass man immer mehr von der DB abgeht. Eigentlich könnte man sie sich irgendwann gleich sparen

[xAaron]

Geht aber nur zum Teil, weil gewisse Inhalte auch auf Topic-Seiten dynamisch sind (Beitragszähler, Avatar, Signaturen, etc.). Wenn man die dynamischen Inhalte allerdings entfernt oder bei Änderung dieser entsprechenden Punkt die jeweiligen Caches zurücksetzt, könnte man auch eine 100%-tige HTML-Seite ausliefern.

Zumindestens Avatar und Signatur sind statisch genug um sie zu cachen und Beitragszahlen dürften auch noch cachebar sein, wobei die natürlich schon ärgerlich sind. Aber alles was nicht an die DB geht ist super, deshalb würde ich auch da mit cache arbeiten. Und selbst wenn es nur die DB-Anfragen halbiert(wahrscheinlich würde es die eher auf 10% verringern) wäre so viel gewonnen.

[mgutt]

Im Grunde sind es nur die Beitragszähler, die ein Problem verursachen. Weil jedesmal global alle Caches zurückgesetzt werden, wenn ein bestimmter User darin geschrieben hat. Da könnte man meiner Ansicht nach nur noch mit einem zeitversetzten Cache arbeiten, aber ich höre jetzt schon die User schreien "ah mein Wohnort stimmt gar nicht bei diesem Beitrag" "ah meine Beitragsanzahl schankt"

Hast Du nicht mal Lust woanders zu disktuieren, wir weichen stark vom Thema ab

[xAaron]

Im Grunde sind es nur die Beitragszähler, die ein Problem verursachen. Weil jedesmal global alle Caches zurückgesetzt werden, wenn ein bestimmter User darin geschrieben hat. Da könnte man meiner Ansicht nach nur noch mit einem zeitversetzten Cache arbeiten, aber ich höre jetzt schon die User schreien "ah mein Wohnort stimmt gar nicht bei diesem Beitrag" "ah meine Beitragsanzahl schankt"

Hast Du nicht mal Lust woanders zu disktuieren, wir weichen stark vom Thema ab

Ja, als letztes: ich würde es durchaus in Kauf nehmen die Beitragszahlen nur alle paar Stunden global zu ändern, sprich den cache aller Beiträge, wo user geschrieben haben, die auch in den letzten Stunden ihre Beitragszahl erhöht haben, zu leeren. Wenn das dazu führt, dass das ganze Forum hervorragend gecacht werden könnte, dann wäre das in meinen Augen eine akzeptable Variante.

[mgutt]

Mir ist was anderes eingefallen. Ich trenne die Caches einfach. Also einmal Beiträge und einmal Autoren. Auf die Art wird immer nur der jeweilige Autorendatensatz aktualisiert.

Aktuell läuft das bei mir sowieso schon so, dass Autordaten separat ausgelesen werden. Daher wäre das eine gute Basis. Allerdings weiß ich nicht, ob ich mit den paar Stunden Differenz so gut fahre. Die Beschwerden der User werden sicher nicht lange auf sich warten. Ist ja auch irgendwie verwirrend, wenn man schreibt und der Zähler sich nicht ändert. Oder wenn man ein Avatar hochlädt und das alte noch angezeigt wird.

Wobei beim Avatar brauch ich das ja nur ändern, dass er nicht mehr einen zufälligen Dateinamen wählt, sondern die UserID als Dateinamen nutzt. So muss man diese Datensätze auch gar nicht speichern, sondern kann einfach nur file_exists() nutzen. Dann ist der Cache auch gar nicht nötig.

Ist jetzt nur die Frage, wie schnell das ist, wenn man bei zehn Autoren auf einer Seite einen Cache für die Beiträge und zehn Cache-Dateien für die Autoren einlesen muss. Dagegen stehen zwei DB-Abfragen (eine für die Beiträge und eine für die Autorendaten).

[bfs]

Wenn du die Userid als Deteinamen für das Avatar nimmst, kannst du es entweder nicht (browser-)cachen, oder du kriegst ebenfalls die Beschwerden der Nutzer. Dann halt, das sie ihr Avatar geändert haben, aber immernoch das alte sehen.

Im übrigen muss man das nicht lange cachen, keinesfalls Stunden. Wenn du 10 Requests die Minute auf dein Cache-Element hast, dann ist die Chance für einen Cache-Hit schon 90%, wenn du das Teil nur 1 Minute cachst. Jedes weitere Prozent Cache-Hits ist exponential steigendem Gemecker deiner Nutzer verbunden

[mgutt]

Wenn du die Userid als Deteinamen für das Avatar nimmst, kannst du es entweder nicht (browser-)cachen, oder du kriegst ebenfalls die Beschwerden der Nutzer. Dann halt, das sie ihr Avatar geändert haben, aber immernoch das alte sehen.

Da würde ich einfach "fuschen" und den Autor selbst bzw. seinen Browser dazu "zwingen" das Avatar neu zu laden. Entweder über einen extra Parm als Anker (keine Ahnung ob das geht) oder eben über die kurzfristige Deaktivierung des Browsercaches, nachdem der Autor das Bild hochgeladen hat.

Das die anderen Nutzer das Bild erst später sehen, dürfte soweit kein Problem sein. Die beschweren sich ja nicht, weil sie davon nichts wissen

Im übrigen muss man das nicht lange cachen, keinesfalls Stunden. Wenn du 10 Requests die Minute auf dein Cache-Element hast, dann ist die Chance für einen Cache-Hit schon 90%, wenn du das Teil nur 1 Minute cachst. Jedes weitere Prozent Cache-Hits ist exponential steigendem Gemecker deiner Nutzer verbunden

Das verstehe ich nicht. Du meinst wenn ich 10 Requests pro Minute hätte und mein Cache "nur" jede Minute aktualisiert wird, so wären die Datensätze in 90% der Fälle alt oder wie

EDIT:
Ich habe das übrigens noch mal geprüft. Die Hauptlast aller Datenbankabfragen resultiert aus Feeds und der Darstellung der "letzten Beiträge". Die rückwärtige Sortierung von Themen mag MySQL nicht, wenn ich mit mehreren Indexwerten arbeiten muss (in meinem Fall die Filterung nach HostID, ForumID und nach dem Status "keine Kopie"), so dass keine Filterung nach Index, sondern nach Range erfolgt. Ein Index über alle drei Spalten bringt übrigens nichts.

[bloddy newbie]

Hallo,

meine Erfahrungen:
- breite Tabellen vermeiden, sofern diese nicht als Detaildaten dienen und / oder sortiert werden müssen; breite Tabellen splitten (Such- und Sortierfelder in einer Tabelle halten, der Rest wo anders hin)
- kompliziertes Rechtekonstrukt vermeiden -> Overhead bei jeder Verbindung -> persistente Verbindungen helfen hier, falls nicht anders möglich
- Meta-Daten (Zähler etc.) nicht mittels JOIN COUNT(*) auslesen sondern als Spalte in META-Tabelle vorhalten
- Abfragen mittels EXPLAIN analysieren -> filesort oder temporary -> Abfrage umschreiben oder fehlende Indexe setzen
- Sortierung von großen Datenmengen nur bei geeigneten Indexen vornehmen, andernfalls ist die Sortierung im Script/Programm schneller; ideal sind natürlich gut gesetzte Indexe
- Subselects sind bei großen Datenmengen zu vermeiden (temporary)
- Optimierung der my.cnf (Cache)
- oft ist es besser, mehrere Abfragen zu tätigen anstatt einer komplexen -> aufdröseln

Das sind einige meiner Erfahrungen mit MySQL. Die meiste Performance ist mit dem Setzen guter Indexe sowie dem Umschreiben von Abfragen raus zu holen. Aber auch der Cache bringt sehr viel Performance.

EDIT:
Ich habe das übrigens noch mal geprüft. Die Hauptlast aller Datenbankabfragen resultiert aus Feeds und der Darstellung der "letzten Beiträge". Die rückwärtige Sortierung von Themen mag MySQL nicht, wenn ich mit mehreren Indexwerten arbeiten muss (in meinem Fall die Filterung nach HostID, ForumID und nach dem Status "keine Kopie"), so dass keine Filterung nach Index, sondern nach Range erfolgt. Ein Index über alle drei Spalten bringt übrigens nichts.

Wie schaut die Abfrage aus?

Grüße

[Synonym]

- oft ist es besser, mehrere Abfragen zu tätigen anstatt einer komplexen -> aufdröseln

Das ist ein Punkt den ich mir schon lange überlege. Hatte anfangs auch viele kleine Abfragen, die dann aber zu einer großen zusammengebaut. In Bezug auf den qCache scheinen mir aber durchaus mehrere kleine sinnvoller. Nur, kann man das auch irgendwie vernünftig testen und vergleichen?

[nerd]

- oft ist es besser, mehrere Abfragen zu tätigen anstatt einer komplexen -> aufdröseln

Das ist ein Punkt den ich mir schon lange überlege. Hatte anfangs auch viele kleine Abfragen, die dann aber zu einer großen zusammengebaut. In Bezug auf den qCache scheinen mir aber durchaus mehrere kleine sinnvoller. Nur, kann man das auch irgendwie vernünftig testen und vergleichen?

wuerde ich so nicht sagen - bei jeder abfrage gibts den overhead das die verbindung aufgebaut, abfrage an den datenbankserver gesendet, verarbeitet und das ergebniss empfangen werden muss. zudem sind rechenoperation in mysql ganz sicher schneller als in php ausgefuehrt.

ich baue lieber wenige "grosse" abfragen die mir alle relevanten daten liefern; da sieht man dann auch besser wo man was optimieren kann...

[mgutt]

zudem sind rechenoperation in mysql ganz sicher schneller als in php ausgefuehrt.

Ein weit verbreiteter Irrglaube in meinen Augen. MySQL ist so aufgebaut, dass sich Abfragen gegenseitig "stören". So kann eine Abfrage, die andere Blockieren (LOCK) oder eine Abfrage dauert länger, so dass die folgenden entsprechend später ausgeführt werden.

Daher sollte man nie Berechnungen von MySQL machen lassen. MySQL ist nur dann sinnvoll, wenn das Ergebnis so schnell ist, dass es kaum messbar ist (0.0001s).

[mgutt]

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).

Mir kam heute dazu eine Idee:
Ich setze bei 1:1000 Fällen einen COUNT() ab, der prüft, wie viele Zeilen ohne Limit resultieren würden. Also

Code: Alles auswählen

if (rand(0,999) == 999) {
  // COUNT()-Abfrage
  if (mysql_affected_rows() > 10) {
    // $last_time wird neu gesetzt (irgendwo in der Config = Cache)
  }
}

Bei mehr als 10 Zeilen, setze ich also $last_time neu. (es gibt auch Fälle, in denen sich der Wert dann nicht ändert, dann bleibt die alte $last_time erhalten).

Die DB-Abfrage wird dann erweitert:

Code: Alles auswählen

SELECT *
FROM topics
WHERE moved_id = 0
AND host_id = 1
AND last_time >= $last_time
ORDER BY last_time DESC
LIMIT 10

Mit der Zeit werden dann zwar immer mehr Zeilen abzuarbeiten sein, aber nach 1.000 Wiederholungen wird ja $last_time wieder zurückgesetzt und schon ist das Ergebnis wieder optimal.

Ich glaube sogar, dass mir das in 1:10000 Fällen reichen würde (und natürlich beim ersten Aufruf). Das müsste man aber simpel testen.

Andere Ideen?