[MySql] Grote logfile naar relationeel database model

[Sjab-X]

Hi There,

Ik ben bezig met het toegankelijk maken van bepaalde gegevens in een logfile. Dit is een soort webserver logfile in een xml achtig formaat. Hierin staan zo'n 400.000 entries per dag. Elke entry bevat gegevens zoals date, src_ip, dst_ip, user, location, method, path, etc.

Nu heb ik een (php) script geschreven dat deze logfile regel voor regel kan "parsen" zodat het in bijvoorbeeld een MySQL database gezet kan worden. Dit gaat in principe goed, alleen wordt de tabel zo'n 400 mb groot, zodat er niet in te zoeken valt (en dat is nou juist de bedoeling).

Mijn idee is om er, in plaats van één tabel, een aantal tabellen (zoals src_ip, dst_ip, user, location, ect.) van te maken met alleen de unieke waardes plus een id. Er moet dan dus 1 tabel komen met alle relaties die dan bijvoorbeeld de volgende velden heeft (rel_id, date, src_ip_id, dst_ip_id, user_id, location_id, etc...).

De losse tabellen zijn makkelijk te maken, gewoon een simpele group by en een id erbij maken, maar ik kom er niet helemaal uit hoe ik dan de uiteindelijke relatie tabel moet maken.

Ik heb een soortgelijke query als de volgende geprobeerd (alleen dan met 19 verschillende velden) maar die is inmiddels al 24 uur aan het stampen en nog niet klaar (owkee, de machine is niet super snel, maar toch...):

CREATE TABLE relations AS SELECT
logfile.logfile_id,
logfile.date,
user.user_id,
location.location_id
...
FROM logfile
LEFT JOIN user ON logfile.user = user.user
LEFT JOIN location ON logfile.location = location.location
...

Dit zou dan alleen om de initiele vulling van de relations tabel gaan, maar als ik later meerdere logfiles moet toevoegen, moet ik dan per veld gaan kijken of die waarde al in een tabel staat, dat id nemen en dat in de relations tabel zetten? (Dan gaat het namelijk nog veel langer duren denk ik zo )

Hoe zou ik dit aan kunnen pakken, ook met het oog om meerdere logfiles later toe te kunnen voegen?

Alvast bedankt

[whoami]

Hoezo er valt niet in te zoeken?
Heb je indexen gelegd op die tabel?

[Sjab-X]

Ik heb nog geen indexen gemaakt op deze tabel. Ik heb me proberen te verdiepen in hoe de indexen werken, en volgens mij zou je voor iedere combinatie van velden in je query een index moeten maken. Een groot nadeel van alle data in één tabel is ook dat het heel groot wordt! Als ik alle velden een eigen tabel geef met alleen de unieke waardes dan is het totaal ongeveer 12 mb, tegenover één grote tabel van 400mb. Dit telt best wel op als er elke dag een logfile bij komt... Vandaar mijn sterke voorkeur voor losse tabellen met één relatie tabel.

[whoami]

Je moet niet voor iedere combinatie van velden een index maken.
Je moet enkel een index leggen op de velden (of de combinatie van velden) waarop je vaakt zoekt.

[4VAlien]

Lees wel even goed in de MySQL documentatie hoe mysql met indexes omgaat. Bijvoorbeeld een index op (a,b) wordt gebruikt als je zoekt op a of a & b, maar niet als je alleen op b zoekt. In dat geval moet je dus een extra index maken op b.

[whoami]

4VAlien schreef op 25 September 2003 @ 16:31:
Lees wel even goed in de MySQL documentatie hoe mysql met indexes omgaat. Bijvoorbeeld een index op (a,b) wordt gebruikt als je zoekt op a of a & b, maar niet als je alleen op b zoekt. In dat geval moet je dus een extra index maken op b.

Als het veel voorkomt dat je alleen op B of alleen op A zoekt, dan is het imho een beter idee om zowel een index te leggen op A, en een index op B , en geen index op (a,b)

[Sjab-X]

Owkee... dat van die indexen begrijp ik nu een beetje, maar ik blijf met het probleem dat de database of dagelijks met 400 mb zal groeien, of de losse tabellen eerst 12 mb zijn en dan de overige dagen met iets van 2 mb ofzo zullen groeien, da's nogal een verschil!
(Maar hoe dan die data netjes in die tabellen te krijgen...)

[djluc]

Je zult moeten kijken hoe die data aan elkaar gerelateerd is, bijvoorbeeld met een session id of iets dergelijks. Je gaat dan dus zoeken of je die sessionid al hebt in je database, als dit al zo is neem je de ID daar van anders maak je een nieuw record aan in de tabel sessions en gebruik je mysql_insert_id() om de id voor de koppeltabel te krijgen.

Dit kun je zo met alle gegevens doen. Dit kost dus aanzienlijk meer performance dan simpelweg alles inserten. Het is dus afhankelijk van wat je met de gegevens wilt doen, als het inserten snel moet gaan gebruik je dus niet deze techniek.

[thomrob]

normaliseren????

[marty]

Sjab-X schreef op 25 September 2003 @ 16:45:
Owkee... dat van die indexen begrijp ik nu een beetje, maar ik blijf met het probleem dat de database of dagelijks met 400 mb zal groeien, of de losse tabellen eerst 12 mb zijn en dan de overige dagen met iets van 2 mb ofzo zullen groeien, da's nogal een verschil!
(Maar hoe dan die data netjes in die tabellen te krijgen...)

als je niet per waarde wil kijken of deze al bestaat of niet zou je ook eerst het hele zwikje kunnen invoeren in een tijdelijk tabel, die naast die kolommen ook een id-kolom heeft voor iedere kolom (dus naast location ook location_id).
Dan die hele tabel updaten zodat alles wat al bestaat een id krijgt (heb je wel mysql4.x voor nodig als je dat zonder tussenkomst van PHP wil doen), dan vervolgens per veld een insert doen op de id-tabel waarbij je groepeert op alle waardes waarvan de id nog een NULL waarde heeft. Vervolgens update je de tabel nogmaals, maar alleen voor alle NULL waardes. En daarna insert je alleen de id-velden in de echte tabel. (kun je het nog volgen?)

denk dat het uiteindelijk wel sneller gaat voor 400.000 records