10 gravierende PostgreSQL-Fehler

Wenn Ihre PostgreSQL-Instanz mit sich selbst kämpft, kann das unter anderem ihrer Konfiguration oder ihrem Design geschuldet sein.
John Carnemolla | shutterstock.com



Mit einer PostgreSQL-Installation kann eine Menge schieflaufen. Schlimmer ist aber, dass dabei auch viele Probleme zunächst unentdeckt bleiben, die sich im Laufe der Zeit verdichten können und am Ende in einem Fail enden. Zum Beispiel in Form von:




Datenbank-Performance, die über die Klippe springt oder



einem drastischen Anstieg des Ressourcenverbrauchs und der damit verbundenen, ausufernden Rechnung. 




Solche Probleme so früh wie möglich zu erkennen – oder besser noch von vorneherein zu vermeiden – ist essenziell. Wir haben dazu eine Liste der häufigsten Fehler zusammengestellt, die in Zusammenhang mit der quelloffenen Enterprise-Datenbank auftreten.



1. Standardkonfiguration verwenden



PostgreSQL funktioniert zwar „out of the box“, ist dann aber nicht besonders gut auf Ihre Bedürfnisse angepasst. Milde ausgedrückt. Die Standardkonfiguration der Open-Source-Datenbank ist sehr simpel gehalten und nicht auf spezifische Workloads abgestimmt. Diese übertrieben konservative Konfiguration ermöglicht, dass PostgreSQL in jeder Umgebung laufen kann. Damit verbunden ist jedoch auch die Erwartung, dass die Anwender die Lösung entsprechend konfigurieren.



Das webbasierte Tool pgtune stellt Konfigurations-Subsets auf Basis der genutzten Hardware-Ressoucren und der Art des Workloads zur Verfügung. Das bietet einen guten Einstiegspunkt, um Ihr PostegreSQL-Cluster auf Ihre Workload-Anforderungen abzustimmen. Dabei müssen Sie aber damit rechnen, möglicherweise die Variablen für autovacuum, log, checkpoint und WAL (write-ahead log) konfigurieren zu müssen.



Um unnötige Neustarts zu vermeiden, ist es äußerst wichtig, dass Ihr Server für die unmittelbaren zukünftigen Anforderungen optimal konfiguriert ist. Überprüfen Sie deshalb alle GUCs mit „Postmaster“-Kontext innerhalb von pg_settings catalog view.



SELECT name, setting, boot_val



FROM   pg_settings



WHERE  context = 'postmaster';



Dies ist besonders kritisch, wenn es darum geht, Hochverfügbarkeits-Cluster einzurichten: Jeder Ausfall des Primärservers beeinträchtigt das Cluster und bewirkt, dass ein Standby-Server dessen Rolle einnimmt.



2. Datenbankarchitektur nicht optimieren



Nicht oder unzureichend optimierte Datenbank-Designs und -Architekturen können richtig ins Geld gehen. Wenn es schlecht läuft, zahlen Sie unter Umständen das Fünffache der eigentlich notwendigen Kosten. Einer der besten Tipps in diesem Zusammenhang: Orientieren Sie sich an den aktuellen und baldigen Workload-Anforderungen und nicht daran, was in sechs Monaten oder einem Jahr erforderlich sein wird. Das kann im Extremfall dazu führen, dass Ihre Datenbanktabellen für Anforderungen gestaltet sind, die unter Umständen nie realisiert werden.



Daneben ist auch eine übertriebene Verwendung von objektrelationaler Abbildung (Object-relational Mapping; ORM) oft ein Grund für Performance-Mängel. Diese wird verwendet, um Anwendungen über objektorientierte Programmiersprachen mit Datenbanken zu verbinden und ist eigentlich dazu gedacht, Entwicklern das Leben leichter zu machen. Dabei ist es jedoch obligatorisch, zu verstehen, was ORM genau macht und welche Auswirkungen das auf die Performance hat: ORM kann potenziell multiple Abfragen durchführen – beispielsweise mehrere Relationen kombinieren, um Aggregationen durchzuführen oder sogar um Query-Daten aufzusplitten. Unter dem Strich werden Sie durch die ORM-Nutzung jedoch höhere Latenzzeiten und einen geringeren Durchsatz mit Blick auf Transaktionen feststellen.



Wenn Sie Ihre Datenbankarchitektur verbessern wollen, sollten Sie darüber hinaus Ihre Read- und Write-Vorgänge optimal auf Indizes und auch auf Relationen ausrichten. Ihre Datenbank zu denormalisieren, ist ein Ansatz, der Sie dabei unterstützen kann: Das reduziert die Komplexität von SQL-Abfragen und die damit verbundenen Joins, was Ihnen ermöglicht, Daten aus weniger Relationen abzurufen.



Letztendlich steht und fällt die PostgreSQL-Performance mit einem einfachen, dreistufigen Prozess, bezogen auf Ihre Applikation und Ihren Workload:




Definieren.



Messen.



Optimieren.




3. Datenbank und Workloads nicht abstimmen



Um PostgreSQL auf einen Workload abzustimmen, müssen Sie wissen:




wie groß die Datenmenge ist, die gespeichert werden,



welche Art von Applikation bedient werden und



welche Art von Queries ausgeführt werden soll.




Passt zum Beispiel die gesamte Datenbank in den verfügbaren Arbeitsspeicher Ihrer Maschine, sollten Sie den entsprechenden Wert für shared_buffers erhöhen. Auch um die checkpoint– und autovacuum-Prozesse zu konfigurieren, ist die Info zum Workload essenziell: Diese werden für einen reinen Append-Workload völlig anders konfiguriert als für einen im Bereich Mixed Online Transaction Processing.



Es gibt eine Reihe nützlicher Open-Source-Tools, die Einblicke in die Performance von Queries ermöglichen. Zum Beispiel:




pg_stat_activity,



pg_stat_statements,



auto_explain oder



pg_stat_monitor.




4. Connection Management vernachlässigen



Auf den ersten flüchtigen Blick sieht die connections-Konfiguration harmlos aus. Allerdings kann ein extrem großer Wert für max_connections zu Out-of-Memory-Fehlern führen. Dementsprechend sollten Sie an dieser Stelle Vorsicht walten lassen. Berücksichtigen sollten Sie dabei insbesondere:




die Anzahl der Kerne,



die zur Verfügung stehende Speichergröße sowie



den Storage-Typ.




Ihre Serverressourcen mit Verbindungen überlasten, die möglicherweise nie genutzt werden, ist etwas, das Sie nicht wollen. Außerdem gilt es auf die Kernel-Ressourcen zu achten, die ebenfalls pro Verbindung zugewiesen werden. Zu diesem Thema können Sie die Kernel-Dokumentation von PostgreSQL konsultieren.



Wenn Clients Queries ausführen, die nur wenig Zeit in Anspruch nehmen, kann ein Connection Pooler die Performance erheblich steigern. Der Overhead, der bei diesem Workload-Typ dadurch erzeugt wird, dass eine Verbindung generiert wird, fällt dadurch signifikant niedriger aus.



5. autovacuum deaktivieren



Es bleibt zu hoffen, dass Sie autovacuum nicht deaktiviert haben. Das passiert leider regelmäßig – meistens, weil ein Problem vorliegt. Wenn autovacuum in Ihrer Umgebung nicht richtig funktioniert, dann gibt es dafür drei mögliche Gründe:




Der Prozess wird nicht ausgelöst – oder zumindest nicht so häufig, wie vorgesehen.



Der Prozess läuft zu langsam ab.



Der Prozess räumt nicht mit „dead rows“ auf.




Die ersten beiden Punkte hängen direkt mit den autovacuum-Konfigurationsoptionen zusammen. Diese können Sie einsehen, indem Sie die pg_settings view abfragen.







SELECT  name



        , short_desc



        , setting



        , unit



        , CASE



            WHEN context = 'postmaster' THEN 'restart'



            WHEN context = 'sighup'     THEN 'reload'



            ELSE context



          END "server requires"



FROM    pg_settings



WHERE   name LIKE '%vacuum%';







Die Geschwindigkeit kann dabei möglicherweise verbessert werden, indem Sie autovacuum_work_mem und die Anzahl der parallelen Worker feineinstellen. Über die Konfiguration von Skalierungsfaktoren oder Schwellenwerten können Sie zudem darauf Einfluss nehmen, wie der vacuum-Prozess getriggert wird.



Was Punkt drei angeht: Wenn der Vakuumprozess tote Tupel nicht aufräumt, ist das ein Hinweis darauf, dass wichtige Ressourcen zurückgehalten werden. Das kann einem der folgenden Gründe – oder einer Kombination – geschuldet sein:




Lang laufende Abfragen oder Transaktionen;



Standby-Server in einer Replikationsumgebung, bei denen die Option hot_standby_feedback aktiviert ist;



Ein größerer Wert als erforderlich für vacuum_defer_cleanup_age;



Replikationsslots, die den xmin-Wert niedrig halten und das Vakuum daran hindern, tote Tupel zu bereinigen.




Wenn Sie das Vakuum einer Relation manuell verwalten möchten, sollten Sie dem Pareto-Gesetz (auch bekannt als die 80/20-Regel) folgen: Stimmen Sie das Cluster auf eine optimale Konfiguration ab und denken Sie daran, dass autovacuum oder toast.autovacuum für eine bestimmte Relation deaktiviert werden können. Dazu geben Sie die zugehörige Speicheroption im Rahmen des create– oder alter-Statements an.



6. Lang laufende Transaktionen zulassen



Es gibt eine ganze Reihe von Umständen, die Ihre PostgreSQL-Cluster „in Geiselhaft“ nehmen können. Beispielsweise Rogue Connections und Transaktionen, die lange laufen. Diese belegen Verbindungs-Slots und verhindern damit, dass diese von anderen Anwendungen genutzt werden können. Wenn essenzielle Ressourcen über einen längeren Zeitraum belegt werden, kann das im gesamten System Verwüstung anrichten. In geringerem Maße können in einer Replikationsumgebung mit aktiviertem hot_standby_feedback lang laufende Transaktionen auf dem Standby-Server das Vakuum auf dem Primärserver auch daran hindern, seine Aufgabe zu erfüllen.



In den meisten Fällen können Sie solche Transaktionen loswerden, indem Sie für idle_in_transaction_session_timeout einen Wert konfigurieren, der auf Ihre Queries abgestimmt ist. Dabei sollten Sie natürlich immer das Verhalten Ihrer Anwendung im Auge behalten. Daneben lohnt es sich auch, pg_stat_activity auf lang laufende Abfragen oder Sessions zu überwachen, die länger als erwartet auf Client-bezogene Ereignisse warten. Diesbezüglich sind relevant:




Zeitstempel,



Wait-Ereignisse und



State Columns.








backend_start    | 2022-10-25 09:25:07.934633+00



xact_start       | 2022-10-25 09:25:11.238065+00



query_start      | 2022-10-25 09:25:11.238065+00



state_change     | 2022-10-25 09:25:11.238381+00



wait_event_type  | Client



wait_event       | ClientRead



state            | idle in transaction







Darüber hinaus können (insbesondere verwaiste) vorbereitete Transaktionen ebenfalls wichtige Systemressourcen (locks oder xmin value) belegen. Empfehlenswert ist an dieser Stelle, dafür eine Nomenklatur einzurichten, um das Alter der Transaktionen zu definieren. Eine vorbereitete Transaktion mit einem maximalen Alter von fünf Minuten lässt sich beispielsweise als PREPARE TRANSACTION „foo_prepared 5m“ anlegen.







SELECT  gid



        , prepared



        , REGEXP_REPLACE(gid, '.* ', '') AS age



FROM    pg_prepared_xacts



WHERE   prepared + CAST(regexp_replace(gid, '.* ', '') AS INTERVAL) < NOW();







7. Over- oder Under-Indexing nutzen



Um die optimale Performance aus Ihrer PostgreSQL-Instanz zu „pressen“, sollten Sie verstehen, wie PostgreSQL Indizes managt. Es gibt mehrere Arten von Indizes in PostgreSQL. Jede hat ihren eigenen Anwendungsfall – und Overhead. Der am häufigsten verwendete Index-Typ ist der B-Tree, der auch für Primärschlüssel zum Einsatz kommt.



Wenn ein Index-Scan auf einer Relation ausgeführt wird, wird für jedes übereinstimmende Tupel auf den Heap zugegriffen, um sowohl Daten als auch Sichtbarkeitsinformationen abzurufen. So wird die für die aktuelle Transaktion sichtbare Version ausgeführt.




Over-Indexing führt dazu, dass mehr Indizes aktualisiert werden, wodurch mehr Ressourcen verbraucht werden, ohne dass die gewünschten Vorteile erzielt werden.



Under-Indexing führt hingegen zu mehr Heap-Scans, was mehr E/A-Operationen zur Folge hat und in einen Leistungsabfall münden  kann.




Bei der Indizierung geht es jedoch nicht nur um die Anzahl der Indizes, die für eine Relation anfallen. Sondern auch darum, wie gut diese Indizes auf die gewünschten Use Cases optimiert sind. Im Idealfall würden Sie jedes Mal einen reinen Index-Scan durchführen wollen, aber es gibt Einschränkungen: B-Tree-Indizes unterstützen für sämtliche Operatoren Index-only-Scans, GiST- und SP-GiST-Indizes nur für einige Operatoren. Auch hier hält die PostgreSQL-Dokumentation weitere Einzelheiten bereit.



Dass Ihr System optimal für Indizes eingerichtet ist, können Sie anhand folgender, simpler Checkliste sicherstellen:




Vergewissern Sie sich, dass die Konfiguration korrekt ist.



Prüfen Sie, ob Statistiken aktuell sind – oder stellen Sie zumindest sicher, dass die analyze– und vacuum-Befehle auf den Relationen mit Indizes ausgeführt werden. Das gewährleistet, dass die Statistiken mehr oder weniger aktuell sind, so dass der Planer eine höhere Wahrscheinlichkeit hat, einen Index-Scan zu wählen.



Erstellen Sie den richtigen Indextyp (B-Tree, Hash oder einen anderen Typ).



Verwenden Sie Indizes für die richtigen Spalten. Vergessen Sie dabei nicht, auch nicht-indizierte Spalten einzubeziehen, um Heap-Zugriffe zu vermeiden. Außerdem wichtig: Nicht alle Indextypen erlauben Covering-Indizes.



Trennen Sie sich von unnötigen Indizes. Für weitere Einblicke in Indizes und Block Hits nutzen Sie pg_statio_user_indexes.



Durchdringen Sie die Auswirkungen von Covering-Indizes auf Funktionen wie Deduplizierung, Duplicate Version Churns und Index-Only-Scans.




Weitere nützliche Queries zur Index-Pflege hält diese Wiki-Seite bereit.



8. Backup-Hygiene vernachlässigen



Hochverfügbarkeit bedeutet mehr, als einen Service am Laufen zu halten. Es geht auch darum, sicherzustellen, dass der Dienst innerhalb der definierten Akzeptanzkriterien reagiert – und die RPO- (Recovery Point Objective) und RTO-Ziele (Recovery Time Objective) erfüllt.



Um letzteres zu bewerkstelligen, gilt es diverse Faktoren zu berücksichtigen. Zum Beispiel:




geplante Ausfallzeiten,



automatisierte oder manuelle Aktivitäten,



deren Häufigkeit und Dauer sowie



die mit ungeplanten Ausfallzeiten verbundenen Kosten.




Um die RPO- und RTO-Ziele definieren zu können, sind regelmäßige Backups und die Fähigkeit, diese effektiv wiederherzustellen, essenziell. Je nach Workload und verfügbaren Wartungsfenstern sollten Backups mindestens einmal alle sieben Tage durchgeführt werden. Darüber hinaus sollten Sie Ihren Recovery-Prozess regelmäßig testen.



9. Extensions falsch managen



PostgreSQL hat standardmäßig mehr als 50 Erweiterungen an Bord. Hinzu kommen viele weitere von Drittanbietern. In erster Linie sollten Sie ganz allgemein sicherstellen, dass alle Erweiterungen, die Sie einsetzen, zusammenarbeiten können und sich nicht gegenseitig negativ beeinflussen.



Daneben spielt hier auch die Performance eine Rolle: Bei einigen Erweiterungen handelt es sich einfach um SQL-Extensions. Es gibt aber auch andere, die ein gemeinsam genutztes Objekt oder eine DLL enthalten, die mehr Ressourcen verbrauchen und die Gesamtleistung beeinträchtigen. Sie sollten deshalb im Blick haben, welche Ressourcen von den Extensions beansprucht werden.



Noch wichtiger ist, dass alle Erweiterungen, die vorgeladen werden, Teil des Servers werden. Unabhängig davon, ob Sie eine SQL-Schnittstelle mit dem CREATE EXTENSION...-Statement erstellt haben oder nicht, werden diese Erweiterungen im Hintergrund arbeiten. So führt es beispielsweise zu Performance-Einbußen, pg_stat_statements zu gemeinsam genutzten, vorab geladenen Bibliotheken hinzuzufügen – unabhängig davon, ob Sie die SQL-Schnittstelle erstellt haben oder nicht. Es empfiehlt sich daher, sorgfältig darüber nachzudenken, welche Erweiterungen Sie wirklich benötigen und welche nicht.



Folgende Queries rund um Extensions sind unter Umständen nützlich für Sie:




SELECT * FROM pg_extension; fragt den pg_extension-Katalog ab, um Informationen über die installierten Erweiterungen zu erhalten.



SELECT * FROM pg_available_extensions(); ermittelt, welche Extensions auf dem System verfügbar sind.



SELECT * FROM pg_available_extension_versions(); identifiziert die verfügbaren Versionen Ihrer Erweiterungen.




10. Support-Tools ignorieren



Ein PostgreSQL-Cluster einzurichten, sollte nicht isoliert betrachtet werden. Es ist unerlässlich, die verfügbaren, unterstützenden Tools zu analysieren und fortlaufend im Auge zu behalten. Beispielhaft drei Tools, die im Ökosystem von PostgreSQL regelmäßig zur Anwendung kommen:




das Load-Balancing-Tool HAProxy;



der leichtgewichtige Connection Pooler PgBouncer;



die Middleware Pgpool-II.




(fm)



Sie wollen weitere interessante Beiträge zu diversen Themen aus der IT-Welt lesen? Unsere kostenlosen Newsletter liefern Ihnen alles, was IT-Profis wissen sollten – direkt in Ihre Inbox!