Claudio Freire <klaussfreire@gmail.com> writes:
> On Thu, Jul 20, 2017 at 11:59 AM, Robert Haas <robertmhaas@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
