Best practices for data buffer cache setting/tuning (v15)
Is there a document which makes recommendations on sizing data buffer
cache, tuning options which evict old/unused data in mem, and cache
fragmentation avoidance for a v15.3 DB ?
Thanks in Advance.
On Mon, 2024-01-29 at 14:39 -0500, David Gauthier wrote:
Is there a document which makes recommendations on sizing data buffer cache,
tuning options which evict old/unused data in mem, and cache fragmentation
avoidance for a v15.3 DB ?
Start with "shared_buffers" sized as a quarter of the available RAM and
define enough huge pages on the Linux kernel to fit shared buffers.
There are no tuning options for evicting buffers, and there cannot be any
fragmentation.
Yours,
Laurenz Albe
On Jan 29, 2024, at 11:39, David Gauthier <dfgpostgres@gmail.com> wrote:
Is there a document which makes recommendations on sizing data buffer cache, tuning options which evict old/unused data in mem, and cache fragmentation avoidance for a v15.3 DB ?
On any modern system, set shared_buffers to 25% of instance RAM, and get on with life.
I realize that seems glib, but small variations in shared_buffers make almost no significant difference in performance. There are unusual workloads (like very high insert rate with few queries) that require different settings, but for most typical ELTP or data warehouse workloads, that is sufficient.
On Mon, Jan 29, 2024 at 01:46:30PM -0800, Christophe Pettus wrote:
On Jan 29, 2024, at 11:39, David Gauthier <dfgpostgres@gmail.com>
wrote:Is there a document which makes recommendations on sizing data
buffer cache, tuning options which evict old/unused data in mem, and
cache fragmentation avoidance for a v15.3 DB ?On any modern system, set shared_buffers to 25% of instance RAM, and
get on with life.I realize that seems glib, but small variations in shared_buffers make
almost no significant difference in performance. There are unusual
workloads (like very high insert rate with few queries) that require
different settings, but for most typical ELTP or data warehouse
workloads, that is sufficient.
This blog entry might explain why Postgres requires less tuning of
shared buffers than other database systems:
https://momjian.us/main/blogs/pgblog/2018.html#December_7_2018
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.