On Wed, 16 Jul 2025 at 04:51, Melanie Plageman
<melanieplageman@gmail.com> wrote:
>
> Hi,
>
> While discussing the next steps for AIO writes in Postgres, Andres
> suggested that a good starting point would be to begin evicting more
> than one buffer at a time in some of the buffer access strategies that
> perform writes. This would make it easier to later combine these
> writes and, eventually, issue them asynchronously.
>
> The attached patch implements this behavior for the BAS_BULKWRITE
> strategy. With the patch applied, I observe average performance
> improvements of about 15-20% for parallel COPY FROM operations on the
> same table.
>
> After some analysis, this improvement appears to be primarily due to
> reduced time spent by each backend waiting on the lock to flush WAL.
>
> Since backends now issue more data file writes before each WAL flush
> (using a heuristic that avoids eviction when it would require flushing
> WAL), there is less interleaving between WAL flushes and data file
> writes. With the patch applied, I observe client backends waiting
> significantly less on the WALWriteLock. I also see lower f_await times
> in iostat, suggesting reduced flush-related waiting at the kernel
> level as well.
>
> It's worth noting that for the serial COPY case (a single COPY FROM),
> performance remains essentially unchanged with the patch. The benefit
> seems to emerge only when multiple backends are concurrently writing
> data and flushing WAL. In fact, the benefits go down the fewer
> parallel COPY FROM operations are performed at a time.
>
Hi!
1) In EvictStrategyRing we find io context for strategy:
> + io_context = IOContextForStrategy(strategy);
but the caller of this function (GetVictimBuffer) already has one.
Should we reuse its context, pass it as function param to
EvictStrategyRing?
2) QuickCleanBuffer function has a return value which is never
checked. Should we change the signature to `void QuickCleanBuffer
(...)` ?
3) In QuickCleanBuffer, we have `buffer =
BufferDescriptorGetBuffer(bufdesc);`, while in QuickCleanBuffer we do
the opposite right before QuickCleanBuffer call. Should we pass
`bufnum` as a parameter?
> The benchmark I did was simple:
>
> -- make 16 source data files that are >= 1GB each
>
> initdb
> pg_ctl start
> createdb
>
> sudo fstrim -v /mnt/data
>
> psql -c "drop table foo; create table foo(a int, b int) with
> (autovacuum_enabled = off);"
>
> time pgbench \
> --no-vacuum \
> -c 16 \
> -j 16 \
> -t 4 \
> -f- <<EOF
> COPY foo FROM '/mnt/data/foo:client_id.data';
> EOF
>
> master -> patch
> 6.2 minutes -> 5 minutes : ~20% reduction
>
> A 15% improvement can be noticed with the same benchmark but 4 workers.
>
> - Melanie
In only get 5-10% improvements
I did this benchmark also. For 16 source data files that are 150MB
each I get 5-10 % speedup (5% with small shared_buffers and 10 % with
big shared_buffers).
--
Best regards,
Kirill Reshke