Oracle2Postgres - große Unterschiede bei den Abfragezeiten

Hallo zusammen,

meine Frage ist erst einmal grundsätzlicher Art. Wenn nötig werden Details nachgeliefert.

Ich muss ein seit Jahren bestehendes Projekt von Oracle nach Postgres (13) umziehen.
Nachdem ich jetzt alle nötigen Anpassungen vorgenommen habe und erste Tests mit realen Datenmengen laufen, muss ich feststellen, dass Abfragen, die bei Oracle nur wenige Sekunden (wenn überhaupt) benötigen bei Postgres über eine Minute brauchen.

Ganz platt, gibt es eine “Schraube” an der ich drehen kann, um bei Postgres ähnliche Abfragezeiten zu erreichen wie bei Oracle? Was habe ich übersehen?

Eine Analyse einzelner SQL-Abfragen scheint mir nicht sinnvoll, da die Anfragen entsprechend der User Auswahl zusammengestellt werden. Die sich daraus ergebenden Variationsmöglichkeiten sind riesig.
Daher bräuchte ich tatsächlich eine grundsätzliche Lösung.

Hallo!

Ganz platt, gibt es eine “Schraube” an der ich drehen kann, um bei Postgres ähnliche Abfragezeiten zu erreichen wie bei Oracle?

Klar gibt es das. Es ist aber wahrscheinlich eine andere Schraube als bei Oracle :wink:
Als erstes würde ich die Serverkonfiguration überprüfen und an deine Bedürfnisse anpassen, die ist per default so gestaltet das Postgres auch auf den kleinsten System läuft. Stichworte sind hier workmem, Anzahl Verbindungen etc.
Dann würde ich überprüfen, ob Optimierungen am Datenmodell notwendig sind anhand einzelner Statements.

Eine Analyse einzelner SQL-Abfragen scheint mir nicht sinnvoll, da die Anfragen entsprechend der User Auswahl zusammengestellt werden. Die sich daraus ergebenden Variationsmöglichkeiten sind riesig.

Such dir die schlimmsten Fälle raus die am häufigsten vorkommen, mit vielen Joins etc. was deinen Usern halt so einfällt. Auch hier kann man am Optimizer noch tunen.
Wie greifen die User darauf zu? Mit selbstgebautem SQL oder über ORM? Auch hier kann man noch an Schrauben drehen.

Grüße
Uwe

Nur um das Offensichtliche mal auszuschließen: habt ihr nach der Migration (und dem Bulk load) ein vacuum analyze der Datenbank gemacht um die Statistiken zu aktualisieren (ist das Äquivalent to dbms_stats.gather_schema_stats() in Oracle)

Fang bei den langsamsten Abfragen an. Du kannst z.B. mit log_min_duration_statement Abfrage loggen lassen, die länger als eine bestimmte Zeit benötigen. Ihr könntet auch die Extension auto explain installieren, und dann die Pläne für die langsamen Statements automatisch loggen lassen.

Die Installation und Aktivierung von pg_stat_statements ist meiner Meinung nach unabdingbar in jedem produktiv genutzten Postgres Server. Damit könnt ihr dann langsame Statements identifizieren ohne die Logfile durchsuchen zu müssen (wie es mit log_min_duration notwendig ist).

Folgende Dinge (Fehler) sind mir schon ein paar Mal bei automatisierten Migrationen von Oracle nach Postgres aufgefallen:

  • es wird numeric statt integer oder bigint verwendet weil’s bei Oracle nur number gibt. Aber ein number oder number(22) sollte ein integer oder bigint in Postgres werden.
  • es werden verschiedene Datentype für PK/FK Felder (int <-> numeric) verwendet, was die Verwendung von Indizes häufig verhindert
  • die Index-Strategie könne man vermutlich überdenken, nicht jeder Index den Oracle braucht wird so in Postgres benötigt. Und Postgres braucht manchmal Indizes die so in Oracle nicht notwendig wären. Das sieht man aber nur am execution plan
  • habt ihr die Postgres Konfiguration geändert? Der Default ist extrem konservativ. shared_buffers, work_mem und random_page_cost müssen mit hoher Wahrscheinlichkeit angepasst werden. log_checkpoints einzuschalten ist sicherlich auch keine schlechte Idee.

Was ich auch schon mehr als einmal gesehen habe: der Postgres Server wurde wesentlich kleiner dimensioniert als der Oracle Server (langsamere Platten, weniger Speicher, weniger CPUs usw) - weil man ja “Geld sparen muss”

Ich würde auch track_io_timing einschalten, dann könnt ihr in einem explain (analyze) sehen ob eventuell der Datendurchsatz der Platten ein Problem ist.

Auf jeden Fall würde ich JIT global abschalten via jit = off in der postgresql.conf in OLTP Umgebungen hat das meistens eher einen Nachteiligen Effekt.

Ihr solltet auch die Verwendung von huge_pages einschalten wenn ihr viel Speicher (>= 64GB) eurem Server habt.

2 „Gefällt mir“

Hier meine Antwort an die richtige Stelle:

Wie es scheint, war der Hinweis, ein “vaccum analyze” durch zuführen entscheident. Jetzt läuft alles so wie es soll.

Ich hatte bisher mit der Datenbankverwaltung als solches nichts zutun, das erfolgte an anderer Stelle. Das als Entschuldigung, warum mir so was - wie es scheint - absolut rudimentäres entgangen ist.

Ich war zugegeben schon etwas in Panik.

Vielleicht noch ein Frage hinterher. Wie häufig sollte man den Befehl ausführen (reicht das eine mal, oder öfter?) und gibt es noch weiter “Wartungsarbeiten” die zu bedenken sind.

Ganz herzlichen Dank für Deine Mühen und viele Grüße

Normalerweise macht das Postgres automatisch. Manuell muss man das nur ausführen nach einem Bulk-Load (wenn sich viele Daten geändert haben) oder wenn die Automatik nicht funktioniert (was aber selten passiert).

Vermutlich hätte sich das in ein paar Tagen von selbst erledigt, wenn die Automatik im Hintegrund die Statistiken aktualisiert.

Das ist im Grunde auch nicht anders als bei Oracle.

1 „Gefällt mir“

Wenns nicht an grundlegenden Dingen wie vacuum analyze oder Puffergrößen hängt, wirst Du um das Analysieren zumindest der langsamsten Queries m.E. nicht herumkommen. Letztlich hängt Datenbank-Performance meist an I/O-Pfaden, und um die zu optimieren gibts Indices, die aber richtig eingesetzt werden wollen - und dazu muß man das Datenmodell und die Workload analysieren.

Es gibt da auch ein paar klassische Fallen, in die man tappen kann.

Nur ein Beispiel: eine Tabelle “aufgaben” mit vielen Datensätzen und einer Boolean-Spalte “erledigt” Das ist immer langsam. Mit Index degeneriert dieser und bremst “auf erledigt setzen” und “neue Aufgabe einfügen” aus. Ohne Index wird “zu erledigende Aufgaben suchen” ein langsamer Table Scan. Diese Datenbank wird also in jedem Fall mindestens linear langsamer mit der Anzahl der Datensätze.

Ausweg: zwei Tablellen “aufgaben_toDo” und “aufgaben_erledigt”, kein Boolean-Feld, beim Bearbeiten werden die Datensätze von einer Tabelle in die andere verschoben. Damit braucht man keinen Index, der degenerieren könnte und die zentralen Operationen auf die Tabelle (“neue Aufgabe”, “Aufgabe erledigen” und “noch nicht erledigte suchen”) bleiben schnell, insbesondere wenn man davon ausgeht daß es wenige nicht erledigte und viele erledigte Aufgaben gibt (was meistens der Fall sein wird). Wer es unbedingt braucht, kann eine updatable View mit INSERT- und UPDATE-Trigger drüberlegen, die das verschieben transparent macht.

Sowas kann man aber auf Ebene des RDBMS nur sehr begrenzt lösen, da hilft nur, die Abfragen anzufassen. Eine alternative Lösung wäre noch, die Tabelle nach dem Boolean-Feld zu clustern, aber auch das wird irgendwann langsam weil es die regelmäßige Reorganisation von Clusterblöcken auslösen dürfte.

Das Prinzip gilt übrigens für jedes andere Statusfeld. Indices mit niedriger Kardinalität sind meistens(!) eine schlechte Idee, und Statusfelder haben nur wenige Werte, also niedrige Kardinalitäten. Wer über Statusfelder suchen möchte, baut besser separate Statustabellen, die nur Primärschlüsselwerte enthalten und nach Bedarf mit INNER JOINs verknüpft werden. Und keine Indices für sehr seltene Stati.

Indizes “degenerieren” nicht (es sei den Du spielst damit auf das Thema “bloat” an - leider immer noch ein Schwachpunkt in Postgres).

In dem von Dir geschilderten Fall würde man vermutlich einen gefilterten Index nehmen um die nicht erledigten Datensätze zu bekommen:

 create index on aufgabe(id, title) where not erledigt;

Dieser Index enthält dann nur die nicht erledigten Aufgaben, und die Suche danach:

select id, title
from aufgabe 
where not erledigt;

verwendet diesen und sucht auch nur in den nicht erledigten Daten - typischerweise deutlich weniger als die Gesamtmenge.