Обсуждение: [GENERAL] shared_buffers smaller than max_wal_size
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?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Thanks for your response. Ss I understand it now the difference is that checkpoints are synchronous but dirty pages eviction from shared buffers are asynchronous, correct? How then Postgres ensures that OS writes data to the disk so WAL can be deleted?
>WAL writes are asynchronous.
Is there a typo? If not then what data is written synchronously?>WAL writes are asynchronous.
On Sat, Sep 23, 2017 at 6:01 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
Hi,Those are mostly unrelated things.
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?
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
On 09/24/2017 11:03 AM, Vladimir Mihailenco wrote: > Thanks for your response. Ss I understand it now the difference is > that checkpoints are synchronous but dirty pages eviction from shared > buffers are asynchronous, correct? How then Postgres ensures that OS > writes data to the disk so WAL can be deleted? > The last step in a checkpoin is fsync() on the files. Without that, the checkpoint is considered incomplete and the database won't rely on it. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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.
On Sun, Sep 24, 2017 at 8:59 PM, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
On 09/24/2017 11:03 AM, Vladimir Mihailenco wrote:
> Thanks for your response. Ss I understand it now the difference is
> that checkpoints are synchronous but dirty pages eviction from shared
> buffers are asynchronous, correct? How then Postgres ensures that OS
> writes data to the disk so WAL can be deleted?
>
The last step in a checkpoin is fsync() on the files. Without that, the
checkpoint is considered incomplete and the database won't rely on it.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general