Re: Setting BLCKSZ 4kB

On 01/16/2018 11:17 AM, Giuseppe Broccolo wrote:
> Hi Sanyam,
>
> Interesting topic!
>
> 2018-01-16 7:50 GMT+01:00 sanyam jain <sanyamjain22(at)live(dot)in
> <mailto:sanyamjain22(at)live(dot)in>>:
>
> Hi,
>
> I am trying to solve WAL flooding due to FPWs.
>
>
> What are the cons of setting BLCKSZ as 4kB?
>
>
> When saw the results published
> on http://blog.coelho.net/database/2014/08/17/postgresql-page-size-for-SSD-2.html
> <http://blog.coelho.net/database/2014/08/17/postgresql-page-size-for-SSD-2.html>
>
> 4kB page is giving better performance in comparison to 8kB except
> when tested with 15kB row size.
>
>
> Does turning off FPWs will be safe if BLCKSZ is set to 4kB given
> page size of file system is 4kB?
>
>
> There is this interesting article of Tomas Vondra:
>
> https://blog.2ndquadrant.com/on-the-impact-of-full-page-writes/
>
> that explains some consequences turning off full_page_writes. If I
> correctly understood, turning off full_page_writes with BLCKSZ set
> to 4kB can reduce significantly the amount of produced WAL, but you
> cannot be sure that you are completely safe with a PostgreSQL page
> that can be completely contained in a 4kB file system page, though
> modern ones are less vulnerable to partial writes.
>

Actually, I don't have a definitive answer to that. I think using 4kB
pages might be safe assuming

(1) it's on a filesystem with 4kB pages

(2) it's on a platform with 4kB memory pages

(3) it's on storage with atomic 4kB writes (e.g. 4kB sectors or BBWC)

But unfortunately that's only something I *think* and I'm still looking
for someone with a deeper knowledge of this topic, who could confirm
that's the case.

>
> In the article, Tomas focus the attention on the fact that most of
> full page writes happens right after a checkpoint: a proper tuning
> of checkpoint can help reducing the amount of writes on the storage,
> continuing to safely keep full_page_writes enabled.
>

Right, and in most cases that's very effective way of reducing the
amount of WAL. Unfortunately, the "right after checkpoint" WAL spikes
are still there, and many workloads are resilient to that (e.g. inserts
with generated UUID values are a good example).

regards

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