Re: Increase Vacuum ring buffer.

On 2017-07-26 20:28, Sokolov Yura wrote:
> On 2017-07-26 19:46, Claudio Freire wrote:
>> On Wed, Jul 26, 2017 at 1:39 PM, Sokolov Yura
>> <funny(dot)falcon(at)postgrespro(dot)ru> wrote:
>>> On 2017-07-24 12:41, Sokolov Yura wrote:
>>> test_master_1/pretty.log
>> ...
>>> time activity tps latency stddev min max
>>> 11130 av+ch 198 198ms 374ms 7ms 1956ms
>>> 11160 av+ch 248 163ms 401ms 7ms 2601ms
>>> 11190 av+ch 321 125ms 363ms 7ms 2722ms
>>> 11220 av+ch 1155 35ms 123ms 7ms 2668ms
>>> 11250 av+ch 1390 29ms 79ms 7ms 1422ms
>>
>> vs
>>
>>> test_master_ring16_1/pretty.log
>>> time activity tps latency stddev min max
>>> 11130 av+ch 26 1575ms 635ms 101ms 2536ms
>>> 11160 av+ch 25 1552ms 648ms 58ms 2376ms
>>> 11190 av+ch 32 1275ms 726ms 16ms 2493ms
>>> 11220 av+ch 23 1584ms 674ms 48ms 2454ms
>>> 11250 av+ch 35 1235ms 777ms 22ms 3627ms
>>
>> That's a very huge change in latency for the worse
>>
>> Are you sure that's the ring buffer's doing and not some methodology
>> snafu?
>
> Well, I tuned postgresql.conf so that there is no such
> catastrophic slows down on master branch. (with default
> settings such slowdown happens quite frequently).
> bgwriter_lru_maxpages = 10 (instead of default 200) were one
> of such tuning.
>
> Probably there were some magic "border" that triggers this
> behavior. Tuning postgresql.conf shifted master branch on
> "good side" of this border, and faster autovacuum crossed it
> to "bad side" again.
>
> Probably, backend_flush_after = 2MB (instead of default 0) is
> also part of this border. I didn't try to bench without this
> option yet.
>
> Any way, given checkpoint and autovacuum interference could be
> such noticeable, checkpoint clearly should affect autovacuum
> cost mechanism, imho.
>
> With regards,
> --
> Sokolov Yura aka funny_falcon
> Postgres Professional: https://postgrespro.ru
> The Russian Postgres Company

I'll run two times with default postgresql.conf (except
shared_buffers and maintence_work_mem) to find out behavior on
default setting.

Then I'll try to investigate checkpoint co-operation with
autovacuum to fix behavior with "tuned" postgresql.conf.

--
Sokolov Yura aka funny_falcon
Postgres Professional: https://postgrespro.ru
The Russian Postgres Company