Re: pg_stat_bgwriter.buffers_backend is pretty meaningless (and more?) - Mailing list pgsql-hackers

Hi,

On 2021-04-12 19:49:36 -0700, Melanie Plageman wrote:
> So, I took a stab at implementing this in PgBackendStatus.

Cool!


> The attached patch is not quite on top of current master, so, alas,
> don't try and apply it. I went to rebase today and realized I needed
> to make some changes in light of e1025044cd4, however, I wanted to
> share this WIP so that I could pose a few questions that I imagine
> will still be relevant after I rewrite the patch.
>
> I removed buffers_backend and buffers_backend_fsync from
> pg_stat_bgwriter and have created a new view which tracks
>   - number of shared buffers the checkpointer and bgwriter write out
>   - number of shared buffers a regular backend is forced to flush
>   - number of extends done by a regular backend through shared buffers
>   - number of buffers flushed by a backend or autovacuum using a
>     BufferAccessStrategy which, were they not to use this strategy,
>     could perhaps have been avoided if a clean shared buffer was
>     available
>   - number of fsyncs done by a backend which could have been done by
>     checkpointer if sync queue had not been full

I wonder if leaving buffers_alloc in pg_stat_bgwriter makes sense after
this? I'm tempted to move that to pg_stat_buffers or such...

I'm not quite convinced by having separate columns for checkpointer,
bgwriter, etc. That doesn't seem to scale all that well. What if we
instead made it a view that has one row for each BackendType?


> In implementing this counting per backend, it is easy for all types of
> backends to keep track of the number of writes, extends, fsyncs, and
> strategy writes they are doing. So, as recommended upthread, I have
> added columns in the view for the number of writes for checkpointer and
> bgwriter and others. Thus, this view becomes more than just stats on
> "avoidable I/O done by backends".
>
> So, my question is, does it makes sense to track all extends -- those to
> extend the fsm and visimap and when making a new relation or index? Is
> that information useful? If so, is it different than the extends done
> through shared buffers? Should it be tracked separately?

I don't fully understand what you mean with "extends done through shared
buffers"?


> Another question I have is, should the number of extends be for every
> single block extended or should we try to track the initiation of a set
> of extends (all of those added in RelationAddExtraBlocks(), in this
> case)?

I think it should be 8k blocks, i.e. RelationAddExtraBlocks() should be
tracked as many individual extends. It's implemented that way, but more
importantly, it should be in BLCKSZ units. If we later add some actually
batched operations, we can have separate stats for that.


> Of course, this view, when grown, will begin to overlap with pg_statio,
> which is another consideration. What is its identity? I would find
> "avoidable I/O" either avoidable entirely or avoidable for that
> particular type of process, to be useful.

I think it's fine to overlap with pg_statio_* - those are for individual
objects, so it seems to be expected to overlap with coarser stats.


> Or maybe, it should have a more expansive mandate. Maybe it would be
> useful to aggregate some of the info from pg_stat_statements at a higher
> level -- like maybe shared_blks_read counted across many statements for
> a period of time/context in which we expected the relation in shared
> buffers becomes potentially interesting.

Let's do something more basic first...


Greetings,

Andres Freund