2018-02-18 14:41 GMT+01:00 Vitaliy Garnashevich <vgarnashevich@xxxxxxxxx>:
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.
When we did calculation of some analytic tasks, then increasing shared_buffers had negative impact on speed. Probably hit ration was too low after change, but the maintenance of shared buffers (searching free blocks) was slower.
So optimal size of SB depends on use case too much - note -- too big SB means small work mem what can be worse .. work_mem must be multiplied by max_connection and by some constant .. 2 or 3.
Regards
Pavel
Regards,
Vitaliy
