Langsame Query

Hallo,
ich habe eine Tabelle "global_accesslog" zum Loggen von Zugriffen:

id int(11) auto_increment
time int(11)
useragent varchar(255)
referer varchar(255)
address varchar(255)
host varchar(255)
uri varchar(255)
querystring varchar(255)

Man moege mir verzeihen, dass ich fuer Zeiten gewohnheitsmaessig immer
einen Timestamp und nicht DateTime verwende ...

Mittlerweile hat die Tabelle ca. 1.5 Mio Eintraege und ist ca. 400 MB
gross (laut phpMyAdmin).

Die Abfrage:

SELECT COUNT( id ) AS Numbers
FROM global_accesslog
WHERE time > 1200870000
LIMIT 0 , 30

dauert:

Showing rows 0 - 0 (1 total, Query took 16.7649 sec)

(time > 1200870000 -> "heute")

Zugegebenermassen ist der MySQl-Server heute ein wenig beschaeftig. Laut
Server-Monitoring meines Providers (domainfactory) zwischen 20 und 30%
der maximal moeglichen gleichzeitigen Verbindungen.

Einen Indes verwende ich fuer die Tabelle nicht. Es erfolgt momentan
lediglich eine Abfrage hinsichtlich der Zeit und da sind die Eintraege
im Index fast gleich count(id). Das habe ich schon getestet.

Irgendeine Idee?

Danke. mfg. klaus.

Re: Langsame Query

Klaus Herzberg schrieb:
> Hallo,
> ich habe eine Tabelle "global_accesslog" zum Loggen von Zugriffen:
>
> id int(11) auto_increment
> time int(11)
> useragent varchar(255)
> referer varchar(255)
> address varchar(255)
> host varchar(255)
> uri varchar(255)
> querystring varchar(255)
>
> Man moege mir verzeihen, dass ich fuer Zeiten gewohnheitsmaessig immer
> einen Timestamp und nicht DateTime verwende ...

Uns kann es ja egal sein womit du dir dein Design versaust.

> Mittlerweile hat die Tabelle ca. 1.5 Mio Eintraege und ist ca. 400 MB
> gross (laut phpMyAdmin).

http://groups.google.de/groups?q=mantra+phpMyAdmin

> Die Abfrage:
>
> SELECT COUNT( id ) AS Numbers
> FROM global_accesslog
> WHERE time > 1200870000
> LIMIT 0 , 30
>
> dauert:
>
> Showing rows 0 - 0 (1 total, Query took 16.7649 sec)
>
> (time > 1200870000 -> "heute")

Ohne Explain eine vollkommen wertlose Aussage.
http://dev.mysql.com/doc/refman/5.0/en/explain.html
http://dev.mysql.com/doc/userguide/en/indexing-explain.html
.....

Warum Limit wenn du sowieso nur eine Zeile krigst?

> Einen Indes verwende ich fuer die Tabelle nicht. Es erfolgt momentan
> lediglich eine Abfrage hinsichtlich der Zeit und da sind die Eintraege
> im Index fast gleich count(id). Das habe ich schon getestet.
....
> Irgendeine Idee?

Indes schlage ich vor, die Datenbank Indizes nutzen zu lassen. Eure
nächsten Schritte sollten demnach zur Erstellung selbiger führen.


Hochachtungsvoll

Mich selber.

Re: Langsame Query

Klaus Herzberg wrote:
> Hallo,
> ich habe eine Tabelle "global_accesslog" zum Loggen von Zugriffen:
>=20
> id int(11) auto_increment
> time int(11)
> useragent varchar(255)
> referer varchar(255)
> address varchar(255)
> host varchar(255)
> uri varchar(255)
> querystring varchar(255)

LOL. Fehlt nur noch, daß das alles utf8 ist.

> Mittlerweile hat die Tabelle ca. 1.5 Mio Eintraege und ist ca. 400 MB
> gross (laut phpMyAdmin).
..
> Einen Indes verwende ich fuer die Tabelle nicht.

LOL^2

> Irgendeine Idee?

Schreib deine Logs in Files, wie alle vernünftigen Menschen. Nur Verkä=
ufer
von Hardware kommen auf die Idee, eine SQL-Datenbank als Backend zur
Speicherung von HTTP Zugriffslogs zu verwenden.

Die Beschäftigung mit Indizes würde sicher auch nicht schaden.


XL
--=20
In der klassischen Kryptographie verschlüsselt Alice Nachrichten an Bob=
um
sie vor Carol zu schützen. Bei DRM sind Bob und Carol die gleiche Perso=
n.

Re: Langsame Query

Klaus Herzberg schrieb:

> Einen Indes verwende ich fuer die Tabelle nicht.

Das ist die Ursache Deines Problems. Du selektierst nach time, und ohne
Index muss MySQL hierfür die gesamte Tabelle scannen.

Setze auf time einen Index, und Dein Problem ist gelöst.

Gruß. Claus

Re: Langsame Query

Hallo,
danke fuer Deine Antwort.

Harald Stowasser wrote:
> Klaus Herzberg schrieb:
>> Man moege mir verzeihen, dass ich fuer Zeiten gewohnheitsmaessig immer
>> einen Timestamp und nicht DateTime verwende ...
>
> Uns kann es ja egal sein womit du dir dein Design versaust.
Ich weiss, aber so schlimm ist es nach meiner Erfahrung nicht, wenn man
Timestamp verwendet.

>> Mittlerweile hat die Tabelle ca. 1.5 Mio Eintraege und ist ca. 400 MB
>> gross (laut phpMyAdmin).
>
> http://groups.google.de/groups?q=mantra+phpMyAdmin
Ja, auch das ist mir bekannt, daher schrieb ich es extra dazu.

>> Die Abfrage:
>>
>>
>> dauert:
>>
>> Showing rows 0 - 0 (1 total, Query took 16.7649 sec)
>>
>> (time > 1200870000 -> "heute")
>
> Ohne Explain eine vollkommen wertlose Aussage.
> http://dev.mysql.com/doc/refman/5.0/en/explain.html
> http://dev.mysql.com/doc/userguide/en/indexing-explain.html
Mir ist die Funktion Explain bekannt, allerdings erschient es mir hier
eher nicht relevant:


id select_type table type possible_keys
------------------------------------------------------------ ---
1 SIMPLE global_accesslog ALL NULL


key key_len ref rows Extra
------------------------------------------------------
NULL NULL NULL 1462068 Using where


> Warum Limit wenn du sowieso nur eine Zeile krigst?
Sorry, das habe ich meine Abfrage von (wiederum sorry) von phpMyAdmin
formatieren lassen und dann die letzte Zeile nicht geloescht. In meinem
Quelltext steht die natuerlich nicht drin.


>> Einen Indes verwende ich fuer die Tabelle nicht. Es erfolgt momentan
>> lediglich eine Abfrage hinsichtlich der Zeit und da sind die Eintraege
>> im Index fast gleich count(id). Das habe ich schon getestet.
> ...
>> Irgendeine Idee?
>
> Indes schlage ich vor, die Datenbank Indizes nutzen zu lassen. Eure
> nächsten Schritte sollten demnach zur Erstellung selbiger führen.
Habe ich gemacht. Allerdings ist der fuer die oben geannte Abfrage
relevante Index ueber die Spalte "time" eher sinnlos, wenn dieser
genauso gross ist wie "id". Aber da lasse ich mich gerne belehren...

Keyname Type Cardinality Field
PRIMARY PRIMARY 1462068 id
time INDEX 1462068 time

mfg. klaus.

Re: Langsame Query

Axel Schwenke wrote:
> Klaus Herzberg wrote:
>> Hallo,
>> ich habe eine Tabelle "global_accesslog" zum Loggen von Zugriffen:
>>
>> id int(11) auto_increment
>> time int(11)
>> useragent varchar(255)
>> referer varchar(255)
>> address varchar(255)
>> host varchar(255)
>> uri varchar(255)
>> querystring varchar(255)
>
> LOL. Fehlt nur noch, daß das alles utf8 ist.
Nein, ist es nicht, aber erklaere doch mal, warum LOL?


>> Mittlerweile hat die Tabelle ca. 1.5 Mio Eintraege und ist ca. 400 MB
>> gross (laut phpMyAdmin).
> ...
>> Einen Indes verwende ich fuer die Tabelle nicht.
>
> LOL^2

Keyname Type Cardinality Field
PRIMARY PRIMARY 1462068 id
time INDEX 1462068 time

Was bringt hier der Index? Oder habe ich das Prinzip der Indizes falsch
verstanden? Aber ansonsten, bitte erklaere mir das LOL^2.

>> Irgendeine Idee?
>
> Schreib deine Logs in Files, wie alle vernünftigen Menschen. Nur Verkäufer
> von Hardware kommen auf die Idee, eine SQL-Datenbank als Backend zur
> Speicherung von HTTP Zugriffslogs zu verwenden.
Ja, dies mag auf den ersten Blick so erscheinen. Allerdings gibt es noch
2 weitere Tabellen, wo die Zugriffe auf einzelne Firmendaten geloggt
werden. Darueber werden dann ein paar wenige Statistiken erstellt. Es
erschien mir einfacher dies einheitlich ueber gemeinsame Scripte zu
realisieren, als die globalen Logs in Files und andere Logs in die DB zu
schreiben. Denn: Es handelt sich um momentan 23.000 Firmen. Da haette
ich die Moeglichkeit, alles in ein File zu schreiben bzw. fuer jede
Firma ein File zu erzeugen. Naja, dafuer gibt es eben DBs.


> Die Beschäftigung mit Indizes würde sicher auch nicht schaden.
Siehe oben.
Hinweis: Das Indizes ueber andere Spalten als "time" sinnvoll sind und
vorhanden sind, sei hier erwaehnt. Es geht mir aber um diese Abfrage.

> XL
mfg. klaus.

Re: Langsame Query

Hallo,
danke fuer Deine Antwort.

Claus Reibenstein wrote:
> Klaus Herzberg schrieb:
>
>> Einen Indes verwende ich fuer die Tabelle nicht.
>
> Das ist die Ursache Deines Problems. Du selektierst nach time, und ohne
> Index muss MySQL hierfür die gesamte Tabelle scannen.
>
> Setze auf time einen Index, und Dein Problem ist gelöst.

Keyname Type Cardinality Field
PRIMARY PRIMARY 1462068 id
time INDEX 1462068 time

Auch dann?


mfg. klaus.

Re: Langsame Query

Klaus Herzberg wrote:
> Axel Schwenke wrote:
>> Klaus Herzberg wrote:
>>> Hallo,
>>> ich habe eine Tabelle "global_accesslog" zum Loggen von Zugriffen:
>>>
>>> id int(11) auto_increment
>>> time int(11)
>>> useragent varchar(255)
>>> referer varchar(255)
>>> address varchar(255)
>>> host varchar(255)
>>> uri varchar(255)
>>> querystring varchar(255)
>>
>> LOL. Fehlt nur noch, daß das alles utf8 ist.

> Nein, ist es nicht, aber erklaere doch mal, warum LOL?

Obiges offenbart ein typisches Denkmuster: "Ich habe keine Ahnung wie gro=
ß
meine Felder sind. Ich will auch gar nicht darüber nachdenken. Also neh=
me
ich einfach die längsten VARCHAR()s die ich bekommen kann."

>>> Mittlerweile hat die Tabelle ca. 1.5 Mio Eintraege und ist ca. 400 MB=

>>> gross (laut phpMyAdmin).
>> ...
>>> Einen Indes verwende ich fuer die Tabelle nicht.
>>
>> LOL^2
>
> Keyname Type Cardinality Field
> PRIMARY PRIMARY 1462068 id
> time INDEX 1462068 time
>=20
> Was bringt hier der Index? Oder habe ich das Prinzip der Indizes falsch=

> verstanden? Aber ansonsten, bitte erklaere mir das LOL^2.

Ohne passenden Index macht deine Abfrage einen Tablescan, liest also die
ganzen 400MB jedes Mal (modulo Caches) von vorn bis hinten. Kein Wunder, =
daß
das langsam ist.

Kardinalität ist irrelevant. In einem Telefonbuch sind (Name, Vorname,
Adresse) und (Telefonnummer) auch jeweils unique. Trotzdem nutzt dir ein
alphabetisch sortiertes Telefonbuch nichts, um Teilnehmer zu finden, dere=
n
Telefonnummer größer als 47110815 ist.

>> Schreib deine Logs in Files, wie alle vernünftigen Menschen. Nur
>> Verkäufer
>> von Hardware kommen auf die Idee, eine SQL-Datenbank als Backend zur
>> Speicherung von HTTP Zugriffslogs zu verwenden.

> Ja, dies mag auf den ersten Blick so erscheinen. Allerdings gibt es noc=
h
> 2 weitere Tabellen, wo die Zugriffe auf einzelne Firmendaten geloggt
> werden. Darueber werden dann ein paar wenige Statistiken erstellt. Es
> erschien mir einfacher dies einheitlich ueber gemeinsame Scripte zu
> realisieren, als die globalen Logs in Files und andere Logs in die DB z=
u
> schreiben.

Dann schreib doch einfach alles in *ein* Logfile. Und laß meinetwegen e=
inmal
am Tag ein Skript drüber laufen und schreib die Ergebnisse in die
Datenbank. Aber für Rohdaten ist die Datenbank zu schade. Es sei denn, =
du
hast Hardware übrig, die du unbedingt beschäftigen willst.

> Denn: Es handelt sich um momentan 23.000 Firmen. Da haette
> ich die Moeglichkeit, alles in ein File zu schreiben bzw. fuer jede
> Firma ein File zu erzeugen.

Normalisierung hast du also auch nicht verstanden.




XL
--=20
In der klassischen Kryptographie verschlüsselt Alice Nachrichten an Bob=
um
sie vor Carol zu schützen. Bei DRM sind Bob und Carol die gleiche Perso=
n.

Re: Langsame Query

Klaus Herzberg schrieb:
> Hallo,
> danke fuer Deine Antwort.
>
> Harald Stowasser wrote:
>> Klaus Herzberg schrieb:
>>> Man moege mir verzeihen, dass ich fuer Zeiten gewohnheitsmaessig immer
>>> einen Timestamp und nicht DateTime verwende ...
>>
>> Uns kann es ja egal sein womit du dir dein Design versaust.
> Ich weiss, aber so schlimm ist es nach meiner Erfahrung nicht, wenn man
> Timestamp verwendet.

Aber du verbaust dir die Möglichkeit diese Funktionen zu benutzen:;
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functio ns.html

> Mir ist die Funktion Explain bekannt, allerdings erschient es mir hier
> eher nicht relevant:

Jetzt weis man das deine Abfrage 1462068 werte überprüfen muss. Und du
wunderst dich, warum das ein wenig dauert?

> id select_type table type possible_keys
> ------------------------------------------------------------ ---
> 1 SIMPLE global_accesslog ALL NULL
>
>
> key key_len ref rows Extra
> ------------------------------------------------------
> NULL NULL NULL 1462068 Using where


>> Indes schlage ich vor, die Datenbank Indizes nutzen zu lassen. Eure
>> nächsten Schritte sollten demnach zur Erstellung selbiger führen.
> Habe ich gemacht. Allerdings ist der fuer die oben geannte Abfrage
> relevante Index ueber die Spalte "time" eher sinnlos, wenn dieser
> genauso gross ist wie "id". Aber da lasse ich mich gerne belehren...

Nein ist er nicht. Das Problem ist, das ein index über die id hier
einfach nicht verwendet werden kann. Belehren tut die Doku. Je höher die
Kardinalität, um so besser.

Für deine Query könnte sogar folgender Index viel bringen:

alter table global_accesslog add KEY supi (`time`,`id`);

Probier das mal, und poste dann deinen Explain und wie lange die Querry
jetzt braucht.

Re: Langsame Query

Harald Stowasser schrieb:
> Für deine Query könnte sogar folgender Index viel bringen:
>
> alter table global_accesslog add KEY supi (`time`,`id`);
>
> Probier das mal, und poste dann deinen Explain und wie lange die Querry
> jetzt braucht.

Ach ja:

> SELECT COUNT( id ) AS Numbers
> FROM global_accesslog
> WHERE time > 1200870000

dürfte auch schlechter sein wie:

SELECT COUNT( * ) AS Numbers
FROM global_accesslog
WHERE time > 1200870000

Weil auf id nicht grupiert wird und id unique ist. Dann dürfte ein Index
_nur_ über time noch besser sein als oben genannter Index.

Re: Langsame Query

Hallo,

Axel Schwenke wrote:
>> Nein, ist es nicht, aber erklaere doch mal, warum LOL?
>
> Obiges offenbart ein typisches Denkmuster: "Ich habe keine Ahnung wie groß
> meine Felder sind. Ich will auch gar nicht darüber nachdenken. Also nehme
> ich einfach die längsten VARCHAR()s die ich bekommen kann."
Haette ich nur einfach ein paar andere Zahlen reingeschrieben und gut
waere es gewesen. Tatsaechlich reicht die Laenge oft gerade so aus. D.h.
ich habe schon ein wenig getestet.

> Kardinalität ist irrelevant. In einem Telefonbuch sind (Name, Vorname,
Gut, das war beim Verstaendnisfehler. Danke fuer den Hinweis.

> Normalisierung hast du also auch nicht verstanden.
Das denke ich schon, ich habe versucht, auf Deinen Vorschlag mit dem
Logfiles zu antworten und erklaert, warum ich die Daten in der DB haben
muss.

mfg. klaus.

Re: Langsame Query

Hallo,
danke fuer die Infos.

Harald Stowasser wrote:
> Jetzt weis man das deine Abfrage 1462068 werte überprüfen muss. Und du
> wunderst dich, warum das ein wenig dauert?
nein, das hat mich nur dahingehend wewundert, dass die Abfragen erst
"seit kurzem" recht langsam waren, obwohl nur unwesentlich mehr
Eintraege vorhanden sind. Sei es drum...

Ich bin davon ausgegangen, dass ein Index, der genauso viele Eintraege
wie die zu indizierende Spalte enthaelt, nichts bringt. Da habe ich mich
aber getaeuscht.

Hier mal das Explain fuer den Index auf "time":


id select_type table type possible_keys
1 SIMPLE global_accesslog range time

key key_len ref rows Extra
time 4 NULL 23787 Using where

Die Abfrage ist wunderbar schnell.

> Für deine Query könnte sogar folgender Index viel bringen:
>
> alter table global_accesslog add KEY supi (`time`,`id`);
Habe Dein weiteres Posting gelesen. Habe es zwar probiert, aber die
Abfrage dauerte wieder recht lange.


mfg. klaus.

Re: Langsame Query

Klaus Herzberg schrieb:
> id select_type table type possible_keys
> 1 SIMPLE global_accesslog range time
>
> key key_len ref rows Extra
> time 4 NULL 23787 Using where
>
> Die Abfrage ist wunderbar schnell.

23787 Vergleiche gehen dann doch etwas schneller als 1462068.

>> Für deine Query könnte sogar folgender Index viel bringen:
>>
>> alter table global_accesslog add KEY supi (`time`,`id`);
> Habe Dein weiteres Posting gelesen. Habe es zwar probiert, aber die
> Abfrage dauerte wieder recht lange.

"Wieder recht lange" ist ohne Explain keine verwertbare Aussage.
Vielleicht spielt Dir der Querry-Cache einen streich?
Außerdem kommt es bei solchen querys auch ein wenig auf die MySQL
Version an. Neuere MySQLs optimieren meist besser. Das kann und will ich
aber gar nicht Vorraussagen ;-)

Probieren geht hier über studieren.


Bei Performance-tests hilft: SQL_NO_CACHE
http://dev.mysql.com/doc/refman/5.1/en/query-cache-in-select .html

Re: Langsame Query

Klaus Herzberg schrieb:

>> Setze auf time einen Index, und Dein Problem ist gelöst.
>
> Keyname Type Cardinality Field
> PRIMARY PRIMARY 1462068 id
> time INDEX 1462068 time
>
> Auch dann?

Gerade dann. Hohe Kardinalität bedeutet IIRC, daß jeder Datensatz einen
"eigenen Eintrag" im Index hat.

Wenn alle Einträge denselben Timestamp hätten, hättest Du eine
Cardinality von 1 und somit einen eher nutzlosen Index.

"The higher the cardinality, the greater the chance that MySQL uses the
index when doing joins."
[http://dev.mysql.com/doc/refman/5.0/en/show-index.html]


Thomas

Re: Langsame Query

Klaus Herzberg schrieb:

> nein, das hat mich nur dahingehend wewundert, dass die Abfragen erst
> "seit kurzem" recht langsam waren, obwohl nur unwesentlich mehr
> Eintraege vorhanden sind.

Das kann viele Gründe haben.

* Ein anderes Programm, das
- viel IO macht und somit Cache braucht
- viel RAM braucht und somit dem OS den RAM wegnimmt, den dieses
ansonsten für Cache verwenden würde
* irgendwelche MySQL-internen Caching-Effekte

usw. usf.


Thomas

Re: Langsame Query

Klaus Herzberg schrieb:

> Claus Reibenstein wrote:
>
>> Setze auf time einen Index, und Dein Problem ist gelöst.
>
> Keyname Type Cardinality Field
> PRIMARY PRIMARY 1462068 id
> time INDEX 1462068 time
>
> Auch dann?

Auch dann was? Ich verstehe diese Frage nicht.

Gruß. Claus

Re: Langsame Query

Hallo,

Claus Reibenstein wrote:
> Klaus Herzberg schrieb:
>
>> Claus Reibenstein wrote:
>>
>>> Setze auf time einen Index, und Dein Problem ist gelöst.
>> Keyname Type Cardinality Field
>> PRIMARY PRIMARY 1462068 id
>> time INDEX 1462068 time
>>
>> Auch dann?
>
> Auch dann was? Ich verstehe diese Frage nicht.
Auch dann, wenn der Index die selbe Kardinalitaet wie id hat. Aber das
ist ja geklaert. Ich dachte wirklich, dass ein Index nichts bringt, wenn
dieser ebensogross wie id ist. Aber das war ein gewaltiger Irrtum. Daher
war Deine Aussage:

"Setze auf time einen Index, und Dein Problem ist gelöst."

absolut richtig und hat mir geholfen. Dafuer noch mal vielen Dank.

mfg. klaus.