Re: shared_buffers smaller than max_wal_size

On 09/25/2017 11:10 AM, Vladimir Mihailenco wrote:
> Thanks again - for some reason I thought that each page should be
> fsynced separately...
>
> I am running ZFS and going to try following config on 32gb server:
>
> shared_buffers = 512mb (previously was 6gb)
> max_wal_size = 8gb
> zfs_arc_max = 24gb
>
> i.e. run with minimal shared buffers and do all the caching in ZFS. As I
> understand it now such config can provide better results since data will
> be cached once in ZFS.
>

Maybe, or maybe not. It really depends on the nature of your workload.
The thing is, if the shared buffers are too small, dirty pages may be
evicted repeatedly, i.e.

1) postgres needs to modify a page, reads it from ARC to shared buffers
and modifies it

2) there's not enough free space in shared buffers, so the page gets
evicted from shared buffers to ARC (and eventually to disk)

3) postgres needs the page again, and reads it from ARC (or disk) back
to shared buffers

4) not enough free space in shared buffers - page gets written out

5) rinse and repeat

I don't recall off-hand how exactly is ARC managed, but I suppose
there's some sort of expiration period, so a single page may end up
being written to disk repeatedly.

With sufficiently large shared buffers the page would be repeatedly
modified in shared buffers, and written our just once by the checkpoint
process.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services