Zu viele Verbindungen: warum mehr davon nicht hilft
Jede Verbindung ist ein eigener Prozess mit eigenem Speicher, deshalb macht ein höheres Limit die Datenbank langsamer statt belastbarer, sobald mehr Sitzungen arbeiten als Kerne da sind.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Die Verbindungsgrenze ist ein Symptom, kein Engpass
PostgreSQL startet für jede Verbindung einen eigenen Serverprozess. Der bringt eigenen Speicher mit, und Arbeitsspeicher für Sortierungen wird je Schritt im Ausführungsplan und je Verbindung vergeben, nicht einmal für das System. Aus 200 erlaubten Verbindungen mit großzügig gesetztem work_mem wird im ungünstigen Fall ein Vielfaches dessen, was der Rechner hat.
Der zweite Effekt ist die Konkurrenz. Sobald mehr Sitzungen gleichzeitig rechnen wollen, als Kerne vorhanden sind, verteilt das Betriebssystem Rechenzeit in immer kleineren Scheiben, und interne Sperren werden zum Engpass. Der Durchsatz steigt bis zu einem Punkt und fällt danach wieder, während die Antwortzeiten für alle steigen. Eine Verdopplung von max_connections verschiebt diesen Punkt nicht, sie lässt ihn schneller überschreiten.
Die interessante Zahl ist deshalb nicht, wie viele Verbindungen offen sind, sondern wie viele davon gerade tatsächlich arbeiten. Bei einem vollen Verbindungslimit sind es erfahrungsgemäß erschreckend wenige. Der Rest sind Sitzungen, die ein Pool geöffnet und nie geschlossen hat, oder eine Anwendung, die eine Transaktion offen hält, während sie auf etwas ganz anderes wartet.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Symptom, Ursache, Lösung
Symptom
Die Anwendung meldet sorry, too many clients already, in pg_stat_activity stehen 180 von 200 Sitzungen untätig.
Ursache
Mehrere Anwendungsinstanzen halten je einen eigenen Verbindungspool. Zehn Instanzen mit einem Pool von zwanzig ergeben zweihundert Verbindungen, unabhängig davon, wie viel Arbeit tatsächlich anfällt.
Lösung
Die Poolgröße je Instanz auf eine Größenordnung senken, die zur Kernzahl des Datenbankservers passt, oder einen zentralen Pooler davorsetzen. Die Summe aller Pools plus Wartungszugänge muss unter max_connections bleiben.
Symptom
Einzelne Sitzungen stehen seit Stunden auf idle in transaction, die Anwendung wirkt trotzdem gesund.
Ursache
Die Anwendung öffnet eine Transaktion und wartet darin auf etwas Externes, etwa einen Webservice oder eine Eingabe, oder ein Fehlerpfad kommt nie bis zum Abschluss der Transaktion.
Lösung
idle_in_transaction_session_timeout setzen, damit solche Sitzungen automatisch enden. Parallel im Code die Stelle suchen: Transaktionen gehören eng um die Schreibvorgänge herum und nicht um einen ganzen Vorgang.
Symptom
Die Zahl der Verbindungen steigt seit dem Start stetig und fällt nie.
Ursache
Verbindungen werden geöffnet, aber nicht zurückgegeben. Typisch ist ein Codepfad mit einer Ausnahme, der die Verbindung nicht schließt, oder ein Hintergrundjob, der je Durchlauf eine neue Verbindung aufbaut.
Lösung
Über application_name und client_addr eingrenzen, welcher Dienst betroffen ist. Anschließend im Pool eine Höchstlebensdauer je Verbindung setzen, damit ein einzelner Fehler nicht dauerhaft Plätze bindet.
Symptom
max_connections wurde auf 1000 gesetzt, die Fehlermeldung ist weg, die Antwortzeiten sind schlechter als vorher.
Ursache
Mehr gleichzeitig arbeitende Sitzungen als Kerne führen zu Konkurrenz um Rechenzeit und um interne Sperren. Zusätzlich vervielfacht work_mem den Speicherbedarf, was im schlimmsten Fall das Betriebssystem zum Eingreifen zwingt.
Lösung
max_connections wieder auf einen Wert senken, den der Rechner tragen kann, und die Warteschlange in einen Pooler verlagern. Dort warten überzählige Anfragen kurz, statt die Datenbank alle gleichzeitig zu belasten.
Symptom
Wartungszugänge kommen noch durch, die Anwendung nicht mehr.
Ursache
PostgreSQL hält einige Plätze für Verbindungen mit erhöhten Rechten zurück, damit im Notfall noch jemand eingreifen kann. Für normale Anmeldungen ist die Grenze also etwas früher erreicht als die eingestellte Zahl.
Lösung
Diesen Zugang nutzen, um die Lage zu untersuchen, statt sofort neu zu starten. Danach gezielt die Sitzungen beenden, die eine Transaktion offen halten, und erst anschließend an den Einstellungen arbeiten.
Fünf Zahlen, bevor du max_connections anfasst
- 01 Wie viele Sitzungen arbeiten gerade wirklich und sind nicht nur verbunden?
- 02 Wie viele stehen seit Minuten im Zustand idle in transaction?
- 03 Wie viele Anwendungsinstanzen halten je einen eigenen Pool?
- 04 Wie viele Kerne hat der Datenbankserver überhaupt zur Verfügung?
- 05 Wie hoch ist work_mem, und wie viele Sortierschritte laufen parallel?
Was du nach dieser Seite in zehn Minuten weißt
Die erste Abfrage sagt dir, ob du ein Kapazitätsproblem oder ein Verhaltensproblem hast. Alles Weitere hängt an dieser Antwort, und in den meisten Fällen ist es das Verhalten.
Sitzungen nach Zustand zählen
SELECT state, count(*) FROM pg_stat_activity GROUP BY state ordnet die Sitzungen in aktiv, untätig und untätig in einer Transaktion. Das Verhältnis entscheidet, in welche Richtung du weitersuchst.
Die Herkunft feststellen
Nimm client_addr, usename und application_name dazu, dann siehst du, welcher Dienst die Verbindungen hält. Häufig ist es ein einzelner Dienst mit zu großem Pool, der über mehrere Instanzen läuft.
Hängende Transaktionen sichtbar machen
Sitzungen im Zustand idle in transaction mit einem xact_start von vor Stunden sind der schlimmste Fall, denn sie belegen einen Platz, halten Sperren und blockieren zusätzlich das Aufräumen alter Zeilenversionen.
Grenzen setzen statt hoffen
idle_in_transaction_session_timeout beendet solche Sitzungen zuverlässig, statement_timeout begrenzt einzelne Anweisungen. Beides lässt sich auf Datenbank- oder Rollenebene setzen, ohne die Anwendung anzufassen.
Die Poolgröße ausrechnen statt zu raten
Multiplizier die Poolgröße mit der Zahl der Instanzen und addier Hintergrunddienste, Auswertungswerkzeuge und Wartungsjobs. Diese Summe muss unter max_connections liegen, sonst ist die Meldung nur eine Frage der Uhrzeit.
Den Pool an die richtige Stelle setzen
Ein Pool in der Anwendung reicht bei wenigen, langlebigen Instanzen. Bei vielen Instanzen, kurzlebigen Prozessen oder Aufrufen ohne festen Server gehört ein zentraler Pooler vor die Datenbank, sonst multipliziert sich jeder lokale Pool.
KI-generiertDieses Bild wurde mit KI erzeugt · Yves Hoppe / KI / cmt
Was eine Verbindung tatsächlich kostet
Jede Verbindung ist ein eigener Betriebssystemprozess. Dazu kommt Speicher für Sortierungen und Hashtabellen, gesteuert über work_mem, und zwar je Schritt im Ausführungsplan. Eine Abfrage mit drei Sortierungen und paralleler Ausführung kann das Mehrfache des eingestellten Werts belegen. Die Rechnung max_connections mal work_mem ist deshalb keine theoretische Obergrenze, sondern ein realistischer Ausblick auf einen schlechten Tag.
Hinzu kommt, dass eine große Zahl von Sitzungen auch dann Aufwand erzeugt, wenn sie nichts tut. Mit PostgreSQL 14 ist das deutlich billiger geworden, weil der Server für einen Snapshot nicht mehr alle laufenden Sitzungen durchgehen muss, aber die Grundregel bleibt: Verbindungen sind nicht kostenlos, und ein Pool, der zehnmal so viele Verbindungen offen hält, wie er braucht, bezahlt das an mehreren Stellen gleichzeitig.
Die sinnvolle Zahl gleichzeitig arbeitender Verbindungen ist entsprechend klein und orientiert sich an der Kernzahl. Die verbreitete Faustformel von HikariCP setzt sie auf die doppelte Kernzahl zuzüglich der Zahl gleichzeitig nutzbarer Datenträger an, was aus der Zeit drehender Platten stammt. Auf SSD-Speicher fällt der zweite Summand praktisch weg, und es bleibt: ungefähr doppelte Kernzahl. Alles darüber gehört in eine Warteschlange und nicht in die Datenbank.
Wo der Pool sitzen sollte
Ein Pool in der Anwendung ist der einfachste Fall und funktioniert gut, solange es wenige, dauerhaft laufende Instanzen gibt. Er kennt den Zustand der Sitzung, kann vorbereitete Anweisungen wiederverwenden und braucht keinen zusätzlichen Dienst. Sein Problem ist die Vervielfachung: Jede weitere Instanz bringt ihren eigenen Pool mit, und niemand koordiniert die Summe.
Ein zentraler Pooler wie PgBouncer sitzt zwischen Anwendung und Datenbank und bündelt viele Client-Verbindungen auf wenige Serververbindungen. Im Transaktionsmodus wird eine Serververbindung nur für die Dauer einer Transaktion zugewiesen, und genau dort liegt der Gewinn: Hunderte untätige Clients belegen dann keine Datenbankprozesse mehr.
Der Transaktionsmodus hat allerdings Bedingungen. Alles, was an der Sitzung hängt, funktioniert nicht mehr zuverlässig: temporäre Tabellen, LISTEN und NOTIFY, Beratungssperren über Transaktionsgrenzen hinweg und Einstellungen, die per SET für die Sitzung gesetzt werden. Prüf das gegen deine Anwendung, bevor du umstellst, sonst tauschst du ein Verbindungsproblem gegen sporadische Fehler.
Sofortmaßnahmen, wenn die Datenbank gerade voll ist
Verschaff dir zuerst einen Zugang. PostgreSQL hält eine kleine Zahl von Plätzen für Verbindungen mit erhöhten Rechten frei, damit genau diese Situation nicht zum Neustart zwingt. Über diesen Zugang siehst du in pg_stat_activity, was los ist.
Danach beende gezielt, was blockiert. pg_terminate_backend beendet eine Sitzung, pg_cancel_backend nur die laufende Anweisung. Sinnvolle Kandidaten sind Sitzungen im Zustand idle in transaction mit altem state_change. Eine Massenbeendigung aller untätigen Sitzungen wirkt kurz, erzeugt im Pool der Anwendung aber sofort neue Verbindungen und löst nichts.
Erst danach kommt die Einstellung an die Reihe. Ein Neustart mit höherem max_connections ist die teuerste aller Optionen, weil er die Anwendung ohnehin trifft und die eigentliche Ursache unberührt lässt. idle_in_transaction_session_timeout dagegen lässt sich im laufenden Betrieb setzen und wirkt sofort.
Die Verbindung zum Aufräumen alter Zeilen
Eine Sitzung, die eine Transaktion offen hält, belegt nicht nur einen Platz. Zusätzlich hält sie die Sichtbarkeitsgrenze fest, ab der Vacuum alte Zeilenversionen entfernen darf. Ein einzelnes vergessenes BEGIN kann deshalb dazu führen, dass eine stark geänderte Tabelle über Stunden anwächst, ohne dass jemand die beiden Symptome miteinander in Verbindung bringt.
Wenn du also gleichzeitig zu viele Verbindungen und wachsende Tabellen siehst, ist das mit hoher Wahrscheinlichkeit dieselbe Ursache. Die Prüfung ist dieselbe Abfrage auf pg_stat_activity, sortiert nach xact_start.
Ein Zeitlimit für untätige Transaktionen behebt daher zwei Probleme mit einer Einstellung. Setz es zunächst großzügig, etwa im Bereich weniger Minuten, und beobachte, ob die Anwendung Fehler meldet. Meldet sie welche, hast du eine Stelle gefunden, an der eine Transaktion viel länger offen ist, als sie sein müsste.
Dazu passende Kurse
Welche Werte zu welcher Maschine passen, rechnen die PostgreSQL-Kurse zu Konfiguration und Ressourcen an konkreten Systemen durch, statt Faustregeln zu wiederholen.
Verbindungsverwaltung und Warteschlangen funktionieren überall nach demselben Prinzip, deshalb passen Kurse zum Betrieb von Datenbanken unter Last auch dann, wenn nicht nur PostgreSQL läuft.
Wie sicher bist du beim Thema wirklich?
Lesen fühlt sich schnell nach Können an. Ein kurzer Test zeigt dir, was davon schon sitzt und wo sich ein Kurs lohnt. Kostenlos, ohne Anmeldung, mit einer Erklärung zu jeder Antwort.
Wo du genau das übst
Super Schulung, in der der Trainer auch auf eigene Fragestellungen eingegangen ist.
Super Grundlagenschulung mit allen wichtigen Punkten und hilfreichen Tipps!
Schön, sich 5 Tage ungestört mit Postgres beschäftigen zu können.
Häufige Fragen
Wie hoch sollte max_connections sein?
Warum reicht ein Pool in der Anwendung nicht immer?
Was ist der Unterschied zwischen idle und idle in transaction?
Hilft es, max_connections einfach zu erhöhen?
Deine Ansprechpartner
Du bist dir nicht sicher, welcher Kurs oder welches Level zu dir passt? Wir beraten dich persönlich und kostenlos.
Yves Hoppe
Weiterbildung & Beratung
Hilft dir, aus dem Datenbanken-Programm den passenden Kurs für deinen Stand zu finden.
Norbert Jansen
Beratung & Inhouse
Plant mit dir Inhouse-Trainings, die auf eure Abläufe und euren Datenbestand zugeschnitten sind.
Verbindungen, die zur Maschine passen
Pools, Zeitlimits und Serverparameter aufeinander abzustimmen, gehen die PostgreSQL-Kurse bei cmt an laufenden Systemen durch.