Thread: 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 datain 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 typicalELTP 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.