Re: shared_buffers smaller than max_wal_size

Hi,

On 09/23/2017 08:18 AM, Vladimir Mihailenco wrote:
> Hi,
>
> I wonder what is the point of setting max WAL size bigger than shared
> buffers, e.g.
>
> shared_buffers = 512mb
> max_wal_size = 2gb
>
> As I understand a checkpoint happens after 2gb of data were modified
> (writter to WAL), but shared buffers can contain at most 512mb of dirty
> pages to be flushed to the disk. Is it still a win or I am missing
> something?

Those are mostly unrelated things.

max_wal_size determines how often you'll do checkpoints. So with a lot
of writes you probably need high max_wal_size, otherwise you'll do
checkpoints very often. Choose reasonable checkpoint_timeout and set
max_wal_size based on that.

Shared buffers are mostly about caching data accessed by queries. If you
can squeeze the frequently accessed data into shared buffers (high cache
hit ratio), great.

Moreover, there's very little relation between max_wal_size and
shared_buffers, for a number of reasons:

1) You can modify the same 8kB page repeatedly - it will still be just
8kB of dirty data in shared buffers, but each update will generate a
little bit of WAL data. In an extreme case a single 8kB page might be
responsible for most of the 2GB of WAL data.

2) When changing the data page, we only really write the minimum amount
of data describing the change into WAL. So it's not 1:1.

3) When a page is evicted from shared buffers, we don't fsync it to disk
immeditely. We write it out to page cache, and leave the eviction to the
OS (with some exceptions), so it's asynchronous. WAL writes are
asynchronous.

4) Shared buffers are not just about dirty data, it's also about caching
reads. No one knows what is the read:write ratio, what part of the
database will receive writes, etc.

So there's nothing inherently wrong with (shared_buffers > max_wal_size)
or (shared_buffers > max_wal_size), it depends on your workload.

regards

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