Performance-Optimierungen in einem Forum

Beitrag von **xAaron** » 30.09.2009, 22:32

oh Fehler von meiner Seite: ich hab gesehen, dass mysql einen Query-cacher hat. Das heißt: die Abfragen kamen nicht aus dem Index, sondern aus dem cache(wäre sonst auch sehr, sehr schnell gewesen).
Teste das doch noch Mal und lösche nach jedem Test den query cache.

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 **lunetics** » 01.10.2009, 01:19

Durchgelesen und mal ein paar tips:

Querytests IMMER mit Select SQL_NO_CACHE * from...

Ansonsten tritt der eben genannte effekt auf (langsam - schnell - schnell).

Der Query Cache für ein Query ist nur so lange aktiv , solang keine änderungen an der Tabelle vorgenommen wurden! (d.h. SQL_NO_CACHE)
a
Brauchst also nicht wie von xAaron beschrieben jedes mal den Query cache zu löschen.

Innodb ist sinnvoll wenn es viele writes gibt.

Niemals Fulltext benutzen, wenn es ein wirklich grosses Forum ist und du eine Volltextsuche brauchst -> SOLR oder Sphinx.

Die grösste Steigerung sollte wohl z.b. memcached bringen, d.h. die Daten im objektcache (memcached) halten, sobald neuer Post -> invalidieren. Das würde auch komplett die Datenbank entlasten, da dort keine abfrage stattfindet. Eventuell eine Kombination von APC/xcache varcache (je nachdem wie dein system aufgebaut ist) und / oder memcached (falls mehrere webserver im einsatz sind)

Wobei allerdings bedacht werden soll dass memcached keine LÖSUNG zu einer schlecht designten Datenbank ist, sondern ein Optimierung für eine bereits "vernünftige" Datenbankstruktur!

Auch ist das config des Mysql servers sehr wichtig.

Mittels Maatkit solltest du z.b. herausfinden können, welche Queries genau die meiste last verursachen etc.pp.

Auf jeden fall ist das Thema Datenbankoptimierung /Queryoptimierung so komplex, dass es kaum in einen Thread passt

Beitrag von **mgutt** » 01.10.2009, 07:26

lunetics hat geschrieben:Durchgelesen und mal ein paar tips:

Querytests IMMER mit Select SQL_NO_CACHE * from...

Gut, dann werde ich mein Debugging gleich um die SQL_NO_CACHE-Variante erweitern. Dann sehe ich die Zeiten für den lokalen Filecache (falls vorhanden), der DB-Abfrage, Explain und eben ohne SQL-Cache. Das sollte dann zum Vergleich genügen.

Doch was ist, wenn eine Abfrage ohne SQL-Cache langsam ist. Heißt das, dass ich da noch optimieren muss?

Innodb ist sinnvoll wenn es viele writes gibt.

Ich habe sowohl alte als auch aktuelle Lektüre studiert und auch diesen Test unter anderem gefunden:
https://www.mysqlperformanceblog.com/20 ... ks-part-1/

Früher wurde immer gesagt, dass InnoDB langsamer sei als MyISAM. Diese Aussagen wurden aber in den aktuellen Berichten revidiert. Mittlerweile empfiehlt eigentlich jeder InnoDB, auch wenn man keine Transaktionen oder Fremdschlüssel benötigt.

Ich denke ich werde daher bald auf InnoDB wechseln.

Niemals Fulltext benutzen, wenn es ein wirklich grosses Forum ist und du eine Volltextsuche brauchst -> SOLR oder Sphinx.

Aktuell ist die Volltextsuche noch recht performant. Wobei das ähnlich abläuft wie zuvor. Wenn die DB entlastet ist, dann dauert eine Volltextsuche ca. 2-3 Sekunden. Bei Auslastung kann das schon mal 30 Sekunden dauern. Aber: Ich werfe die Ergebnisse in der Regel über einen Cache aus. Das kann ich machen, weil bei mir die Sortierung priorisiert nach Genauigkeit abläuft. Dieser Cache bleibt 30 Tage lang stabil. Ich könnte vermutlich auch 90 Tage draus machen. Die User haben es bis heute nicht gemerkt. Übrigens kann ich das jedem Forumbetreiber nur empfehlen. Bei der Suche nach Genauigkeit kommen auch ältere Themen zum Vorschein, so dass doppelte Themen reduziert werden

Bei Sphinx ist z.B. das ja auch der Nachteil, so viel ich weiß. Die Daten sind schnell ausgelesen, aber langsam drin. Das wäre insofern problematisch, als das die Sortierung nach Aktualität dann nicht mehr funktionieren würde (die habe auch mal gecached, da waren die Nutzer gar nicht gut drauf zu sprechen, da Ihre Kleinanzeigen im Marktplatz nicht mehr gefunden werden konnten

).

Die grösste Steigerung sollte wohl z.b. memcached bringen, d.h. die Daten im objektcache (memcached) halten, sobald neuer Post -> invalidieren. Das würde auch komplett die Datenbank entlasten, da dort keine abfrage stattfindet. Eventuell eine Kombination von APC/xcache varcache (je nachdem wie dein system aufgebaut ist) und / oder memcached (falls mehrere webserver im einsatz sind)

Auf Grund der Kompatibilität wurde xCache schon vor längerer Zeit installiert. Dieser hat eine ganze Menge gebracht.

memcached kann mir insofern nicht helfen, als dass ich Ergebnisse aller Abfragen, die sich wiederholen, in einem lokalen Filecache stehen habe. Klar würde das Lesen aus dem Cache eine maginale Verbesserung herbeiführen, allerdings steigere ich damit nicht die dynamischen Query-Results, die ja im Moment die größte Last resultieren. Ich glaube eher, dass ich einen nachteiligen Effekt damit bekäme, da der Arbeitsspeicher dann wiederrum für andere Operationen fehlt.

Auch ist das config des Mysql servers sehr wichtig.

Die überlasse ich den Technikern von All-Inkl.com

Mittels Maatkit solltest du z.b. herausfinden können, welche Queries genau die meiste last verursachen etc.pp.

Aktuell kann ich nur nach Ausführungszeit gehen, da ich keinen Zugriff auf die Kommandozeile besitze. Solange ich das aber nicht ausgereizt habe, sollte das auch nicht nötig sein.

Auf jeden fall ist das Thema Datenbankoptimierung /Queryoptimierung so komplex, dass es kaum in einen Thread passt

Die eine oder andere Sache hat mir schon geholfen. Das Problem ist, dass wenig Seiten im Netz Schlagworte aufführen, wie sie xAaron in seinem ersten Beitrag aufgeführt hatte. Dank so einer Liste, kann man zumindest mal recherchieren, was für einen selbst sinnvoll ist.

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 **bfs** » 01.10.2009, 08:52

mgutt hat geschrieben: Doch was ist, wenn eine Abfrage ohne SQL-Cache langsam ist. Heißt das, dass ich da noch optimieren muss?

Nicht unbedingt. Wenn der Query ein Read/Write Verhältnis deutlich zugunsten Read hat und der Query oft genug abgefeuert wird, das er im Query Cache kann das reichen. Taucht der Query denn im Slow Log auf, wenn du das auf Queries >= 1s konfigurierst?

Mittlerweile empfiehlt eigentlich jeder InnoDB, auch wenn man keine Transaktionen oder Fremdschlüssel benötigt.

Unter Low Memory Conditions hat MyIsam noch Vorteile, aber das ist bei aktuellen Rechnern tatsächlich kaum noch der Fall. Bei mir hat der Umstieg einer ca. 18 GB grossen Datenbank von MyIsam auf InnoDB auf einem Rechner mit 16 GB Speicher (12 davon für InndoDB) einen Geschwindigkeitsgewinn von mehr als 50% gebracht.

Ich denke ich werde daher bald auf InnoDB wechseln.

Vorsicht, da gibts einige Fallstricke. Einfache Queries der Form "SELECT count(*) FROM table" (also ohne WHERE Clause) dauern plötzlich unendlich lange da InnoDB hier immer einen Fulltable Scan machen muss. Du solltest also sicherstellen, dass du derartige Sachen nicht mehr Code hast. Auch bei Indizes gibts ein paar Unterschiede, du solltest aufpassen das du die Primary Keys nicht nochmal in einem Index drin hast. Alles in allem: vor dem Umstieg gut Testen, vor allem eben einen umfangreichen Lasttest machen.

[...] Fulltext [...]

Dazu wurde bereits alles gesagt. Relationale Datenbanken sind nicht für Volltextsuche gebaut, das ist ein echtes No-Go.

[...] memcached [...]

Hilft eigentlich immer wenn du PHP benutzt. Andere Spachen/Frameworks wie z.b. Java/J2EE haben hier einen Applicationscope, in dem sie Requestübergreifend Objekte im Hauptspeicher halten können. Aber am meisten hilft memcached eben dann, wenn du mehr als einen Renderserver verwendest. Je mehr du davon im Einsatz hast, um so grösser ist der Effekt eines memcached-servers/clusters.

Auch ist das config des Mysql servers sehr wichtig.
Die überlasse ich den Technikern von All-Inkl.com

Naja, die haben sicher ihre 0815 Konfiguration. Mindestens wenn du tatsächlich auf InnoDB umsteigen willst solltest du kontrollieren ob der Server dafür gut eingerichtet ist. Wenn er 8 GB Speicher hat, aber InnoDB davon nur 512MB bekommt, dann wirst du sonst hier im Forum bald berichten können, wie langsam InndoDB ist

Beitrag von **bloddy newbie** » 01.10.2009, 08:53

mgutt hat geschrieben:
Innodb ist sinnvoll wenn es viele writes gibt.
Ich habe sowohl alte als auch aktuelle Lektüre studiert und auch diesen Test unter anderem gefunden:
https://www.mysqlperformanceblog.com/20 ... ks-part-1/

Früher wurde immer gesagt, dass InnoDB langsamer sei als MyISAM. Diese Aussagen wurden aber in den aktuellen Berichten revidiert. Mittlerweile empfiehlt eigentlich jeder InnoDB, auch wenn man keine Transaktionen oder Fremdschlüssel benötigt.

Ich denke ich werde daher bald auf InnoDB wechseln.

Ich würde dir empfehlen, vor dem Wechsel der Storage Engine ausgiebige Tests zu fahren. InnoDB verhält sich in vielen Dingen anders als MyIsam. Dem von dir verlinkten Test würde ich nur bedingt Aufmerksamkeit schenken, da in der Realität die Abfragen doch um einiges komplexer sind. Es lässt sich definitiv nicht pauschalisieren, dass InnoDb schneller ist als MyIsam.

mgutt hat geschrieben:
Auch ist das config des Mysql servers sehr wichtig.
Die überlasse ich den Technikern von All-Inkl.com

Überlege dir das noch einmal. Unter Umständen ist der Server noch mit den Standard Einstellungen konfiguriert. Diese Einstellungen sind tatsächlich entscheidend, was die Performance der DB angeht. 3-4 Einstellungen erzielen oftmals eine ungeahnte Wirkung. Es reicht aus, den Technikern mitzuteilen, dass sie beispielsweise den Query Cache (query_cache_size) von xxxx auf yyyy anheben möchten - das sollte keine Probleme verursachen.

In PHPMyAdmin "Laufzeit-Informationen" kannst du sehr gut sehen, warum deine Datenbank oftmals klemmt. Hier erhältst du auch weiche Tipps zur Optimierung.

Grüße BN

Beitrag von **bloddy newbie** » 01.10.2009, 09:00

Wir projektieren aktuell eine 60GB Datenbank zu großen Teilen auf InnoDB umzustellen. Mich würde daher interessieren, wie viele gleichzeitige Benutzer in deinem Fall auf die Datenbank zugreifen (ungefähr)? Eine Replikation wurde bereits auf InnoDB umgestellt und hier hatten wir das Problem, dass die Indexe zu gelaufen sind. Wie auch immer das passieren konnte...

bfs hat geschrieben: Unter Low Memory Conditions hat MyIsam noch Vorteile, aber das ist bei aktuellen Rechnern tatsächlich kaum noch der Fall. Bei mir hat der Umstieg einer ca. 18 GB grossen Datenbank von MyIsam auf InnoDB auf einem Rechner mit 16 GB Speicher (12 davon für InndoDB) einen Geschwindigkeitsgewinn von mehr als 50% gebracht.

Beitrag von **mgutt** » 01.10.2009, 09:22

bfs hat geschrieben:Nicht unbedingt. Wenn der Query ein Read/Write Verhältnis deutlich zugunsten Read hat und der Query oft genug abgefeuert wird, das er im Query Cache kann das reichen. Taucht der Query denn im Slow Log auf, wenn du das auf Queries >= 1s konfigurierst?

Wenn ich die aktiv hätte, würden sie da auftauchen. Die Slow Logs sind wenig aussagekräftig, da sie eben nur nach Zeit auswerfen und nicht die aktuelle Performance berücksichtigen. In dem Bereich, wo ich jetzt optimiere kommen daher sogar die schnellsten Abfragen mal in die Slow Logs. Das bringt also nicht wirklich was.

Bei mir hat der Umstieg einer ca. 18 GB grossen Datenbank von MyIsam auf InnoDB auf einem Rechner mit 16 GB Speicher (12 davon für InndoDB) einen Geschwindigkeitsgewinn von mehr als 50% gebracht.

Das hört sich verlockend an

Vorsicht, da gibts einige Fallstricke. Einfache Queries der Form "SELECT count(*) FROM table" (also ohne WHERE Clause) dauern plötzlich unendlich lange da InnoDB hier immer einen Fulltable Scan machen muss.

Gibt es eine Seite, wo man sich über sowas informieren kann?

Ich wollte etappenweise vorgehen und bestehende tables kopieren und dann nach und nach die Config auf diese neuen tables umstellen.

Allerdings wäre es schon von Vorteil, wenn ich gleich die "Fehler" ausmerze und solche typischen Probleme ausschließen könnte.

Auch bei Indizes gibts ein paar Unterschiede, du solltest aufpassen das du die Primary Keys nicht nochmal in einem Index drin hast.

D.h. es darf in keinem Fall ein Index ein Primary Key enthalten? Auch nicht, wenn ich einen mehrspaltigen Index habe?

[...] Fulltext [...]
Dazu wurde bereits alles gesagt. Relationale Datenbanken sind nicht für Volltextsuche gebaut, das ist ein echtes No-Go.

Also ist Deine Empfehlung ebenfalls Sphinx?

[...] memcached [...]
Hilft eigentlich immer wenn du PHP benutzt. Andere Spachen/Frameworks wie z.b. Java/J2EE haben hier einen Applicationscope, in dem sie Requestübergreifend Objekte im Hauptspeicher halten können. Aber am meisten hilft memcached eben dann, wenn du mehr als einen Renderserver verwendest. Je mehr du davon im Einsatz hast, um so grösser ist der Effekt eines memcached-servers/clusters.

Vielleicht missverstehe ich die Einsatzmöglichkeit von memcached. Ich dachte jetzt daran bestimmte SQL-Results im Arbeitsspeicher als PHP-Array bereitzuhalten oder geht es bei memcached noch um mehr wie z.B. Templates im Arbeitsspeicher zu halten oder wie?

Naja, die haben sicher ihre 0815 Konfiguration. Mindestens wenn du tatsächlich auf InnoDB umsteigen willst solltest du kontrollieren ob der Server dafür gut eingerichtet ist. Wenn er 8 GB Speicher hat, aber InnoDB davon nur 512MB bekommt, dann wirst du sonst hier im Forum bald berichten können, wie langsam InndoDB ist

Ich denke ich kann schon darauf vertrauen, dass ein DB-Server der explizit als solcher eingekauft wird, entsprechend optimiert wurde (also RAM nahezu 100% MySQL zugeordnet wurde). Später muss ich allerdings aktiv um eine weitere Optimierung bitten. Das stimmt natürlich. Aber das habe ich in der Vergangenheit schon öfter gemacht und es wurde auch immer was getan. Allerdings habe ich noch nie Optimierungen in der MySQL-Config "gespürt". Da merke ich mehr, wenn ich eine Abfrage völlig dezimiere oder cache.

In der Vergangenheit hat nur folgendes spürbar was gebracht:
- Cache statt DB-Abfrage
- Template-Caching und Filterung überflüssiger Zeichen
- xCache
- besserer Server

Eine weitere Optimierung von mir bezüglich von Hitcountern, kann ich auch nur jedem empfehlen:

Code: Alles auswählen

function update_rand&#40;&#41; &#123;
	switch &#40;mt_rand&#40;2, 5&#41;&#41; &#123;
		case 2&#58;
			if &#40;mt_rand&#40;1, 2&#41; == 2&#41; &#123;
				return 2;
			&#125;
			break;
		case 3&#58;
			if &#40;mt_rand&#40;1, 3&#41; == 3&#41; &#123;
				return 3;
			&#125;
			break;
		case 4&#58;
			if &#40;mt_rand&#40;1, 4&#41; == 4&#41; &#123;
				return 4;
			&#125;
			break;
		case 5&#58;
			if &#40;mt_rand&#40;1, 5&#41; == 5&#41; &#123;
				return 5;
			&#125;
			break;
	&#125;
	return false;
&#125;
// increment the number of views
if &#40;$views = update_rand&#40;&#41;&#41; &#123;
	$sql = 'UPDATE LOW_PRIORITY topics
				SET views = views + ' . intval&#40;$views&#41; . '
				WHERE topic = ' . intval&#40;$topic&#41;;
	$db->sql_query&#40;$sql, false, __LINE__, __FILE__&#41;;
&#125;

Damit werden Hitcounter nur per Zufall aktualisiert, bleiben aber trotzdem korrekt (mit steigender Hitzahl wird das Ergebnis genauer).

Beitrag von **nerd** » 01.10.2009, 09:56

um hier auch nochmal ungefragt vorschlaege einzuwerfen:

welche engine die beste ist, kommt auf deinen verwendungszweck an, und un welchem verhaeltniss du reads/writes erwartest. hab da auf dem anderen komputer eine gute zusammenfassung in den bookmarks (komme ich aber gerade nicht ran).

je nachdem wie du deine deinen index einrichtest, koennen count() anfragen recht of auch aus dem index beantwortet werden, ohne das zugriff auf den table erfolgen muss. wenn du z.b. eine spalte "artikel_vorrat" hast, und darauf einen index anlegst sollte eine abfrage "select count(artikel_vorrat) from artikel where artikel_vorrat = 1" ohne tablescan auskommen.

volltextsuche in mysql ist moppelkotze - such mal nach 'pre-index' als schnelle und flexiblere alternative. im prinzip zerlegst du dein volltext-feld in die einzelnen woerter und speicherst dann wort und zugehoerige id (und eventuell noch wie oft das wort in dem artikel vorkommt) in einer weiteren tabelle und findest darueber raus welche id fuer deine suchabfrage relevant ist. dauert ein bischen laenger beim anlegen des indexes, aber alle suchabfragen sind dann deutlich schneller.

Beitrag von **bfs** » 01.10.2009, 10:06

bloddy newbie hat geschrieben:Wir projektieren aktuell eine 60GB Datenbank zu großen Teilen auf InnoDB umzustellen. Mich würde daher interessieren, wie viele gleichzeitige Benutzer in deinem Fall auf die Datenbank zugreifen (ungefähr)? Eine Replikation wurde bereits auf InnoDB umgestellt und hier hatten wir das Problem, dass die Indexe zu gelaufen sind. Wie auch immer das passieren konnte...

Der Server läuft mit max_connections=120 und bedient 6 Renderserver, die jeweils bis zu 20 persistente/gepoolte Connections aktiv haben. In Spitzenbelastungszeiten werden davon vielleicht 80 tatsächlich Traffic haben, aber das ist grob geschätzt. Die Read/Write Ratio liegt über alle Queries bei ca. 1:100, also recht typisch für Foren.

Beitrag von **Synonym** » 01.10.2009, 10:19

im prinzip zerlegst du dein volltext-feld in die einzelnen woerter und speicherst dann wort und zugehoerige id (und eventuell noch wie oft das wort in dem artikel vorkommt) in einer weiteren tabelle

Wären da zwei Tabellen nicht besser?

Eine mit
wort_id wort

und eine mit
wort_id artikel_id

Wenn alles in einer Tabelle ist wird die nur unnötig groß. Das Suchen verlangsamt sich auch, da in der Regel ja nach einem Wort (String) gesucht wird, der dann auch noch mehrmals vorhanden sein kann.

Nimmt man zwei Tabellen, dann ist das Wort unique. So kann man schon vorher die "kleine" Wortdatenbank abfragen ob es den Suchbegriff überhaupt gibt und wenn ja, dann die Trefferdatenbank nach den entsprechenden Artikeln befragen.

Oder liege ich da nun ganz falsch? Habe das nämlich so bei mir laufen.

Beitrag von **bfs** » 01.10.2009, 10:27

mgutt hat geschrieben: Wenn ich die aktiv hätte, würden sie da auftauchen. Die Slow Logs sind wenig aussagekräftig, da sie eben nur nach Zeit auswerfen und nicht die aktuelle Performance berücksichtigen. In dem Bereich, wo ich jetzt optimiere kommen daher sogar die schnellsten Abfragen mal in die Slow Logs. Das bringt also nicht wirklich was.

Ich hab immer ein slow log mit >= 3 sec mitlaufen. du hast recht, wenn die load auf der db gross ist, tauchen da auch "normale" Queries auf, aber meist findet man unter den aufgelisten Queries trotzdem den Verursacher. So kann man einfach eine gewisse Hygiene halten.

mgutt hat geschrieben:Gibt es eine Seite, wo man sich über sowas informieren kann?

Kenne ich jetzt nicht, würde ich aber dazu sind wir ja alle Google Experten

D.h. es darf in keinem Fall ein Index ein Primary Key enthalten? Auch nicht, wenn ich einen mehrspaltigen Index habe?

Na du musst einfach nur bedenken, das inndob an JEDEN Index immer als letzten Wert den PK anhängt. Deswegen solltest du bei InnoDB auch lange PK's vermeiden, weil sie deine Indizes aufblähen.

Also ist Deine Empfehlung ebenfalls Sphinx?

Ich kenne Sphinx nicht gut genug um da wirklich eine saubere Empfehlung abgeben zu können. Ich selbst setze direkt auf einer Lucene-basierten Eigenimplementierung auf und das ist nunmal das Such-Framework #1. Da SOLR auch Lucene-basiert ist, würde ich wohl eher dazu raten.

Vielleicht missverstehe ich die Einsatzmöglichkeit von memcached. Ich dachte jetzt daran bestimmte SQL-Results im Arbeitsspeicher als PHP-Array bereitzuhalten oder geht es bei memcached noch um mehr wie z.B. Templates im Arbeitsspeicher zu halten oder wie?

Memcached cached Objekte, also prinzipiell kannst du dort alles vorrätig halten, sogar Binärdaten. Aber am meisten Sinn aus Aufwand/Nutzen-Sicht machen sicher Query-Ergebnisse oder eben auch HTML Schnipsel.

Beitrag von **mgutt** » 01.10.2009, 11:44

nerd hat geschrieben:such mal nach 'pre-index' als schnelle und flexiblere alternative.

Habe ich und bin prompt auf Abakus gestoßen, sonst aber nichts wirklich informatives gefunden

Ich weiß aber was Du meinst.

im prinzip zerlegst du dein volltext-feld in die einzelnen woerter und speicherst dann wort und zugehoerige id (und eventuell noch wie oft das wort in dem artikel vorkommt) in einer weiteren tabelle und findest darueber raus welche id fuer deine suchabfrage relevant ist. dauert ein bischen laenger beim anlegen des indexes, aber alle suchabfragen sind dann deutlich schneller.

Das halte ich für ein Gerücht. Dieses System war damals in phpBB2 Standard und es ist wird immer langsamer, umso mehr Beiträge man hat. Gerade das Hinzufügen von Beiträgen wird dann zu einer Geduldsprobe. Damit das dann halbwegs performant abläuft filterte phpBB2 common-Wörter aus und bestimmte Inhalte wie Links wurden auch ignoriert, aber unter dem Strich war das System sehr langsam.

Das liegt einfach daran, dass man jedes Wort erstmal in der DB prüfen muss. D.h. je Wort ein SELECT, um die ID zu ermitteln. Dann in einem weiteren table die WordIDs und die PostIDs verknüpfen usw.

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.

Da ist jede MATCH()-Abfrage ohne "IN BOOLEAN MODE" schneller, da es mit Stoppwortliste und der 50% Common-Regel genauso "schlechte" Ergebnisse liefert. Zumindest nach meiner Erfahrung.

Ein weiterer Nachteil an diesem System ist die mangelnde Optimierungsmöglichkeit. Sobald man nur einen Filter ändert, muss man den kompletten Index neu aufbauen. Ich habe sowas mal in einem größeren Board hinter mich gebracht. 3 Tage hat das gebraucht

Es hat sicher auch seinen Grund, dass phpBB3, vBulletin und WBB mittlerweile alle zu Fulltext übergegangen sind. Wenn der integrierte Index jeweils besser wäre, würden sie es gar nicht erst umsetzen.

Übrigens macht mysql fulltext nichts anderes als das was Du hier selber nachbauen willst. Nur mysql arbeitet mit weniger Filtern, so dass man seine Abfrage entsprechend gut vorbereiten muss.

Ich für meinen Teil finde 2-3 Sekunden eigentlich gut, wenn ich bedenke, dass ich sogar 3-stellige Wörter als Suchwort dulde.

Und meine Abfrage ist "untypisch", da sie komplexer ist und dadurch den Titel doppelt gewichtet:

Code: Alles auswählen

AND MATCH&#40;pt.post_subject, pt.post_text&#41; AGAINST&#40;'+honda +civic' IN BOOLEAN MODE&#41;
ORDER BY &#40;MATCH&#40;pt.post_subject&#41; AGAINST&#40;'+honda +civic' IN BOOLEAN MODE&#41;*2+MATCH&#40;pt.post_text&#41; AGAINST&#40;'+honda +civic' IN BOOLEAN MODE&#41;&#41; DESC
LIMIT 1000

Laut MySQL soll man das nicht machen, da ich nicht exakt mit dem zweispaltigen Index übereinstimme, allerdings ist es anders nicht möglich gute Ergebnisse zu erhalten.

bfs hat geschrieben:Na du musst einfach nur bedenken, das inndob an JEDEN Index immer als letzten Wert den PK anhängt. Deswegen solltest du bei InnoDB auch lange PK's vermeiden, weil sie deine Indizes aufblähen.

Was meinst Du mit "lang"?

Beitrag von **nerd** » 01.10.2009, 13:01

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

na und? dazu machst du einen index auf das suchwort. selbst bei 16.8 millionen zeilen brauchst du damit im schlimmsten falle nur 24 zugriffe um jeden beliebigen wert zu finden. eventuell kanns auch schneller sein nur die ersten 3, 4 oder 5 zeichen jedes wortes zu indizieren.
die stopwoerter wuerde ich nicht mit in die tabelle aufnehmen, das spart auch ne menge platz.

Beitrag von **xAaron** » 01.10.2009, 13:11

nerd hat geschrieben:
mgutt 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.
na und? dazu machst du einen index auf das suchwort. selbst bei 16.8 millionen zeilen brauchst du damit im schlimmsten falle nur 24 zugriffe um jeden beliebigen wert zu finden. eventuell kanns auch schneller sein nur die ersten 3, 4 oder 5 zeichen jedes wortes zu indizieren.
die stopwoerter wuerde ich nicht mit in die tabelle aufnehmen, das spart auch ne menge platz.

Für so einen Wort-Index würde ich einen Keyword-Tree implementieren. Wundert mich eigentlich, dass ein Fulltext-Index von Mysql nicht so aufgebaut ist.

Beitrag von **mgutt** » 01.10.2009, 13:45

nerd hat geschrieben:
mgutt 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.
na und? dazu machst du einen index auf das suchwort. selbst bei 16.8 millionen zeilen brauchst du damit im schlimmsten falle nur 24 zugriffe um jeden beliebigen wert zu finden. eventuell kanns auch schneller sein nur die ersten 3, 4 oder 5 zeichen jedes wortes zu indizieren.
die stopwoerter wuerde ich nicht mit in die tabelle aufnehmen, das spart auch ne menge platz.

Das mit der Limitierung der Indexlänge habe ich auch ausprobiert. Das bringt kaum was im Vergleich. Und am Ende steht trotzdem noch das Hinzufügen der Daten. Und wehe jemand sendet mal einen langen Text.

Und egal wie performant man das ganze aufbaut, Du bildest nur den fulltext-Index von MySQL nach, da es das "gleiche" Grundgerüst ist.

Allerdings hat fulltext eine wesentliche Funktion, die es überhaupt so "langsam" macht und das ist die Sortierung nach Relevanz. Und die musst Du erstmal mit einem eigenen System performant nachbauen.

In meinen Augen kann es gar nicht möglich sein MySQL auf diese Art zu schlagen. Man kann wenn überhaupt nur eine abgespeckte Variante realisieren.

Ansonsten empfehle ich mal diese Präsentation:
https://www.mysqlperformanceblog.com/fi ... Search.pdf

Allerdings ist die auch schon was in die Jahre gekommen