Zeitreihen in Postgres vernünftig speichern

[M. Pritsch]

Hallo alle zusammen,

weis nicht ob ich im richtigen Forum bin, frag aber trotzdem mal:

Und zwar hab ich die Aufgabe mein Speicherverfahren von Zeitreihen

(-> http://de.wikipedia.org/wiki/Zeitreihen)

in Postgresql/Unix neu zu überdenken. Ein Zeitreihenglied besteht in der Regel aus einem Zeit- und einem Meßwert, sowie möglicherweise mehreren Zusatzinformationen. Man kann sie als Zeit-Funktion mit x-y-Werten auffassen bzw. wenn man sie in Blöcke zerlegt, als Vektoren in einem n-dimensionalen Raum.

In meiner Datenbank liegen schon an die 50 millionen Einträge und steigern sich täglich um ca. 25 000 weitere. Das bisherige Speicherverfahren beschränkte sich auf triviales Einfügen aller Daten in eine Tabelle. Dabei Stand sequentielles Durchsuchen/Sortieren nach Datum im Vordergrund. Leider dauert dieses mit der Zeit immer länger. Auch das interne Indizierungsverfahren mit B-Trees stellt nicht die Verbesserung dar, die gewünscht wäre. Am meisten bemerkbar machen sich aber die ganzen Joins.

Ist hier jemanden bekannt welche Techniken oder Indexe (z.B. Skyline Index) sich bewehrt haben bzw. ob vielleicht ein Hybridverfahren mit Auslagerung in Blockdateien (z.B. in C geschrieben - vorallem unter welcher lib-benutzung) den Datenzugriff beschleunigen könnten. Hier würde ich mich sehr über Tipps zu einschläger Literatur freuen.

Desweiteren würde mich das Quadermodell interessieren, mit dem das destatis arbeitet (hab leider keine Publikationen im Netz gefunden).

Vielleicht kann mir ja jemand mit ein bischen Erfahrung auf diesem Gebiet einschlägige Ratschläge geben. Würde mir sehr weiterhelfen. Danke.

[Nachtgeist]

Das ist ein bischen wenig Information fuer ein solches Problem.

Kannst du uns vielleicht sagen, wie deine Queries ausschauen?

Wie die Tabellen aufgebaut sind?

Welche Indizies du angelegt hast?

Was EXPLAIN und EXPLAIN ANALYZE zu deinen Queries sagt?

Laesst du ANALYZE bzw VACUUM ANALYZE regelmaessig laufen?

Welche Version von PostgreSQL benuetzt du?

Wenn wir hier nicht weiterkommen, koenntest du auch die Gurus auf den Mailinglisten nerven: http://www.postgresql.org/lists.html

HAND

[M. Pritsch]

Also:

>Kannst du uns vielleicht sagen, wie deine Queries ausschauen?

im moment entweder so:

select <gewünschte spalten> from <tabellenname> where <spalte mit timestamp> between '2003-09-22 23:45:00+00' and '2003-09-22 23:49:00+00';

oder zu 90% so:

select <gewünschte spalten> from <tabellenname> where epoche='2003-09-22 23:45:00+00';

oder oft so:

select <gewünschte spalten> from <tabellenname> where sensor_id=17;

und seltener so:

select <gewünschte spalten> from <tabellenname> where value=17;

>Wie die Tabellen aufgebaut sind?

im generellen so:

sensor_id | epoche | value

-----------+------------------------+--------

47 | 2003-09-22 23:45:00+00 | 72.7

...

Welche Indizies du angelegt hast?

...

public | wetter_epoche_idx_1995 | index | postgres | wetter

public | wetter_epoche_idx_1996 | index | postgres | wetter

...

public | wetter_pkey | index | postgres | wetter

public | wetter_pkey_1990 | index | postgres | wetter

public | wetter_pkey_1991 | index | postgres | wetter

public | wetter_pkey_1992 | index | postgres | wetter

...

public | wetter_sensor_id_idx_1988 | index | postgres | wetter

public | wetter_sensor_id_idx_1989 | index | postgres | wetter

public | wetter_sensor_id_idx_1990 | index | postgres | wetter

...

>Was EXPLAIN und EXPLAIN ANALYZE zu deinen Queries sagt?

Ein "explain analyze select * from wetter where epoche='2003-12-12 11:11:11+00';" liefert:

QUERY PLAN

-------------------------------------------------------------------------

Index Scan using wetter_sensor_id_idx_2003 on wetter (cost=0.00..5.42 rows=1 width=16) (actual time=28.32..28.32 rows=0 loops=1)

Index Cond: (epoche = '2003-12-12 11:11:11+00'::timestamp with time zone)

Total runtime: 28.32 msec

>Laesst du ANALYZE bzw VACUUM ANALYZE regelmaessig laufen?

ja

>Welche Version von PostgreSQL benuetzt du?

psql 7.3.2

Hoffentlich reichen diese Angaben vorerst einmal. Würde aber am liebsten die Datenbank generell neu aufrollen und bin grad auf der Suche nach effektieven Lösungsmöglichkeiten;)

Danke für den Tipp mit der mailinglist, hab auch schon darangedacht und werds dort vielleicht auch mal versuchen

mfg max

[Nachtgeist]

QUERY PLAN

-------------------------------------------------------------------------

Index Scan using wetter_sensor_id_idx_2003 on wetter (cost=0.00..5.42 rows=1 width=16) (actual time=28.32..28.32 rows=0 loops=1)

Index Cond: (epoche = '2003-12-12 11:11:11+00'::timestamp with time zone)

Total runtime: 28.32 msec

Kann sein, dass ich aufm Schlauch stehe, aber was gibts an ner Laufzeit von 28 msec auszusetzen?

psql 7.3.2

Schonmal mit 7.4 versucht?

Oder auch mit der beta von 8.0?

Da hat sich einiges getan.

[M. Pritsch]

explain analyze select * from wetter where sensor_id=27 and epoche between '2002-10-11 23:01:34+00' and '2004-08-21 10:10:10+00';

QUERY PLAN

----------------------------------------------------------------------

Index Scan using wetter_pkey on wetter (cost=0.00..407599.60 rows=101534 width=16) (actual time=52.73..22024.92 rows=41616 loops=1)

Index Cond: ((sensor_id = 27) AND (epoche >= '2002-10-11 23:01:34+00'::timestamp with time zone) AND (epoche <= '2004-08-21 10:10:10+00'::timestamp with time zone))

Total runtime: 22196.80 msec

(3 rows)

Die Releaseversion lasse ich im moment noch ausser acht, weil ich sowieso die Datenbank neu aufsetzten und organisieren will.

[Nachtgeist]

Schau dir mal das an:

http://www.postgresql.org/docs/7.3/static/indexes-multicolumn.html

Du solltest schauen, ob du fuer deine Queries auch die passenden Indizies parat hast.

[M. Pritsch]

thx

werde damit ein bischen rumexperimentieren

mfg max