Speicherung von Messdaten

Hallo,

ich entwerf grad ein Konzept zur Speicherung von Messdaten und rätzel grad, welches wohl das Vorteilhafteste sein könnte. Vielleicht könnt ihr ja auf Anhieb was sagen.

Merkmale sind:

  • unbestimmte (von min 100 bis mehrere 1000) Anzahl von Datenpunkten (im Projekt momentan ca. 300)
  • prinzipiell statische Messfrequenz, (für einzelne Datenpunkte oder kürzere Zeiträume aber dynamisch anpassbar)
  • Messungen erfolgen über mehrere Jahre hinweg kontinuierlich
  • Abfragen auf die Werte erfolgen auf kurze Zeiträume (z.b. Woche) aber auch über Langzeitraum (z.b. 3 Jahre)
  • Abfragen erfolgen nicht nur über einen Datenpunkt, sondern auch über mehrere oder alle

Meine Überlegungen zur Speicherarchitektur dieser Zeitreihen:

  • Speichere die Zeitreihe in einer Tabelle und jeden Datenpunkt in einer eigenen, verknüpfe die Datenpunkttabellen mit der Zeitreihentabelle. Vorteil für jeden Datenpunkt wird nur dann ein Insert ausgeführt wenn auch ein Wert geschrieben wird. der Datenpunkt mit der höchsten Messfrequenz gibt die Speicherung der Zeitstempel vor.
    Nachteil: Die Verknüpfung der Tabellen in einer Abfrage mit Joins kostet verdammt viel Zeit.

  • Speichere Alle Datenpunkte in einer Tabelle, pro Datenpunkt eine Spalte.
    Vorteil: keine Join-Operation nötig, mit guten Indizes Performante abfragen möglich
    Nachteil: irgendwann rießige Tabellen (vielleicht pro Jahr eine Tabelle?), Anzahl der Datenpunkte könnte die maximale Spaltenzahl sprengen, Datenpunkte mit niedrigerer Messfrequenz erzeugen Spalten mit vielen NULL-Werten

  • ein Kompromis aus den beiden oben genannten, durch Gruppierung von mehreren Datenpunkten in jeweils eine Tabelle

  • Speicherung der Messwerte in einem ARRAY oder TUPEL dass bei der Konfiguration der Datenbank dynamisch erzeugt wird…, dieses Tupel oder Array in einer Tabelle.

Speziell die letzte Idee gefällt mir ganz gut, aber ich habe keinerlei Erfahrung damit bzgl. Performance, Wartung, Stabilität… Kann da einer von euch ne Aussage zu machen?

Oder habt ihr noch andere Vorschläge??

Viele Grüße
beho

Mir gefällt die Idee mit dem Arrays nicht. Der Zugriff auf die Werte innerhalb ist nicht wirklich angenehm, und das ist dann auch ein Stück weit weg von dem, was man Normalisierung nennt.


Oder habt ihr noch andere Vorschläge??

ganz klassisch normalisiert.

Zeitpunkt, Datenpunkt, Meßwert

Wahlweise mit table-partitionierung, per range auf Zeitpunkt. Datenpunkt kannst du als FK auf eine andere Tabelle machen, die Details zu den Datenpunkten hat.

(ich hoffe, dein Problem richtig verstanden zu haben)

Andreas

Du meinst alle Messwerte in eine Tabelle, in eine Spalte egal von welchem Datenpunkt? Also inetwa so?:

CREATE TABLE ( id bigserial PRIMARY KEY, zeitstempel time);

CREATE TABLE  (id serial PRIMARY KEY, datenpunkt text );

CREATE TABLE ( 
id bigserial PRIMARY KEY, 
messwert double,  
time_id REFERENCES time (id)
datenpunkt_id REFERENCES datenpunkt (id)
);

Das ist genau was ich vermeiden will. Ich sollte vielleicht dazu sagen, dass ich eine Datenmenge von mehreren Milliarden Messwerten erwarte.

Die Hauptfrage die mich über die Arrays nachdenken lässt, ist, ob es bei Abfragen performant ist…

Viele Grüße
beho

Die Tabelle mit den Zeitspempel ist hyperfluid, speicher doch den Zeitspempel gleich in der Tabelle.

Die Hauptfrage die mich über die Arrays nachdenken lässt, ist, ob es bei Abfragen performant ist…

Viele Grüße
beho

Ja. Du willst jetzt die Summe der Meßwerte vom Juni 2007 wissen, die im Array an 627. Stelle stehen. Index? Fehlanzeige.
Dafür sind aber im Durchschnitt von den 1000 Meßpunkten nur 50 gefüllt. In Deinem Array also im Schnitt pro Zeile 950 Nullen. Ich kann beim besten Willen nicht erkennen, wo Du da einen Vorteil erwartest.

Andreas

Hallo beho,

leider hast Du nichts darüber geschrieben, in welcher Form die Meßdaten vorliegen und was Du mit den Daten machen willst.

Bei der Menge der Meßdaten und Meßpunkte würde ich die Daten eines Meßpunktes in der Form Timestamp, Meßwert in eine (CSV-) Datei schreiben und die Eigenschaften des Meßpunktes sowie die Referenz auf die entsprechende Datei in der Datenbank verwalten. Sollen die Meßdaten bewertet werden, kann man die entspechende Daten der Meßpunkte in die Datenbank laden und auswerten.



mfg

fristo

Er schrieb u.a.:
“- Abfragen auf die Werte erfolgen auf kurze Zeiträume (z.b. Woche) aber auch über Langzeitraum (z.b. 3 Jahre)”
“- Abfragen erfolgen nicht nur über einen Datenpunkt, sondern auch über mehrere oder alle”

Bei der Menge der Meßdaten und Meßpunkte würde ich die Daten eines Meßpunktes in der Form Timestamp, Meßwert in eine (CSV-) Datei schreiben und die Eigenschaften des Meßpunktes sowie die Referenz auf die entsprechende Datei in der Datenbank verwalten. Sollen die Meßdaten bewertet werden, kann man die entspechende Daten der Meßpunkte in die Datenbank laden und auswerten.

Naja, das stelle ich mir bei Deiner Form der Speicherung abenteuerlich vor…:

  • Summe der Meßwerte vom Punkt 523 und 648 des Monats Juni
  • Minimum der Meßwerte der Punkte 425 bis 340 vom Mai, wenn am Punkt 123 der Wert zwischen 7 und 10 liegt

to bee continued…

Andreas

Nein, nein, ich würde in dem Fall ein Script erstellen, welches beim Konfigurieren des Programmes das Array mit genauso vielen Stellen initialisiert, wie ich Datenpunkte habe und also Messstellen bekomme. Anschließend dann eine entsprechende Tabelle mit genau diesem Array erstellen…

Das Konzept die Messwerte in eine Spalte einer Tabelle zuschreiben, und dann noch die Zeit daneben, ist aus meiner Sicht Speicherverschwendung. Vielleicht mal ein kleines Beispiel wie meine Messreihen inetwa aussehen werden:

Zeitstempel; DP1; DP2; DP3;...;DPn
2007-07-11 16:58:00; 100.256; 56.589; 0.001; ...; #.###
2007-07-11 16:58:10; 100.000; 57.385; NULL; ...; #.###
2007-07-11 16:58:20; 100.550; 70.301; 0.005; ...; #.###
...

Indizes stell ich mir in der Form vor, dass ich aus dem Zeitstempel, min, h, woche und monat extrahier indiziere, so dass ich zumindest bei Abfragen über gewisse Zeitfenster optimieren kann. Die würde ich dann ebenfalls beim initialisieren der Datenbank per Script erzeugen. Übrigens ist mir nicht klar, wie ich Indizes auf die Messwerte selbst anwenden sollte.

Die Idee die Daten in ein cvs zu schreiben ist inakzeptabel, weil ich wirklich performante Abfragen über die gesamte Datenmenge benötige.

beho

schon klar, aber iirc kannst Du nicht via Index auf Values aus dem Array zugreifen. Daher wirst Du relativ viel aus der DB lesen müssen, um Analysen zu machen. Speicherst Du aber Timestamp:Meßpunkt:Meßwert, dann kannst Du auf Timestamp und Meßpunkt je einen Index legen. Seit 8.1 haben wir Memory Bitmap Index Scan, das geht dann richtig flott. Und du vergeudest keinen Platz.

Das Konzept die Messwerte in eine Spalte einer Tabelle zuschreiben, und dann noch die Zeit daneben, ist aus meiner Sicht Speicherverschwendung.

Gut, kann man drüber nachdenken. Wenn zu einem Zeitpunkt sehr viele Meßwerte sind, dann das so wie von Dir bereits skizziert mit der extra Tabelle für die Zeitpunkte.


Indizes stell ich mir in der Form vor, dass ich aus dem Zeitstempel, min, h, woche und monat extrahier indiziere, so dass ich zumindest bei Abfragen über gewisse Zeitfenster optimieren kann.

Wie fragst Du denn ab: willst Du nur Werte der Tage eines Monats jeweil von 15:00 bis 16:00 Uhr und den Rest nicht oder hast Du einen zusammenhängenden Zeitbereich?

Die würde ich dann ebenfalls beim initialisieren der Datenbank per Script erzeugen. Übrigens ist mir nicht klar, wie ich Indizes auf die Messwerte selbst anwenden sollte.

min() und max() für einen gegebenen Zeitraum?


Andreas

ahm, doch:

SELECT myarray[index] FROM mytable;

die Frage is halt ob das performant ist…

Daher wirst Du relativ viel aus der DB lesen müssen, um Analysen zu machen. Speicherst Du aber Timestamp:Meßpunkt:Meßwert, dann kannst Du auf Timestamp und Meßpunkt je einen Index legen. Seit 8.1 haben wir Memory Bitmap Index Scan, das geht dann richtig flott. Und du vergeudest keinen Platz.

Memory Bitmap Index Scan → du meinst das kombinieren von mehreren Indizees? Ja, davon geh ich aus. Andernfalls muss ich erstmal lesen… :wink:

Wie fragst Du denn ab: willst Du nur Werte der Tage eines Monats jeweil von 15:00 bis 16:00 Uhr und den Rest nicht oder hast Du einen zusammenhängenden Zeitbereich?

Sowohl als auch… man schreit nach der Eierlegenden Wollmilchsau… weißt doch wies ist.

min() und max() für einen gegebenen Zeitraum?

Oh, stimmt…

beho

Exakt. Daß Du auf ein Element im Array über den Index zugreifen kannst ist klar, was aber nicht geht: via Tabellen-Index feststellen, ob das 388. Element Deines Arrays NULL oder einen Wert hat. Hier hat PG dann bei Abfragen das ganze Array mit den 1000 Werten zu lesen, um festzustellen, daß da NULL ist. Dumm gelaufen.

Memory Bitmap Index Scan → du meinst das kombinieren von mehreren Indizees? Ja, davon geh ich aus. Andernfalls muss ich erstmal lesen… > :wink:

Vermutlich reden wir auch hier vorbei.

Ich meine damit die Fähigkeit von PG, Indexe auf mehreren Spalten, die als Suchkriterium dienen, im Speicher zu einer Bitmap zu kombinieren. Bringt teilweise 10 bis 20-fachen Zuwachs bei dafür passenden Abfragen. Man sieht das in der Ausgabe von EXPLAIN ANALYSE.



Andreas

Ok jetz hab ichs.

Ich meine damit die Fähigkeit von PG, Indexe auf mehreren Spalten, die als Suchkriterium dienen, im Speicher zu einer Bitmap zu kombinieren.

Auch ok, da muss ich mich also ma schlau machen.

Dank dir einstweilen, is echt n Spitzensupport den du hier leistest!!

btW: Spräche aus deiner Sicht trotzdem noch irgendwas für die Speicherung aller Werte in einer Spalte, so wie du es zuerst vorgeschlagen hast? Nö oder? Nur falls ich da was noch total übersehe…

Viele Grüße
beho