Query abbrechen

[Dukanos]

Hallo zusammen,

ich sitze hier auf ner MySQL-DB die sich um die 100Mio Zeilen (aus Logdateien) bewegt. Mein Problem ist, dass ich eine Maske in PHP gebastelt habe mit der ich die Logeinträge filtern kann. Nur wie ihr euch vorstellen könnt, können diese Anfragen schon mal ein paar Minuten dauern.

Zu meiner Frage:

Ist es irgendwie möglich rein mit PHP und MySQL eine query abzubrechen? Denn der Standarduser wartet natürlich nicht gern und klickt nochmal und nochmal... was jedes mal wieder eine Query startet.

Ich würde gerne die ursprüngliche Query abbrechen, falls nochmal geklickt wird. Hab aber noch keinen Weg dazu entdeckt.

Ich weiß, dass man einen Prozess, mit Hilfe der ID killen kann, aber ein SHOW PROCESSLIST kann ich nicht absetzen bzw. wird gelocked bis der vorhergehende Query fertig ist.

Grüße

Dukanos

[dr.dimitri]

Nur wie ihr euch vorstellen könnt, können diese Anfragen schon mal ein paar Minuten dauern.

Nein kann ich nicht. Du hast als Ergebnismenge ja wohl nicht zehntausende Sätze oder? Indizier die Tabelle vernünftig, dann ist das eine Sache von Sekunden(bruchteilen).

Dim

[Dukanos]

Ich hab auf allem nach dem gesucht/gefiltert werden kann nen Index und die Ergebnismenge sind gerne mal über 10.000 Sätze...

Wie gesagt 100Mio aktuell und jeden Tag laufen im Moment weitere 300.000/Tag rein.

Achja, die Tabelle ist übrigends MyISAM... da ich u.A. auch Volltextsuche benötige.

[dr.dimitri]

Ok. Dann halt ich eine reine Webanwendung dafür aber auch eher für ungeeignet, denn ein statuslose Protokoll ist für solche OLAP Auswertungen m.M. nach ungeeignet (Beispiel Browser schließen, einen neuen öffnen und neue Abfrage starten etc).

Ich würd das so implementieren, dass Du über die Weboberfläche einen Job antriggerst der dann in der DB läuft und dem User das Ergebnis zur Verfügung stellt (z.B. in dem eine Zwischentabelle befüllt wird o.Ä.).

Während der Laufzeit bekommt der User nur eine Statusmeldung angezeigt, welche von dem Job in eine Statustabelle geschrieben wird und von dir periodisch ausgelesen wird. Die Webseite kannst ja z.B. alle 5 Sekunden refreshen lassen. Solange ein Job läuft kann der User keinen neuen starten.

Ist der Job fertig, wird das ebenfalls vermerkt und das Ergebnis kann angezeigt werden (in welcher Form auch immer).

Dim

[Dukanos]

*grübel*

Stimmt, so könnte man´s machen. Danke :-)

Das Ganze soll im Intranet verfügbar sein... deswegen hat sich PHP angeboten.

[flashpixx]

Hallo,

es ist die Frage wie aktuell die Daten sein müssen, Du kannst den Job zeitgesteuert starten und die Daten entsprechend vorbereiten. Eine vernünftige Indizierung hilft. Stored Procedures sind schneller, wie wenn Du alle Anfrage aus dem Programm durchführst. Es wäre ebenfalls zu überlegen, ob Du ein anderes DBMS einsetzte, z.B. bietet Postgres eine gute Fuzzylogik für Volltextsuche. Evtl versuchst Du Deine Daten auch automatisiert zu klassifizieren / clustern, so dass Du nur die Cluster komplett lesen musst, wenn eine Anfrage rein kommt. Performancetechnisch könntest Du auch einen Datenbankcluster aufsetzen. Schau Dir aber auch Deine Statements einmal an, häufig sind diese nicht optimal (über alls ein Select *, auch wenn nur ein Feld benötigt wird). Ebenso prüfe noch einmal die Dabellenstrukturen, befinden sie sich in der 3. Normalform, sind Schlüssel korrekt definiert und verwendet, etc.

Phil

[dr.dimitri]

Stored Procedures sind schneller, wie wenn Du alle Anfrage aus dem Programm durchführst.

Das kann man so pauschal nicht sagen. Sofern man nicht einen extrem schlechten DB-treiber verwendet, ist im Prinzip kein Unterschied wie ein SQL zur Ausführung gebracht wird - es läuft beidemale innerhalb des Servers.

Unterschiede können sich im Ausführungsplan ergeben, was aber dann andere Gründe hat.

Grundsätzlich bin ich jedoch ein klarer Befüworter von SP. Nicht unbedingt aus Performance sondern aus Kapselungs- und Sicherheitsgründen.

Dim

[Dukanos]

Ich kann an der Hardware oder am System nichts ändern.

edit: Plödsinn rausgenommen :-)

[flashpixx]

Hallo,

Das kann man so pauschal nicht sagen. Sofern man nicht einen extrem schlechten DB-treiber verwendet, ist im Prinzip kein Unterschied wie ein SQL zur Ausführung gebracht wird - es läuft beidemale innerhalb des Servers.

Hatte das Problem mit einem Beta Treiber mal, deswegen kam ich auf den Gedanken

Grundsätzlich bin ich jedoch ein klarer Befüworter von SP. Nicht unbedingt aus Performance sondern aus Kapselungs- und Sicherheitsgründen.

Auf jeden Fall, ich habe so den Eindruck, dass es darum geht, aus Tabelle X in Y Daten zu schieben, da würde sich das auf jeden Fall anbieten und das ganze dann einmal von außen via Cron ansteuern.

An den OP: Wie sehen denn die Tabellenstrukturen, Schlüssel, Verknüpfungen und Statements aus

Phil

[Dukanos]

Das Ding besteht hauptsächlich aus 2 Tabellen:

Tabelle logs:

Spalten:

-host VARCHAR(32) <- IP-Adressen

-priority VARCHAR(10) <- Priorität der Meldung (6 verschiedene Zustände)

-datetime DATETIME <- Eintragungszeit

-msg TEXT <- Syslog-Meldung

-seq BIGINT <- Primärschlüssel

host, msg, datetime haben nen INDEX

Tabelle dns:

ip VARCHAR(32) <- IP-Adressen, gleichzeitig Primärschlüssel

dns VARCHAR(50) <- DNS-Name zur entsprechenden IP

cat VARCHAR(50) <- IPs können in Kategorien geordnet werden

Eine typische Frage könnte so aussehen:

SELECT l.host,l.priority,l.datetime,l.msg,d.cat,d.dns FROM logs l INNER JOIN dns d ON l.host = d.ip WHERE l.host IS NOT NULL AND l.datetime BETWEEN '2008-05-14 00:00:00' AND '2008-05-14 23:59:59' AND d.dns LIKE 'hier steht der dns-name' ORDER BY l.datetime LIMIT 0,400

Verbesserungsvorschläge?

[Amstelchen]

Verbesserungsvorschläge?

- sieh dir die ausführungspläne deiner SQLs an.

- sieh dir MySQL :: MySQL 5.1 Reference Manual :: 19 Partitioning an - wenn du MySQL ab 5.1 einsetzt, kannst du viele daten partitionieren.

-host VARCHAR(32) <- IP-Adressen

wieso 32? wie lang sind denn deine IP-adressen? oder hast du da adressen mit in-addr.arpa drin?

s'Amstel

[dr.dimitri]

Verbesserungsvorschläge?

Ja ein paar:

WHERE l.host IS NOT NULL

Die Bedingung kannst dir sparen, denn bei einem INNER JOIN werden NULL Werte nicht berücksichtigt (mal davon abgesehen, dass ein PK nie NULL sein kann). Auch ist NULL immer ungleich NULL. HOST und DATETIME sollten in einem zusammengesetzten Index indiziert sein. Damit kann in einem lesevorgang gejoint und eingeschränkt werden.

AND d.dns LIKE 'hier steht der dns-name'

Ist das tatsächlich eine LIKE Bedingung mit Jokerzeichen oder in Wirklichkeit eine verborgende = Bedingung? In diesem Fall sollte auch ein = verwendet werden. Wenn über msg wahlfrei gesucht werden soll, würde ich eine Volltextindizierung verwenden. Bei einem LIKE '%text%' kann der Index nicht verwendet werden. Nur wenn Du LIKE 'text%' schreibst. Die bereits vorgeschlagene Partitionierung ist auch eine Möglichkeit. Ich würde das Datum mal als möglichen Partitionsschlüssel ins Auge fassen.

ip VARCHAR(32)

Überleg dir, ob Du die IP als Zahl speichern (Punkte vor dem Einfügen entfernen) und die "echte" IP in einem extra Feld ablegen. Das hätte den Vorteil, dass der Index viel kleiner wird und die DB deshalb weniger verarbeiten muss. Die Berechnugn der Indexgröße bei mysql sieht etwa so aus: (länge+4)/0.67.

Das wären bei 100 Mio IP-Adressen mit durchschnittlich sagen wir 12 Zeichen immerhin über 1,1GB. bei einem Integer wären es nur ca. 770MB. Also schon mal 30% weniger zu lesen bei einem Join.

Problematisch wirds allerdings wenn ipv6 kommt, denn der größte nummerische Datentyp in mysql ist BIGINT und mit 64 Bit leider nur halb so breit wie benötigt. Überleg dir den Schritt also gut. Ist sowieso eher der letzte Schritt.

Dim

[Dukanos]

@Amstelchen:

Auf der Kiste läuft MySQL 5.0.41. Also leider keine Partitionierung möglich.

Warum host ein VARCHAR(32) ist kann mir nur Gott sagen :-)

Die DB wurde nicht von mir aufgesetzt. Vllt. 32 damit fast eine IPv6-Adresse reinpassen könnte ;-)

@Dimitri:

Ich habe die Werte mal angepasst. Also "WHERE l.host IS NOT NULL" rausgeworfen und die "LIKE"s durch "=" ersetzt.

Ein Index über HOST und DATETIME einen INDEX zu legen würde nur bei dieser speziellen Query was bringen.

SELECT l.host,l.priority,l.datetime,l.msg,d.cat,d.dns 

FROM logs l INNER JOIN dns d ON l.host = d.ip 

WHERE l.datetime BETWEEN '2008-05-14 00:00:00' AND '2008-05-14 23:59:59' 

ORDER BY l.datetime LIMIT 0,400

Bei dieser bräuchte ich aber dann noch einen gesonderten INDEX über DATETIME. Seh ich das richtig oder?

Sind viele Indices eigentlich ein Problem?

[Dukanos]

Ich habe gerade ein paar Statements mit EXPLAIN laufen lassen und unter "Possible Keys" tauchen immer DATETIME und HOST auf. Interpretier ich das damit richtig, dass es wirklich geschickt und ausreichend wäre nur über diese beiden einen INDEX zu machen?

[dr.dimitri]

Bei dieser bräuchte ich aber dann noch einen gesonderten INDEX über DATETIME. Seh ich das richtig oder?

Wieso? das ist doch praktisch das gleiche. Über ip und host wird gejoint und das Datum ist die wohl am meisten einschränkende Bedingung.

Sind viele Indices eigentlich ein Problem?

Sie verlangsamen DML Befehle, da sie natürlich immer mitgepflegt werden müssen.

Interpretier ich das damit richtig, dass es wirklich geschickt und ausreichend wäre nur über diese beiden einen INDEX zu machen?

Ich würde diesen zusätzlichen index mal anlegen, da passiert nichts ausser, das es Plattenplatz kostet und Zeit in anspruch nimmt und dann nochmal testen, prüfen ob der Index verwendet wird (wovon ich ausgehe) und ob die Abfragedauer sich verbessert hat.

Dim

[Dukanos]

Ok, vielen Dank für die Hilfe!

Schon wieder was dazu gelernt :-)

Grüße

Roland

[dr.dimitri]

Kannst ja mal posten ob es was gebracht hat.

Dim

[Dukanos]

Mach ich sobald die Index-Erstellung fertig ist :-)

Hab´s gestern Mittag angeschmissen, aber das läuft sicher noch ein Weilchen.

[Dukanos]

Also als Ergebnis ist festzuhalten, dass sich die Abfragezeiten grob über den Daumen gepeilt halbiert haben :-)