pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option
Date: Thu, 06 Apr 2023 23:41:14 +0000
Message-ID: <E1pkZE9-001lHb-Cm@gemulon.postgresql.org> (raw)

Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option

Add new options to the VACUUM and ANALYZE commands called
BUFFER_USAGE_LIMIT to allow users more control over how large to make the
buffer access strategy that is used to limit the usage of buffers in
shared buffers.  Larger rings can allow VACUUM to run more quickly but
have the drawback of VACUUM possibly evicting more buffers from shared
buffers that might be useful for other queries running on the database.

Here we also add a new GUC named vacuum_buffer_usage_limit which controls
how large to make the access strategy when it's not specified in the
VACUUM/ANALYZE command.  This defaults to 256KB, which is the same size as
the access strategy was prior to this change.  This setting also
controls how large to make the buffer access strategy for autovacuum.

Per idea by Andres Freund.

Author: Melanie Plageman
Reviewed-by: David Rowley
Reviewed-by: Andres Freund
Reviewed-by: Justin Pryzby
Reviewed-by: Bharath Rupireddy
Discussion: https://postgr.es/m/20230111182720.ejifsclfwymw2reb@awork3.anarazel.de

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/1cbbee03385763b066ae3961fc61f2cd01a0d0d7

Modified Files
--------------
doc/src/sgml/config.sgml                      | 30 +++++++++
doc/src/sgml/ref/analyze.sgml                 | 20 ++++++
doc/src/sgml/ref/vacuum.sgml                  | 24 +++++++
src/backend/commands/vacuum.c                 | 95 ++++++++++++++++++++++++++-
src/backend/commands/vacuumparallel.c         | 14 +++-
src/backend/postmaster/autovacuum.c           | 18 +++--
src/backend/storage/buffer/README             | 12 ++--
src/backend/storage/buffer/freelist.c         | 61 ++++++++++++++---
src/backend/utils/init/globals.c              |  5 +-
src/backend/utils/misc/guc_tables.c           | 11 ++++
src/backend/utils/misc/postgresql.conf.sample |  3 +
src/bin/psql/tab-complete.c                   |  4 +-
src/include/miscadmin.h                       |  9 +++
src/include/storage/bufmgr.h                  |  5 ++
src/include/utils/guc_hooks.h                 |  2 +
src/test/regress/expected/vacuum.out          | 19 ++++++
src/test/regress/sql/vacuum.sql               | 15 +++++
17 files changed, 322 insertions(+), 25 deletions(-)



view thread (7+ messages)  latest in thread

Message-ID: <E1pkZE9-001lHb-Cm@gemulon.postgresql.org>
Permalink:  ../E1pkZE9-001lHb-Cm@gemulon.postgresql.org/
Also on:    postgresql.org/message-id/E1pkZE9-001lHb-Cm@gemulon.postgresql.org

 · 

reply

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-committers@postgresql.org
  Cc: drowley@postgresql.org, pgsql-committers@lists.postgresql.org
  Subject: Re: pgsql: Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option
  In-Reply-To: <E1pkZE9-001lHb-Cm@gemulon.postgresql.org>

* 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