Re: [HACKERS] Increase Vacuum ring buffer. - Mailing list pgsql-hackers

On 2017-07-27 11:53, Sokolov Yura wrote:
> 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,
> 
> 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.
> 

I've accidentally lost results of this run, so I will rerun it.

This I remembered:
- even with default settings, autovacuum runs 3 times faster:
9000s on master, 3000s with increased ring buffer.
So xlog-fsync really slows down autovacuum.
- but concurrent transactions slows down (not so extremely as in
previous test, but still significantly).
I could not draw pretty table now, cause I lost results. I'll do
it after re-run completes.

Could someone suggest, how to cooperate checkpoint with autovacuum,
to slow down autovacuum a bit during checkpoint?

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