Horton

Re: Option to not use ringbuffer in VACUUM, using it in failsafe mode

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
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>
Date: 2023-01-11T18:54:45Z
Lists: pgsql-hackers 
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

Commits

  1. Add unit to vacuum_buffer_usage_limit value in postgresql.conf.sample.

  2. Improve VACUUM/ANALYZE BUFFER_USAGE_LIMIT docs

  3. Add --buffer-usage-limit option to vacuumdb

  4. Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option

  5. Move various prechecks from vacuum() into ExecVacuum()

  6. Always make a BufferAccessStrategy for ANALYZE

  7. Rename BufferAccessStrategyData.ring_size to nbuffers

  8. Disable vacuum's use of a buffer access strategy during failsafe

  9. Only make buffer strategy for vacuum when it's likely needed

  10. Remove some global variables from vacuum.c

  11. Doc: add Buffer Access Strategy to the glossary