Re: shared_buffers 8GB maximum

2018-02-18 14:41 GMT+01:00 Vitaliy Garnashevich <vgarnashevich(at)gmail(dot)com>:

>
> 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
>
>
>