On 2017-07-20 22:51, Tom Lane wrote:
Robert Haas <robertmh...@gmail.com> 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 <funny.fal...@postgrespro.ru>
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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
