[SQL] Tabelontwerp voor eventlog met veel kolommen

[Radiant]

Van een bepaald stukje apparatuur verzamelen wij (binaire) eventlogs om een historie van de apparaten bij te houden, de staat te controleren en voor diagnose bij eventuele problemen. Nu willen we dit gaan automatiseren en de logs gaan archiveren in een database, maar loop ik een beetje vast bij het tabelontwerp voor deze logs.

De binaire structuur van een log entry is ongeveer als volgt:

code:

DWORD logid;
DWORD timestamp;
BYTE logtype;
union {
  struct {
    ..velden voor logtype 1..
  }
  struct {
    ..velden voor logtype 2..
  }
  struct {
    ..velden voor logtype 3..
    union {
      ..velden voor logtype 3a..
      ..velden voor logtype 3b..
    }
  }
  ..enz..
}

Hierbij kan het aantal niveau's van unions soms 2 of 3 zijn. In totaal kom ik op ongeveer 200 velden.
De hoeveelheid nieuwe data is naar verwachting iets van 12000 nieuwe records per dag. De logbestanden zullen worden aangeboden aan de software en die plaatst deze in de database. Momenteel is er alleen een viewer voor de binaire bestanden die alle kolommen gewoon achter elkaar plaatst en met een dropdown menu de gebruiker de keuze geeft welke koppen hij wil zien, dit werkt in de praktijk prima.

Om het geheel doorzoekbaar te houden is id,timestamp,logtype,data absoluut niet wenselijk. Meerdere tabellen voor verschillende types is ook niet echt wenselijk omdat het belangrijk is om een bepaald tijdsraam van de volledige log te kunnen bekijken.

De enige mogelijkheden waar ik nog op uit kom is een tabel met een vaste set kolommen die andere betekenissen hebben bij verschillende logtypes maar dat lijkt me een nachtmerrie om te onderhouden. Dan is de volgende optie om een tabel te maken waarin gewoon alle kolommen bestaan en het grootste deel dus steeds NULL is, dit lijkt vooralsnog wel de beste optie.

Ik heb er ook al op zitten googlen maar er is weinig te vinden over het opslaan van dit soort data, alleen dat een grote hoeveelheid kolommen kennelijk niet zo'n probleem is.

Hebben mensen hier ervaring met de opslag van dit soort data of nog andere briljante suggesties die ik over het hoofd zie? Of nog tips om dit te optimaliseren om het geheel wel snel te houden?

DBMS is overigens MySQL (InnoDB). Mocht MSSQL erg grote voordelen opleveren bij dit soort data (alleen al in performance geloof ik dat graag) dan is switchen nu nog een optie.

[Hydra]

Waarom zou je geen verschillende tabellen voor verschillende logtypes kunnen hebben? Dit lijkt me vanuit een OO standpunt (overerving) juist de beste manier. Oftewel, je het 1 master tabel:

logevent
- id
- timestamp
- andere velden die alle events hebben

eventtype1
- id
- master_id (verwijzing naar de tabel hierboven)
- veld1a
- veld1b

eventtype2
- id
- master_id (verwijzing naar de tabel hierboven)
- veld2a
- veld2b

Etc.

[Radiant]

Vanuit OO-oogpunt is dat inderdaad vrij logisch en misschien wel prettig werken (ik had nog niet aan een mastertabel gedacht), maar dit betekent wel dat als ik een volledige log wil laten zien op chronologische volgorde ik 17 JOINs moet gaan doen.. Ik zal eens testen wat dat doet qua performance.

[Apache]

http://docs.jboss.org/hib.../en/html/inheritance.html

daar heb je zowat de bekende strategieen hiervoor, ongeacht of je hibernate of niet gebruikt, het blijft natuurlijk wel bij deze opties.

1 grote table met alle kolommen in + 1 discriminator kolom lijkt me momenteel je beste optie, vooral als je vaak wil gaan sorteren op bepaalde velden.

[Hydra]

Radiant schreef op maandag 03 december 2012 @ 13:36:
Vanuit OO-oogpunt is dat inderdaad vrij logisch en misschien wel prettig werken (ik had nog niet aan een mastertabel gedacht), maar dit betekent wel dat als ik een volledige log wil laten zien op chronologische volgorde ik 17 JOINs moet gaan doen.. Ik zal eens testen wat dat doet qua performance.

Als al die joins op id's zijn is dat geen enkel probleem. Een join is niet duur. Tablescans en filesorts zijn duur.

[JaQ]

Hydra schreef op maandag 03 december 2012 @ 14:53:
Als al die joins op id's zijn is dat geen enkel probleem. Een join is niet duur. Tablescans en filesorts zijn duur.

Dit m.i. behoeft enige nuance. Een join kan enorm duur zijn, als je bijvoorbeeld de data eigenlijk niet nodig hebt en toch joint dan is join belachelijk duur. Als je standaard meer dan 50% van de data ophaalt uit twee tabellen wordt je join duur (en gaat denormaliseren in je voordeel werken). Als je met jezelf gaat joinen, dan word je join duur. Enzovoorts. Een full tablescan op een tabel met weinig rijen (minder dan 1 MB aan data) is bijna altijd beter dan joinen of indexeren (ik ga voor het gemak even uit van Linux of Windows, als ik mij niet vergis kan dit op Solaris anders zijn).

Anyway, genoeg nuance (wellicht ken je de nuance zelf wel, maar je ziet altijd dat er mensen topics lezen "op internet" en adviezen uit verband trekken).

Terug naar het probleem van de TS.

@Radiant:
Naar mijn mening is het verstandig om je datamodel aan te passen op je specifieke gebruik. Je hebt het over een significante hoeveelheid gegevens (bij gelijkblijvend gebruik zo'n 4.3 miljoen records per jaar met een flinke rij-lengte).

In algemene bewoordingen is het beste datamodel het model waarbij je zo min mogelijk data hoeft op te halen om aan je functionele wens voldoet. Hiervoor moet je over de volgende zaken nadenken (en compromissen maken):

Heb je enig idee hoe de data doorzocht wordt? Vrije tekst zoekacties in de tekst van een subevent, of enkel op device-id, eventtype en datum?

Ken je de datatypen van alle velden waarom gezocht wordt? Als iets een float is, zet het dan in een float ipv een integer. Dat is meetbaar sneller, zeker bij grotere hoeveelheden data. En dat geldt voor veel meer datatypen. Vermijd impliciete conversies in je selecties (als je zoekt op een datum of datum range, geef dan ook een datum als filter en geen string die je database zelf om moet zetten naar datum).

En wel goed indexeren hè

Ook is het raadzaam om te onderzoeken wat met de data gebeurt. Worden hele sets opgehaald (bladeren door pagina's met events), of worden een enkel (sub)event opgehaald en geanalyseerd? Of beide? Haal in geen geval data op die je niet nodig hebt, dat is namelijk het duurste wat je kan doen (verspillen).

Als je weet dat er altijd door hele recordsets heen wordt gebladerd (met een zeer ruw filter, bijvoorbeeld enkel op datum) dan heeft het niet zoveel nut om te gaan normaliseren. Je haalt immers toch alles op. Een lompe tabel met alle events naast elkaar is dan geen slechte oplossing. Als je weet dat de gebruiker super fijn filtert en dan enkel wat tekst van een subevent bekijkt heeft normaliseren wel veel nut en verspil je minimaal.

Ik hoop dat je hier iets mee kan.

edit: toevoeging:
ow... en van het begin af aan nadenken over hoe je de data op enig moment gaat verwijderen (als je tenminste data gaat verwijderen) helpt ook. Partitioning kan je hier enorm bij helpen (naast dat het ook de performance van je selecties kan verhogen).

[ Voor 4% gewijzigd door JaQ op 05-12-2012 11:20 ]

[Radiant]

Het gebruik is nogal gevarieerd, in sommige gevallen worden datalogs gebruikt om een trend te onderzoeken in meetgegevens en soms wordt het alleen gebruikt in probleemgevallen (alarmen). In ieder geval is het niet de bedoeling data weg te laten, want alle informatie blijkt in de praktijk nuttig bij een diagnose (waarbij de problemen vaak slecht te voorspellen zijn). Het is inderdaad wel de bedoeling om alle data in vaste eenheden en in de goede datatypes op te slaan om geen conversies te doen (de conversies die gaan gebeuren doen we bij het invoeren).
Het zijn vooral hele pagina's met events die doorgebladerd worden (met soms een filter op datum).

Daarom nu gekozen voor 1 tabel met alle kolommen erin (zijn er overigens 296 uiteindelijk; 193 bits en daarnaast vooral smallints, ints, tinyints). Qua schijfruimte lijkt het ook allemaal wel mee te vallen (MSSQL 2012: 1,2gb voor 4,4 miljoen rijen, 220mb met sparse columns (best case)) dus ik ben er blij mee zo