Die PostgreSQL Dokumentation empfiehlt etwa 25% des Systemspeichers für PostgreSQL shared_buffers zu verwenden. Aber wie lässt sich eine gute Größe für den Anwendungsfall bestimmen?
Mein Server hat mit 64GB relativ viel RAM, die PostgreSQL Datenbank läuft als ein Dienst von vielen auf dem Server.
Die Datenbank ist im Verhältnis auch recht überschaubar. Wie sollten nun die shared_buffers konfiguriert werden?
Die Empfehlung aus der Dokumentation hier 25% des verfügbaren Speichers zu verwenden klingen im Verhältnis zur Datenbankgröße zu groß.
Die aktuelle Einstellung lässt sich direkt mit einer SQL Abfrage auslesen:
|
1 |
SHOW shared_buffers; |
Aber welche Größe ist sinnvoll? Brauche ich wirklich 16GB wie empfohlen? Ein zu großer Buffer ist eher schlecht, da er vom Betriebssystem Speicher wegnimmt und somit schlecht für die Caches dort ist.
Analyse der Buffer mit pg_buffercache
Zum Glück liefert PostgreSQL ein passendes Analysewerkzeug mit. Auf der zu untersuchenden Datenbank muss man die Extension pg_buffercache installieren.
|
1 |
CREATE EXTENSION IF NOT EXISTS pg_buffercache; |
Mit dieser Erweiterung lässt sich auslesen wie der Buffer verwendet wird. Streng genommen muss die Erweiterung nur in einer Datenbank installiert werden, da shared_buffers zwischen allen Datenbanken geteilt sind.
Ein besonders interessantes Feld aus pg_buffercache ist der usagecount.
Der usagecount beschreibt wie intensiv ein Eintrag (ein Buffer) aus dem Cache verwendet wird. Bei einem Zugriff wird der Wert erhöht (maximal bis 5). Jedes mal wenn PostgreSQL auf der Suche nach einem freien Buffer einmal durch den ganzen cache durch ist und keinen Platz gefunden hat wird der counter um 1 reduziert. Einträge die bei einem Count von 0 angekommen sind werden als nächstes neu vergeben.
Somit lässt sich am usagecount ablesen wie populär ein cache-Eintrag ist. Populäre Einträge zu cachen klingt sinnvoll.
Wenn man davon ausgeht, dass ein Cache erst dann richtig nützlich ist wenn die Einträge häufiger verwendet werden lässt sich mit einer recht einfachen Abfrage eine gute Näherung für die Größe bestimmen.
Optimale Größe für shared_buffers bestimmen
Nun ist es so, dass je nach Anwendung die Daten unterschiedlich intensiv angefragt werden. Um ein Gefühl für eine gute Größe zu bekommen konfiguriere ich den Buffer zuerst relativ groß. Dann führe ich eine Zeit lang die typischen Datenbankoperationen durch.
Wenn ich nur die populären Buffer (mit einem usagecount >= 3) berücksichtige kann ich basierend auf der blocksize (sind typisch 8k) ausrechen wie groß shared_buffers sein sollte damit zumindest diese populären Einträge Platz haben.
Die in PostgreSQL verwendete block_size lässt sich auslesen und ist in der Regel 8192 (8 Kilobyte):
|
1 |
SHOW block_size; |
Der verwendete Speicher von populären Buffern ist dann die Anzahl der Buffer multipliziert mit der Größe.
|
1 |
SELECT pg_size_pretty(count(*)*pg_size_bytes(current_setting('block_size'))) AS popular_shared_buffers FROM pg_buffercache WHERE usagecount>=3; |
Bei meinem speziellen Anwendungsfall hat sich gezeigt, dass die populären Einträge in 3GB passen. Mit einer Größe von 3500M für shared_buffers bin ich nun auf der sicheren Seite und spare deutlich RAM gegenüber den pauschalen 25% die in der Dokumentation genannt werden.
Eigentlich auch nicht verwunderlich, schließlich sind alle Anwendungen unterschiedlich. Da gibt es kein „one size fits all“.
Die Abfrage von pg_buffercache blockiert kurz den Cache. Daher sollte man das nicht zu häufig machen.
Monitoring der Werte
Wenn man die Größeneinstellung längerfristig beobachten will lässt sich das zum Beispiel mit Zabbix erledigen.
Ich habe auf Github ein Template bereitgestellt das die Größe der populären Buffer überwacht und alarmiert wenn diese 95% der Größe von shared_buffers überschreiten.