Effiziente Historisierung

Ich muss jetzt nochmal nachfragen, nachdem wir hier lange Ausführungszeiten der Queries beobachten. Die Anfrage ist so aufgebaut, dass postgres die Daten (schnell) über den Index finden müsste. Hier ein Beispiel:

select * from dalvekurzint_1___ where time_ > '2013-09-01 00:00:00' and time_ < '2013-09-04 00:00:00' and eaid = 2024303 and status_ = 1;

Die Ausführung der Anfrage dauert 79 Sekunden, es wurden 17279 Datensätze zurückgegeben. Wenn ich mir jetzt das Analyze dazu ansehe steht dort leider zusätzlich zum Index Scan noch etwas von einem Seq Scan. Warum?

"Result  (cost=0.00..567796.86 rows=11101 width=68)"
"  Output: public.dalvekurzint_1___.status_, public.dalvekurzint_1___.time_, public.dalvekurzint_1___.eaid, public.dalvekurzint_1___.qkfz, public.dalvekurzint_1___.qpkw, public.dalvekurzint_1___.qlkw, public.dalvekurzint_1___.vkfz, public.dalvekurzint_1___. (...)"
"  ->  Append  (cost=0.00..567796.86 rows=11101 width=68)"
"        ->  Seq Scan on public.dalvekurzint_1___  (cost=0.00..0.00 rows=1 width=68)"
"              Output: public.dalvekurzint_1___.status_, public.dalvekurzint_1___.time_, public.dalvekurzint_1___.eaid, public.dalvekurzint_1___.qkfz, public.dalvekurzint_1___.qpkw, public.dalvekurzint_1___.qlkw, public.dalvekurzint_1___.vkfz, public.dalvek (...)"
"              Filter: ((public.dalvekurzint_1___.time_ > '2013-09-01 00:00:00+02'::timestamp with time zone) AND (public.dalvekurzint_1___.time_ < '2013-09-04 00:00:00+02'::timestamp with time zone) AND (public.dalvekurzint_1___.eaid = 2024303) AND (public (...)"
"        ->  Index Scan using dalvekurzint_1____2013_08_13_00_00_00_index on public.dalvekurzint_1____2013_08_13_00_00_00 dalvekurzint_1___  (cost=0.00..567796.86 rows=11100 width=68)"
"              Output: public.dalvekurzint_1___.status_, public.dalvekurzint_1___.time_, public.dalvekurzint_1___.eaid, public.dalvekurzint_1___.qkfz, public.dalvekurzint_1___.qpkw, public.dalvekurzint_1___.qlkw, public.dalvekurzint_1___.vkfz, public.dalvek (...)"
"              Index Cond: ((public.dalvekurzint_1___.time_ > '2013-09-01 00:00:00+02'::timestamp with time zone) AND (public.dalvekurzint_1___.time_ < '2013-09-04 00:00:00+02'::timestamp with time zone) AND (public.dalvekurzint_1___.eaid = 2024303) AND (pu (...)"

Der Index zur Tabelle passt meiner Meinung nach genau zur Anfrage:

CREATE INDEX dalvekurzint_1____2013_08_13_00_00_00_index
  ON dalvekurzint_1____2013_08_13_00_00_00
  USING btree
  (time_, eaid, status_);

Der Seq Scan kommt dadurch, dass die Anfrage über die Basis-Tabelle läuft. Wenn ich die passende Tabelle direkt abfrage geht es laut analyze direkt über den Index. Es bleibt aber bei den langen Ausführungszeiten. Kann es an der schieren Größe der Tabelle liegen? Die Daten in jeder der abgeleiteten Tabellen belegen 65 GB, der Index jeder dieser Tabellen ist 27 GB groß.

Korrekt. Es wird das Basis-Tabelle angefragt, die (normalerweise) keine Daten hat - da ist ein Seq-Scan schneller als ein Index-Scan.

Kannst Du ein “explain (analyse, buffers) select …” zeigen? Wie ist PG optimiert, wieviel RAM hast Du?

Der Index wird doch auch verwendet:

@castorp: er meinte das:

"  ->  Append  (cost=0.00..567796.86 rows=11101 width=68)"
"        ->  Seq Scan on public.dalvekurzint_1___  (cost=0.00..0.00 rows=1 width=68)"

Hier der explain (analyze, buffers) output:

"Result  (cost=0.00..507717.34 rows=10889 width=68) (actual time=0.042..22714.827 rows=23039 loops=1)"
"  Buffers: shared hit=53322 read=17777"
"  ->  Append  (cost=0.00..507717.34 rows=10889 width=68) (actual time=0.040..22695.963 rows=23039 loops=1)"
"        Buffers: shared hit=53322 read=17777"
"        ->  Seq Scan on dalvekurzint_1___  (cost=0.00..0.00 rows=1 width=68) (actual time=0.002..0.002 rows=0 loops=1)"
"              Filter: ((time_ > '2013-10-01 00:00:00+02'::timestamp with time zone) AND (time_ < '2013-10-05 00:00:00+02'::timestamp with time zone) AND (eaid = 2024303))"
"        ->  Index Scan using dalvekurzint_1____2013_08_13_00_00_00_index on dalvekurzint_1____2013_08_13_00_00_00 dalvekurzint_1___  (cost=0.00..507717.34 rows=10888 width=68) (actual time=0.037..22692.724 rows=23039 loops=1)"
"              Index Cond: ((time_ > '2013-10-01 00:00:00+02'::timestamp with time zone) AND (time_ < '2013-10-05 00:00:00+02'::timestamp with time zone) AND (eaid = 2024303))"
"              Buffers: shared hit=53322 read=17777"
"Total runtime: 22717.993 ms"

Die Instanz ist in diesem Sinne gar nicht optimiert. Der Rechner hat 24 GB Ram, in postgres sind als effective_cache_size 20000 MB eingetragen, bei shared_buffers steht 8 GB.

naja, doch nicht sooo schlecht, oder? 23.000 Rows. Wie viele sind in der Tabelle insgesamt?

Beim Speichher hat ja relativ viel aus dem Cache.

In dieser Tabelle sind 178 Millionen Datensätze. Ich hab jetzt einmal statt Quartalstabellen Wochentabellen erzeugt, jede Tabelle enthält also nur noch ca. ein 12tel der Datensätze. Die Performance beim Schreiben bricht dramatisch ein, wenn man Datensätze über die Basis-Tabelle einfügt. Die Queries auf einen kleinen Teil der Daten sind immer noch sehr langsam:

"Aggregate  (cost=123680.32..123680.33 rows=1 width=0) (actual time=50441.168..50441.168 rows=1 loops=1)"
"  Buffers: shared hit=2 read=17685"
"  ->  Append  (cost=0.00..123664.21 rows=6443 width=0) (actual time=19.022..50435.698 rows=5759 loops=1)"
"        Buffers: shared hit=2 read=17685"
"        ->  Seq Scan on dalvekurzint_1___  (cost=0.00..0.00 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1)"
"              Filter: ((time_ > '2013-05-02 00:00:00+02'::timestamp with time zone) AND (time_ < '2013-05-03 00:00:00+02'::timestamp with time zone) AND (eaid = 2024303))"
"        ->  Index Only Scan using dalvekurzint_1____2013_05_01_00_00_00_index on dalvekurzint_1____2013_05_01_00_00_00  (cost=0.56..123664.21 rows=6442 width=0) (actual time=19.020..50433.113 rows=5759 loops=1)"
"              Index Cond: ((time_ > '2013-05-02 00:00:00+02'::timestamp with time zone) AND (time_ < '2013-05-03 00:00:00+02'::timestamp with time zone) AND (eaid = 2024303))"
"              Heap Fetches: 5759"
"              Buffers: shared hit=2 read=17685"
"Total runtime: 50441.483 ms"

Wenn man jetzt eine “ernsthafte” Auswertung über mehrere Wochen macht kommen so extrem hohe Abfragezeiten heraus, dass das praktisch nicht anwendbar ist. Das erstaunt mich jetzt, ich bin beispielsweise von Informix erheblich kürzere Anfragezeiten gewohnt.

“Buffers: shared hit=2 read=17685” - das ist also faktisch zu 100% von der Platte zu hobeln.

Wie selektiv ist “eaid = 2024303”? Evtl. wäre es besser, den im Index vorne zu haben, falls das selektiver als time ist. Probieren.

Ja, bei der Tabellengröße ist ja bei großen Abfragen zu ewarten, dass letztlich alles von der Platte kommt. Es gibt ingesamt 2700 verschiedene eaids, zu denen jeweils minütlich ein Datensatz in der Tabelle vorhanden ist. Der Index über Zeit und dann eaid ist dann doch genau richtig, oder?

17685 Datenblöcke entsprechen rund 138MB. Dafür 50 Sekunden Laufzeit scheint mir aber doch recht langsam zu sein.

Der Test-Rechner ist mit einer normalen Festplatte ausgerüstet, da geht die meiste Zeit für die Positionierung des Kopfes drauf (die Zeit maximaler Aktivität im Ressourcen Monitor ist 100%). Andererseits ist der Zugriff auf 138 MB Daten zur Lieferung von 11000 Datensätzen auch nicht schön. Gibt es noch Optimierungsmöglichkeiten z.B. über die Block-Größe die postgres verwendet?

Index-Scan -> Random Read. Da kommt schon was zusammen. Der Index hat, wenn ich mich recht erinnere, ein drittes Feld, was hier aber nicht gebraucht wird. (falls ich mich jetzt ned irre, hab das nicht alles im Kopf was Du hier gezeigt hast… prüf das noch mal)

Ja, es hat noch Feld “status_” das zur Versionierung von Datensätzen mit gleichem Zeitstempel genutzt wird. Das könnte man für diese Abfragen hier komplett wegfallen lassen. Ich mache jetzt eine Testreihe mit verschiedenen Abfragezeiträumen um besser zu verstehen, wie die Abfragedauer mit der Länge des Zeitintervalls der Abfrage skaliert.

Hier die Ergebnisse der Benchmarks. Ich hatte zwei Systeme zur Verfügung. Eines, auf dem die Datenbank auf einer isolierten und exklusiv genutzten Partition lag, ein anderes auf dem gleichzeitig ein Informix-Server lief und auf dem der Postgres-Server nicht exclusiv für die Benchmarks genutzt wurde. Auf dem exclusiven System hatte ich zusätzlich für eine Beispieldatenart den verkleinerten Index nur auf time_, eaid statt auf time_, eaid, status_ eingesetzt. Hier zunächst die Ergebnisse für das “langsamere” System (Tab = Anzahl betroffener Tabellen, Int = abgefragtes Intervall in Tagen, Dur = Ausführungsdauer der Abfrage in Sekunden):

Tab Int Dur
1	1	34		  Buffers: shared hit=1 read=31014
1	2	140		  Buffers: shared hit=1 read=62045
1	5	203		  Buffers: shared hit=1 read=154863
1	10	415		  Buffers: shared hit=1 read=309969
1	15	1256		  Buffers: shared hit=2 read=465658
1	30	1976		  Buffers: shared hit=2 read=950375
2	60	2524		  Buffers: shared hit=6 read=1862270

Und hier das Ergebnis für das exclusive System mit kleinerem Index:

Tab Int Dur
1	1	23		  Buffers: shared hit=3 read=17619
1	2	74		  Buffers: shared hit=2 read=35365
1	5	97		  Buffers: shared hit=1 read=88459
1	10	340		  Buffers: shared hit=1 read=176595
1	15	616		  Buffers: shared hit=1 read=265445
1	30	659		  Buffers: shared hit=86 read=540608
2	60	1441		  Buffers: shared hit=297 read=1159264

Bei beiden Systemen wurden die Daten tatsächlich von der Platte gelesen, sowohl der Filesystem-Cache als auch der postgres cache war so gut das möglich war “kalt”. An den Unterschieden bei “Buffers: read” kann man schön sehen, dass der Index in der zweiten Konstellation, obwohl er nur eine von drei Spalten weniger enthält, erheblich kleiner wurde. In Zahlen ausgedrückt bedeutet das eine Indexgröße von 18 GB beim dreispaltigen Index und nur 10 GB beim zweispaltigen Indexn pro History-Tabelle.

Im absoluten Vergleich ist postgres auf den verglichenen Servern damit bei diesen konkreten Abfragen bereits ohne den optimierten Index nicht nennenswert langsamer ist als Informix. Die vergleichbare Anfrage hat unter Informix 32 Minuten (1920 Sekunden) gedauert. Das war jetzt natürlich kein Test unter Laborbedingungen, reicht uns aber aus um ein Gefühl für die Leistungsfähigkeit von Postgres für diese spezifische Anwendung zu bekommen.

Guten Tag ich hätte auch noch eine Frage

gehe ich richtig in der Annahme dass wenn wir in der where clause nicht nur nach dem key (der für partionierung verwendet) wird einschränken sondern auch noch andere attribute einschränken die Partitionierung dann gar nichts bringt
weil der select dann eh über alle Partitionen geht

nach dem 2 Feld können wir nicht partionieren da wir ja bei postgres die partitionen im voraus quasi anlegen müssen und wir die werte im 2 feld nicht im voraus wissen können

oder kann man on the fly partitionen anlegen falls es eine partition noch nicht gibt?

wie sieht es aus kann man die master tabelle in einem schema halten und die “child” tabellen in einem anderen
ansonsten wird das viel zu unübersichtlich

Wenn in der Where-Clause die Partitionierung greift, dann klappt das auch. Weitere Conditions werden dann nur in der / den gültigen Partitionen geprüft.

Du kannst via TRIGGER auch on-the-fly Partitionen anlegen.

Und Du kannst die Master-Tabelle in einem anderen Schema haben wie die Kind-Tabellen.

wie ist eure erfahrung ?

wie stabil ist das ganze?

ich kenne es nur von Oracle und dort ist es alles sagen wir mal automatisch und nicht soviel händisch

grad auch was updates betrifft muss man ja dann selber partitionen “welchseln” falls nötig
zudem gibt es ja kein unique key mehr über alle partitionen

Normalerweise sollte der Key zur Partitionierung sich ja eher nicht ändern, oder? Wenn Du unbedingt einen Unique Key über alle Partitionen haben willst dann verwendet halt OID’s, aber eigentlich ist dieser Ratschlag nicht wirklich gut, besser wäre es zu prüfen, ob Du den wirklich benötigst.