Verbindung 2er Tabellen, zeitlich gesteuert (Archiv- + Tages-aktuelle Daten)

1 Mio Daten in der Grundtabelle oder für die tägliche Auswertung?

Wenn es 1 Mio. Daten in der Grundtabelle sind, dann würde ich mir überlegen wie ich die Auswertung direkt dort hinkriege ohne das System lahm zu legen. Das sollte kein Problem sein.
 
Werbung:
Sind es 1mio Datensätze aktuell? Sprich es werden nicht mehr? Hat man die Indizes auf der Tabelle so im Griff das read/write 0 problemo ist?

Ja klar bei nur 1 mio Datensätze könnte man direkt auf 1 Tabelle arbeiten. Ich würde aber mal tippen es werden täglich mehr.
 
Ja 1 Million bei etwa 3 Jahren bei einer Tabelle.
Ich habe aber 60-70 Tabellen mit unterschiedlichen Datenmengen (mal "nur" 100.000, dann mal 600.000 oder auch, wie besagt, diese 1Mio).

Die Datensätze werden täglich mehr.
Und ich wollte hier einen Weg finden, den ich abarbeiten kann, den ich auch jeder Zeit abändern kann und den ich vor allem verstehe. Und da war der Prozess, den mir Chuki aufgezeigt hat, recht verständlich und klar.

Die Auswertung läuft aktuell über die gesamte Datenmenge.
Ich bräuchte da, wie gesagt, einen Weg um eben diese Abfrage performance-seitig zu minimieren und weiterhin meine Abfragen zu generieren.

Alles in Allem habe ich jetzt das, was ich benötige. Vielen Dank nochmal dafür.

MDDaniel schrieb:
1 Mio in 3 Jahren entspricht Daumen x Pi ~ 2000 Datensätze / Tag.
Wenn es da Probleme gibt kann man das in den Griff bekommen.
Zum Vergrößern anklicken....

Es gibt ja so keine Probleme, nur wollte einen sicheren Prozess haben, den ich anwenden kann, damit es eben zu keinen Problemen kommt, wenn ich meine Abfragen tätige...
 
Mal als Faustregel: wenn Du partitionierst, dann sollten je Partition schon wenigsens einige 10 Millionen Rows sein, eher mehr. Das ist für aktuelle Datenbanken (außer MySQL vielleicht ...) alles kein Problem.

Wegen weniger als 1 Million oder auch 3 Millionen in 3 Jahren kommst Du noch vermutlich locker in die Rente.
 
Worauf ich raus will ist:
Bei den Datenmengen sehe ich keinen Grund die Daten in eine andere Tabelle zu kopieren um sie auszuwerten. Du kannst Sie direkt aus der Grundtabelle heraus auswerten.

Wenn du nur Tagesdaten auswertest sind die Mengen so klein dass bei entsprechender Optimierung der Abfragen kein Problem entstehen sollte.
Solltest du die Auswertungen über einen größeren Zeitraum haben verschiebst du ohne Optimierung das Problem nur von einer Tabelle auf eine anderen und opferst dabei noch zusätzliche Ressourcen.

Das ist so wie wenn du einen Ordner voll Papier hast und jeden Abend 10 Blätter rausnimmst, sie kopierst und dann die Kopien nimmst um davon abzuschreiben statt gleich vom Original abzuschreiben.
Am Ende des Tages hattest du die meiste Arbeit mit dem Kopieren und Papier nachfüllen.
 
Ich lade hier nicht nur tagesaktuelle Daten aus. Es kann auch vorkommen, dass ich in die Vergangenheit, sagen wir letztes Jahr, schauen muss und davon meine neuen Berechnungen mache.
Und wenn ich jetzt einen solchen Zeitraum auswähle, kann ich damit schon unser Produktiv-System verlangsamen.
Daher die Daten einmal für das Archiv erstellt und auf eine NICHT-Produktive-DB abgelegt, und diese befülle ich mit den tagesaktuellen Daten am Ende des Tages bzw. rufe beide Tabellen/Views fürs Reporting auf.
 
um das mal etwas zu simulieren:

Code: 
edb=# create table messung(ts timestamp, value int);
CREATE TABLE
edb=*# insert into messung select '2010-01-01'::date + '5 minutes'::interval * s, random() *10000 from generate_series(1,10000000)s;
INSERT 0 10000000
edb=*# create index idx_ts on messung (ts);
CREATE INDEX
edb=*# commit;
COMMIT

edb=*# select max(ts) from messung ;
         max         
---------------------
 2105-01-25 05:20:00
(1 row)


Wir haben also 10 Millionen Datensätze verteilt auf 95 Jahre. Und einen Index.

Nun fragen wir Tagessummen ab, von 2 Tagen, mal eher am Anfang, mal eher am Ende:

Code: 
edb=*# explain analyse select sum(value) from messung where ts between '2020-01-01' and '2020-01-02';
                                                                   QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=15.71..15.72 rows=1 width=8) (actual time=0.342..0.343 rows=1 loops=1)
   ->  Index Scan using idx_ts on messung  (cost=0.43..15.01 rows=279 width=4) (actual time=0.080..0.255 rows=289 loops=1)
         Index Cond: ((ts >= '2020-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2020-01-02 00:00:00'::timestamp without time zone))
 Planning Time: 0.736 ms
 Execution Time: 0.394 ms
(5 rows)

edb=*# explain analyse select sum(value) from messung where ts between '2099-01-01' and '2099-01-02';
                                                                   QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=16.48..16.49 rows=1 width=8) (actual time=0.446..0.448 rows=1 loops=1)
   ->  Index Scan using idx_ts on messung  (cost=0.43..15.69 rows=313 width=4) (actual time=0.167..0.350 rows=289 loops=1)
         Index Cond: ((ts >= '2099-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2099-01-02 00:00:00'::timestamp without time zone))
 Planning Time: 0.168 ms
 Execution Time: 0.500 ms
(5 rows)

edb=*#

Die Execution-times sind annähernd gleich.

tl;dr

Wenn bei Abfragen auf einen älteren Zeitraum, bei nur 1 Millionen Rows in 3 Jahren, Du bei älteren Datensätzen ein Problem hast, dann liegt das eher an der Abfrage und fehlenden/falschen Indexen als an der Größe der Tabelle.
 
und hier noch mal mit 25 Millionen Rows in ca. 3 Jahren: (jetzt mit Rows aller 5 Sekunden, erst aller 5 Minuten)

Code: 
edb=*# create table messung(ts timestamp, value int);
CREATE TABLE
edb=*# insert into messung select '2010-01-01'::date + '5 seconds'::interval * s, random() *10000 from generate_series(1,25000000)s;
INSERT 0 25000000
edb=*# create index idx_ts on messung (ts);
CREATE INDEX
edb=*# select max(ts) from messung ;
         max         
---------------------
 2013-12-17 18:13:20
(1 row)
edb=*# explain analyse select sum(value) from messung where ts between '2012-01-01' and '2012-01-02';
                                                                   QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=735.76..735.77 rows=1 width=8) (actual time=5.539..5.540 rows=1 loops=1)
   ->  Index Scan using idx_ts on messung  (cost=0.44..688.84 rows=18770 width=4) (actual time=0.018..3.969 rows=17281 loops=1)
         Index Cond: ((ts >= '2012-01-01 00:00:00'::timestamp without time zone) AND (ts <= '2012-01-02 00:00:00'::timestamp without time zone))
 Planning Time: 0.165 ms
 Execution Time: 5.567 ms
(5 rows)

edb=*#

Mehr Datensätze insgesamt und mehr Datensätze (17281) für einen Tag, daher nun etwas länger. Desktop-PC, ungetunte Datenbank.
 
Werbung:
akretschmer schrieb:
Die Execution-times sind annähernd gleich.
Zum Vergrößern anklicken....

:confused: Eigentlich besagt das nur das die B-Tree Suche bei gleichmäßig verteilten simulierten Daten sich kaum unterscheidet. Was Logisch und klar sein sollte.

Wenn die Daten für Tableau aufbereitet werden müssen hat das aber mit der Simulation keinerlei Aussagekraft.
Abhängig von den Abfragen, Verknüpfungen und Berechnungen mag es Sinn haben Daten "fertig" bereitzustellen.
Wenn man die Daten Just-in-Time berechnet bringt es nichts wenn man sie von einer Tabelle in eine andere verschiebt, außer vielleicht gewisse Fußangeln falls es mal nicht funktioniert hat.
Die Last auf der Datenbank bleibt dann die gleiche und belastet das Produktivsystem zumindest gleichermaßen.
 
Zurück
Oben