From: Nathan Bossart <nathandbossart@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Andy Fan <zhihuifan1213@163.com>
Cc: Justin Pryzby <pryzby@telsasoft.com>
Cc: Maxim Orlov <orlovmg@gmail.com>
Cc: Pavel Borisov <pashkin.elfe@gmail.com>
Cc: Bossart, Nathan <bossartn@amazon.com>
Cc: Maxim Orlov <m.orlov@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Pre-allocating WAL files
Date: Wed, 22 Jan 2025 09:50:59 -0600
Message-ID: <Z5ET4xCZJEIx3bKK@nathan> (raw)
In-Reply-To: <vfhyldq3rtnwd4uxbb7ro2opmvbmfswvkycavmzcqb55mmo4jk@g2d2n6xnwicb>
References: <CALT9ZEG+R9ne6xTyEVSiLhZ7wJAQ1Jq-9XVZs=zbd_jQJyPTdA@mail.gmail.com>
<CACG=ezb5Os_ohi1caZJLKsGJc-nSSqGXFrMjmKw4qN8fG_=NEg@mail.gmail.com>
<20220301144044.GG25269@telsasoft.com>
<20220301150704.GA1030078@nathanxps13>
<20220317231212.GB1415284@nathanxps13>
<20220408203003.GA1630183@nathanxps13>
<87sepc4zcg.fsf@163.com>
<Z4_C04ZJ_SymvYes@nathan>
<Z4_Hmj2R8NXnAI-K@nathan>
<vfhyldq3rtnwd4uxbb7ro2opmvbmfswvkycavmzcqb55mmo4jk@g2d2n6xnwicb>
On Tue, Jan 21, 2025 at 11:23:06AM -0500, Andres Freund wrote:
> On 2025-01-21 10:13:14 -0600, Nathan Bossart wrote:
>> On Tue, Jan 21, 2025 at 09:52:51AM -0600, Nathan Bossart wrote:
>> > On Tue, Jan 21, 2025 at 03:31:27AM +0000, Andy Fan wrote:
>> >> 3. Why is the purpose of preallocated_segments directory? what in my
>> >> mind is we just prellocate the normal filename so that XLogWrite could
>> >> open it directly. This is same as what wal_recycle does and we can reuse
>> >> the same strategy to clean up them if they are not needed anymore.
>> >
>> > The purpose is to limit the use of pre-allocated segments to only
>> > situations where WAL recycling is not sufficient. Basically, if writing a
>> > record would require a new segment to be created, we can quickly pull a
>> > pre-allocated one instead of creating it ourselves. Besides simplifying
>> > matters, this prevents a lot of unnecessary pre-allocation, since many
>> > workloads will almost never need anything beyond the recycled segments.
>
> I don't really understand that argument - we should be able to predict rather
> precisely whether we need to preallocate or not. We have the recent WAL "fill
> rate", we know the end of the WAL and we can easily track how far ahead of the
> current point we have allocated. Why preallocate when we have a large reserve
> of "future" segments? Why preallocate in a separate directory when we have no
> future segments?
If we can indeed reliably predict whether we need pre-allocation, then
sure, let's just create future segments directly in pg_wal. I'm not sure
we could reliably predict whether WAL will be recycled in time, so we might
pre-allocate a bit more than necessary, but that's not too terrible. My
"pooling" approach was intended to keep the pre-allocation to a minimum
(IME you really only need a couple at any given time) and to avoid the
guesswork involved in predicting.
>> That being said, it would be nice to avoid the fsync() overhead to move a
>> pre-allocated WAL into place. My first instinct is that would be
>> substantially more complicated and may not actually improve matters all
>> that much, but I agree that it's worth exploring.
>
> FWIW, I've seen the fsyncs around recycling being a rather substantial
> bottleneck. To the point of the main benefit of larger segments being the
> reduction in number of fsyncs at the end of a checkpoint. I think we should
> be able to make the fsyncs a lot more efficient by batching them, first rename
> a bunch of files, then fsync them and the directory. The current pattern
> bascially requires a separate filesystem jouranl flush for each WAL segment.
+1, these kinds of fsync() patterns should be fixed.
--
nathan
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, andres@anarazel.de, zhihuifan1213@163.com, pryzby@telsasoft.com, orlovmg@gmail.com, pashkin.elfe@gmail.com, bossartn@amazon.com, m.orlov@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Pre-allocating WAL files
In-Reply-To: <Z5ET4xCZJEIx3bKK@nathan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox