& (DBI::) & (mySQL): Performance-Messung

[Michael Schröpl]

Hallo Leute,

ich bin derzeit dabei, abschließendes Feintuning für eine Anwendung zu betreiben. Diese ist in Perl geschrieben und spricht über die DBI::-Schnittstelle eine mySQL-Datenbank an, wobei sie pro Aufruf des Perl(CGI)-Programms zwischen 5 und ca. 30 SQL-Statements abfeuert (nur SELECT, teilweise mit CREATE TEMPORARY TABLE zur Simulation von Sub-Selects).

Die Perl-Anwendung selbst kann ich schon ganz ordentlich benchmarken - die Ergebnisse der Funktion times() reichen mir dafür aus.
("use strict;" etc. sind basierend auf diesen Messungen inzwischen durch etwas Kürzeres abgelöst worden, weil die Handvoll Standard-Modul-Einbindungen schon 50% der Laufzeit des Perl-Skripts ausmachten ...)

Allerdings mißt "times()" nur die CPU-Zeiten (user und system) des Prozesses selbst und seiner Kindprozesse. mySQL selbst ist jedoch kein Kindprozeß - mein Programm macht zwar über die DBI::-Schnittstelle eine Verbindung zu mySQL auf, aber der wesentliche CPU-Zeit-Vebrauch findet innerhalb des mySQL-daemon-Prozesses statt (ich kann mit "top" zusehen - meine eigene Anwendung verbraucht praktisch keine Zeit, der mySQL-Prozeß geht aber kurzfristig steil nach oben).

Nun würde ich gerne innerhalb meiner Perl-Anwendung auch die durch meine SQL-Statements verbrauchte Zeit irgendwie sinnvoll mit erfassen (um sie beispielsweise im generierten HTML-Dokument anzuzeigen). Außerdem würde ich überhaupt erst mal vernünftige Werte sehen wollen - bisher kann ich nur grob schätzen, welcher Aufruf wieviel kostet.
Da ich zudem im Tabellen-Design schon Tuning-Elemente drin habe, bei denen ich ggf. noch an ein paar Schräubchen drehen könnte, wenn ich wüßte, was das genau bewirkt, würden mich exakte Werte für bestimmte Aufrufe ebenso interessieren wie eine Art "Hitparade" über alle durchgeführten Progammläufe. Ich glaube zwar die "schnellen" und die "langsamen" Abfragen zu kennen, aber die Faktoren, um welche sich beide voneinander unterscheiden, kenne ich nur sehr ungefähr.

Mein Programm könnte zwar die abgefeuerten SQL-Statements in eine Datei mitprotokollieren und ich könnte diese Datei über die Kommandozeile an "mysql" übergeben und dort Zeitmessung betreiben, aber ich habe die Erfahrung gemacht, daß mySQL dabei doch irgendwas cached und dasselbe Statement bei wiederholter Ausführung schneller wird als beim ersten Zugriff - welcher im realen Einsatz aber der interessante ist.

Mich würde deshalb ganz allgemein interessieren, aus welcher Richtung Ihr an mein Problem einer sinnvollen Zeitmessung meines Programms herangehen würdet.
Links auf entsprechende Dokumentationen würden mir ebenso weiter helfen wie ganz allgemeine Ideen und Diskussionsbeiträge.

Viele Grüße
      Michael

[Cheatah]

Hi,

Die Perl-Anwendung selbst kann ich schon ganz ordentlich benchmarken - die Ergebnisse der Funktion times() reichen mir dafür aus.

Du kennst aber perldoc Benchmark?

mySQL selbst ist jedoch kein Kindprozeß

Richtig. MySQL ist ein Server, der von Perl aus angesprochen wird - und genau wie bei einem HTTP-Server hast Du vom Client (Deinem Script) aus keine Chance, die Roundtrip-Zeit con der Arbeitszeit zu trennen, geschweige denn noch detailliertere Werte zu erhalten, solange der Server diese Werte nicht selbst liefert.

der mySQL-Prozeß geht aber kurzfristig steil nach oben).

Das ist ja auch nicht schlimm. Wenn der Rechner diese Peaks verkraftet, dürfen sie ruhig auftauchen - und eine kurze Load-Steigerung von 0.3 auf 3 oder so ist nun wirklich nichts erschreckendes :-)

Nun würde ich gerne innerhalb meiner Perl-Anwendung auch die durch meine SQL-Statements verbrauchte Zeit irgendwie sinnvoll mit erfassen (um sie beispielsweise im generierten HTML-Dokument anzuzeigen).

Wie gesagt: Das wird schwierig. Mir ist nicht bekannt, dass MySQL solche Werte ausliefern würde.

Außerdem würde ich überhaupt erst mal vernünftige Werte sehen wollen - bisher kann ich nur grob schätzen, welcher Aufruf wieviel kostet.

Dazu kannst Du Deine Statements mit "EXPLAIN SELECT ..." testen; zumindest erfährst Du damit die Vorgehensweise des Servers. Ansonsten sollte in der Regel der Roundtrip am meisten Zeit verbraten - reduziere also die Anzahl der ausgeführten Statements, arbeite so gut es geht mit Joins, hole so viele Werte wie möglich (und nötig!) auf einmal.

Ich glaube zwar die "schnellen" und die "langsamen" Abfragen zu kennen, aber die Faktoren, um welche sich beide voneinander unterscheiden, kenne ich nur sehr ungefähr.

Vielleicht kann Dir da jemand mit Erfahrung zur Seite stehen. Beschreibe das DB-Layout (inklusive Indizes - und Statements, denn auch die gehören dazu!) und die vorhandenen Datenmengen, Änderungsraten etc.

daß mySQL dabei doch irgendwas cached und dasselbe Statement bei wiederholter Ausführung schneller wird als beim ersten Zugriff - welcher im realen Einsatz aber der interessante ist.

Nein; idealerweise sollte der Cache verwendet werden, denn dazu ist er da. Wenn das bei Dir die Ausnahme ist, könnte es ein Problem bei Deinen Statements geben.

Cheatah

[Michael Schröpl]

Hallo Cheatah,

Die Perl-Anwendung selbst kann ich schon ganz ordentlich benchmarken - die Ergebnisse der Funktion times() reichen mir dafür aus. Du kennst aber perldoc Benchmark?

nicht im Detail - würde ich die mySQL-Zeiten dabei mit erfassen können, dann wäre genau jetzt der Zeitpunkt, mich dort einzulesen.

Richtig. MySQL ist ein Server, der von Perl aus angesprochen wird - und genau wie bei einem HTTP-Server hast Du vom Client (Deinem Script) aus keine Chance, die Roundtrip-Zeit von der Arbeitszeit zu trennen, geschweige denn noch detailliertere Werte zu erhalten, solange der Server diese Werte nicht selbst liefert.

Das habe ich befürchtet - danke.

der mySQL-Prozeß geht aber kurzfristig steil nach oben). Das ist ja auch nicht schlimm. Wenn der Rechner diese Peaks verkraftet, dürfen sie ruhig auftauchen - und eine kurze Load- Steigerung von 0.3 auf 3 oder so ist nun wirklich nichts erschreckendes :-)

Ja - aber die Information ist zu ungenau, um sie auf mehrere tausend auf dem Server arbeitende Kunden sinnvoll hochzurechnen.

Außerdem würde ich überhaupt erst mal vernünftige Werte sehen wollen - bisher kann ich nur grob schätzen, welcher Aufruf wieviel kostet. Dazu kannst Du Deine Statements mit "EXPLAIN SELECT ..." testen; zumindest erfährst Du damit die Vorgehensweise des Servers.

Das habe ich schon hinter mir. Tabellen und Indexe stehen im Wesentlichen; mir großem Interesse habe ich allerdings die Aussagen von Sven Rautenberg über die unterschied- liche Auswertung diverser JOINs in mySQL gelesen, da werde ich noch ein paar Experimente machen müssen. (Als Ex-Oracle-Anwender kenne ich halt vor allem den Trivial-JOIN ... und der scheint hier nicht unbedingt der Königsweg zu sein.)

daß mySQL dabei doch irgendwas cached und dasselbe Statement bei wiederholter Ausführung schneller wird als beim ersten Zugriff - welcher im realen Einsatz aber der interessante ist. Nein; idealerweise sollte der Cache verwendet werden, denn dazu ist er da. Wenn das bei Dir die Ausnahme ist, könnte es ein Problem bei Deinen Statements geben.

Ich ziehe diesen Punkt mal vor ...

Meine Anwendung ist im Wesentlichen eine Art Suchmaschine. Wenn ich dieselbe Suche nach demselben Suchbegriff zweimal hinter- einander durchführe, dann wird da sicherlich etwas in einem mySQL- Cache gehalten werden können. Im realen Einsatz habe ich aber überwiegend verschiedene Suchbegriffe, so daß die exakte Anforderung erstens nicht identisch ist und zweitens ganz andere Datenmengen berechnet werden. Und in meinem Fall kommt noch dazu, daß die konkreten SQL-Statements sich von Anforderung zu Anforderung heftig unterscheiden können - der SQL-Code dafür besteht aus etwa 5 bis 20 SELECT-Statements, deren genauer Inhalt aus etwa 10 CGI-Parametern dynamisch berechnet wird.

Ansonsten sollte in der Regel der Roundtrip am meisten Zeit verbraten - reduziere also die Anzahl der ausgeführten Statements, arbeite so gut es geht mit Joins, hole so viele Werte wie möglich (und nötig!) auf einmal.

Genau um das vernünftig messen zu können, fehlt mir ein wenig die Infrastruktur. Es sind einfach zu viele und zu vielfältige Statements, um jedes einzelne davon manuell in mysql durchzutesten - gerade weil jede einzelne Abfrage eine variable und selbst für mich kaum vorher- sehbare Menge an SQL-Code generiert.

Ich glaube zwar die "schnellen" und die "langsamen" Abfragen zu kennen, aber die Faktoren, um welche sich beide voneinander unterscheiden, kenne ich nur sehr ungefähr. Vielleicht kann Dir da jemand mit Erfahrung zur Seite stehen.

Was SQL angeht, glaube ich die selbst zu haben - bei mySQL sieht es nicht so gut aus.

Beschreibe das DB-Layout (inklusive Indizes - und Statements, denn auch die gehören dazu!) und die vorhandenen Datenmengen, Änderungsraten etc.

Ich fange mal mit einer sehr abstrakten Beschreibung an, um das Prinzip klar zu machen. Konkreten SQL-Code liefere ich nach, wenn er erforderlich ist.

Ich habe eine Tabelle mit Nachrichten. (Sechs- bis siebenstellig viele, es könnte ein Gigabyte werden, vielleicht sogar mehr.) Der Inhalt und die Headline der Nachricht sind jeweils Spalten (TEXT, < 64KB) meiner "Tabelle" (stell Dir erst mal vor, es sei nur eine - das wird später nicht mehr ganz wahr sein ...), und über diesen beiden Spalten liegt ein FULLTEXT-Index, der über diese Texte automatisch einen Worte-Baum erzeugt (mySQL-feature).

Änderungsraten sind kein Thema - die Datenversorgung zuckelt im Hintergrund vor sich hin und fügt online eintreffende Nachrichten in diese Tabelle ein, vielleicht 10000 oder so pro Tag - völlig harmlos. Interessant sind nur die Lesezugriffe - deshalb habe ich praktisch jeden Index, den Du Dir auch nur theoretisch vorstellen kannst, verfügbar. (Schreiben darf langsam sein - Lesen muß schnell sein.)

Für dieser Tabelle durchlaufe ich nacheinander folgende Schritte:

1. Inhaltliche Auswahl der Treffermenge Das ist üblicherweise ein "WHERE MATCH(d_headline,d_story) AGAINST $term"; es kann auch eine AND-Verknüpfung mehrerer MATCHes mit nachgeschaltetem Phrasenfilter AND ((d_story LIKE "%$phrase%") OR (d_story LIKE "%$phrase%")) sein - so simuliere ich eine Phrasensuche mit diesem wortbasierten Index. (Diese Idee ist ein Abfallprodukt der Entwurfsphase der Reimplementierung der Self-Suche mit mySQL - über- haupt hat das ganze Projekt einiges an Ähnlichkeit mit der Self-Suche.)

Diese Abfrage kann noch bis zu sechs weitere WHERE-Klauseln haben, falls die Werte der entsprechenden CGI-Parameter das verursacht haben

• wenn dort wildcards gesetzt sind, fallen diese Klauseln weg. Das Ergebnis dieser Abfrage landet in einer temporären Tabelle; diese Entscheidung muß ich noch mal überdenken, vermute ich - das hängt auch davon ab, daß ich eine möglichst performante JOIN-Form für die nach- folgenden Schritte finde, denn an dieser Stelle wäre das Zeilenprodukt noch irre groß. Aber die Lösung mit den temporären Tabellen liefert angesichts der vorliegende Datenmengen schon erfreulich gute Ergebnisse.

2. Entitlement-Filterung Jetzt folgt ein JOIN dieses Ergebnisses über eine dritte Spalte gegen eine Entitlement-Tabelle, aus der ich durch ein WHERE mit einer anderen Spalte das Berechtigungsprofil des Anwenders heraus hole - nur die "berechtigten" Treffer "überleben" diesen Filter. (Die Entitlement- Tabelle hat fünfstellig viele Einträge, von denen jeder Benutzer drei-stellig viele Zeilen "besitzt" - das ist eine Art "Positivliste".) Das Ergebnis landet in einer weiteren temporären Tabelle.

3. Duplikat-Eliminierung Als nächstes folgt der Duplikat-Filter - falls er denn gewünscht ist (das ist einer der vielen CGI-Parameter). Realisiert ist der über ein GROUP BY über Spalte 5 und 6 der Treffer- zeilen - was in diesen beiden Dimensionen gleich ist, gilt als Duplikat, und mySQL hat an dieser Stelle die angenehme Eigenschaft, von ansonsten völlig unterschiedlichen Zeilen bei GROUP BY einfach "die erste" (also "irgend eine") auszugeben. (Oracle würde eine solche Query ablehnen, glaube ich mich zu erinnern.) Das Ergebnis landet wieder in einer temporären Tabelle; falls gewünscht, fliegen dabei etwa 10-40% der vorherigen Trefferzeilen raus. (Wäre der Filter besser, dann würde ich Schritt 2 und 3 tauschen.) Duplikate sind nicht gerade selten, aber in der Minderheit - sie müssen aber im Datenbestand drin bleiben, weil sie unterschiedlich entitled sein können und weil die übrigen CGI-Parameter-Filter ggf. auch nur eines aus den vorhandenen Duplikaten auswählen - zumindest kommt das häufig vor.

4. Treffersortierung und -begrenzung Als letzter Schritt werden die Treffer nach der 7. Spalte (timestamp) sortiert und mit LIMIT mengenbegrenzt (gegen einen der vielen CGI- Parameter, siehe aber unten). Das Ergebnis landet via DBI::-API im Anwendungsprogramm - was hier ausgeliefert wird, sind nun endlich echte Treffer, und zwar in der bereits richtigen Sortierung für die Anzeige. Diese Sortierung ist allerdings m. E. der teuerste Teil der gesamten Verarbeitung - ich habe in hinreichend vielen Fällen noch einige tausend Treffer, will aber nur ein paar dutzend oder hunderte anzeigen. Und es ist wirklich bitter, 5000 Zeilen zu sortieren und danach alle bis auf 20 wegzuwerfen. Mein Problem ist, daß ich unbedingt den Volltextindex brauche, um die Treffermenge effizient zu berechnen, diese dann anschließend aber nach einem völlig anderen Kriterium sortieren muß, um danach erst mit LIMIT die gewünschte Ausgabemenge zu ermitteln und deshalb für das Sortieren selbst keinen bereits existierenden Index mehr nutzen kann. (Geben würde es den nämlich schon ... und es gibt sogar Suchanfragen, die davon profitieren können, nämlich solche ohne Suchbegriff, die dann eben nicht "suchen", sondern nur "filtern", über die vielen anderen CGI-Parameter - es ist halt eine Art "Hybridmaschine".)

Um diese Sortierung zu entschärfen, habe ich das Konzept einer einzigen Tabelle aufgegeben und meine Daten auf ein System aus einem halben Dutzend Tabellen (heute, gestern, diese Woche, diesen Monat etc.) verteilt - ein eigenes kaskadierendes Caching-System aus "kleinen" Tabellen für aktuelle und "größeren" Tabellen für ältere Inhalte. Das bedeutet, daß der ganze oben beschriebene Ablauf schlimmstenfalls über sämtliche dieser Tabellen drüber laufen muß, wobei sich jeweils der LIMIT-Wert um die bereits gefundene Anzahl an Treffern reduziert. Allerdings kann ich an mehreren Stellen abbrechen: Wenn ich genügend Treffer habe und wenn die CGI-Parameterwerte zur Beschreibung des gewünschten Zeitintervalls bestimmte dieser Tabellen ausschließen. Es kommt ziemlich oft vor, daß ich nur die erste, vielleicht noch die zweite Tabelle verarbeiten muß, und beide enthalten nur je <1% des gesamten Datenbestandes ... und damit sind die oben beschriebenen 5000 Treffer nur noch in ganz abenteuerlichen Sonderfällen möglich, meistens sind es viel weniger Treffer. Je "schlechter" die Suchanfrage, desto öfter greift diese Tabellenaufteilung, während bei "guten" Suchbegrif- fen zwar alle Tabellen durchsucht werden müssen, was sehr viele, im einzelnen aber lauter sehr "schnelle" SQL-Statements (mit sehr kleinen Treffermengen) verursacht. Ich habe also die "schnellen" Anfragen etwas gebremst, um bei den "langsamen" Abfragen den Super-GAU zu verhindern. Auch innerhalb des beschriebenen Ablaufs steige ich an jeder Stelle aus, wo ich erkannt habe, daß 0 Treffer vorliegen und nachfolgende JOINs keinen Sinn mehr machen. (Ein guter query optimizer würde das auch hinkriegen - was genau mySQL an dieser Stelle tut, weiß ich leider nicht annähernd.) Ich habe also buchstäblich keine Ahnung, wie viel SQL-Code tatsächlich generiert und ausgeführt wird, bevor ich ihn selbst sehe. (Ich habe allerdings durchaus eine Vorstellung davon, was ein "guter" oder "schlechter" Suchbegriff ist und wie viele Treffer in welchen Tabellen etwa zu erwarten sind ... so, wie in diesem Forum hier "HTML" ein außergewöhnlich schlechter und "fatalsToBrowser" ein außergewöhnlich guter Suchbegriff wäre, gibt es ähnliches auch in meinem Datenbestand.)

Alles in allem bin ich mit meinem Modell schon ziemlich zufrieden (der zu schlagende Vorgänger macht halt auch einen full table scan ... ;-), aber es würde mich später ärgern, wenn ich durch einen Fingerfehler irgendwo einen nennenswerten Faktor liegen lassen würde. Und deshalb möchte ich die einzelnen SQL-Statements möglichst oft möglichst genau messen, um bei auffälligen Streuungen einzelner State- ments potentielle Schwachpunkte dieses Systems schnell zu erkennen. Über eine Messung innerhalb der CGI-Anwendung (und ein entsprechendes logfile) wäre das extrem einfach gewesen ...

In gespannter Erwartung allfälliger Kommentare    Michael

[Cheatah]

Hi,

Du kennst aber perldoc Benchmark?
nicht im Detail - würde ich die mySQL-Zeiten dabei mit erfassen
können, dann wäre genau jetzt der Zeitpunkt, mich dort einzulesen.

leider nicht; es kann auch nur die Daten auswerten, die es bekommt.

Ja - aber die Information ist zu ungenau, um sie auf mehrere
tausend auf dem Server arbeitende Kunden sinnvoll hochzurechnen.

Nun ja... auf Lasttests, WebStress etc. brauche ich Dich vermutlich nicht hinzuweisen ;-) Im Zweifel sollte ein kleines Script, welches zufällige Kombinationen aus beispielhaften Werten bildet und diese als HTTP-Request absetzt, genügend Werte liefern. Für Dich sicher eine leichte Übung.

Dazu kannst Du Deine Statements mit "EXPLAIN SELECT ..." testen;
Das habe ich schon hinter mir.

Ah. Hätte ich mir denken können.

Meine Anwendung ist im Wesentlichen eine Art Suchmaschine.

Verstehe. Ja, ist im Detail nicht ganz leicht vorherzusehen, was da passieren wird.

Und in meinem Fall kommt noch dazu, daß die konkreten SQL-Statements
sich von Anforderung zu Anforderung _heftig_ unterscheiden können -

Das klingt fast so, als wäre die Statementgenerierung eher hysterisch gewachsen. Aus eigener Erfahrung kann ich Dir empfehlen, noch mal darüber nachzudenken, eine _glasklare_ Fallunterscheidung zu Papier zu bringen, mögliche Eingabetypkombinationen _felsenfest_ zu definieren und daraus n _eindeutig_ differenzierbare Statements zu hinterlegen. Wenn es beginnt unübersichtlich zu werden, hast Du praktisch schon verloren. Glaub mir, ich weiß wovon ich rede :-)

Vielleicht kann Dir da jemand mit Erfahrung zur Seite stehen.
Was SQL angeht, glaube ich die selbst zu haben - bei mySQL sieht es
nicht so gut aus.

Ja, so meinte ich das auch. Wer deutsch fließend spricht, muss noch lange koi schwäb'sch babbele gönne...

Ich habe eine Tabelle mit Nachrichten. (Sechs- bis siebenstellig
viele, es könnte ein Gigabyte werden, vielleicht sogar mehr.)

Sagt Dir ht://dig (htdig, ht/dig, ht-Dig, ...) etwas? Unter Umständen kann es Deine Performancebedenken ausräumen; auch wenn es natürlich völlig anders gehandhabt wird als ein DBMS. Leider habe ich damit keine praktische Erfahrung; aber hier im Haus wird es ganz gerne eingesetzt, wenn große Datenmassen schnell durchsucht werden müssen - wenigstens für eine Vorauswahl der in Frage kommenden Datensätze.

Interessant sind nur die Lesezugriffe - deshalb habe ich praktisch
jeden Index, den Du Dir auch nur theoretisch vorstellen kannst,
verfügbar. (Schreiben darf langsam sein - Lesen muß schnell sein.)

Hast Du Dir überlegt, redundante oder "überflüssige" Daten mit abzulegen, die Du programmlogisch vorberechnen kannst, um die Datenmenge pre-reduzieren zu können? Der Speicherplatz scheint kein wesentlicher Faktor zu sein; und ein ideales DB-Layout ist nicht so wichtig wie die Performance.

Das Ergebnis dieser Abfrage landet in einer temporären Tabelle; diese
Entscheidung muß ich noch mal überdenken, vermute ich - das hängt auch
davon ab, daß ich eine möglichst performante JOIN-Form für die nach-
folgenden Schritte finde, denn an dieser Stelle wäre das Zeilenprodukt
noch irre groß.

Schade. Bei einer geringeren Datenmenge würde es sich vielleicht(!) lohnen, eine ID-Menge heruaszuholen und aus dieser ein neues Statement mit IN-Klausel zu generieren. Ansonsten kenne ich mich mit temporären Tabellen leider zu wenig aus.

Jetzt folgt ein JOIN dieses Ergebnisses über eine dritte Spalte
gegen eine Entitlement-Tabelle, aus der ich durch ein WHERE mit einer
anderen Spalte das Berechtigungsprofil des Anwenders heraus hole - nur
die "berechtigten" Treffer "überleben" diesen Filter. (Die Entitlement-
Tabelle hat fünfstellig viele Einträge, von denen jeder Benutzer drei-stellig viele Zeilen "besitzt" - das ist eine Art "Positivliste".)
Das Ergebnis landet in einer weiteren temporären Tabelle.

Möglicherweise ist es zu überlegen, die Suchreihenfolge umzukehren. Dieser Schritt scheint in eine relativ konstante Datenmasse zu resultieren, während die vorherige Suche vermutlich erheblich weniger, aber auch erheblich mehr Daten liefern kann. Kannst Du vorher abschätzen, wie der Fall gelagert ist, und ggf. die Reihenfolge wählen?

Als nächstes folgt der Duplikat-Filter - falls er denn gewünscht ist
(das ist einer der vielen CGI-Parameter).
Realisiert ist der über ein GROUP BY über Spalte 5 und 6 der Treffer-
zeilen -

Hast Du die Ergebnisse mit DISTINCT verglichen? Möglicherweise kannst Du hier zwei Schritte vereinen.

(Oracle würde eine solche Query ablehnen, glaube ich mich zu erinnern.)

Ja, würde es :-)

Als letzter Schritt werden die Treffer nach der 7. Spalte (timestamp)
sortiert

Ich weiß nicht, wie MySQL arbeitet; aber Oracle benutzt mit "SELECT ... WHERE a=... AND b=... ORDER BY c" einen Index über a, b und c, ohne per ROWID auf die Tabelle zuzugreifen. Möglicherweise kannst Du Dir das auch bei MySQL zunutze machen.

Diese Sortierung ist allerdings m. E. der teuerste Teil der gesamten
Verarbeitung - ich habe in hinreichend vielen Fällen noch einige
tausend Treffer, will aber nur ein paar dutzend oder hunderte anzeigen.

Kannst Du in der WHERE-Klausel abschätzen, in welchem Zeitbereich sich die herauszulesenden Zeilen wohl bewegen mögen? Wenn Du ein wenig großzügig reduzierst, braucht nicht so viel sortiert zu werden. Wenn Du nur ungefähr raten kannst, ist diese Methode davon abhängig, ob "ein paar Datensätze weniger" ein akzeptables Ergebnis darstellen.

Und es ist wirklich bitter, 5000 Zeilen zu sortieren und danach alle
bis auf 20 wegzuwerfen.

Naja, das ist eigentlich nicht so tragisch, solange der Sort im Speicher stattfinden kann.

Mein Problem ist, daß ich unbedingt den Volltextindex brauche, um die
Treffermenge effizient zu berechnen, diese dann anschließend aber nach
einem völlig anderen Kriterium sortieren muß, um _danach_ erst mit
LIMIT die gewünschte Ausgabemenge zu ermitteln und deshalb für das
Sortieren selbst keinen bereits existierenden Index mehr nutzen kann.

Vielleicht kannst Du das Pferd umgekehrt aufzäumen. Erst sortieren (sortierten Index verwenden - naja, ein Index mit Timestamp als erste Spalte ist natürlich auch nicht das beste...), dann grob limitieren, dann gegen die Suchbegriffe gegenchecken. Ich runzele zwar gerade sehr die Stirn, aber vielleicht bringt Dich ja ein anderer Denkansatz auf einen völlig neuen Weg...

(Geben würde es den nämlich schon ... und es gibt sogar Suchanfragen,
die davon profitieren können, nämlich solche ohne Suchbegriff, die
dann eben nicht "suchen", sondern nur "filtern", über die vielen
anderen CGI-Parameter - es ist halt eine Art "Hybridmaschine".)

Btw, gibt es bei MySQL ein Äquivalent zu Oracles Hints?

Allerdings kann ich an mehreren Stellen abbrechen: Wenn ich genügend
Treffer habe und wenn die CGI-Parameterwerte zur Beschreibung des
gewünschten Zeitintervalls bestimmte dieser Tabellen ausschließen.

Dafür ist allerdings Programmlogik nötig; das bedeutet mehr Roundtrips und damit mehr Zeit, DB-seitige Analysen usw. Eine Auftrennung in mehrere Statements ist i.d.R. nur dann sinnig, wenn _üblicherweise_ nur das erste verwendet wird, jedes weitere aber die Ausnahme bildet.

Ein ähnliches System habe ich mal mit dem Intermedia Text Package verwendet: Die contains()-Funktion wird erst mit dem Suchbegriff gefüttert; wenn das nichts liefert, mit "Suchbegriff%", dann mit "%Suchbegriff%", dann mit "fuzzy(Suchbegriff)". Der letzte Fall dauert ewig (teilweise über 1-2 Sekunden), war aber gegenüber einer leeren Treffermenge der akzeptablere Vorschlag.

Es kommt ziemlich oft vor, daß [...] Ich habe also die "schnellen" Anfragen etwas
gebremst, um bei den "langsamen" Abfragen den Super-GAU zu verhindern.

Klingt durchdacht. Das besagt leider noch nicht, ob es praxistauglich ist - wenn die "schnellen" Anfragen überwiegen, durch dieses Konzept aber langsamer werden, ist es eine Verschlimmbesserung.

Auch innerhalb des beschriebenen Ablaufs steige ich an jeder Stelle
aus, wo ich erkannt habe, daß 0 Treffer vorliegen und nachfolgende
JOINs keinen Sinn mehr machen.

Nun ja, das ist wohl selbstverständlich :-)

Ich habe also buchstäblich keine Ahnung, wie viel SQL-Code tatsächlich
generiert und ausgeführt wird, bevor ich ihn selbst sehe. (Ich habe
allerdings durchaus eine Vorstellung davon, was ein "guter" oder
"schlechter" Suchbegriff ist und wie viele Treffer in welchen Tabellen
etwa zu erwarten sind ... so, wie in diesem Forum hier "HTML" ein
außergewöhnlich schlechter und "fatalsToBrowser" ein außergewöhnlich
guter Suchbegriff wäre, gibt es ähnliches auch in meinem Datenbestand.)

Wenn Du das auflisten oder patternmäßig erkennen kannst, kannst Du individuell darauf reagieren. Ich fange zum Beispiel "www.*" und "*.de" ab, wenn sie in "meiner" Produktsuchmaschine eingegeben werden... seltsamerweise passiert das fast in 50% aller(!) Fälle, bei einem Suchformular _ohne_ Autofokus...

Alles in allem bin ich mit meinem Modell schon ziemlich zufrieden

Das sind schlechte Voraussetzungen für eine Optimierung... Du wirst versuchen, an dem Konzept festzuhalten :-)

(der zu schlagende Vorgänger macht halt auch einen full table scan ... ;-),

*brüll* Ich wette, seine Suchfunktion hat alle Rekorde gebrochen ;-)))

aber es würde mich später ärgern, wenn ich durch einen Fingerfehler
irgendwo einen nennenswerten Faktor liegen lassen würde.

Das Tabellenlayout kann ebenfalls von entscheidender Bedeutung sein; gerade wenn ein Index nur bedingt verwendet werden kann. Leider kann ich Dir da nicht im Detail helfen; ich vermute aber, dass solche Dinge wie "erst schmale Spalten, dann breite Spalten" ebenfalls gelten. Über die Selbstorganisation verschiedener Spaltentypen steht aber auch in der Doku einiges interessantes.

Über eine Messung innerhalb der CGI-Anwendung (und ein entsprechendes
logfile) wäre das extrem einfach gewesen ...

Bau mal ein Logfile, das Dir die Statements inklusive der Größe des Resultsets (und vielleicht solchen Goodies wie EXPLAIN) mitprotokolliert. Sowas ist zwar ziemlich bescheiden auszuwerten, aber dafür beliebig detailliert - spätestens wenn man rücktestet. Ob MySQL ein Stats-Pack hat, weiß ich leider nicht.

Cheatah

[Michael Schröpl]

Hi Cheatah,

Nun ja... auf Lasttests, WebStress etc. brauche ich Dich vermutlich nicht hinzuweisen ;-) Im Zweifel sollte ein kleines Script, welches zufällige Kombinationen aus beispielhaften Werten bildet und diese als HTTP-Request absetzt, genügend Werte liefern. Für Dich sicher eine leichte Übung.

das Problem ist, daß die bisherige Implementierung so schlecht ist (und deshalb wenig verwendet wird) und daß die neue so viel mehr können wird (beispielsweise das Filtern nach Kriterien, welche das alte System gar nicht kennt), daß ich aus den Server-Logs keine reprä- sentativen Anforderungen für das neue System erzeugen kann. Ich habe keine Ahnung, was die Anwender mit der neuen Maschine tun werden ...

Das klingt fast so, als wäre die Statementgenerierung eher hysterisch gewachsen.

Gar nicht. ;-) Das Ding kann einfach nur viel ... stell Dir eine Self-Archivsuche vor, bei der Du für jedes einzelne Feld (Topic, Verfasser, Titel, Body, Datum) Eingabefelder (oder Dropdownlisten) hättest, also parallel nach allem Möglichen suchen und/oder filtern könntest, und für den Suchbegriff auch noch eines. Das kommt dann ziemlich gut hin.

Und die Vielzahl der Statements liegt im Wesentlichen an dem unten beschriebenen Cache-System, welches die Statements eben (bis zu) versechsfacht. Die Self-Suche muß nichts sortieren, die macht einen full table scan auf einer bereits richtig sortierten Liste - und kann abbrechen, wenn ihr Trefferlimit erreicht ist. Da ist der Code ziemlich trivial.

Aus eigener Erfahrung kann ich Dir empfehlen, noch mal darüber nachzudenken, eine glasklare Fallunterscheidung zu Papier zu bringen, mögliche Eingabetypkombinationen felsenfest zu definieren und daraus n eindeutig differenzierbare Statements zu hinterlegen. Wenn es beginnt unübersichtlich zu werden, hast Du praktisch schon verloren. Glaub mir, ich weiß wovon ich rede :-)

Ich habe meine Fallunterscheidungen - die sind auch ziemlich einfach. Es sind einfach nur relativ viele, wegen der vielen Tabellen - die sich aber spürbar lohnen. (Fünfstellig viele Zwischenergebnisse zu sortieren würde meine Maschine umbringen, und es gibt etliche sehr notwendige Suchbegriffe, die eben gerade so schlecht projezieren.

Ich habe eine Tabelle mit Nachrichten. (Sechs- bis siebenstellig viele, es könnte ein Gigabyte werden, vielleicht sogar mehr.) Sagt Dir ht://dig (htdig, ht/dig, ht-Dig, ...) etwas? Unter Umständen kann es Deine Performancebedenken ausräumen; auch wenn es natürlich völlig anders gehandhabt wird als ein DBMS.

Die Suche selbst mit mySQL-FULLTEXT ist so schnell, daß ich da nichts tunen muß. Nur das Sortieren ist teuer, weil es auf einem Objekt statt finden muß, das keinen Index über das Sortierkriterium mehr haben kann, weil es erst als Zwischenergebnis entsteht.

Hast Du Dir überlegt, redundante oder "überflüssige" Daten mit abzulegen, die Du programmlogisch vorberechnen kannst, um die Datenmenge pre-reduzieren zu können?

Einige der Tabellenfelder sind von meinem Importer berechnet worden

• teilweise mit abenteuerlicher Kristallkugel-KI ... zur Treffer- ausgabe muß ich nur noch Feldwerte ablesen, nichts mehr umrechnen. Alles, was ich tun muß, ist durch die beschriebenen Phasen zur Mengenbeschränkung durchlaufen.

Der Speicherplatz scheint kein wesentlicher Faktor zu sein; und ein ideales DB-Layout ist nicht so wichtig wie die Performance.

Von Phase zu Phase werden im Wesentlichen fast alle Felder der Aus- gangstabelle durchgereicht, weil ich auch fast alle für die ver- schiedenen Phasen der Filterung brauche - und eine konfigurierbare Menge davon (noch einer der CGI-Parameter) für die Trefferanzeige.

Das einzige Feld, was definitiv nicht gebraucht wird, ist das deut- lich größte mit der Story drin - auf die greife ich nur in Filter- phase 1 zu, und hoffentlich fast immer über den FULLTEXT-Index. (Nämlich immer dann, wenn der Suchbegriff ein Wort ist und keine Phrase - glücklicherweise wird der nachgeschaltete Phrasenfilter, der ja mangels Index ein full table scan sein muß, nur auf die Treffermenge der vorherigen FULLTEXT-Matches angewendet.)

Das Ergebnis dieser Abfrage landet in einer temporären Tabelle; diese Entscheidung muß ich noch mal überdenken, vermute ich - das hängt auch davon ab, daß ich eine möglichst performante JOIN-Form für die nachfolgenden Schritte finde, denn an dieser Stelle wäre das Zeilenprodukt noch irre groß.

Schade. Bei einer geringeren Datenmenge würde es sich vielleicht(!) lohnen, eine ID-Menge herauszuholen und aus dieser ein neues Statement mit IN-Klausel zu generieren.

Wenn die Treffermenge an dieser Stelle schon klein wäre, dann würden alle nachfolgenden Schritte praktisch nichts mehr kosten - vor allem das Sortieren nicht. Wenn sie groß ist, dann ist Deine Optimierung nicht einsetzbar - und genau dann wird das Sortieren teuer ... "ein Teufelskreis".

Jetzt folgt ein JOIN dieses Ergebnisses über eine dritte Spalte gegen eine Entitlement-Tabelle, aus der ich durch ein WHERE mit einer anderen Spalte das Berechtigungsprofil des Anwenders heraus hole - nur die "berechtigten" Treffer "überleben" diesen Filter. (Die Entitlement-Tabelle hat fünfstellig viele Einträge, von denen jeder Benutzer dreistellig viele Zeilen "besitzt" - das ist eine Art "Positivliste".) Das Ergebnis landet in einer weiteren temporären Tabelle. Möglicherweise ist es zu überlegen, die Suchreihenfolge umzukehren. Dieser Schritt scheint in eine relativ konstante Datenmasse zu resultieren, während die vorherige Suche vermutlich erheblich weniger, aber auch erheblich mehr Daten liefern kann.

Das täuscht. Das Entitlement ist keine Positivliste über die Einträge der ersten Tabelle (wäre das so, dann wäre Deine Idee prima), sondern nur eine Positivliste über die Filterspalte dieses Filterschritts (die "Nachrichtenquellen"). Hier wäre nun Deine Idee mit der expliziten IN-Klausel anwendbar - allerdings ist die Wertemenge je nach Kundenvertrag irgendwas zwischen vielleicht 5 und 30 Werten groß ... (dreistellig ist theoretisch mög- lich, aber praktisch sehr unwahrscheinlich.) Hast Du eine Vorstellung, bei welcher Mengengröße "IN" (plus ein vor- heriges SELECT zur Berechnung dieser Menge!) besser wäre als ein JOIN?

Kannst Du vorher abschätzen, wie der Fall gelagert ist, und ggf. die Reihenfolge wählen?

Der Entitlement-Filter ist eine pro Benutzer relativ konstante prozen- tuale Filterung - die Treffermenge wäre riesig, wenn das der erste Schritt wäre. (Diese 5 bis 30 Einträge könnten 10 bis 100% des gesamten Datenvolumens bedeuten.) In vielen Fällen filtert die Entitlement-Prüfung nur marginal - sie ist aber unverzichtbar, weil diese Entitlement-Klassen Geld kosten. (Hm ... bei dieser Gelegenheit müßte ich mal testen, ob signifikant viele Benutzer alle Entitlement-Klassen besitzen und ich bei diesen den Entitlement-Filter einfach überspringen sollte ...)

Als nächstes folgt der Duplikat-Filter - falls er denn gewünscht ist (das ist einer der vielen CGI-Parameter). Realisiert ist der über ein GROUP BY über Spalte 5 und 6 der Trefferzeilen - Hast Du die Ergebnisse mit DISTINCT verglichen? Möglicherweise kannst Du hier zwei Schritte vereinen.

Leider nicht. Die Tabellenträge sind sicherlich in mehreren anderen Feldern außer Nr. 5. und 6 unterschiedlich, sonst hätten sie gar keine Existenzberechtigung in meinem Datenvorrat. ("Triviale" Duplikate würde schon mein Importer erkennen.)

DISTINCT eignet sich nicht als Duplikat-Filter - ich brauche danach immer noch fast alle Felder für die Trefferanzeige, und über alle Spalten habe ich definitiv keine Duplikate. (Das ist so ein Fall, wo ich keinen autoincrement brauche, weil meine Felder einen schönen Primärschlüssel hergeben, der - redundant zu den vier einzelnen Feldern, aus deren Inhalt er sich zusammensetzt - als zusätzliche Spalte existiert.)

Als letzter Schritt werden die Treffer nach der 7. Spalte (timestamp) sortiert Ich weiß nicht, wie MySQL arbeitet; aber Oracle benutzt mit "SELECT ... WHERE a=... AND b=... ORDER BY c" einen Index über a, b und c, ohne per ROWID auf die Tabelle zuzugreifen. Möglicherweise kannst Du Dir das auch bei MySQL zunutze machen.

Meine temporäre Tabelle hat aber keine Indexe ... diese aufzubauen ist etwa so teuer wie das Sortieren ohne Index.

Diese Sortierung ist allerdings m. E. der teuerste Teil der gesamten Verarbeitung - ich habe in hinreichend vielen Fällen noch einige tausend Treffer, will aber nur ein paar dutzend oder hunderte anzeigen. Kannst Du in der WHERE-Klausel abschätzen, in welchem Zeitbereich sich die herauszulesenden Zeilen wohl bewegen mögen?

Der Zeit-Filter hat bereits vor dem Sortieren stattgefunden: Einerseits ggf. explizit durch die WHERE-Klauseln der allerersten Auswahl, andererseits implizit durch die Angabe des Tabellennamens. (Das ist auch der Grund, weshalb ich manche Tabellen nicht durchsu- chen muß, wenn per CGI-Parametern ein "kleines" Zeitintervall ange- geben ist.)

Wenn Du ein wenig großzügig reduzierst, braucht nicht so viel sortiert zu werden. Wenn Du nur ungefähr raten kannst, ist diese Methode davon abhängig, ob "ein paar Datensätze weniger" ein akzeptables Ergebnis darstellen.

An dieser Stelle habe ich leider nicht wirklich eine definierte Aufgabenstellung. ;-\

Ich weiß, daß ich die exakte Trefferzahl nur dann zuverlässig bekomme, wenn ich vorher nicht "auf Verdacht" limitiere. Ich weiß auch, daß ich bei geforderten 20 Treffern mit hoher Wahrscheinlich- keit schon den ersten Filter auf 100 Treffer limitieren dürfte - es wäre aber durchaus möglich, daß ich dann aus Versehen nur 18 Treffer nach Entitlement und Duplikatfilter bekomme. Und die Wahrscheinlichkeit dafür hängt wesentlich von der Menge der Entitlement-Einträge dieses Benutzers ab - und vom zufälligen Auf- treten von Duplikaten ... das ist alles ziemlich wildes Kristall- kugelraten. (Nicht, daß ich an fünf anderen Stellen nicht ohnehin bereits wildes Kristallkugelraten machen würde - das soll sogar in gewisser Weise die Stärke der ganzen Maschine werden ...)

Aber ich habe diese Idee im Hinterkopf - ich werde mal ausprobieren, ob ich im Testbetrieb tatsächlich Treffer dadurch verliere. Bei der vorherigen Maschine (von der diese hier eine Art Klon mit anderem Datenuniversum ist) hatte ich eine solche Logik tatsächlich drin - nur ist es dort so, daß der Benutzer weiß, wonach er sucht, also das Fehlen des erwarteten Treffers merken würde. Die hier beschriebene Suche entspricht eher der Self-Archiv-Suche, was die Daten angeht - wenn ich dort etwas nicht finde, weil die Suchmaschine den Treffer unterdrückt, dann habe ich verloren. Und das kann schlimm sein.

Und es ist wirklich bitter, 5000 Zeilen zu sortieren und danach alle bis auf 20 wegzuwerfen. Naja, das ist eigentlich nicht so tragisch, solange der Sort im Speicher stattfinden kann.

Meine Beobachtungen des Antwortzeitverhaltens weisen deutlich darauf hin, daß schon bei mehreren hundert Treffern das Sortieren alles andere erschlägt und spürbar lange dauert.

(... das Forum verkraftet meine Antwort nicht in einem Stück ...)

[Michael Schröpl]

... also weiter im Text:

Mein Problem ist, daß ich unbedingt den Volltextindex brauche, um die Treffermenge effizient zu berechnen, diese dann anschließend aber nach einem völlig anderen Kriterium sortieren muß, um danach erst mit LIMIT die gewünschte Ausgabemenge zu ermitteln und deshalb für das Sortieren selbst keinen bereits existierenden Index mehr nutzen kann. Vielleicht kannst Du das Pferd umgekehrt aufzäumen. Erst sortieren (sortierten Index verwenden - naja, ein Index mit Timestamp als erste Spalte ist natürlich auch nicht das beste...),

Warum nicht? Der projeziert doch wunderbar?

dann grob limitieren, dann gegen die Suchbegriffe gegenchecken. Ich runzele zwar gerade sehr die Stirn, aber vielleicht bringt Dich ja ein anderer Denkansatz auf einen völlig neuen Weg...

Du hast gerade potentiell die Menge der möglichen Varianten an zu erzeugendem SQL-Code verdoppelt ...

Es gibt tatsächlich Fälle, in denen Deine Alternative deutlich schneller wäre - nämlich sehr schlechte Suchabfragen für sehr kurze Zeiträume. Und ich habe tatsächliche solche Fälle - ich denke aber eher darüber nach, dies durch eine noch feinere Aufteilung der Ta- bellen zu bekämpfen ("this hour" etc.). Es gibt aber auch viele Fälle, in denen das andere Verfahren besser wäre - insbesondere alle Abfragen für große Zeiträume, bei denen ich dann wiederum einen full table scan über eine Million Artikel machen müßte. In diesem Falle hätte Deine Idee alle Nachteile der Self-Suche. Hm ... ich fürchte, das wird in relativ wenigen Fällen gut sein ...

(Geben würde es den nämlich schon ... und es gibt sogar Suchanfragen, die davon profitieren können, nämlich solche ohne Suchbegriff, die dann eben nicht "suchen", sondern nur "filtern", über die vielen anderen CGI-Parameter - es ist halt eine Art "Hybridmaschine".) Btw, gibt es bei MySQL ein Äquivalent zu Oracles Hints?

Das Problem ist, daß fast alle Abfragen zuerst nach dem Suchbegriff filtern müssen (das ist der deutlich beste Filter von allen, der unbedingt von siebenstellig auf höchstens vierstellig reduzieren muß) und danach mit temporären Daten ohne Indexstruktur weiter arbeiten. Und zwei Indexe gleichzeitig benutzen geht nun mal nicht ...

Allerdings kann ich an mehreren Stellen abbrechen: Wenn ich genügend Treffer habe und wenn die CGI-Parameterwerte zur Beschreibung des gewünschten Zeitintervalls bestimmte dieser Tabellen ausschließen. Dafür ist allerdings Programmlogik nötig; das bedeutet mehr Roundtrips und damit mehr Zeit, DB-seitige Analysen usw. Eine Auftrennung in mehrere Statements ist i.d.R. nur dann sinnig, wenn üblicherweise nur das erste verwendet wird, jedes weitere aber die Ausnahme bildet.

Im Prinzip gebe ich Dir recht. Vielleicht mache ich das auch mal.

Voraussetzung wäre, daß ich a) die Informationen über die Trefferzahlen nach jedem Zwischenschritt    nicht brauche (die habe ich nämlich bisher und zeige sie sogar im    Ergebnis an, um dem Anwender zu erklären, was er bei einer kleinen    Änderungen seiner Formularwerte noch alles bekommen könnte) und b) mySQL den JOIN für die beiden ersten Filterstufen performant packt    (das teste ich diese Woche mal). Das GROUP BY läßt sich ja leicht mit hinein pfriemeln, wenn ich schon am Zusammenfassen bin.

Ein ähnliches System habe ich mal mit dem Intermedia Text Package verwendet: Die contains()-Funktion wird erst mit dem Suchbegriff gefüttert; wenn das nichts liefert, mit "Suchbegriff%", dann mit "%Suchbegriff%", dann mit "fuzzy(Suchbegriff)". Der letzte Fall dauert ewig (teilweise über 1-2 Sekunden), war aber gegenüber einer leeren Treffermenge der akzeptablere Vorschlag.

Die bisherige Suche dauert zeitweise über 20-30 Sekunden! (Deshalb schreibe ich ja eine neue ... und deshalb habe ich auch so wenig Er- fahrungsdaten darüber, was die Kunden mit der neuen Suche tun werden.)

Ich gehe davon aus, die vorherige full-table-scan-Lösung bei zehn- facher Datenmenge (die bisher wegen des full-table-scans künstlich klein gehalten werden muß) im Schnitt um Faktor 5-10 zu schlagen.

Ich bin aber immer noch unzufrieden damit, daß die reale Suchdauer nach einem für den Anwender in keinster Weise nachvollziehbaren Schema zwischen einer und fünf Sekunden schwanken kann ... je nach- dem, wie gut ich mit meinen Cache-Tabellen zufällig das Trefferlimit erreiche oder auch nicht. Das ist ein reines Glücksspiel: Mehr Treffer anzuzeigen kann genausogut nichts kosten wie eine Katastrophe sein, je nachdem, wie viele ich bei der "kleineren" Suche weggeworfen habe.

Mit dem "nichts finden" habe ich durchaus auch noch meine Probleme - Deine Anregung für das Hinterhersenden "ähnlicher" Anforderungen sind notiert, wobei ich leider aber "[%]Suchbegriff%" nicht machen kann, das gibt meine bisherige Struktur nicht her. (Dann dürfte ich FULLTEXT nicht mehr bzw. nicht mehr als einzige Infrastruktur verwenden; falls das als Kundenanforderung kommt, wird der SQL-Code-Generator wahr-scheinlich noch etwas flexibler werden müssen ...)

"fuzzy(Suchbegriff)" würde in meinem Fall wenig bringen - die Anwender sind keine DAUs, was ihre Suchbegriffe angeht. Eher würde schon "alias" etwas bringen ... darüber denken wir in der Tat nach. Leider ist die Situation ziemlich kompliziert: Wenn ein Anwender nach "Daimler" sucht, bekommt er durchaus reichlich Treffer, würde aber bei "DaimlerChrysler" viel mehr (und großenteils andere!) bekommen (weil in FULLTEXT eben nur Worte drin sind und erst mySQL 4 etwas Anderes als einen exakten Match darauf machen kann). Wie ich ihm das klar machen soll, weiß ich noch nicht ... vielleicht steigen wir bis dahin ja auf mySQL 4 um ...

Es kommt ziemlich oft vor, daß [...] Ich habe also die "schnellen" Anfragen etwas gebremst, um bei den "langsamen" Abfragen den Super-GAU zu verhindern. Klingt durchdacht. Das besagt leider noch nicht, ob es praxis- tauglich ist - wenn die "schnellen" Anfragen überwiegen, durch dieses Konzept aber langsamer werden, ist es eine Verschlimm- besserung.

Das werde ich sehen, wenn ich reale Logdaten habe - schlimmstenfalls kann ich mein System der vielen Cache-Tabellen sehr leicht abschalten. Ich habe wenig Angst davor, daß der Server durch die Suchmaschine zu stark belastet werden wird - mir geht es vor allem darum, daß die Verwendung der Suchmaschine durch subjektiv schnelle Antworten mit guter Qualität überhaupt attraktiv genug wird (die bisherige ist das definitiv nicht). Der Punkt ist, daß die Ausbremsung der "schnellen" Anfragen noch innerhalb der zumutbaren Antwortzeit liegt und die Be- schleunigung der langsamen diese überhaupt erst in diesen zumutbaren Bereich bringt. Jetzt sind alle Abfragen möglich, ohne daß ein Anwen- der vor der Tastatur einschläft. ;-)

Auch liegen teilweise Informationen über typisches Kundenverhalten vor (die Leute kommen morgens ins Büro und wollen erst mal sehen, was seit gestern nachmittag so alles in "ihrer Welt" passiert ist) - das hat die konkrete Art der Tabellenzerlegung überhaupt erst angeregt. Ich hoffe zuversichtlich, daß diese Zerlegung ein Volltreffer wird.

Ich habe also buchstäblich keine Ahnung, wie viel SQL-Code tatsächlich generiert und ausgeführt wird, bevor ich ihn selbst sehe. (Ich habe allerdings durchaus eine Vorstellung davon, was ein "guter" oder "schlechter" Suchbegriff ist und wie viele Treffer in welchen Tabellen etwa zu erwarten sind ... so, wie in diesem Forum hier "HTML" ein außergewöhnlich schlechter und "fatalsToBrowser" ein außergewöhnlich guter Suchbegriff wäre, gibt es ähnliches auch in meinem Datenbestand.) Wenn Du das auflisten oder patternmäßig erkennen kannst, kannst Du individuell darauf reagieren.

Auflisten - vielleicht, ich werde das später mit Log-Daten aus dem realen Betrieb versuchen.

Pattern - keine Chance. Nimm statt "HTML" und "fatalsToBrowser" beliebige Begriffe der deut- schen Sprache - da ist kein "System" drin. Dazu müßte ich die Begrif- fe "verstehen". (Ich habe eine Stopwortliste für den FULLTEXT-Index, aber noch keine "bad words"-Liste ... immerhin könnte ich mir eine bauen, aus einer Häufigkeitstabelle über meinen Datenvorrat ... dafür habe ich sogar schon ein Programm.)

Ich fange zum Beispiel "www." und ".de" ab, wenn sie in "meiner" Produktsuchmaschine eingegeben werden... seltsamerweise passiert das fast in 50% aller(!) Fälle, bei einem Suchformular ohne Autofokus...

Lustig. Aber so etwas habe ich nicht. Ich muß die Benutzer auch nach "schlechten" Begriffen suchen lassen. Wobei bei mir "schlechte" Begriffe diejenigen sind, die viele Treffer liefern - Begriffe, die gar nichts liefern, sind "prima".

Alles in allem bin ich mit meinem Modell schon ziemlich zufrieden Das sind schlechte Voraussetzungen für eine Optimierung... Du wirst versuchen, an dem Konzept festzuhalten :-)

Woher weißt Du das nur? ;-)

Deine Empfehlung der präventiven Limitierung "auf Verdacht" hat mich immerhin ermutigt, dies ernsthaft in Erwägung zu ziehen - die Ent- scheidung, wenige Prozent an Trefferzahl für eine Beschleunigung aller Anfragen zu opfern, steht bisher noch auf der Kippe. Der Punkt ist, daß es in meinem Falle schlimmer wäre als bei Google, wenn ich einem Anwender einen Treffer "vorenthalte" - es könnte auf genau diesen einen Treffer ankommen.

Du hast mich aber gerade auf eine Idee gebracht: Ich kann erkennen, ob eine solche "präventive" Limitierung zugeschlagen hat oder nicht, und dem Benutzer das im Dialog anzeigen - vorausgesetzt, ich mache eben gerade nicht alles in einem einzigen großen JOIN, sondern wie bisher in mehreren nacheinander geschalteten Filtern! Wenn ich den Benutzer zuverlässig warnen kann, daß er nicht alle mög- lichen Treffer sieht, kann er wenigstens darauf reagieren und seine Anfrage ändern. - Das ist auf jeden Fall einen Versuch wert!

(der zu schlagende Vorgänger macht halt auch einen full table scan ... ;-), brüll Ich wette, seine Suchfunktion hat alle Rekorde gebrochen ;-)))

Und wie - siehe oben. (Da bekommt "der zu schlagende" eine interessante Nebenbedeutung. ;-)

Allerdings war sein Datenuniversum eben auch so klein (wegen des mise- ablen Algorithmus), daß bestimmte Suchanforderungen keinen Sinn mach- ten: Ein "Archiv", das künstlich auf 14 Tage limitiert werden muß, taugt halt nichts, wenn man langfristige Entwicklungen beobachten will.

aber es würde mich später ärgern, wenn ich durch einen Finger- fehler irgendwo einen nennenswerten Faktor liegen lassen würde. Das Tabellenlayout kann ebenfalls von entscheidender Bedeutung sein; gerade wenn ein Index nur bedingt verwendet werden kann. Leider kann ich Dir da nicht im Detail helfen; ich vermute aber, dass solche Dinge wie "erst schmale Spalten, dann breite Spalten" ebenfalls gelten. Über die Selbstorganisation verschiedener Spaltentypen steht aber auch in der Doku einiges interessantes.

"So weit unten" reichen meine Kenntnisse über mySQL bisher nicht - ich habe die Spalten bisher nicht bewußt angeordnet. Danke für den Tip! (Wahrscheinlich bin ich von Oracle einfach zu verwöhnt - dem hätte ich zugetraut, sich um so etwas selbst zu kümmern. ;-)

(... das Forum verkraftet meine Antwort auch nicht in zwei Stücken ...)

[Michael Schröpl]

... und jetzt noch der Rest:

Über eine Messung innerhalb der CGI-Anwendung (und ein ent-
sprechendes logfile) wäre das extrem einfach gewesen ...
Bau mal ein Logfile, das Dir die Statements inklusive der Größe
des Resultsets (und vielleicht solchen Goodies wie EXPLAIN)
mitprotokolliert. Sowas ist zwar ziemlich bescheiden auszuwerten,
aber dafür beliebig detailliert - spätestens wenn man rücktestet.

Diese recordset-zahlen habe ich - daher weiß ich ja auch, ab welcher
Anzahl der Treffer das Sortieren spürbar langsam wird.
Und das ist leider noch innerhalb der Größenordnung von Treffern,
die ich dem Benutzer tatsächlich zur Anzeige erlauben will.

Meine Maschine hat noch kein "Blätter-Konzept" in den Treffern wie
etwa Google - nicht zuletzt, weil ich dann ganz kurze Trefferlisten
("eine Fensterhöhe") ausgeben wollen, also ein noch ungünstigeres
Verhältnis zwischen berechneten und weggeworfenen Treffern bekommen
würde, d. h. sehr viele relativ teure Abfragen statt deutlich weniger
kaum teureren.
Die Realisierung des Blätterns (mit "LIMIT offset, window") wurde hier
im Forum ja schon angesprochen, das zu bauen ist nicht das Problem ...

Ob MySQL ein Stats-Pack hat, weiß ich leider nicht.

Immerhin wieder eine Anregung für mich, danach zu suchen ...

Vielen Dank für die interessanten Kommentare!
     Michael