> I certainly wouldn't recommend using 1/2 of RAM right away. There's a
> good chance it would be a waste of memory - for example due to double
> buffering, which effectively reduces "total" cache hit ratio.
Double buffering is often mentioned in context of tuning shared buffers.
Is there a tool to actually measure the amount of double buffering
happening in the system?
> Those evictions are performed either by backends or bgwriter, both of
> which are less efficient than checkpointer. Not only can checkpointer
> perform various optimizations (e.g. sorting buffers to make the writes
> more sequential), but it also writes each dirty buffer just once. With
> smaller shared_buffers the page may have be written multiple times.
In the case when shared_buffers cover most of RAM, most of writes should
happen by checkpointer, and cache hit ratio should be high. So a
hypothetical question: Could shared_buffers=200GB on a 250 GB RAM server
ever be a reasonable setting? (assuming there are no other applications
running except postgres, and 50GB is enough for allocating
work_mem/maintenance_work_mem and for serving queries)
> The best thing you can do is set shared buffers to some conservative
> value (say, 4-8GB), let the system run for a day or two, compute the
> cache hit ratio using metrics in pg_stat_database, and then decide if
> you need to resize shared buffers.
>
> Gradual increases are a good approach in general. And yes, having
>
> buffers_checkpoint > buffers_clean > buffers_backend
>
> is a good idea too. Together with the cache hit ratio it's probably a
> more sensible metric than looking at usagecount directly.
Thanks! While increasing shared_buffers we'll be looking at changes in
cache hit ratio too.
Regards,
Vitaliy