Feedback-ID: id4a34324:Fastmail
Date: Wed, 11 Jan 2023 10:54:45 -0800
From: Andres Freund <andres@anarazel.de>
To: pgsql-hackers@postgresql.org, Peter Geoghegan <pg@bowt.ie>,
	Robert Haas <robertmhaas@gmail.com>
Cc: Melanie Plageman <melanieplageman@gmail.com>,
	David Rowley <dgrowleyml@gmail.com>
Subject: Re: Option to not use ringbuffer in VACUUM, using it in failsafe mode
Message-ID: <20230111185445.rbs5tm32wstrc2yj@awork3.anarazel.de>
References: <20230111182720.ejifsclfwymw2reb@awork3.anarazel.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20230111182720.ejifsclfwymw2reb@awork3.anarazel.de>
Archived-At: <https://www.postgresql.org/message-id/20230111185445.rbs5tm32wstrc2yj%40awork3.anarazel.de>
Precedence: bulk

Hi,

On 2023-01-11 10:27:20 -0800, Andres Freund wrote:
> On cloud hardware with higher fsync latency I've seen > 15x time differences
> between using the ringbuffers and avoiding them by using pg_prewarm.

A slightly edited version of what I've in the past to defeat the ringbuffers
using pg_prewarm, as I think it might be useful for others:

WITH what_rel AS (
  SELECT 'copytest_0'::regclass AS vacuum_me
),
what_to_prefetch AS (
    SELECT vacuum_me, greatest(heap_blks_total - 1, 0) AS last_block,
        CASE WHEN phase = 'scanning heap' THEN heap_blks_scanned ELSE heap_blks_vacuumed END AS current_pos
    FROM what_rel, pg_stat_progress_vacuum
    WHERE relid = vacuum_me AND phase IN ('scanning heap', 'vacuuming heap')
)
SELECT
    vacuum_me, current_pos,
    pg_prewarm(vacuum_me, 'buffer', 'main', current_pos, least(current_pos + 10000, last_block))
FROM what_to_prefetch
\watch 0.1

Having this running in the background brings the s_b=128MB, ringbuffer enabled
case down from 77797ms to 14838ms. Close to the version with the ringbuffer
disabled.


Unfortunately, afaik, that trick isn't currently possible for the index vacuum
phase, as we don't yet expose the current scan position. And not every index
might be as readily prefetchable as just prefetching the next 10k blocks from
the current position.

That's not too bad if your indexes are small, but unfortunately that's not
always the case...

Greetings,

Andres Freund