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

On Thu, Mar 22, 2012 at 6:07 PM, Peter Geoghegan <peter(at)2ndquadrant(dot)com> wrote:
> On 23 January 2012 02:08, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> On Sat, Jan 21, 2012 at 6:32 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> 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.
>>
>> +1 for separating those.
>
> I decided to have a go myself. Attached patch breaks out strategy
> allocations in pg_stat_bgwriter, but not strategy writes. My thinking
> is that this may serve to approximate non-BAS_NORMAL writes, with the
> considerable advantage of not requiring that I work backwards to
> figure out strategy from some block when backend-local syncing (yes,
> syncing, not writing) a buffer to work out which strategy object
> references the buffer. The bookkeeping that that would likely entail
> seems to make it infeasible.
>
> Incidentally, it seems Postgres doesn't currently record backend
> writes when the buffer doesn't go on to be sync'd. That seems
> problematic to me, or at the very least a misrepresentation, since
> temporary tables will be written out by the backend for example. Not
> sure if it's worth fixing, though I've added a comment to that effect
> at the site of where backend_writes is bumped.
>
> I have corrected the md.c bug. This previously would have prevented
> the sync_files (number of relation segments synced) value from being
> valid in non-log_checkpoints configurations.
>
> I'm not currently confident that the strategy_alloc filed is a very
> useful proxy for a strategy_backend_writes field. I think that rather
> than bumping the strategy allocation count analogously to the way the
> overall count is bumped (within StrategyGetBuffer()), I should have
> bumped earlier within BufferAlloc() so that it'd count if the buffer
> was requested with non-BAS_NORMAL strategy but was found in
> shared_buffers (so control never got as far as StrategyGetBuffer() ).
> That might make the value more closely line-up to the would-be value
> of a strategy_backend_writes column. What do you think?

Although I liked the idea of separating this out, I wasn't thinking we
should do it as part of this patch. It seems like an independent
project. At any rate, I strongly agree that counting the number of
strategy allocations is not really a viable proxy for counting the
number of backend writes. You can't know how many of those actually
got dirtied.

Since any backend write is necessarily the result of that backend
trying to allocate a buffer, I think maybe we should just count
whether the number of times it was trying to allocate a buffer *using
a BAS* vs. the number of times it was trying to allocate a buffer *not
using a BAS*. That is, decide whether or not it's a "strategy write"
not based on whether the buffer came in via a strategy allocation, but
rather based on whether it's going out as a result of a strategy
allocation.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company