From e610bc78a2e3ecee50bd897e35084469d00bbac5 Mon Sep 17 00:00:00 2001 From: Thomas Munro Date: Thu, 4 Apr 2024 21:11:06 +1300 Subject: [PATCH v2 2/2] Increase default vacuum_buffer_usage_limit to 2MB. The BAS_VACUUM ring size has been 256kB since commit d526575f. Commit 1cbbee03 made it configurable but retained the traditional default. The correct default size has been debated for years, but 256kB is certainly very small. VACUUM soon needs to write back data it dirtied only 32 blocks ago, which usually requires flushing the WAL. New experiments in prefetching pages for VACUUM exacerbated the problem by crashing into dirty data even sooner. Let's make the default 2MB. That's 1.5% of the default toy buffer pool size, and 0.2% of 1GB, which would be a considered a small shared_buffers setting for a real system these days. Users are still free to set the GUC to a different value. Discussion: https://postgr.es/m/20240403221257.md4gfki3z75cdyf6%40awork3.anarazel.de Discussion: https://postgre.es/m/CA%2BhUKGLY4Q4ZY4f1rvnFtv6%2BPkjNf8MejdPkcju3Qii9DYqqcQ%40mail.gmail.com --- doc/src/sgml/config.sgml | 2 +- src/backend/storage/buffer/freelist.c | 2 +- src/backend/utils/init/globals.c | 2 +- src/backend/utils/misc/guc_tables.c | 2 +- src/backend/utils/misc/postgresql.conf.sample | 2 +- 5 files changed, 5 insertions(+), 5 deletions(-) diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 624518e0b0..d8e1282e12 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1961,7 +1961,7 @@ include_dir 'conf.d' valid sizes range from 128 kB to 16 GB. If the specified size would exceed 1/8 the size of shared_buffers, the size is silently capped - to that value. The default value is 256 kB. If + to that value. The default value is 2MB. If this value is specified without units, it is taken as kilobytes. This parameter can be set at any time. It can be overridden for and diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c index c69590d6d8..65a6b3b357 100644 --- a/src/backend/storage/buffer/freelist.c +++ b/src/backend/storage/buffer/freelist.c @@ -561,7 +561,7 @@ GetAccessStrategy(BufferAccessStrategyType btype) ring_size_kb = 16 * 1024; break; case BAS_VACUUM: - ring_size_kb = 256; + ring_size_kb = 2048; break; default: diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c index 3e38bb1311..cc61937eef 100644 --- a/src/backend/utils/init/globals.c +++ b/src/backend/utils/init/globals.c @@ -143,7 +143,7 @@ int max_parallel_workers = 8; int MaxBackends = 0; /* GUC parameters for vacuum */ -int VacuumBufferUsageLimit = 256; +int VacuumBufferUsageLimit = 2048; int VacuumCostPageHit = 1; int VacuumCostPageMiss = 2; diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index c12784cbec..7d4e4387cf 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -2275,7 +2275,7 @@ struct config_int ConfigureNamesInt[] = GUC_UNIT_KB }, &VacuumBufferUsageLimit, - 256, 0, MAX_BAS_VAC_RING_SIZE_KB, + 2048, 0, MAX_BAS_VAC_RING_SIZE_KB, check_vacuum_buffer_usage_limit, NULL, NULL }, diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index baecde2841..2166ea4a87 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -166,7 +166,7 @@ # mmap # (change requires restart) #min_dynamic_shared_memory = 0MB # (change requires restart) -#vacuum_buffer_usage_limit = 256kB # size of vacuum and analyze buffer access strategy ring; +#vacuum_buffer_usage_limit = 2MB # size of vacuum and analyze buffer access strategy ring; # 0 to disable vacuum buffer access strategy; # range 128kB to 16GB -- 2.39.3 (Apple Git-146)