- Postgres-Datenbanken nutzen große Mengen an RAM. Beim Erstellen von Ergebnismengen durchlaufen sie Schritte wie Index-Matching, das Abrufen relevanter Zeilen aus Tabellen sowie das Zusammenführen, Filtern, Aggregieren und Sortieren von Tupeln – und all diese Schritte sind auf Speicher angewiesen
- Um die Speichernutzung von Postgres zu optimieren, sollte der verfügbare RAM möglichst gut ausgenutzt werden, während verschiedene Arten von Speicherzuweisungen effizient abgestimmt werden und zugleich verhindert wird, dass das OS Prozesse wegen übermäßiger Speichernutzung beendet
Sharing is Caring
- Der größte Teil des mit Postgres verbundenen RAM heißt
shared_buffers und repräsentiert die Zeilen der am häufigsten abgefragten Tabellen und Indizes. Unterstützt wird dies durch eine Heuristik, die anhand der Nutzungshäufigkeit Punkte vergibt
shared_buffers ist ein fester Wert, der beim Start von Postgres zugewiesen wird, und trägt nicht zu unerwarteten Speicherproblemen bei- Der Standardwert beträgt 128MB
- Das OS betrachtet ihn jedoch möglicherweise nicht als vorab reservierten Speicher, daher kann es riskant sein, sehr hohe Werte bis zur gesamten RAM-Menge einer Instanz anzugeben
- Die gängigste Empfehlung für
shared_buffers in Produktionssystemen liegt bei 25 % des verfügbaren RAM. Da dies an die Hardware angepasst wird, ist es für die meisten Systeme ein sinnvoller Startpunkt
- Benchmarks zeigen, dass die 25%-Empfehlung in der Regel ausreicht, dies aber davon abhängen kann, wie die Datenbank genutzt wird
- Ein Reporting-System kann zum Beispiel wegen komplexer Ad-hoc-Abfragen eine niedrige Cache-Trefferquote haben und bei einer etwas niedrigeren Einstellung sogar geringfügig bessere Leistung zeigen
- Mit der Erweiterung
pg_buffercache lässt sich exakt feststellen, welche Tabellen und Indizes den Shared Buffer belegen. Anhand der Zahl der genutzten Seiten im Buffer lässt sich der Wert für shared_buffers anpassen
- Wird der Buffer Cache nicht zu 100 % genutzt, ist die Einstellung möglicherweise zu hoch; dann kann die Instanzgröße oder der Wert von
shared_buffers reduziert werden
- Liegt die Nutzung bei 100 % und sind von vielen Tabellen nur Teilmengen im Cache, kann es sinnvoll sein, den Wert schrittweise zu erhöhen, bis der zusätzliche Nutzen abnimmt
- Auch die neue Sicht
pg_stat_io in Postgres 16 kann beim Tuning von shared_buffers helfen. Sie zeigt Trefferquoten sowie Lese-/Schreibvorgänge der Client-Backends
- Liegt das Verhältnis von Reads zu Writes nahe 1, kann das darauf hindeuten, dass Postgres dieselben Seiten ständig durch
shared_buffers rotiert. Um dieses Thrashing zu reduzieren, sollte shared_buffers erhöht werden
- Wenn man beginnt, über 50 % des System-RAM zu gehen, sollte man über eine größere Instanz nachdenken, da Postgres weiterhin Speicher für Benutzersitzungen und die zugehörigen Abfragen benötigt
Working Memory
- Die andere Hälfte des Speichers, den Postgres tatsächlich für die Arbeit nutzt, ist der Arbeitsspeicher, der über den Parameter
work_mem gesteuert wird
- Der Standardwert beträgt 4MB und ist einer der ersten Werte, die Nutzer zur Beschleunigung der Abfrageausführung anpassen
- Wenn das OS Postgres jedoch wegen Meldungen wie „Speicher knapp“ beendet, könnte es naheliegend erscheinen,
work_mem zu erhöhen – das verschlimmert das Problem aber nur. Es erhöht die von Postgres verwendete RAM-Menge und macht solche Abschaltungen wahrscheinlicher
- Viele verstehen „Arbeitsspeicher“ als eine einzelne Zuweisung für alle Operationen, die Postgres während einer Abfrage ausführt, tatsächlich kann es aber deutlich mehr sein
- Jeder Schritt (Node) erhält eine eigene Instanz von
work_mem. Nutzt man also den Standardwert von 4MB und eine Abfrage benötigt 4 Nodes, kann sie bis zu 16MB RAM verbrauchen
- Wenn auf einem stark ausgelasteten Server 100 solcher Abfragen gleichzeitig laufen, können allein für die Berechnung der Ergebnisse bis zu 1,6GB RAM verwendet werden. Komplexere Abfragen können je nach Anzahl der für die Ausführung nötigen Nodes noch mehr RAM benötigen
- Mit dem Befehl
EXPLAIN lässt sich der Ausführungsplan einer Abfrage prüfen; er zeigt, wie Postgres die Abfrage ausführt und welche Nodes zur Erzeugung der Ausgabe erforderlich sind
- In Kombination mit der Erweiterung
pg_stat_statements lassen sich die aktivsten Abfragen isolieren und der gesamte durch work_mem verursachte Speicherverbrauch abschätzen
- Ist
work_mem zu niedrig gesetzt, werden Zeilen oder Zwischenergebnisse, die nicht in den RAM passen, auf die Festplatte ausgelagert, was deutlich langsamer ist
- In der Sicht
pg_stat_database lassen sich die kumulierte Größe und Anzahl aller auf Festplatte geschriebenen temporären Dateien prüfen; wenn deren Durchschnittsgröße sinnvoll erscheint, kann work_mem um diesen Betrag erhöht werden
- Um grob abzuschätzen, wie viel RAM pro Sitzung verfügbar ist, kann folgende Formel verwendet werden:
(80 % des gesamten RAM - shared_buffers) / (max_connections)
- Bei 16GB RAM, 4GB Shared Buffers und 100 maximalen Verbindungen stehen zum Beispiel etwa 88MB pro Sitzung zur Verfügung
- Teilt man diesen Wert durch die durchschnittliche Anzahl der Plan-Nodes einer Abfrage, erhält man eine gute Einstellung für
work_mem
Ongoing Maintenance
- Der letzte abstimmbare Teil der Postgres-RAM-Nutzung ähnelt dem Arbeitsspeicher, ist aber speziell für Wartung zuständig und hat den ähnlich benannten Parameter
maintenance_work_mem
- Der Standardwert beträgt 64MB und legt fest, wie viel RAM für Operationen wie
VACUUM, CREATE INDEX und ALTER TABLE ADD FOREIGN KEY reserviert wird
- Da dies auf eine Operation pro Sitzung begrenzt ist und viele gleichzeitige Wartungsarbeiten eher unwahrscheinlich sind, gilt ein höherer Wert als relativ sicher
- Solche Wartungsaufgaben können sehr speicherintensiv sein und deutlich schneller abgeschlossen werden, wenn sie vollständig im RAM arbeiten können; daher sind Einstellungen von 1GB oder 2GB sehr verbreitet
- Ein wichtiger Vorbehalt ist der Postgres-Autovacuum-Prozess, der tote Tupel zur späteren Wiederverwendung markiert
- Autovacuum startet Hintergrund-Worker bis zum Limit von
autovacuum_max_workers, und jeder davon kann eine vollständige Instanz von maintenance_work_mem nutzen
- Server mit reichlich freiem RAM sind mit 1GB Wartungs-Arbeitsspeicher meist auf der sicheren Seite, bei knapperem RAM sollte man jedoch vorsichtiger sein
- Speziell zur Begrenzung von Autovacuum-Workern gibt es den separaten Parameter
autovacuum_work_mem
- Postgres-Autovacuum-Worker können nicht mehr als 1GB nutzen; eine Konfiguration von
autovacuum_work_mem über diesem Wert bringt daher keinen Effekt
Session Pooling
- Der einfachste Weg, den Speicherverbrauch zu senken, besteht darin, potenzielle Zuweisungen logisch zu begrenzen
- Postgres ist derzeit eine prozessbasierte Engine, daher erhält jede Benutzersitzung einen physischen Prozess statt eines Threads
- Dadurch bringt jede Verbindung einen bestimmten RAM-Overhead mit sich und trägt zu Kontextwechseln bei
- Deshalb lautet die übliche Empfehlung,
max_connections auf höchstens das Vierfache der verfügbaren CPU-Threads zu setzen. So wird die Zeit minimiert, die für das Umschalten aktiver Sitzungen zwischen CPUs nötig ist, und zugleich der gesamte RAM-Verbrauch der Sitzungen auf natürliche Weise begrenzt
- Wenn alle Sitzungen Abfragen ausführen und jeder Node eine Zuweisung von
work_mem darstellt, ergibt sich der theoretische maximale Verbrauch an Arbeitsspeicher aus connections * nodes * work_mem
- Die Komplexität von Abfragen zu reduzieren ist nicht immer möglich, die Zahl der Verbindungen aber meist schon
- Wenn eine Anwendung dauerhaft eine bestimmte erhöhte Zahl von Sitzungen offen hält oder mehrere einzelne Microservices auf Postgres angewiesen sind, ist das allerdings leichter gesagt als getan
- Die Formel
work_mem * max_connections * 5 ist eine grobe Schätzung für die maximale RAM-Menge, die eine Postgres-Instanz Benutzersitzungen zur Verarbeitung grundlegender Abfragen zuweisen könnte, unter der Annahme, dass alle Verbindungen aktiv sind
- Hat der Server dafür nicht genug RAM, sollte man einen der Faktoren reduzieren oder den RAM erhöhen
- Die angenommene Zahl von 5 Nodes pro durchschnittlicher Abfrage passt möglicherweise nicht zu Ihrer Anwendung und sollte angepasst werden, sobald ein besseres Verständnis der tatsächlichen Ausführungspläne vorliegt
- Der nächste Schritt ist die Einführung eines Connection Poolers wie PgBouncer
- Er entkoppelt Client-Verbindungen von der Datenbank und nutzt teure Postgres-Sitzungen zwischen Clients wieder
- Bei korrekter Konfiguration können Hunderte Clients einige Dutzend Postgres-Verbindungen teilen, ohne die Anwendung zu beeinträchtigen
- Es wurde beobachtet, dass PgBouncer auf diese Weise mehr als 1000 Verbindungen auf 40–50 multiplexen kann und so den gesamten Speicherverbrauch durch Prozess-Overhead deutlich reduziert
Reducing Bloat
- Einer der schwierigsten Aspekte beim Nachverfolgen der Speichernutzung ist wahrscheinlich Table Bloat
- Postgres verwendet zur Darstellung von Daten im Speichersystem Multi-Version Concurrency Control (MVCC)
- Das bedeutet: Jedes Mal, wenn eine Tabellenzeile geändert wird, erstellt Postgres irgendwo in der Tabelle eine weitere Kopie dieser Zeile und markiert sie mit einer neuen Versionsnummer
- Der
VACUUM-Prozess von Postgres markiert alte Zeilenversionen als „nicht verwendeten“ Speicherplatz, damit dort neue Versionen abgelegt werden können
- Postgres verfügt über einen Autovacuum-Hintergrundprozess, der solche wiederverwendbaren Zuweisungen fortlaufend findet und verhindert, dass Tabellen unbegrenzt wachsen
- Manchmal reicht die Standardkonfiguration dafür jedoch nicht aus, insbesondere bei großen Systemen, und diese Wartung gerät ins Hintertreffen
- Das Ergebnis kann sein, dass Tabellen mehr tote als lebende Zeilen enthalten und mit alten Daten „aufgebläht“ werden
- Wenn eine Tabelle stark aufgebläht ist, sollte man die Auswirkungen auf die Shared Buffers berücksichtigen
- Enthält jede Seite nur eine lebende Zeile und mehrere tote Zeilen, müssen für eine Abfrage mit 10 benötigten Zeilen 10 Seiten in die Shared Buffers geladen werden; das verschwendet viel Speicher, der anderweitig genutzt werden könnte
- Ist die Nachfrage nach diesen Zeilen besonders hoch, führt ihre Nutzung dazu, dass sie in den Shared Buffers verbleiben und die Cache-Effizienz deutlich sinkt
- Im Internet kursieren viele Abfragen zur Schätzung von Table Bloat, aber die einzige konkrete Möglichkeit, zu sehen, wie die Seiten einer Tabelle aussehen, bietet die Erweiterung
pgstattuple
- Ist
free_percent größer als 30 %, muss Autovacuum möglicherweise aggressiver konfiguriert werden. Liegt der Wert deutlich über 30 %, kann es sinnvoll sein, den Bloat vollständig zu beseitigen
- Die derzeit einzige unterstützte Methode dafür ist der Befehl
VACUUM FULL, der die Tabelle im Wesentlichen neu aufbaut. Dabei werden alle lebenden Zeilen an neue Positionen verschoben und die alte aufgeblähte Kopie verworfen
- Dieser Prozess vergibt für seine Dauer eine exklusive Zugriffssperre und erfordert daher in fast allen Fällen eine gewisse Downtime
- Eine Alternative dazu ist die von Tembo unterstützte Erweiterung
pg_repack
- Dieses Kommandozeilenwerkzeug kann Tabellen neu organisieren, um Bloat vollständig online und ohne exklusive Sperre zu entfernen
- Da dieses Tool außerhalb des Postgres-Kerns existiert und den Speicher von Tabellen und Indizes verändert, wird es oft als fortgeschrittene Nutzung betrachtet
- Vor dem Einsatz wird gründliches Testen außerhalb der Produktionsumgebung empfohlen
- Man kann noch weiter gehen und die Spaltenreihenfolge neu anordnen, um per „Spalten-Tetris“ die Zahl der Zeilen pro Seite zu maximieren
- Das ist vermutlich eine eher extreme Form der Optimierung, kann aber in Umgebungen, in denen sich Tabellen auf diese Weise frei neu aufbauen lassen, eine praktikable Strategie sein
The Balancing Act
- Die richtige Konfiguration all dieser Parameter und Ressourcen ist Kunst und Wissenschaft zugleich
- Wir haben uns angesehen, wie sich die tatsächliche Nutzung der Shared Buffers messen lässt und wie man erkennt, ob
work_mem zu niedrig ist
- Aber was, wenn – wie so oft – verfügbare Hardware oder Budget begrenzt sind? Genau hier kommt der „künstlerische“ Teil ins Spiel
- Bei knappen Speicherressourcen muss
shared_buffers vielleicht etwas reduziert werden, um Raum für mehr work_mem zu schaffen. Möglicherweise müssen auch beide Werte gesenkt werden
- Benötigt eine Anwendung viele Sitzungen, kann es sinnvoller sein,
work_mem zu reduzieren oder Connection Pooling einzuführen, damit gleichzeitige Sitzungen keine umfangreichen RAM-Zuweisungen aufsummieren
- Wenn man in der Vergangenheit
maintenance_work_mem erhöht hat, weil man davon ausging, dass genug RAM für alles vorhanden ist, kann es sinnvoller sein, diesen Wert wieder zu senken. Es gibt viel zu berücksichtigen
- Bei Instanzen mit wenig Speicher reichen selbst die obigen Empfehlungen möglicherweise nicht aus. In solchen Fällen sollte man die folgende Reihenfolge befolgen, um die Speichernutzung zu maximieren und Ressourcenerschöpfung zu vermeiden:
- Einen Connection Pooler hinzufügen und
max_connections senken. Das ist der schnellste und einfachste Weg, den maximalen Ressourcenverbrauch zu reduzieren
- Mit
EXPLAIN für die in pg_stat_statements gemeldeten häufigsten Abfragen die maximale statt der durchschnittlichen Zahl von Query-Nodes ermitteln. Anschließend work_mem auf höchstens (80 % des gesamten RAM - shared_buffers) / (max_connections * maximale Zahl der Plan-Nodes) setzen
maintenance_work_mem und autovacuum_work_mem auf den Standardwert von 64MB zurücksetzen. Wenn Wartungsaufgaben zu langsam sind und mehr RAM verfügbar gemacht werden kann, eine Erhöhung in 8MB-Schritten erwägen- Mit der Erweiterung
pg_buffercache die Menge der in shared_buffers gespeicherten Tabellen prüfen. Jede Tabelle und jeden Index genau untersuchen und prüfen, ob sich die Belegung durch Datenarchivierung, angepasste Abfragen mit geringerem Informationsbedarf usw. reduzieren lässt. Dazu kann auch VACUUM FULL oder pg_repack gehören, um von aktiv aufgeblähten Tabellen belegte Seiten zu komprimieren
- Wenn
pg_buffercache zeigt, dass shared_buffers voll ist und sich nicht weiter reduzieren lässt, ohne aktive Seiten zu verdrängen, die aktivsten Seiten mit der Spalte usagecount priorisieren. Da diese Werte von 1 bis 5 reichen, kann man sich auf Seiten mit 3–5 Nutzungen konzentrieren, um shared_buffers zu verkleinern, ohne die Leistung stark zu beeinträchtigen
- Schließlich leistungsfähigere Hardware bereitstellen. Wenn die Datenbank für die aktuelle Workload mehr RAM benötigt und eine Reduzierung der obigen Parameter die Systemleistung zu stark verschlechtern würde, ist ein Upgrade in der Regel sinnvoller
Noch keine Kommentare.