On 2017-07-20 19:04, Tom Lane wrote:
> 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
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