BLOB v.s.Relationele database

[mvanberkel]

Wij maken op ons bedrijf gebruik van een applicatie voor het opslaan van meetgegevens. (1000 lokaties die elk kwartier een stuk of 5 analoge waarde meten -> bijna 500.000 waardes per dag)
Omdat het om erg veel data gaat en alle historische data opgeslagen en opvraagbaar moet zijn, wordt door de leverancier alles "geblobt" in de database opgeslagen.
Hierdoor wordt het systeem voor het opvragen van data volgens de leverancier vele malen sneller.
Een mogelijke ontsluiting die je wilt hebben is een grafiek van de afgelopen 20 jaar van 3 analoge waarde's over 1 specifieke locatie. Nu is die grafiek binnen 1 seconde gegenereert.

Het grote nadeel van BLOB is dat ik niet de data kan bekijken van de database (bijv. om er een Business Objects rapport van te maken)

Is het opslaan van zoveel data in BLOB nog wel efficienter in vergelijking met een relationele database ?
Zijn de databases ondertussen niet zover, dat het niets meer uitmaakt, hoe je grote hoeveelheden data opslaat ?

[Wiebbe]

Tja, het opslaan van BLOB's is op zich natuurlijk niks mis mee. De vraag ik me zou stellen is of je dan wel een RDBMS nodig hebt. Het grote voordeel van een RDBMS is naar mijn idee dat je relaties tussen data kan leggen. Als je dan data nodig hebt tussen verschillende objecten of bijvoorbeeld een grafiek, kan hij zelf de relatie leggen dus deze dingen.

Wat je niet zegt is of er alleen 1 tabel is met miljoenen aan BLOB's en een id er aan oid. Slaan ze ook data, tijden, lokaties op bij de BLOB?

Als dat niet zo is kunnen ze volgens mij net zo goed een filesystem gebruiken voor de opslag, misschien dat dat nog sneller is ook. Zo niet, dan zie ik niet in waarom het slecht zou zijn.

[cariolive23]

Om hoeveel data (in KB, MB, GB) gaat het? En wanneer je dat gaat comprimeren (een blob?), wat blijft er dan nog van over? Een database zoals PostgreSQL kan zelf ook comprimeren (bv. TEXT), dat kan de boel een stuk compacter maken, schijfruimte hoeft dus geen probleem te zijn.

RDBMS-en zijn gemaakt om data op te slaan en beschikbaar te stellen, dat is dus geen probleem. Met veel data moet je uiteraard wel wat aan configuratie gaan doen, out of the box zal het waarschijnlijk niet passen bij jouw toepassing.

Tip: Maak eens een proof of concept met data van een paar dagen en ga wat testjes doen. Zonder de details van jouw toepassing te kennen, wordt het erg lastig om een goed advies te geven.

[Boss]

Als het alleen maar om een reeks waarden gaat dan is een blob helemaal niet zo slecht. Je hebt immers redelijk wat overhead voor een record en die wordt relatief groter als het per record maar 1 veld is met een meetresultaat. Ga je er ook nog eens indexen op aanleggen dan wordt het nog groter.

Afhankelijk van wat je met de gegevens wil doen maak je een datamodel en keuzes. Als bij het ontwerp de rapportages die jij nu wilt maken niet zijn meegenomen kan je nu wellicht een probleem hebben, maar daar kan de leverancier niets aan doen.

Je kan ook doelgericht exports maken van de data en die ontrafelen in een format dat je nodig hebt voor een specifiek rapport. Dat zou ik dan wel doelgericht doen, niet zomaar een beetje gaan normaliseren voor allerlei overzichten die 'wel geinig' zijn. Daarvoor zijn het waarschijnlijk net wat te veel gegevens.

[matthijsln]

cariolive23 schreef op maandag 19 april 2010 @ 21:08:
Om hoeveel data (in KB, MB, GB) gaat het? En wanneer je dat gaat comprimeren (een blob?), wat blijft er dan nog van over? Een database zoals PostgreSQL kan zelf ook comprimeren (bv. TEXT), dat kan de boel een stuk compacter maken, schijfruimte hoeft dus geen probleem te zijn.

RDBMS-en zijn gemaakt om data op te slaan en beschikbaar te stellen, dat is dus geen probleem. Met veel data moet je uiteraard wel wat aan configuratie gaan doen, out of the box zal het waarschijnlijk niet passen bij jouw toepassing.

Tip: Maak eens een proof of concept met data van een paar dagen en ga wat testjes doen. Zonder de details van jouw toepassing te kennen, wordt het erg lastig om een goed advies te geven.

Ik heb even een simpele test gedaan. Bijvoorbeeld de meest naieve implementatie:

SQL:

create table meetwaarde(
    locatie int, 
    sensor int, 
    tijd timestamp, 
    waarde float, 
    primary key(locatie, tijd, sensor));

Wanneer je 1000 locaties, 5 sensors met 96 waardes per dag opslaat, levert dit in PostgreSQL voor een dag 38 MB aan data op (14 MB voor de index, 480000 rijen). Dat is met meer dan 10 GB per jaar misschien niet erg praktisch.

Als je echter de volgende tabel maakt, waarbij je voor een hele dag de waardes in een array opslaat in plaats van per kwartier, slinkt de tabel voor een dag tot 4,6 MB met een index van 176 kB (5000 rijen) - 1,7 GB per jaar. Echter heb ik random waardes gebruikt in mijn test dus zal compressie niet veel doen. Zoals genoemd door cariolive23 comprimeert PostgreSQL automatisch door TOAST. Dit zou je misschien nog kunnen tunen - dat heb ik niet gedaan (bijv. minimum grootte voordat wordt gecomprimeerd).

SQL:

create table meetwaarde_dag(
    locatie int, 
    sensor int,
    dag date,
    waardes float[], 
    primary key(locatie, dag, sensor));

Je moet wel wat meer SQL gebruiken om bewerkingen op de waardes uit te voeren, maar op zich niet extreem lastig, bijvoorbeeld het gemiddelde van een bepaalde sensor van een bepaalde locatie:

SQL:

select avg(waarde)
from meetwaarde
where locatie = 123
and sensor = 3;

select avg(waarde) 
from 
  (select unnest(waardes) as waarde
  from meetwaarde_dag
  where locatie=123
  and sensor=3) waardes;

Alleen als je gebruik wilt maken van de tijd van de dag van de waarde (bijvoorbeeld alleen in de ochtend), zul je gebruik moeten maken van het feit dat de waarde op index n van de array van tijdstrip (n-1)*15 minuten is:

SQL:

select avg(waarde)
from meetwaarde
where locatie=123
and sensor=3
and extract('hour' from tijd) < 12;

select avg(waarde) 
from 
  -- pak de eerste 48 waardes: ochtend is 12 uren keer een kwartier
  (select unnest(waardes[1:(12*4)]) as waarde 
  from meetwaarde_dag
  where locatie=123
  and sensor=3) waardes;

Voor je rapportagetool zou je met dit soort sql statements views kunnen maken of iets dergelijks.

De volledige SQL code voor het testje, waarmee je makkelijk grotere datasets en andere queries kan testen: http://anansi.tweakdsl.nl/meetwaardes_test_got.txt

Verder zijn er nog wel wat optimalisaties te verzinnen, zoals bijvoorbeeld redundante gegevens opslaan (bijvoorbeeld een gemiddelde per sensor per locatie per dag).

[Soultaker]

mvanberkel schreef op maandag 19 april 2010 @ 17:20:
Is het opslaan van zoveel data in BLOB nog wel efficienter in vergelijking met een relationele database?
Zijn de databases ondertussen niet zover, dat het niets meer uitmaakt, hoe je grote hoeveelheden data opslaat?

Ik zou zeggen probeer het eens. Je hebt de data al, en een idee in wat voor soort tabelstructuur je die liever zou opslaan. Bouw dus gewoon die tabel en importeer (een deel van) de data en meet hoeveel ruimte dat kost en of je queries nog snel genoeg gaan.

matthijsln geeft een aardig idee van wat er mogelijk is, maar de (sowieso subjectieve) beslissing of de extra functionaliteit van een genormaliseerde database de overhead in tijd en ruimte waard is kun je pas maken als je weet hoe groot die overhead in jouw geval precies is.

[cariolive23]

matthijsln schreef op dinsdag 20 april 2010 @ 01:08:
Wanneer je 1000 locaties, 5 sensors met 96 waardes per dag opslaat, levert dit in PostgreSQL voor een dag 38 MB aan data op (14 MB voor de index, 480000 rijen). Dat is met meer dan 10 GB per jaar misschien niet erg praktisch.

Dan zit over 100 jaar op ongeveer 1,4 TB (365 * 38MB * 100). Dat is toch geen probleem? 14 GB per jaar stelt niks voor: wanneer je nu een nieuwe server zou aanschaffen, dan zul je in elk geval al kijken naar eentje met minimaal 16GB RAM, dat kost je maar een paar honderd euro. Dus over 100 jaar koop je er gewoon eentje met een 16TB RAM, dan heb je helemaal nergens last van. En schijfruimte zal ook geen groot probleem zijn, 146GB is zo'n beetje het minimum voor 15k SAS-schijfjes. Ook daar kun je dus al 10 jaar mee vooruit.

Met slechts 38MB data per dag, zou ik het in een database wegschrijven. Dat levert je veel mogelijkheden op om de boel te analyseren, dat is toch wat je uiteindelijk met de data wilt doen.

Succes!

[JaQ]

mvanberkel schreef op maandag 19 april 2010 @ 17:20:
Is het opslaan van zoveel data in BLOB nog wel efficienter in vergelijking met een relationele database ?
Zijn de databases ondertussen niet zover, dat het niets meer uitmaakt, hoe je grote hoeveelheden data opslaat ?

Dat heeft niets met databases te maken, maar met gewenste functionaliteit. Data zal nog steeds vanaf disk (of uit memory) ingelezen moeten worden waarna jij er iets mee kan doen. Als jij meer data moet inlezen, dan zal dat altijd meer tijd kosten. Soms accepteer je die penalty, omdat je functionaliteit nodig hebt. Het is dus niet zo dat normaliseren altijd beter is

cariolive23 schreef op dinsdag 20 april 2010 @ 08:33:
Met slechts 38MB data per dag, zou ik het in een database wegschrijven. Dat levert je veel mogelijkheden op om de boel te analyseren, dat is toch wat je uiteindelijk met de data wilt doen.

Als je tenminste data wilt analyseren (=aanname!) Stel dat je die data enkel gebruikt wordt om een grafiek uit te genereren en verder niets, dan is de huidige methode veruit het snelste. 1 fetch levert namelijk alle data op die je nodig hebt ipv een compleet relationeel systeem doorwerken

Het zou dus maar zo eens kunnen dat je in dit specifieke geval beide wilt doen (zowel relationeel opslaan als in een blob), maar nogmaals: dat is afhankelijk van de gevraagde functionaliteit.

[mvanberkel]

Allereerst bedankt voor jullie reacties. Ik heb hier veel aan!

De data wordt als volgt opgeslagen in de database
LokatieId, SensorId, Begindatumtijd, Einddatumtijd, Blobdata

Als er een nieuwe meetreeks wordt gestart (bijv. door het verhogen/verlagen van de meetfrequentie of het verplaatsen van de sensor)
wordt er een nieuwe rij aangemaakt. Over het algemeen gebeurd dat gemiddeld 1 x per 3 jaar per lokatie.
De database is nu een paar GB groot, maar zal de komende jaar sterk toenemen, doordat we ook de radarbeelden van het KNMI hierin gaan opnemen (veel data!)
Om nu toch de data te kunnen benaderen via Business Objects , wordt dagelijks de database ge-ontblobt. (Duurt ongeveer 2 uur)

Het is moeilijk om een eerlijke vergelijking te maken in snelheid. (tussen wel of niet blobben)
Naast het opvragen van data zit er natuurlijk ook tijd in het generen van een grafiek/rapport in zowel de applicatie als BO.
Bovendien weet ik niet HOE de data geblobt is. Alleen via een "black-box" JDBC driver kan daar nu namelijk bijgekomen worden.

Als ik hier jullie argumenten lees, kom ik al wel tot de conclusie dat mijn BLOB-aversie wellicht onterecht is en begrijp ik de leverancier al een stuk beter.
In plaats van een "open en transparante" database hebben zij gekozen voor snelheid.

[SPee]

Als de leverancier de binaire data die hij direct in zijn programmatuur gebruikt opslaat, is dat veel sneller met verwerken. Als de gegevens waar je veelal op zoekt los staan, kan de DB toch indexes leggen op die kolommen. Alleen de BLOB met de meetdata kan dat niet, maar hoe vaak zoek je daar op?
vraag: waarom wil je het datamodel van je leverancier veranderen?

Als ze een mogelijkheid aanbieden om deze data toch te extraheren (naar een andere DB) dan heb je er ook niet zoveel last van. Anders dat het veel tijd kost in converteren.

Op mijn werk hebben ze bij een project ook een grote DB, echter gebruiken ze daar Oracle en verdelen ze de data in verschillende tablespaces per tijdseenheid. Hierdoor kunnen ze de data per periode 'loskoppelen' en kunnen die tablespaces zo groot maken als ze zelf willen. Is de space (bijna) op, moeten ze een nieuwe aanmaken. Na verloop van tijd kunnen ze de oude dan loskoppelen en die oude data archiveren.

[JaQ]

SPee schreef op woensdag 21 april 2010 @ 18:01:
Op mijn werk hebben ze bij een project ook een grote DB, echter gebruiken ze daar Oracle en verdelen ze de data in verschillende tablespaces per tijdseenheid. Hierdoor kunnen ze de data per periode 'loskoppelen' en kunnen die tablespaces zo groot maken als ze zelf willen. Is de space (bijna) op, moeten ze een nieuwe aanmaken. Na verloop van tijd kunnen ze de oude dan loskoppelen en die oude data archiveren.

Deze optie heet partitioning, moet wel apart gelicenseerd worden en is enkel beschikbaar op de enterprise edition van de Oracle databsae. Ook postgresql kent deze techniek, maar ik heb geen idee hoe volwassen deze techniek is. (los daarvan, de TS heeft ook niet verteld welke smaakje database gebruikt wordt in zijn situatie)

[cariolive23]

JaQ schreef op donderdag 22 april 2010 @ 15:33:
[...]
Ook postgresql kent deze techniek, maar ik heb geen idee hoe volwassen deze techniek is.

Heel erg volwassen, dit zit al sinds het begin van PostgreSQL (toen heette het nog Postgres) in de code, hier komt ook de O van ORDBMS vandaan: object-oriented databases

http://www.postgresql.org...tutorial-inheritance.html

Meer uitleg en voorbeeldcode vind je in de wiki:
http://wiki.postgresql.org/wiki/Table_partitioning

[leuk_he]

mvanberkel schreef op woensdag 21 april 2010 @ 17:41:
Al
Om nu toch de data te kunnen benaderen via Business Objects , wordt dagelijks de database ge-ontblobt. (Duurt ongeveer 2 uur)

Inderdaad niet ongebruikelijk. Je BI database zal erg in grootte toenemen. Maar omdat je op elk moment de BI databse opniuew kunt creeren vanuit de blobdatabase kun je hier toe met realtief simpele hardware ( niet alles hoeft redundand te zijn en alleen de database struktuur moet je backupen, niet je data)

[JaQ]

cariolive23 schreef op donderdag 22 april 2010 @ 15:46:
Heel erg volwassen, dit zit al sinds het begin van PostgreSQL (toen heette het nog Postgres) in de code, hier komt ook de O van ORDBMS vandaan: object-oriented databases

En ik maar denken dat een object oriënted database per definitie geen RDBMS was? (iig is posgresql geen ODBMS) En bovendien: wat heeft partitioning met object oriënted databases te maken?

[ Voor 8% gewijzigd door JaQ op 22-04-2010 16:56 ]

[cariolive23]

JaQ schreef op donderdag 22 april 2010 @ 16:48:
[...]

En ik maar denken dat een object oriënted database per definitie geen RDBMS was? (iig is posgresql geen ODBMS) En bovendien: wat heeft partitioning met object oriënted databases te maken?

Je haalt hier wat dingen door elkaar, zie de uitleg over ORDBMS en de wijze waarop PostgreSQL dit heeft geimplementeerd. Vervolgens gebruikt PostgreSQL dit ook voor partionering.

Maar ga het niet verwarren met OO-databases, dat is echt wat anders dan Object Relationele DBMS

[JaQ]

cariolive23 schreef op donderdag 22 april 2010 @ 17:00:
Je haalt hier wat dingen door elkaar, zie de uitleg over ORDBMS en de wijze waarop PostgreSQL dit heeft geimplementeerd. Vervolgens gebruikt PostgreSQL dit ook voor partionering.

Maar ga het niet verwarren met OO-databases, dat is echt wat anders dan Object Relationele DBMS

Ik had de link inderdaad niet gevolgd, my bad.