Effektives Update einer Tabelle mit 32Mio Datensätzen - wie?

Hallo zusammen!

Mein Problem ist folgendes:

  • 2 Tabellen, eine mit ca. 400.000 Datensätzen (kapdaten) und eine mit ca 32Mio Datensätzen (gesamtumsatz)

  • Auf Grundlage der kleineren Tabelle sollen Umsatzdaten in der größeren zeilenweise verschoben werden. Wichtig sind vor allem die beiden u.a. UPDATE-Befehle:

  set ums_brut_n = ums_brut_n - k."Umsatz", menge_n = menge_n - k."Bez_Menge"

und

  set ums_brut_n = ums_brut_n + k."Umsatz", menge_n = menge_n + k."Bez_Menge"
  • Die INSERT-Befehle ergänzen die große Tabelle mit Zeilen, die bisher nur in der kleinen Tabelle vorhanden waren.

  • Die komplette Funktion (s.u.) habe ich vorher an kleineren Test-Tabellen ausprobiert und keinerlei Probleme gehabt. Wende ich sie nun auf die beiden großen Tabellen an, braucht er für 100 Durchläufe der FOR-Schleife über eine Stunde und ist dann mit den ersten 100 Datensätzen immer noch nicht fertig!


    Hilfe! Lassen sich die Abfragen irgendwie effektiver programmieren? Wenn ich auf 400.000 Datensätze hochrechne, komme ich auf eine Berechnungszeit von über einem Monat (!)

Es ist also ganz klar, dass ich mein Problem anders angehen muss – nur wie? Ich bin für jeden Tipp dankbar!

Ich bin kein Experte auf dem Gebiet und habe einfach aus dem Bauch heraus den Code geschrieben. Für mich ist es die logische Abfolge von notwendigen Befehlen um die Tabelle „gesamtumsatz“ zu aktualisieren. - Nur hatte ich noch nie das Vergnügen mit einer so großen Anzahl an Datensätzen… :slight_smile:

Vielen Dank im Voraus für eure Antworten!

Grüße, Chris




create function updategesamtdaten () RETURNS void AS '
DECLARE
x double precision;
BEGIN
for x in 1..400000

Loop


insert into gesamtumsatz (

select store_id, "Best_Dat", art_id, 0, 0 
from kapdaten_21_04_08
WHERE not exists 
    
    (select g.dat_id 
    from gesamtumsatz g, kapdaten_21_04_08 k
    WHERE g.dat_id = k."Best_Dat"
    AND g.store_id = k.store_id
    AND g.art_id = k.art_id
    and k."Kap_ID" = x)
    
AND "Kap_ID" = x

);


insert into gesamtumsatz (

select store_id, "Bez_Dat", art_id, "Bez_Menge", "Umsatz" 
from kapdaten_21_04_08
WHERE not exists 
    
    (select g.dat_id 
    from gesamtumsatz g, kapdaten_21_04_08 k
    WHERE g.dat_id = k."Bez_Dat"
    AND g.store_id = k.store_id
    AND g.art_id = k.art_id
    and k."Kap_ID" = x)
    
AND "Kap_ID" = x

);


update gesamtumsatz
set ums_brut_n = ums_brut_n - k."Umsatz", menge_n = menge_n - k."Bez_Menge"
from kapdaten_21_04_08 k
WHERE gesamtumsatz.dat_id = k."Bez_Dat"
AND gesamtumsatz.store_id = k.store_id
AND gesamtumsatz.art_id = k.art_id 
and k."Kap_ID" = x;

update gesamtumsatz
set ums_brut_n = ums_brut_n + k."Umsatz", menge_n = menge_n + k."Bez_Menge"
from kapdaten_21_04_08 k
WHERE gesamtumsatz.dat_id = k."Best_Dat"
AND gesamtumsatz.store_id = k.store_id
AND gesamtumsatz.art_id = k.art_id
AND k."Kap_ID" = x;


End Loop;
END;
'language plpgsql;

Das kommt mir alles etwas verworren vor.

Hast du mal getestet, an welchen Stellen genau du die meiste Zeit verlierst? Ich möchte schätzen, die Insert Operationen kosten einiges an Zeit.


Desweiteren: was für eine Konfiguration hast du auf dem Server?


P.S.: Ich habe nicht wirklich verstanden, was du da erreichen möchtest, aber dazu kommen wir ev. später.

Dein Ziel, ‘Daten zeilenweise zu verschieben’ klingt wie eine unglückliche Formulierung von ‘Prinzip von Datenbanktabellen nicht verstanden’. Aber ich hab auch nicht wirklich verstanden, was Du machen willst.

set ums_brut_n = ums_brut_n - k.“Umsatz”, menge_n = menge_n - k.“Bez_Menge”

>
> und
>
> ```text
  set ums_brut_n = ums_brut_n + k."Umsatz", menge_n = menge_n + k."Bez_Menge"

Das könnte man u.U. in einem Update schreiben, mittels CASE.

  • Die INSERT-Befehle ergänzen die große Tabelle mit Zeilen, die bisher nur in der kleinen Tabelle vorhanden waren.

  • Die komplette Funktion (s.u.) habe ich vorher an kleineren Test-Tabellen ausprobiert und keinerlei Probleme gehabt.

Wende ich sie nun auf die beiden großen Tabellen an, braucht er für 100 Durchläufe der FOR-Schleife über eine Stunde und ist dann mit den ersten 100 Datensätzen immer noch nicht fertig!


Hilfe! Lassen sich die Abfragen irgendwie effektiver programmieren? Wenn ich auf 400.000 Datensätze hochrechne, komme ich auf eine Berechnungszeit von über einem Monat (!)

Es ist also ganz klar, dass ich mein Problem anders angehen muss – nur wie? Ich bin für jeden Tipp dankbar!

Ich bin kein Experte auf dem Gebiet und habe einfach aus dem Bauch heraus den Code geschrieben. Für mich ist es die logische Abfolge von notwendigen Befehlen um die Tabelle „gesamtumsatz“ zu aktualisieren. - Nur hatte ich noch nie das Vergnügen mit einer so großen Anzahl an Datensätzen… :slight_smile:

Vielen Dank im Voraus für eure Antworten!

Grüße, Chris

\

create function updategesamtdaten () RETURNS void AS ’
DECLARE
x double precision;
BEGIN
for x in 1…400000

Loop

insert into gesamtumsatz (

select store_id, “Best_Dat”, art_id, 0, 0
from kapdaten_21_04_08
WHERE not exists

(select g.dat_id 
from gesamtumsatz g, kapdaten_21_04_08 k
WHERE g.dat_id = k."Best_Dat"
AND g.store_id = k.store_id
AND g.art_id = k.art_id
and k."Kap_ID" = x)

AND “Kap_ID” = x

);

insert into gesamtumsatz (

select store_id, “Bez_Dat”, art_id, “Bez_Menge”, “Umsatz”
from kapdaten_21_04_08
WHERE not exists

(select g.dat_id 
from gesamtumsatz g, kapdaten_21_04_08 k
WHERE g.dat_id = k."Bez_Dat"
AND g.store_id = k.store_id
AND g.art_id = k.art_id
and k."Kap_ID" = x)

AND “Kap_ID” = x

);

update gesamtumsatz
set ums_brut_n = ums_brut_n - k.“Umsatz”, menge_n = menge_n - k.“Bez_Menge”
from kapdaten_21_04_08 k
WHERE gesamtumsatz.dat_id = k.“Bez_Dat”
AND gesamtumsatz.store_id = k.store_id
AND gesamtumsatz.art_id = k.art_id
and k.“Kap_ID” = x;

update gesamtumsatz
set ums_brut_n = ums_brut_n + k.“Umsatz”, menge_n = menge_n + k.“Bez_Menge”
from kapdaten_21_04_08 k
WHERE gesamtumsatz.dat_id = k.“Best_Dat”
AND gesamtumsatz.store_id = k.store_id
AND gesamtumsatz.art_id = k.art_id
AND k.“Kap_ID” = x;

End Loop;
END;
'language plpgsql;

>

- warum ist Deine Variable x, mit der Du die ID's vergleichst, als double definiert? Ich rate mal wild, das die ID's int sind. Nicht nur, daß durch das nötige casten Zeit drauf geht, es kann auch passieren, daß durch Rundungsfehler einige Deiner ID's nicht gefunden werden.

- deine Schleife läuft 400.000 mal durch, da kommt es u.a. zu ebenso vielen Insert-befehlen, welche wiederum soviel selects auslösen. Eigentlich willst Du doch alle 400.000 Datensätze in die andere Tabelle einfügen, oder? Warum also 799999 zusätzliche Operationen?

- Du fügst erst die Daten ein, mittels 400.000 mal Select und 400.000 mal Insert, um dann nochmals 2 mal je 400.000 Update-Befehle auf diese Daten anzuwenden, oder sehe ich das flasch?

- Deine Udates updaten das selbe Feld in abhängigkeit, ob ein Feld mal gleich dem einen und mal dem anderen Feld ist. Bist Du sicher, daß das so richtig ist?

Wenn Du Dir und uns das alles a bissl leichter machen willst, zeige uns mal anhand von Beispieltabellen, was Du erreichen willst. Möglicherweise geht all das,was Du da mit insgesammt 4 mal 400.000 Befehlen machst, mit exakt einem Befehl.

Andreas

Vielen Dank für die schnellen Antworten :slight_smile:

  • warum ist Deine Variable x, mit der Du die ID’s vergleichst, als double definiert? Ich rate mal wild, das die ID’s int sind. Nicht nur, daß durch das nötige casten Zeit drauf geht, es kann auch passieren, daß durch Rundungsfehler einige Deiner ID’s nicht gefunden werden.

→ Da hast du Recht: Hab die Variable anfangs als ohne weiter darüber nachzudenken als Double definiert…

  • deine Schleife läuft 400.000 mal durch, da kommt es u.a. zu ebenso vielen Insert-befehlen, welche wiederum soviel selects auslösen. Eigentlich willst Du doch alle 400.000 Datensätze in die andere Tabelle einfügen, oder? Warum also 799999 zusätzliche Operationen?

→ Nein, es soll kein Datensatz aus der kleineren Tabelle in die große eingefügt werden. Die kleinere Tabelle enthält Teilumsatzwerte, die nach der Zuordnung zur richtigen Spalte der großen Tabelle in den Update-Befehlen für Rechenoperationen verwendet werden. Es werden also keine Zeilen eingefügt, sondern lediglich die Teilumsatzwerte verwendet um die Umsatzwerte der großen Tabelle zu verändern.


Die INSERT-Befehle habe ich eingefügt, weil in einigen Fällen (ca. 5%) die entsprechende Zeile auf die ein UPDATE durchgeführt werden soll, noch nicht existiert und erst erstellt werden muss. Daher auch auch der “where not exists”-Teil.

  • Du fügst erst die Daten ein, mittels 400.000 mal Select und 400.000 mal Insert, um dann nochmals 2 mal je 400.000 Update-Befehle auf diese Daten anzuwenden, oder sehe ich das flasch?

→ Ich füge ja nur in 5% der Fälle tatsächlich eine ganze Zeile aus der kleineren Tabelle ein. Zu 95% werden die Daten aus der kleinen Tabelle lediglich dazu verwendet, die Umsatzwerte in der großen Tabelle durch Addition oder Subtraktion mit dem Wert der kleinen Tabelle zu verändern.

  • Deine Udates updaten das selbe Feld in abhängigkeit, ob ein Feld mal gleich dem einen und mal dem anderen Feld ist. Bist Du sicher, daß das so richtig ist?

→ Ja, das ist korrekt so. Im Prinzip soll folgendes geschehen:
Nimm eine Zeile aus der kleinen Tabelle und ordne sie der entsprechenden Zeile in der großen Tabelle zu. Führe in dieser Zeile eine Subtraktion durch (1. UPDATE-Befehl) und addiere den Wert in einer anderen Zeile (2. UPDATE-Befehl).

Wenn eine der beiden Zeilen in der großen Tabelle noch nicht existiert, erstelle sie vorher mittels INSERT-Befehl.

Beispiel:

Die kleine Tabelle enthält folgenden Datensatz:
store_id = 446
art_id = 514881
Best_Dat = 22.02.2008 (<- Bestelldatum)
Bez_Dat = 08.03.2008 (<- Datum an dem tatsächlich auch bezahlt wurde)
Menge = 1
Umsatz = 22,49

Die erste Zeile der großen Tabelle sieht so aus:
store_id = 446
art_id = 514881
dat_id = 08.03.2008
Menge = 1
Umsatz = 22,49

Es werden nun Menge und Umsatz anhand der Werte aus der kleinen Tabelle subtrahiert und in folgender Zeile der großen Tabelle wieder eingefügt:

store_id = 446
art_id = 514881
dat_id = 22.02.2008
Menge = 13
Umsatz = 322,87

Das bedeutet, dass die Menge nun = 14 und der Umsatz nun = 345,36 ist.

Dieser Prozess soll auf Grundlage der kleineren Tabelle in einer Schleife durchlaufen werden (also ca. 400.000 Mal).

Ist jetzt soweit klar, was ich meine?

Ich bedanke mich wieder im Voraus für eure Optimierungsvorschläge! :slight_smile:

Ich versuch, Deine Logik zu erkennen, aber nur mit recht wenig Erfolg. Deine ‘große’ Tabelle soll eine Art Umsatzstatistik je Artikel werden?

→ Ja, das ist korrekt so. Im Prinzip soll folgendes geschehen:
Nimm eine Zeile aus der kleinen Tabelle und ordne sie der entsprechenden Zeile in der großen Tabelle zu. Führe in dieser Zeile eine Subtraktion durch (1. UPDATE-Befehl) und addiere den Wert in einer anderen Zeile (2. UPDATE-Befehl).

Linke Tasche → rechte Tasche?

Wenn eine der beiden Zeilen in der großen Tabelle noch nicht existiert, erstelle sie vorher mittels INSERT-Befehl.

Beispiel:

Die kleine Tabelle enthält folgenden Datensatz:
store_id = 446
art_id = 514881
Best_Dat = 22.02.2008 (<- Bestelldatum)
Bez_Dat = 08.03.2008 (<- Datum an dem tatsächlich auch bezahlt wurde)
Menge = 1
Umsatz = 22,49

Die erste Zeile der großen Tabelle sieht so aus:
store_id = 446
art_id = 514881
dat_id = 08.03.2008
Menge = 1
Umsatz = 22,49

Es werden nun Menge und Umsatz anhand der Werte aus der kleinen Tabelle subtrahiert und in folgender Zeile der großen Tabelle wieder eingefügt:

store_id = 446
art_id = 514881
dat_id = 22.02.2008
Menge = 13
Umsatz = 322,87

Das bedeutet, dass die Menge nun = 14 und der Umsatz nun = 345,36 ist.

>
>
> Dieser Prozess soll auf Grundlage der kleineren Tabelle in einer Schleife durchlaufen werden (also ca. 400.000 Mal).
>
> Ist jetzt soweit klar, was ich meine?

Etwa so?:

```text
test=# select * from kapdaten ;
 store_id | art_id | best_datum | bez_datum  | menge | umsatz
----------+--------+------------+------------+-------+--------
      446 | 514881 | 2008-02-22 | 2008-03-08 |     1 |  22.49
(1 row)

test=*# select * from gesamtumsatz ;
 store_id | art_id |   dat_id   | menge | umsatz
----------+--------+------------+-------+--------
      446 | 514881 | 2008-03-08 |     1 |  22.49
      446 | 514881 | 2008-02-22 |    13 | 322.87
(2 rows)

test=*# update gesamtumsatz  set menge=(case when dat_id=k.bez_datum then gesamtumsatz.menge-k.menge when dat_id=k.best_datum then gesamtumsatz.menge+k.menge end), umsatz=(case when dat_id=k.bez_datum then gesamtumsatz.umsatz-k.umsatz when dat_id=k.best_datum then gesamtumsatz.umsatz+k.umsatz end) from kapdaten k where gesamtumsatz.store_id=k.store_id and gesamtumsatz.art_id=k.art_id;
UPDATE 2
test=*# select * from gesamtumsatz ;
 store_id | art_id |   dat_id   | menge | umsatz
----------+--------+------------+-------+--------
      446 | 514881 | 2008-03-08 |     0 |   0.00
      446 | 514881 | 2008-02-22 |    14 | 345.36
(2 rows)

Andreas

[quote=“akretschmer”]Ich versuch, Deine Logik zu erkennen, aber nur mit recht wenig Erfolg.

Für den Fall, daß ich das doch richtig erkannt habe, könntest Du Deine Insert-Orgie so verkürzen:

test=# select * from kapdaten ;
 store_id | art_id | best_datum | bez_datum  | menge | umsatz
----------+--------+------------+------------+-------+--------
      446 | 514881 | 2008-02-22 | 2008-03-08 |     1 |  22.49
      447 |   4711 | 2008-01-01 | 2008-02-01 |    10 |  10.00
(2 rows)

test=*# select * from gesamtumsatz ;
 store_id | art_id |   dat_id   | menge | umsatz
----------+--------+------------+-------+--------
      446 | 514881 | 2008-03-08 |     1 |  22.49
      446 | 514881 | 2008-02-22 |    13 | 322.87
(2 rows)

test=*# -- (store_id,art_id) mit (447,4711) fehlt in gesamtumsatz
test=*# insert into gesamtumsatz select store_id, art_id, best_datum, 0,0 from kapdaten where (store_id,art_id) not in (select distinct store_id, art_id from gesamtumsatz);
INSERT 0 1
test=*# select * from gesamtumsatz ;
 store_id | art_id |   dat_id   | menge | umsatz
----------+--------+------------+-------+--------
      446 | 514881 | 2008-03-08 |     1 |  22.49
      446 | 514881 | 2008-02-22 |    13 | 322.87
      447 |   4711 | 2008-01-01 |     0 |   0.00
(3 rows)

test=*# -- analog dann noch ein insert wie oben für bez_datum, schaffst Du nun selber

Andreas

Allgemeiner Nachtrag:

Eine Funktion, die weder Parameter erwartet noch zurückliefert, ist an sich schon mal verdächtig. as, was Du innerhalb der Funktion machst, also die SQL-Befehle, solltest Du separat mal auf Geschwindigkeit (Ausführungsplan) untersuchen. Bist Du einklich sicher, daß in der ‘kleinen’ Tabelle immer exakt 400.000 Datensätze sind? Ich unterstelle einfach mal: nein.

weiter:

warum aktualisierst Du Deine ‘große’ Tabelle nicht gleich, wenn ein Ereigniss in der ‘kleinen’ Tabelle eintritt? Hint: TRIGGER wurden bereits erfunden.

Andreas

Vielen Dank für die Mühe und die vielen Antworten!

Ich habe ab jetzt leider bis Freitag keine Zeit mich weiter darum zu kümmern, werde aber versuchen, deine Vorschläge dann umzusetzen.

PS: Eine Abfrage mit der von mir angegebenen Funktion und nur den ersten 100 Datensätzen aus kapdaten habe ich nach 16 Stunden (!) abbrechen müssen… Wie kann das sein? Es kann doch nicht 16 Stunden dauern, bis 100 Zeilen aktualisiert worden sind?

Ich habe meine Funktion zuvor an kleineren Testtabellen ausprobiert, wo sie ohne Probleme lief (kapdaten = 204 Datensätze, gesamtdaten = ca 13000 Datensätze).

Grüße, Chris

warum aktualisierst Du Deine ‘große’ Tabelle nicht gleich, wenn ein Ereigniss in der ‘kleinen’ Tabelle eintritt? Hint: TRIGGER wurden bereits erfunden.

Es handelt sich um ein einmalige Datenbankkorrektur, die so eigentlich nicht vorgesehen war…

Du machst da u.a. ein

insert … ( select … where (select …))

Schau einfach mal, wie lange das dauert und welcher Ausführungsplan da zum Einsatz kommt. Schau, wie lange die Update-Befehle brauchen und was da für ein Plan ist. Bedenke, daß da möglicherweise bei Deinen Select-Befehlen relativ große Zwischenresultate anfallen, daß da u.U. geswappt werden muß, daß da vielleicht Seq-Scans zum Einsatz kommen (weil passende Indexe fehlen) und so weiter. Ohne solche Details kann ich und jeder andere hier nur Mußmaßungen anstellen.

Deine Idee, einfach faktisch zu Fuß für jede ID ‘der kleinen Tabelle’ in einer Schleife durch die ‘große Tabelle’ wie eine schlachtreife Weihnachtgans zu watscheln ist offensichtlich nicht gut. SQL bietet da andere Möglichkeiten, ich hab versucht, Dir das zu zeigen.

Andreas

Hallo nochmal,

ich hab´s ja gestern schon erwähnt, bin kein Experte auf dem Gebiet :slight_smile: Komme eher aus der Java-Ecke und hab mich vorher immer mit TransAct-SQL und dem SQL Server Management Studio rumgeschlagen… Mir fällt es daher einfach schwer, mich an gewisse (oder eher ziemlich viele) Sachen in Postgres zu gewöhnen :slight_smile:

Wie gesagt, werde mich am Freitag nochmal dran setzen.
Ach, und die beabsichtigte Vorgehensweise der Funktion hast du richtig verstanden. Werde die beiden von dir vorgeschlagenen Befehle

update gesamtumsatz  set menge=(case when dat_id=k.bez_datum then gesamtumsatz.menge-k.menge when dat_id=k.best_datum then gesamtumsatz.menge+k.menge end), umsatz=(case when dat_id=k.bez_datum then gesamtumsatz.umsatz-k.umsatz when dat_id=k.best_datum then gesamtumsatz.umsatz+k.umsatz end) from kapdaten k where gesamtumsatz.store_id=k.store_id and gesamtumsatz.art_id=k.art_id;

und

insert into gesamtumsatz select store_id, art_id, best_datum, 0,0 from kapdaten where (store_id,art_id) not in (select distinct store_id, art_id from gesamtumsatz);

am Freitag ausprobieren :slight_smile: Weiterhin werde ich eine Indizierung der beiden Tabellen vornehmen.

Bis dahin erstmal vielen Dank!

Darum holt man sich jemanden mit Fachwissen ins Haus, um nicht genau durch solche Kleinigkeiten tagelang hängen zu bleiben.

Nun, was ads vielleicht sagen wollte: Dinge wie Indexe sind nicht PG-spezifisch. Auch EXPLAIN ist das nicht und auch das SQL, welches PG spricht, ist nicht wirklich PG-spezifisch. Wenn ihr mit Datenbanken arbeitet, dann ist ein gewissen Grundmaß an Wissen zu SQL ganz allgemein sinnvoll, und natürlich auch zum real eingesetzten RDBMS.

Aber egal, ich würd mich freuen, wenn am Freitag oder so Feedback bekomme, ob das erstens) mit meinen Statements funktioniert (ich geb da keine Garantie, is klar, gell) und ob das zweitens) auch noch schneller ist (falls es funktioniert, sollte es um Größenordnungen schneller sein).

Andreas

Hallo!
Hab mich am Freitag hingesetzt und mal o.a. Vorschläge und Verbesserungen durchgeführt. Leider habe ich noch keine Möglichkeit gefunden, den UPDATE-Befehl ohne Schleife auszuführen.

  update gesamtumsatz  
  set menge=(case 
  when dat_id=k.bez_datum then gesamtumsatz.menge-k.menge 
  when dat_id=k.best_datum then gesamtumsatz.menge+k.menge end), 
  umsatz=(case 
  when dat_id=k.bez_datum then gesamtumsatz.umsatz-k.umsatz 
  when dat_id=k.best_datum then gesamtumsatz.umsatz+k.umsatz end) 
  from kapdaten k 
  where gesamtumsatz.store_id=k.store_id 
  and gesamtumsatz.art_id=k.art_id
  and gesamtumsatz_2008_aktuell.dat_id = k."Best_Dat";

bringt nicht das gewünschte Ergebnis, d.h. es werden zwar die gewünschten Spalten in der großen Tabelle anhand der Daten in der kleinen Tabelle aktualisiert, aber alle nicht zu verändernden Spalten in der großen Tabelle mit „NULL“ gefüllt. Gibt es eine Möglichkeit den Befehl so umzuformulieren, dass die Werte der anderen Spalten nicht „angerührt“ werden?

Das gewünschte Ergebnis erhalte ich nur, wenn ich um die UPDATE-Befehle eine Schleife herumlege, die alle Datensätze der kleinen Tabelle durchläuft, und den Zusatz

  and k."Kap_ID" = x;

an den UPDATE-BEFEHL in der WHERE-Bedingung hänge.

Dazu noch eine Anmerkung: Die einzelnen Datensätze der beiden Tabellen lassen sich nur dann eindeutig zuordnen, wenn die Spalten „store_id, art_id und dat_id“ <-> „store_id, art_id, Bez_Dat“ miteinander verglichen werden.

Leider habe ich auch noch ein anderes Problem: Anscheinend kann Postgres nicht mit der bei der Berechnung erzeugten Datenmenge umgehen? Ich bekomme nach einiger Zeit einen „Out of Memory“-Error angezeigt. Eigentlich müsste Postgres doch mit der Datenmenge (32 Mio und 400.000 Datensätze) umgehen können, oder?

Die Einzelindizierung der Spalten „store_id, art_id und dat_id“ in beiden Tabellen hat die Abfragegeschwindigkeit in meinen Tests um einiges erhöht.

Vielen Dank im Voraus erneut für eure Antworten!
Grüße, Chris

Unten nun der von mir momentan verwendete Code:

  drop function if exists updategesamtdaten ();
  create function updategesamtdaten () RETURNS void AS '
  DECLARE
  x integer; 
  BEGIN
   
  insert into gesamtumsatz_2008_aktuell ( 
  select distinct store_id, "Bez_Dat", art_id, "Bez_Menge", "Umsatz" 
  from kapdaten_21_04_08 
  where (store_id, art_id, "Bez_Dat") not in 
                 (select distinct store_id, art_id, dat_id from gesamtumsatz_2008_aktuell));
   
  insert into gesamtumsatz_2008_aktuell ( 
  select distinct store_id, "Best_Dat", art_id, 0, 0 
  from kapdaten_21_04_08
  where (store_id, art_id, "Best_Dat") not in 
                 (select distinct store_id, art_id, dat_id from gesamtumsatz_2008_aktuell));
      
   
  for x in 1..100  
   
  Loop
   
  update gesamtumsatz_2008_aktuell     
  set
  menge_n= menge_n-k."Bez_Menge", ums_brut_n= ums_brut_n-k."Umsatz" 
   
  from kapdaten_21_04_08 k 
  where gesamtumsatz_2008_aktuell.store_id=k.store_id 
  and gesamtumsatz_2008_aktuell.art_id=k.art_id
  and gesamtumsatz_2008_aktuell.dat_id = k."Bez_Dat"
  and k."Kap_ID" = x;
   
   
   
  update gesamtumsatz_2008_aktuell   
  set
  menge_n=menge_n + k."Bez_Menge", ums_brut_n=ums_brut_n + k."Umsatz" 
   
  from kapdaten_21_04_08 k 
  where gesamtumsatz_2008_aktuell.store_id=k.store_id 
  and gesamtumsatz_2008_aktuell.art_id=k.art_id
  and gesamtumsatz_2008_aktuell.dat_id = k."Best_Dat"
  and k."Kap_ID" = x;
     
  End Loop; 
   
  END;
  'language plpgsql;

Wirf mal einen Blick in postgresql.conf, dort kann man diverse Parameter, unter anderem die Werte für den Speicherverbrauch einstellen.

Dein Fehler könnte auch schlicht dadurch resultieren, dass du den Stack mit Daten füllst, z.B. durch die vielen Schleifen ect.

Hallo!

Ich habe gerade mal einen Blick in die postgresql.config geworfen… Hier der Inhalt bzgl. Speicher:

# - Memory -

shared_buffers = 32MB            # min 128kB or max_connections*16kB
                    # (change requires restart)
#temp_buffers = 8MB            # min 800kB
#max_prepared_transactions = 5        # can be 0 or more
                    # (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB                # min 64kB
#maintenance_work_mem = 16MB        # min 1MB
#max_stack_depth = 2MB            # min 100kB

# - Free Space Map -

max_fsm_pages = 204800        # min max_fsm_relations*16, 6 bytes each
                    # (change requires restart)
#max_fsm_relations = 1000        # min 100, ~70 bytes each
                    # (change requires restart)

# - Kernel Resource Usage -

#max_files_per_process = 1000        # min 25
                    # (change requires restart)
#shared_preload_libraries = ''        # (change requires restart)

# - Cost-Based Vacuum Delay -

#vacuum_cost_delay = 0            # 0-1000 milliseconds
#vacuum_cost_page_hit = 1        # 0-10000 credits
#vacuum_cost_page_miss = 10        # 0-10000 credits
#vacuum_cost_page_dirty = 20        # 0-10000 credits
#vacuum_cost_limit = 200        # 0-10000 credits

# - Background writer -

#bgwriter_delay = 200ms            # 10-10000ms between rounds
#bgwriter_lru_percent = 1.0        # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5        # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333        # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5        # 0-1000 buffers max written/round

Was lässt sich da optimieren?

Einiges, fsm kann man anpassen, Arbeitsspeicher kann man anpassen, die Stackgröße ist in deinem Fall vielleicht auch interessant. Mit den autovacuum Optionen kann man sich auch viel Performance zurückholen, das dürfte in deinem Fall auch etwas ausmachen.

Das Problem ist, dass man dafür keine “so macht man das” Anleitung geben kann, da die Parameter sich gegenseitig beeinflussen und sehr stark von der Aufgabe und Hardware abhängig sind.

Mittels WHERE verhindern, daß diese zum Update ‘gefunden’ werden?



Leider habe ich auch noch ein anderes Problem: Anscheinend kann Postgres nicht mit der bei der Berechnung erzeugten Datenmenge umgehen? Ich bekomme nach einiger Zeit einen „Out of Memory“-Error angezeigt. Eigentlich müsste Postgres doch mit der Datenmenge (32 Mio und 400.000 Datensätze) umgehen können, oder?

An sich schon. Ohne mir das jetzt anzusehen (zeitmangel wegens Urlaub): vielleicht eine endlose Rekursion, ein Kreuzprodukt oder sowas. Ich würde jedenfalls an Deiner Stelle mal in dieser Richtung schauen.

Andreas

Hallo!

Also, um´s nochmal auf den Punkt zu bringen: Momentan habe ich folgende Probleme, bei denen ich einfach nicht weiterkomme:

Die beiden von mir geschriebenen Codes (Post #1 und Post #14) werden aus irgendeinem Grund von der Datenbank nicht ausgeführt, obwohl sie syntaktisch korrekt sind und die gewünschten Ergebnisse liefern würden. Mögliche Ursachen:

1. Speicherverbrauch zu hoch. Selbst ein einzelner INSERT-Befehl dieser Art

  insert into gesamtumsatz_2008_aktuell ( 
  select distinct store_id, "Best_Dat", art_id, 0, 0 
  from kapdaten_21_04_08
  where (store_id, art_id, "Best_Dat") not in 
                 (select distinct store_id, art_id, dat_id from gesamtumsatz_2008_aktuell));

verursacht Probleme und wird nach einigen Stunden mit einem „out of memory“ quittiert. Die Tabelle „gesamtumsatz_2008_aktuell“ enthält ca. 32Mio Datensätze und mit folgendem Befehl aus zwei anderen Tabellen („normalumsatz“, ca.30 Mio Datensätze und „werbeumsatz“, ca. 18Mio Datensätze) folgendermaßen zusammengestellt:

  select normalumsatz_2008.store_id , normalumsatz_2008.dat_id, 
                 normalumsatz_2008.art_id, menge_n as Absatz, ums_brut_n as Umsatz
  from normalumsatz_2008
  where normalumsatz_2008.dat_id >= '2008-01-01'::date 
                 and normalumsatz_2008.art_id = 757926 or normalumsatz_2008.art_id = 72036
  union select werbeumsatz.store_id , werbeumsatz.dat_id, 
                 werbeumsatz.art_id, menge_a as Absatz, ums_brut_a as Umsatz
  from werbeumsatz
  where werbeumsatz.dat_id >= '2008-01-01'::date

Dieser Befehl wurde innerhalb von 1,5h (!) durchgeführt. Es ist mir daher wirklich unverständlich, warum selbst der o.a. INSERT-Befehl zu Speicherproblemen innerhalb der Datenbank führen sollte…

Ein FULL-VAKUUM auf beide Tabellen habe ich mittlerweile auch schon durchgeführt.

2. Zu viele Befehle aufgrund der der ca. 400.000 x durchlaufenen Schleife. Der von Andreas vorgeschlagene Befehl (siehe Post #5)

  update gesamtumsatz  
  set menge=(case 
  when dat_id=k.bez_datum then gesamtumsatz.menge-k.menge 
  when dat_id=k.best_datum 
  then gesamtumsatz.menge+k.menge end),
   umsatz=(case 
  when dat_id=k.bez_datum then gesamtumsatz.umsatz-k.umsatz 
  when dat_id=k.best_datum then gesamtumsatz.umsatz+k.umsatz end) 
  from kapdaten k 
  where gesamtumsatz.store_id=k.store_id 
  and gesamtumsatz.art_id=k.art_id
  and gesamtumsatz.dat_id=k.bez.datum;

ist ein guter Ansatz um diese Problematik zu umgehen. Leider ist es aber so, dass es einige Ausnahmefälle gibt:

In allen Fällen enthält die kleine Tabelle Daten, die in art_id, store_id und dat_id mit einem jeweiligen Datensatz aus der großen Tabelle übereinstimmen.

Manche Artikel wurden aber im gleichen Geschäft am selben Tag bestellt (best.datum) und an unterschiedlichen Tagen bezahlt (bez.datum). Diese Tatsache erfordert eine Summenfunktion innerhalb des UPDATE-Befehls, da ansonsten die Werte nicht aufsummiert in die Zeile „dat_id“ der großen Tabelle geschrieben werden, sondern einfach nur der letzte zutreffende Wert. Ich hoffe, Ihr seid mitgekommen :-/

Ich muss also irgendwie eine Summenfunktion in dem UPDATE-Befehl unterbringen können, oder eben die Summenfunktion in einem getrennten INSERT-Befehl ausführen. Leider ist lässt sich eine Summenfunktion aber eben NICHT in einem UPDATE-Befehl unterbringen - es sei denn, es gibt da ein paar Tricks? Diesbezüglich bin ich langsam mit meinem Latein am Ende…

Zusammenfassend habe ich also ein „out of memory“-Problem mit der Datenbank und die Problematik, die 400.000fach durchlaufene Schelife umgehen zu wollen.
Ich bin wirklich für jede Hilfe dankbar und langsam ziemlich verzweifelt! Besonders dankbar wäre ich für einen Tipp, wie ich die Schleife durch einzelne Befehle überflüssig machen könnte.

Es kann doch alles nicht so schwer sein?
Erneut vielen Dank im Voraus!!!
Grüße,
Chris

In der Zeit wird die DB dann doch schon einiges an RAM verbraucht haben, schliesslich muss sie den gesamten Status im Speicher vorhalten.


Dieser Befehl wurde innerhalb von 1,5h (!) durchgeführt. Es ist mir daher wirklich unverständlich, warum selbst der o.a. INSERT-Befehl zu Speicherproblemen innerhalb der Datenbank führen sollte…

Und wieviele Daten liefert dieser SELECT?
Bedenke, dass der Insert eine riesige Menge Daten im RAM vorhalten muss, dazu kommt noch die komplette Transaktion aussen herum.


Irgendwie habe ich das Gefühl, das ganze sollte man sich mal in Ruhe anschauen … denn:


Es kann doch alles nicht so schwer sein?

Nein, generell nicht, aber im Prinzip doktoren wir hier aus der Ferne herum, ohne z.B. konkret sehen zu können, was auf der DB passiert, was man optimieren, verändern oder einstellen könnte.