Re: Purpose of wal_init_zero

Hi @Andres Freund <andres(at)anarazel(dot)de>

> I'm not sure I understand the specifics here - did the high WAL generation
> rate result in the recycling taking too long? Or did checkpointer take
too
> long to write out data, and because of that recycling didn't happen
frequently
> enough?

If the WAL generation rate highly exceeds the max_wal_size within a
checkpoint interval, there aren't enough recycled WAL files available, the
system will create a large number of new WAL files. This can significantly
increase the initialization time, especially if we've increased the WAL
segment size to 64 MB (which would be 4x of 16 MB segment size).
Conversely, setting a very high max_wal_size to retain more recycled WAL
files can lead to longer recovery times, as the total WAL size might become
very large.

I'll talk to Theodore and confirm on the fallocate part.

> I think what we instead ought to do is to more aggressively initialize WAL
> files ahead of time, so it doesn't happen while holding crucial locks. We
> know the recent rate of WAL generation, and we could easily track up to
which
> LSN we have recycled WAL segments. Armed with that information walwriter
(or
> something else) should try to ensure that there's always a fair amount of
> pre-allocated WAL.

I agree. Having preallocated WAL files ahead of time will be the ideal
scenario.

> I put some patches together for this a few years ago [0], but ended up
> abandoning them due to lack of interest. I'm happy to revisit that effort
> if folks do become interested.

Great to know about this, and it aligns with our thinking. We can continue
the discussion on the other thread. I can also help wherever needed.

On Tue, 21 Jan 2025 at 06:39, Andy Fan <zhihuifan1213(at)163(dot)com> wrote:

>
> Hi,
>
> > On Fri, Jan 17, 2025 at 04:29:14PM -0500, Andres Freund wrote:
> >> I think what we instead ought to do is to more aggressively initialize
> WAL
> >> files ahead of time, so it doesn't happen while holding crucial locks.
> We
> >> know the recent rate of WAL generation, and we could easily track up to
> which
> >> LSN we have recycled WAL segments. Armed with that information
> walwriter (or
> >> something else) should try to ensure that there's always a fair amount
> of
> >> pre-allocated WAL.
> >
> > I put some patches together for this a few years ago [0], but ended up
> > abandoning them due to lack of interest. I'm happy to revisit that
> effort
> > if folks do become interested.
>
> Great to know this, I went through that thread and found the main
> considerations are pretty similar with what I am thinking when working
> out the Poc. I will go to [0] for further dicussion on this topic.
>
> > [0] https://postgr.es/m/20220408203003.GA1630183%40nathanxps13
> --
> Best Regards
> Andy Fan
>
>