Re: Perf Benchmarking and regression. - Mailing list pgsql-hackers

From Fabien COELHO
Subject Re: Perf Benchmarking and regression.
Date
Msg-id alpine.DEB.2.10.1606031726180.9861@sto
Whole thread Raw
In response to Re: Perf Benchmarking and regression.  (Noah Misch <noah@leadboat.com>)
List pgsql-hackers
Hello Noah,

> The usual PostgreSQL handling of a deeply workload-dependent performance
> feature is to disable it by default.  That's what I'm inclined to do here, for
> every GUC the feature added.  Sophisticated users will nonetheless fully
> exploit this valuable mechanism in 9.6.

>> I don't think checkpoint_flush_after is in that class, due to the
>> fsync()s we already emit at the end of checkpoints.

I agree with Andres that checkpoint_flush_after shoud not be treated as 
other _flush_after settings.

> That's a promising hypothesis.

This is not an hypothesis but a proven fact. There has been hundreds of 
hours of pgbenchs runs to test and demonstrate the positive impact in 
various reasonable configurations.

> Some future project could impose a nonzero default 
> checkpoint_flush_after, having demonstrated that it imposes negligible 
> harm in the plausible cases it does not help.

I think that the significant and general benefit of checkpoint_flush_after 
has been largely demonstrated and reported on the hacker thread at various 
point of the development of the feature, and that it is safe, and even 
highly advisable to keep it on by default.

The key point is that it is flushing sorted buffers so that it mostly 
results in sequential writes. It avoids in a lot of case where the final 
sync at the end of the checkpoint generates too many ios which results in 
putting postgresql off line till the fsync is completed, from seconds to 
minutes at a time.

The other *_flush_after do not benefit for any buffer reordering, so their 
positive impact is maybe more questionnable, so I would be okay if these 
are disabled by default.

-- 
Fabien.



pgsql-hackers by date:

Previous
From: Masahiko Sawada
Date:
Subject: Re: Reviewing freeze map code
Next
From: Robert Haas
Date:
Subject: Re: Reviewing freeze map code