Database design voor een miljard records

[Baron]

Ik heb een tooltje geschreven dat een 5000 metingen (temperatuur, debiet, ...) van onze fabriek in een database schrijft.

Deze metingen willen voor analyse lange tijd bewaren.

Wat is nu de beste manier om deze data in SQL Server te bewaren?

1 tabel met velden datum_tijd, metingnaam, waarde. Dit is makkelijker.
of
1 tabel per meting met velden datum_tijd, waarde.

[simon]

1 tabel per meting lijkt me bij voorbaat een totaal niet logische keuze. Je wil natuurlijk wel iets doen met die data. En paar miljard records met een datetime, varchar en een int lijkt me niet erg groot. Je kan 't testen door die DB met mock data te vullen, moet kunnen.

[FireDrunk]

Hoeveel unieke soorten metingen zijn er? Zijn dat er maar een paar (<20-30) of heel veel (> 200). Anders kan je per metingsoort een tabel maken. Daarna kan je aggegreren in de applicaties die de data gaan uitlezen.

Als je daarna goede indexes legt (hoewel dat bij 1 id, 1 waarde en 1 waarde type niet echt nodig is) is een paar miljard records niet echt spannend.

[AlphaRomeo]

Geen specialist, maar het eerste voorstel 'metingnaam' gaat een heleboel redundante informatie opleveren, dan zou ik denken aan een koppeltabel metingId. metingnaam. Dat gaat schijfruimte schelen.

Er vanuit gaande dat je waarde 4 bytes is wordt het dan per record:
DateTime (4-8 bytes)
MeasurementId (4 bytes)
Value (4 bytes)

Als je een varchar opslaat wordt het veel meer data.

[Gomez12]

Wil je dit wel in sql hebben? Waarom niet in een time-series database zoals een influxdb of graphite of ...

En wat je je moet afvragen is wil je over tijd wel alles bewaren in je primaire database, of wil je bijv alles na een maand gaan aggregeren per uur en per half-jaar naar per dag.
Waarbij je bijv een file-backup ernaast houdt voor de details (die kan je makkelijk zippen en backuppen).

[MAX3400]

Ik zou een miljard records absoluut niet in 1 database hangen en zelfs niet op 1 server. Afhankelijk van de gekozen *sql-oplossing, moet je dan enorm gaan tunen om cachen, stored procedures etc. op 1 machine werkbaar te houden.

De vraag is ook een beetje hoe nauwkeurig de gegevens zijn zoals AlphaRomeo in "Database design voor een miljard records" probeert aan te geven; een miljard records van elk 1 byte totaal wordt maximaal 1GB. Maar zodra je meer gegevens erin zet, zeg even 512 bytes per record, zit je ineens aan 512GB aan data.

Vergeet ook niet je transaction-logging goed in te richten; als jij per dag 10 miljoen records genereert vanuit de fabriek, dan wil je je transaction-log wel goed op orde hebben. Jouw metingen zijn namelijk zeer specifiek en/of niet herhaalbaar dus elke meting is uniek in "time and space".

[Baron]

Is het een optie dat data op deze manier wordt opgeslagen

DatumTijd, Waarde_Meting1, Waarde_Meting2, Waarde_Meting3, ...

Op deze manier komen er wel veel NULLs in de tabel.

[MAX3400]

Baron schreef op vrijdag 25 november 2016 @ 13:59:
Is het een optie dat data op deze manier wordt opgeslagen

DatumTijd, Waarde_Meting1, Waarde_Meting2, Waarde_Meting3, ...

Op deze manier komen er wel veel NULLs in de tabel.

Nope... Tenminste, de vraag is wat je er later mee wil/moet? Is het ooit ter naslag? Moet je (bijna) realtime aggregeren of analyses uitvoeren?

Even zelf denken; Er zijn 5 machines. Je hebt in de fabriek 10 sensoren geplaatst. Dat zijn 2 sensoren per "machine". Alle 10 moeten iets uitlezen. Elke uitleespoging gebeurt elke 0.5s. Je enige unieke waarde voor alle machines/sensoren tegelijk is tijd & datum (mits deze overal synchroon loopt).

1 database
--> 5 tabellen (eentje voor elke machine)
----> elke tabel vul je dan met Time/Date | Sensor1 | Sensor2

Maar dat is voor mij logisch denken op basis van de gegeven informatie. Maar stel nou dat je elke sensor een apart ID hebt gegeven, dan kan je ook tabellen aanmaken op SensorID en dan alleen vullen met Time/Date | Value

[Morrar]

Sta je ook open voor NoSQL oplossingen?

Is het niet een optie om naar bijvoorbeeld ElasticSearch met TimeLion te gaan kijken? ElasticSearch is een document store voor JSON objecten. Je zou dan per meting een object kunnen wegschrijven, bijvoorbeeld:

code:

{
  'sensor_id': 1,
  'timestamp': '2016-11-25 14:16:11.234',
  'metric': 'temperature',
  'value': 37
}

TimeLion is gemaakt voor tijdseries data en met Kibana kun je makkelijk dashboards bouwen. Ook kun je de metingen dan heel eenvoudig doorzoeken.

Metingen inlezen zou je kunnen doen met bijvoorbeeld LogStash of een zelf geprogrammeerde connector. Performance is dan lineair te schalen door bakken bij te plaatsen waar nodig.

[ Voor 22% gewijzigd door Morrar op 25-11-2016 14:24 ]

[Gomez12]

Baron schreef op vrijdag 25 november 2016 @ 13:59:
Is het een optie dat data op deze manier wordt opgeslagen

DatumTijd, Waarde_Meting1, Waarde_Meting2, Waarde_Meting3, ...

Op deze manier komen er wel veel NULLs in de tabel.

Waarom kom je met deze vraag?

Het is een achteruitgang tov je opzet in de TS.

[borft]

Ik zou het gewoon simpel houden. Gewoon een datetime, een typeId en een value. Bij alle kolommen jezelf afvragen wat voor resolutie je nodig hebt.

bv MySQL kan makkelijk tabellen met meer dan een miljard rows aan, ook op een server.

Overigens is dit alleen haalbaar als al je sensores/metingen een zelfde datatype opleveren (bv een int of een decimal)

[ Voor 20% gewijzigd door borft op 25-11-2016 14:24 ]

[Umbrah]

Los van alle goede bedoelingen van TS en medeposters, denk ik dat de kennis van de TS mogelijk niet op het niveau is waarmee hij 'door de bomen heen' het bos kan gaan zien, en medetweakers vol goede bedoelingen mogelijk niet direct op dat niveau kunnen lullen. Gezien de hoeveelheid gok ik dat de TS toch zeker voor een middengroot bedrijf werkt... nou vraag ik me af: zijn er hier al SCADA systemen bij betrokken? Is er een Pi-systeem of iets gelijkwaardigs (niet de raspberry soort, maar meer de osisoft soort)?

Tijd gebaseerd capturen van data kan enorm veel waarde opbrengen voor een bedrijf, namelijk... maar het is eigenlijk een vak apart, en "we rammen de meuk even in mysql" gaat niet altijd op. Zeker als "een tabel per meting of een record per meting maar ik vind NULLs erg" als opmerking wordt geplaatst.

Mijn advies: huur een professional in, of zet een marktverkenning op, en koop deze kennis in... met prestatie verplichtingen, uiteraard. Een database is een mooi product om iets in op te slaan, maar presenteren, data beveiliging, data op waarde beoordelen is zeker net zo belangrijk. Mogelijk heeft je leverancier zelfs een kant-en-klaar oplossing.

En dan ook weer de vraag... op het moment dat je de data logt, en het van belang begint te worden voor je kernproces; wat gebeurt er op het moment dat je huidige 'maatwerk' omvalt zonder backup? Je wilt bij voorkeur nu al een voortschrijdend inzicht-situatie voorkomen...

TL;DR: doe dit niet noodzakelijk zelf, maar huur hulp in/besteed het werk uit!

[TRON]

Beschrijf voor jezelf eerst even het doel. Waarom sla je het op? Wat wil je er mee kunnen doen? Moeten de resultaten bloedsnel geladen kunnen worden of mag het even duren? Hoe vaak ga je die data opvragen?

Schrijf eens een paar scenario's voor wat je wanneer met deze data wil doen.

[Baron]

Wat Umbrah schrijft is vrijwel de nagel op de kop.

We hebben een OPC HDA systeem waarin alle metingen worden opgeslagen.
Mensen van productie en externe firma's willen deze data gebruiken om er allerhande analyses mee te doen en rapporten er van te maken.

Bijvoorbeeld een maandelijks rapport van de verbruikte energie of een het opzetten van een wiskundig model voor een bepaalde installatie.

Mensen willen aan de slag met Excel, Power Bi, Reporting services en weet ik veel welke tools nog allemaal.

Deze tools hebben kunnen allemaal data vanuit een database binnen trekken. Vandaar dat ik de raw data van OPC HDA naar SQL server kopieer.
Ook de capaciteit van onze SQL Server is veel groter dan de HDA server, wat beter is voor lange termijn opslag. Een typische opslagtermijn per meting is 1meting/sec en 31dagen bewaren.

Als deze data eenmaal in een database zit is het achteraf veel eenvoudiger deze in het gewenste formaat te zetten voor de juiste toepassing.
In het verleden heb ik deze data altijd op voorhand geaggregeerd en dan pas in de database geschreven.
Dit geeft een mooie performantie maar je verliest details.

Dus op dit moment wil ik gewoon de data uit de HDA server naar SQL server, deze op een zo efficiënt mogelijke manier opslaan en achteraf zien wat ermee moet gebeuren.

[FireDrunk]

Hoe efficiënt je het opslaat hangt 100% af van hoe de rapporten er uiteindelijk uit komen te zien.
Zonder dat je dat vooraf weet, moet je niet vooraf gaan optimaliseren.

Je moet de data dus gewoon 'goed' volgens de standaard normalisatie normen opslaan. Als er daarna eenmaal pakketten aan gekoppeld worden, kan je altijd nog verder optimaliseren.

Als je niet weet wat normalisatie is: lees dit: Wikipedia: Databasenormalisatie

[Megamind]

Waarom sla je ze niet op in een time series database? Zoals Influx? Dat lijkt mij veel beter geschikt.

[ Voor 4% gewijzigd door Megamind op 25-11-2016 16:54 ]

[Morrar]

Megamind schreef op vrijdag 25 november 2016 @ 16:54:
Waarom sla je ze niet op in een time series database? Zoals Influx? Dat lijkt mij veel beter geschikt.

Ik had de vraag ook even verkeerd begrepen maar de TS wil alleen weten hoe het in MSSQL server het beste kan. Volgens mij zit hij vast aan het MS ecosysteem en mensen die qua skills vast zitten aan SQL, Excel en Power BI.

Lijkt me ook dat andere oplossingen deze usecase veel beter passen, maar als dat de requirements zijn waar hij aan vastzit.

[ Voor 12% gewijzigd door Morrar op 25-11-2016 20:43 ]

[P_Tingen]

De opmerking van Gomez12 vind ik wel een goeie, omdat je de data beschikbaar wil stellen, is het wellicht handig om - naast de ruwe data - ook geaggregeerde data beschikbaar te hebben. Je kunt verschillende vormen van aggregatie aanbieden. Een beetje database wordt niet echt anders van een miljard records, maar of Excel daar zo vrolijk van wordt betwijfel ik. In dat geval is het waarschijnlijk behapbaarder om data per uur / dag / week te hebben.

De ruwe data zou ik in 1 tabel opslaan met een identifier erin voor de soort meting. Dit maakt het makkelijker om analyse te doen op de verschillende metingen. Als je 30 tabellen hebt, zul je in je analysetools ook rekening moeten houden met 30 tabellen. Simpel is vaak beter, vooral in databaseontwerp. Verwar hier 'simpel' hier overigens niet met ongenormaliseerd. Normaliseren is niet voor niks uitgevonden....

[Hydra]

FireDrunk schreef op vrijdag 25 november 2016 @ 16:23:
Hoe efficiënt je het opslaat hangt 100% af van hoe de rapporten er uiteindelijk uit komen te zien.
Zonder dat je dat vooraf weet, moet je niet vooraf gaan optimaliseren.

Exact dit. Bij dit soort data volumes gelden de standaard regels wat betreft databases en hoe je dingen inricht vaak een stuk minder. In echt 'big data' land hebt je een datastore die goed om kan gaan met dergelijke volumes data (wij gebruiken zelf Cassandra, die schaalt vrijwel linear) en daarnaast 'read' datastores (in CQRS termen) die de aggregaties voor de verschillende rapportages bevatten.

Morrar schreef op vrijdag 25 november 2016 @ 20:41:
Ik had de vraag ook even verkeerd begrepen maar de TS wil alleen weten hoe het in MSSQL server het beste kan. Volgens mij zit hij vast aan het MS ecosysteem en mensen die qua skills vast zitten aan SQL, Excel en Power BI.

Bij dergelijke data-volumes zit je niet vast aan MSSQL server. Dat is echt compleet backwards. Er is genoeg literatuur over hoe je dit met bijvoorbeeld Cassandra op kan lossen en ook is hier een CQRS achtige oplossing vaak een uitkomst. Stellen dat het in MSSQL moet en er dan later als je inderdaad zoveel data hebt erachter komen dat een enkele MSSQL instance gewoon volledig op z'n gaat gaat is nogal onhandig.

[Gomez12]

Baron schreef op vrijdag 25 november 2016 @ 16:12:
Mensen van productie en externe firma's willen deze data gebruiken om er allerhande analyses mee te doen en rapporten er van te maken.

Yikes, dus interne en externe mensen mogen vrijelijk query's maken in die data? En staan er dan nog SLA's / afspraken op dat die data beschikbaar is?
Want het data-model van metingen is (in basis) supersimpel. Echter heb je dan wel een riskant data-model want je krijgt grote tabellen en het nadeel met grote tabellen is dat je ze wel goed moet aanspreken anders heb je grote kans op locking-issues en ik begrijp dat jij geen invloed hebt op hoe ze aangesproken worden?

Oftewel wat gebeurt er als stagiair bij externe firma 3 jouw database locked voor een half uur en alle andere klanten er niet bij kunnen?

Als ik het goed begrijp is de data enkel maar een kopie van andere data en enkel om het te ontsluiten? Want dan zou ik zeggen : No necessary backup. En dan wordt schijfruimte opeens cheap.
Dan zou ik gewoon tig tabellen oid opzetten, waarbij overal de metingsnamen weggenormaliseerd zijn.
01 bevat dan alles
02 is bijv een uurlijkse aggregatie die alleen uurgemiddelden heeft
03 is bijv een nachtelijke aggregatie die alleen dagelijkse gemiddelden heeft
04 bevat dan wekelijkse gemiddelden
etc.
11 bevat dan bijv hetzelfde als 1 maar dan enkel van de laatste maand
21 bevat dan bijv hetzelfde als 1 maar dan enkel van de laatste week

En dat hele setje in stand houden met triggers en scheduled functies (enkel 1 wordt extern gevuld).
Zo kan je iedereen richting een bepaalde dataset duwen die voor die toepassing geschikt is.

Bijv een maandelijks rapport van de verbruikte energie dat heeft niet daadwerkelijk elke seconde / milliseconde data nodig, dat rapporteert reeel slechts per uur over de laatste maand oftewel die zou genoeg hebben aan tabel 12 (uurlijkse aggregatie van de laatste maand). En dit geldt zeer waarschijnlijk voor alle maandelijkse rapportages, oftewel 1x aggregeren en dan uit die tabel rapporteren.

Zo verlaag je de kans dat een externe het hele systeem platlegt met een verkeerde query (selfjoin over selfjoin over selfjoin bijv)

Hydra schreef op zaterdag 26 november 2016 @ 10:43:
[...]
Exact dit. Bij dit soort data volumes gelden de standaard regels wat betreft databases en hoe je dingen inricht vaak een stuk minder. In echt 'big data' land hebt je een datastore die goed om kan gaan met dergelijke volumes data (wij gebruiken zelf Cassandra, die schaalt vrijwel linear) en daarnaast 'read' datastores (in CQRS termen) die de aggregaties voor de verschillende rapportages bevatten.

Alleen hij beweert hier niet in big data land te zitten, maar wel te zitten met dat schijnbaar iedereen alles en alles moet kunnen query'en met tools die voornamelijk sql praten.

Ik vind het een redelijk krankzinnige eis dat iedereen maar alles en alles moet kunnen queryen met sql tools, maar als dat de business case is dan is dat de business case...

[Hydra]

Gomez12 schreef op zaterdag 26 november 2016 @ 14:04:
Alleen hij beweert hier niet in big data land te zitten, maar wel te zitten met dat schijnbaar iedereen alles en alles moet kunnen query'en met tools die voornamelijk sql praten.

Ik vind het een redelijk krankzinnige eis dat iedereen maar alles en alles moet kunnen queryen met sql tools, maar als dat de business case is dan is dat de business case...

Ik heb nooit gezegd dat je geen SQL kunt gebruiken voor de aggregate views. Maar aggregaties rechtstreeks op zo'n enorme dataset doen gaat hoogstwaarschijnlijk issues opleveren (want die data moet echt van disk komen). CQRS is in dergelijke systemen een veelvoorkomend pattern.

Als software engineer heb je als taak werkende software te bouwen. Als je dus domweg doet wat je opgedragen wordt en dan een systeem hebt waar een enkele user een minuut op z'n query zit te wachten heb je je werk gewoon slecht gedaan.

[Morrar]

@Hydra: ik zeg ook niet dat ik vind dat het in MSSQL zou moeten, maar dat is expliciet wat hij vroeg en ook het geaccepteerde antwoord.

Zou persoonlijk ook eerder naar NoSQL kijken voor ruwe data en evt SQL voor aggregaten en rapportage. Maar goed, keuze is niet aan mij en wellicht ook niet aan hem.

Als IT beheer bijvoorbeeld zegt dat het alleen MS en SQL server mag zijn... (kom dat soort restricties regelmatig tegen).

[Gomez12]

Hydra schreef op zaterdag 26 november 2016 @ 14:29:
[...]
Als software engineer heb je als taak werkende software te bouwen. Als je dus domweg doet wat je opgedragen wordt en dan een systeem hebt waar een enkele user een minuut op z'n query zit te wachten heb je je werk gewoon slecht gedaan.

Echter is werkende software dan wel gedefinieerd volgens de eisen van de klant.

Maarja, mij bekruipt toch een beetje het gevoel dat TS het niet helemaal overziet, dat TS begonnen is met wat metingen op te slaan en dat dat goed ging (zolang hij als enige queryde en bjv een query van minuten lang wel acceptabel was) en dat management toen heeft gevraagd of klanten het niet ook zouden kunnen.

En dat TS niet ziet dat het heel erg snel op zijn bek gaat als klanten er ongelimiteerd in gaan query'en.
Dan wordt het opeens ipv een klein hobby-projectje van een paar uurtjes wat data in sql rossen een project van weken / maanden omdat je opeens "onbekende" tools moet inzetten vanwege scalability etc.

[KopjeThee]

Ik zou overwegen de details per week of maand oid op te slaan.

Standaard filters of aggregaties kun je voor de oude maanden gewoon apart opslaan, en beinvloeden je performance ook op geen enkele manier meer. Je heb daarvoor dan alleen met de huidige week of maand te maken.

Voor nieuwe informatie vragen zul je meer oude maanden door moeten werken. Per geval kan je bekijken hoeveel historie nodig is. Als dat veel is, dan blijft het hoe dan ook eenmalig wat resources vragen.

[inquestos]

Ik heb wel ervaring met dergelijke volumes; in eerste instantie sloeg ik het ook allemaal op in een SQL tabel, maar dat was vrij snel onhoudbaar. Dit soort gegevens zijn nauwelijks nog toegankelijk. Hoe ga je dit indexeren? Ook wanneer je een kleine subset maakt (van denk de laatste maand). Je zou dan ook kunnen partitionen per maand.

Uiteindelijk bleek dat de eindgebruiker voorlopig enkel behoefte heeft aan geaggregeerde data. Dus tegenwoordig wordt alles geaggregeerd (~1%) weggeschreven. De rest schrijf ik voorlopig als blob weg naar de cloud, zodat als er ooit behoefte is de mogelijkheid bestaat te analyseren. Wellicht later naar een HDFS, dat is geschikter voor big data analyses.

[Hydra]

KopjeThee schreef op zaterdag 26 november 2016 @ 20:38:
Ik zou overwegen de details per week of maand oid op te slaan.

Nee. Want dan ga je tegen het probleem aanlopen dat er straks feature requests gaan zijn waar je de informatie niet voor hebt omdat je die dacht niet nodig te hebben. Da's een van de grootste shifts wat betreft 'big data'; we gooien niks meer weg. Je slaat het dus op in een systeem die daar goed mee om kan gaan en hebt ondertussen een continue proces that ook de aggregaties berekent. Die agregaties query je op en als later blijkt dat je ze aan moet passen kun je in een nachtelijke batch bijvoorbeeld die aggregaties opnieuw berekenen.

Data weggooien raad ik je af.

Morrar schreef op zaterdag 26 november 2016 @ 14:44:
@Hydra: ik zeg ook niet dat ik vind dat het in MSSQL zou moeten, maar dat is expliciet wat hij vroeg en ook het geaccepteerde antwoord.

Snap ik. Maar als ik vind dat de TS de verkeerde richting in denkt behoed ik 'em graag voor fouten die ik eerder gemaakt / gezien heb Miljarden records in MS SQL stoppen en daar ad-hoc aggregaties op doen gaat niet werken. Heb iets dergelijks ooit gebouwd voor zoekpatronen van werk.nl. Dat ging vrij hard

[ Voor 27% gewijzigd door Hydra op 27-11-2016 12:51 ]

[KopjeThee]

Hydra schreef op zondag 27 november 2016 @ 12:48:
[...]


Nee. Want dan ga je tegen het probleem aanlopen dat er straks feature requests gaan zijn waar je de informatie niet voor hebt omdat je die dacht niet nodig te hebben. Da's een van de grootste shifts wat betreft 'big data'; we gooien niks meer weg. Je slaat het dus op in een systeem die daar goed mee om kan gaan en hebt ondertussen een continue proces that ook de aggregaties berekent. Die agregaties query je op en als later blijkt dat je ze aan moet passen kun je in een nachtelijke batch bijvoorbeeld die aggregaties opnieuw berekenen.

Data weggooien raad ik je af.


[...]


Snap ik. Maar als ik vind dat de TS de verkeerde richting in denkt behoed ik 'em graag voor fouten die ik eerder gemaakt / gezien heb Miljarden records in MS SQL stoppen en daar ad-hoc aggregaties op doen gaat niet werken. Heb iets dergelijks ooit gebouwd voor zoekpatronen van werk.nl. Dat ging vrij hard

Ik stel ook niet voor om iets weg te gooien hoor! Ik ben het met je eens. Ik stel alleen voor om het op weken of maanden te partitioneren.

[Hydra]

KopjeThee schreef op zondag 27 november 2016 @ 13:38:
Ik stel ook niet voor om iets weg te gooien hoor! Ik ben het met je eens. Ik stel alleen voor om het op weken of maanden te partitioneren.

Ah okay. Het was me niet helemaal duidelijk dus ik wou het even expliciet maken

[Wasp]

Ik zie dat je SQL Server gebruikt. Recentere versies beschikken over een techniek om tabellen kolom-georiënteerd op te slaan, middels een clustered columnstore index.

Dit is mogelijk vanaf SQL Server 2012, maar kan pas echt goed gebruikt worden sinds 2014 of verder.

Door de enorme datacompressie (kan over de 90% gaan) heb je veel minder IO nodig om tot een resultaat te komen. Wel gaat de CPU belasting omhoog daardoor.

Heb je dat al overwogen/onderzocht?

[Montaner]

Het is toch niet bizar veel data? Zeker niet als je maar 31 dagen terug wilt. Als je verder terug wil kan je eventueel nog je tabel gaan partitionen op bepaalde tijdsperiodes. Het is sowieso wel verstandig om de business en andere afnemers niet te laten querien op dezelfde tabel waarin tegelijkertijd de records worden weggeschreven.