Re: Publish checkpoint timing and sync files summary data to pg_stat_bgwriter

On Sun, Jan 15, 2012 at 10:28 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> I would like to be able to tell people they need never turn on
> log_checkpoints if they monitor pg_stat_bgwriter instead, because that sets
> a good precedent for what direction the database is going.  It would be nice
> for pg_stat_bgwriter's format to settle down for a long period too.

While on the topic of the format of pg_stat_bgwriter and it converging
on stability:

I'm finding the backend_writes column pretty unfortunate. The only
use I know of for it is to determine if the bgwriter is lagging
behind. Yet it doesn't serve even this purpose because it lumps
together the backend writes due to lagging background writes, and the
backend writes "by design" due to the use buffer access strategy
during bulk inserts.

If I'm running a tightly controlled benchmark on an otherwise unused
server and I know that no BAS is being used, then I can meaningfully
use backend_writes. That is a pretty limiting limit.

I think we should either create a separate column to segregate BAS
backend_writes, or just don't report them at all and report only the
non-BAS ones into pg_stat_bgwriter.

I know you've argued against this before
(http://archives.postgresql.org/pgsql-performance/2011-03/msg00340.php),
but I think the assumption that all writes are absorbed by the OS
without blocking is assuming an awful lot. If the server is not
behaving well, then it very well may be blocking on writes. If we are
confident that the OS can always efficiently cache writes, why bother
with a background writer at all?

I think the argument that it is not important to know which strategy
motivated a backend write could just as validly be reformulated as an
argument that we don't need to know how many backend writes there were
in the first place.

Knowing only an aggregation of BAS-motivated backend writes and
straggling-bgwriter-motivated backend writes just doesn't seem useful
to me. Am I missing a use for it? Not only is it probably not
useful, it seems to be actively harmful as I've seen several times
where it leads people down false paths (including me several times, as
about once a year I forget why it is useless and spend time
rediscovering it).

If we are not willing to separate the two types of backend writes, or
just omit the BAS ones altogether, then I would suggest we drop the
column entirely.

Would a patch to separate the backend writes into two columns be
entertained for 9.3?

Cheers,

Jeff