Re: [HACKERS] 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.falcon@postgrespro.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