On 2017-07-20 22:51, Tom Lane wrote:
Robert Haas <[email protected]> writes:I think that's a valid point. There are also other concerns here - e.g. whether instead of adopting the patch as proposed we ought to (a) use some smaller size, or (b) keep the size as-is but reduce the maximum fraction of shared_buffers that can be consumed, or (c) divide the ring buffer size through by autovacuum_max_workers. Personally, of those approaches, I favor (b). I think a 16MB ring buffer is probably just fine if you've got 8GB of shared_buffers but I'm skeptical about it when you've got 128MB of shared_buffers.WFM. I agree with *not* dividing the basic ring buffer size by autovacuum_max_workers. If you have allocated more AV workers, I think you expect AV to go faster, not for the workers to start fighting among themselves. It might, however, be reasonable for the fraction-of-shared-buffers limitation to have something to do with autovacuum_max_workers, so that you can't squeeze yourself out of shared_buffers if you set that number really high. IOW, I think the upthread suggestion of min(shared_buffers/8/autovacuum_workers, 16MB) is basically the right idea, though we could debate the exact constants. regards, tom lane
Attached version is with min(shared_buffers/8/autovacuum_workers, 16MB). With regards -- Sokolov Yura aka funny_falcon Postgres Professional: https://postgrespro.ru The Russian Postgres Company
From 8ebd5e7eb498fdc75fc7b724ace1f6de8fbcf3fd Mon Sep 17 00:00:00 2001 From: Sokolov Yura <[email protected]> Date: Tue, 18 Jul 2017 12:33:33 +0300 Subject: [PATCH] Set total vacuum ring buffer 16MB Vacuum suffers a lot from small ring buffer in a way bulk writer suffered before Tom Lane's fix at 6382448cf96: > the smaller size resulted in an undesirable decrease in bulk data > loading speed, due to COPY processing frequently getting blocked > for WAL flushing. During discussion were decided to set it to min(shared_buffers/8/autovacuum_max_workers, 16MB), so that many autovacuum workers will not consume significant part of shared buffers. --- src/backend/storage/buffer/freelist.c | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) diff --git a/src/backend/storage/buffer/freelist.c b/src/backend/storage/buffer/freelist.c index 9d8ae6ae8e..da83cd155b 100644 --- a/src/backend/storage/buffer/freelist.c +++ b/src/backend/storage/buffer/freelist.c @@ -16,6 +16,7 @@ #include "postgres.h" #include "port/atomics.h" +#include "postmaster/autovacuum.h" #include "storage/buf_internals.h" #include "storage/bufmgr.h" #include "storage/proc.h" @@ -526,6 +527,7 @@ GetAccessStrategy(BufferAccessStrategyType btype) { BufferAccessStrategy strategy; int ring_size; + int n; /* * Select ring size to use. See buffer/README for rationales. @@ -541,12 +543,15 @@ GetAccessStrategy(BufferAccessStrategyType btype) case BAS_BULKREAD: ring_size = 256 * 1024 / BLCKSZ; + n = 1; break; case BAS_BULKWRITE: ring_size = 16 * 1024 * 1024 / BLCKSZ; + n = 1; break; case BAS_VACUUM: - ring_size = 256 * 1024 / BLCKSZ; + ring_size = 16 * 1024 * 1024 / BLCKSZ; + n = autovacuum_max_workers; break; default: @@ -556,7 +561,7 @@ GetAccessStrategy(BufferAccessStrategyType btype) } /* Make sure ring isn't an undue fraction of shared buffers */ - ring_size = Min(NBuffers / 8, ring_size); + ring_size = Min(NBuffers / 8 / n, ring_size); /* Allocate the object and initialize all elements to zeroes */ strategy = (BufferAccessStrategy) -- 2.11.0
-- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
