On 2017-07-20 19:04, Tom Lane wrote:
Claudio Freire <klaussfre...@gmail.com> writes:
On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas <robertmh...@gmail.com>
wrote:
I think the question for this patch is "so, why didn't we do it this
way originally?".
It's no secret that making the ring buffer larger will improve
performance -- in fact, not having a ring buffer at all would improve
performance even more. But it would also increase the likelihood
that
the background work of vacuum would impact the performance of
foreground operations, which is already a pretty serious problem that
we probably don't want to make worse. I'm not certain what the right
decision is here, but I think that a careful analysis of possible
downsides is needed.
IIRC, originally, the default shared_buffers settings was tiny.
At the time we set the ring buffer size to 256K, the maximum
shared_buffers that initdb would configure was 32MB; and you often
didn't
get that much due to SHMMAX. Now of course it's 128MB, and you'll
pretty
much always get that. So there's certainly room to argue that it's
time
to increase vacuum's ring buffer size, but that line of argument
doesn't
justify more than ~10X increase at most.
Like Robert, I'm afraid of changing this number in a vacuum (ahem).
If you've got the default number of autovacuum workers going (3), you'd
have them thrashing a total of 3/8ths of shared memory by default,
which
seems like a lot. We do need to look at the impact on foreground
processing, and not just at the speed of vacuum itself.
One idea for addressing this would be to raise the max values in the
switch, but tighten the fraction-of-shared-buffers limit just below.
I wouldn't have any objection to a 16MB ring buffer for vacuum when
it is coming out of a 1GB arena ... it just seems like a rather large
fraction of 128MB to give to a background process, especially to each
of several background processes.
Maybe the fraction-of-shared-buffers shouldn't be one size fits all,
but a different limit for each case?
regards, tom lane
It could be 'min(shared_buffers/8/autovacuum_workers, 16MB)'.
It quite rarely people live shared_buffers as 128MB, and those people
don't really care about other settings. So 5MB will be enough for
their autovacuum's ring buffer.
People, who care about tuning its postgresql, will increase their
shared_buffers, and autovacuum will have its 16MB ring buffer.
With regards,
--
Sokolov Yura aka funny_falcon
Postgres Professional: https://postgrespro.ru
The Russian Postgres Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
