nicht angemeldet
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:
- 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.
-
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.
-
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.
-
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