Ich habe diesen Prozess, der eine Reihe von Abfragen mit pl / pgsql:
machen muss %Vor%Um alles in einem Aufruf ausführen zu können, habe ich eine Prozessfunktion als solche erstellt:
%Vor% Das Problem ist, wenn ich die Zeit addiere, die jede Funktion für sich beansprucht, beträgt die Summe 200 Sekunden, während die Zeit, die die Funktion process() benötigt, mehr als eine Stunde beträgt!
Vielleicht ist es ein Speicherproblem, aber ich weiß nicht, welche Konfiguration in postgresql.conf ich ändern soll.
Die DB läuft auf PostgreSQL 9.4, in einem Debian 8.
Sie haben bemerkt, dass die 4 Funktionen nacheinander ausführen müssen. Man kann also davon ausgehen, dass jede Funktion mit Daten aus Tabellen arbeitet, die von der vorherigen Funktion modifiziert wurden. Das ist mein Hauptverdächtiger.
Jede Postgres-Funktion wird innerhalb der Transaktion des äußeren Kontexts ausgeführt. Daher haben alle Funktionen denselben Transaktionskontext, wenn sie in eine andere Funktion gepackt sind. Jeder kann natürlich Auswirkungen auf Daten von früheren Funktionen sehen. (Obwohl Effekte für andere gleichzeitige Transaktionen weiterhin unsichtbar sind.) Statistiken werden jedoch nicht sofort aktualisiert.
Die Abfragepläne basieren auf Statistiken an beteiligten Objekten. PL / pgSQL plant keine Anweisungen, bis sie tatsächlich ausgeführt werden, was zu Ihren Gunsten funktionieren würde. Pro Dokumentation:
Da jeder Ausdruck und SQL-Befehl zuerst in der Funktion ausgeführt wird, Der PL / pgSQL-Interpreter analysiert und analysiert den Befehl zum Erstellen eines vorbereitete Anweisung mit der SPI_prepare-Funktion des SPI-Managers.
PL / pgSQL kann Abfragepläne zwischenspeichern, aber nur innerhalb derselben Sitzung und (in S. 9.2 + mindestens) erst nach ein paar Ausführungen den gleichen Abfrageplan gezeigt haben, der am besten immer wieder funktioniert. Wenn Sie vermuten, dass dies für Sie schief geht, können Sie mit dynamischem SQL umgehen, das jedes Mal einen neuen Plan erzwingt:
%Vor% Der wahrscheinlichste Kandidat , den ich sehe, sind jedoch ungültige Statistiken, die zu minderwertigen Abfrageplänen führen. SELECT / PERFORM Anweisungen (gleiche Sache) innerhalb der Funktion werden in schneller Folge ausgeführt, es gibt keine Chance für autovacuum um die Statistiken zwischen einer Funktion und der nächsten zu aktualisieren. Wenn eine Funktion im Wesentlichen Daten in einer Tabelle ändert, mit der die nächste Funktion arbeitet, könnte die nächste Funktion ihren Abfrageplan auf veraltete Informationen stützen. Typisches Beispiel: Eine Tabelle mit ein paar Zeilen ist mit vielen tausend Zeilen gefüllt, aber der nächste Plan denkt immer noch, dass ein sequentieller Scan für die "kleine" Tabelle am schnellsten ist. Sie sagen:
wenn ich die Zeit addiere, die jede Funktion allein einnimmt, ist die Summe 200 Sekunden, während die Zeit, die die Funktion process () benötigt, mehr ist als eine Stunde!
Was genau bedeutet "für sich selbst"? Haben Sie sie in einer einzigen Transaktion oder in einzelnen Transaktionen ausgeführt? Vielleicht sogar mit etwas Zeit dazwischen? Das würde AutoVacuum erlauben, Statistiken zu aktualisieren (es ist normalerweise ziemlich schnell) und möglicherweise zu völlig unterschiedlichen Abfrageplänen führen, die auf der geänderten Statistik basieren.
Sie können Abfragepläne innerhalb von plpgsql-Funktionen mit Auto-Explain überprüfen
Wenn Sie ein solches Problem identifizieren können, können Sie ANALYZE zwischen Anweisungen erzwingen. Da es nur einige% SELECT / PERFORM -Anweisungen gibt, können Sie auch eine einfachere SQL-Funktion verwenden und Plan-Caching vermeiden (aber siehe unten!):
Auch wenn wir nicht den tatsächlichen Code Ihrer aufgerufenen Funktionen sehen, kann eine beliebige Anzahl anderer versteckter Effekte sein.
Beispiel: Sie könnten SET LOCAL ... einen Konfigurationsparameter eingeben, um die Leistung von function1() zu verbessern. Wenn in separaten Transaktionen aufgerufen wird, die den Rest nicht beeinflussen. Der Effekt hält nur bis zum Ende der Transaktion an. Aber wenn es in einer einzigen Transaktion aufgerufen wird, beeinflusst es auch den Rest ...
Grundlagen:
Plus: Transaktion akkumuliert Sperren, die eine zunehmende Menge an Ressourcen binden und die Reibung mit konkurrierenden Prozessen erhöhen können. Alle Sperren werden am Ende der Transaktion freigegeben. Es ist besser, große Funktionen in separaten Transaktionen auszuführen, wenn möglich , nicht in einer einzigen Funktion (und somit Transaktion) verpackt. Dieser letzte Punkt steht im Zusammenhang mit dem @klin und IMSoP bereits abgedeckt.
Warnung für zukünftige Leser (2015-05-30).
Die in der Frage beschriebene Technik ist eine der schlauesten Möglichkeiten, den Server effektiv zu blockieren.
In einigen Unternehmen kann der Einsatz dieser Technologie die Belohnung in Form der sofortigen Kündigung des Arbeitsvertrages finden.
Versuche, diese Methode zu verbessern, sind nutzlos. Es ist einfach, schön und ausreichend effektiv.
In RDMS ist die Unterstützung von Transaktionen sehr teuer. Beim Ausführen einer Transaktion muss der Server Informationen über alle an der Datenbank vorgenommenen Änderungen erstellen und speichern, um diese Änderungen im Falle einer erfolgreichen Beendigung in der Umgebung (anderen gleichzeitigen Prozessen) sichtbar zu machen und im Falle eines Fehlers den Zustand vor der Transaktion wiederherzustellen so bald wie möglich. Daher besteht das natürliche Prinzip, das die Serverleistung beeinflusst, darin, eine minimale Anzahl von Datenbankoperationen in eine Transaktion aufzunehmen, d. nur so viel wie nötig ist.
Eine Postgres-Funktion wird in einer Transaktion ausgeführt. Viele Operationen, die unabhängig voneinander ausgeführt werden können, sind eine schwerwiegende Verletzung der oben genannten Regel.
Die Antwort ist einfach: tu es einfach nicht. Eine Funktionsausführung ist keine bloße Ausführung eines Skripts.
In den prozeduralen Sprachen, die zum Schreiben von Anwendungen verwendet werden, gibt es viele andere Möglichkeiten, den Code durch Verwendung von Funktionen oder Skripten zu vereinfachen. Es gibt auch die Möglichkeit, Skripte mit Shell auszuführen.
Die Verwendung einer Postgres-Funktion für diesen Zweck wäre sinnvoll, wenn Transaktionen innerhalb der Funktion verwendet werden könnten. Gegenwärtig gibt es eine solche Möglichkeit nicht, obwohl Diskussionen zu diesem Thema bereits eine lange Geschichte haben (Sie können darüber lesen, zB in postgres mailing lists ).
Tags und Links postgresql database-performance query-performance plpgsql postgresql-performance