Hi Jim,
On Fri, Oct 13, 2006 at 06:17:47PM +0530, NikhilS wrote:
> Currently a "select * from pg_statio_user_tables;" displays only
> heap_blks_read, heap_blks_hit stats amongst others for the main relation. It
> would be good to have the following stats collected too. I think these stats
> can be used to better statistically analyze/understand the block I/O
> activity on the relation:
>
> heap_blks_reused: The number of buffers returned by the FSM for use to store
> a new tuple in
The description on this is misleading... FSM doesn't return buffers, it
returns pages that have free space on them.
<Nikhils>
FSM returns the block number from which we fetch the buffer. This is similar to the way we track buffer_read stats in ReadBuffer.
<Nikhils>
I'm also not sure if this metric is what you actually want, since a
single page can be returned many times from the FSM even between
vacuums. Tracking how many pages for a relation have been put into the
FSM might be more useful...
<Nikhils>
Pages might be put into the FSM, but by this metric don't we get the actual usage of the pages from the FSM? Agreed a single page can be returned multiple times, but since it serves a new tuple, shouldn't we track it?
<Nikhils>
> heap_blks_extend: The number of times file extend was invoked on the
> relation
>
> heap_blks_truncate: The total number of blocks that have been truncated due
> to vacuum activity e.g.
>
> As an addendum to the truncate stats above, we can also have the additional
> following stats:
>
> heap_blks_maxtruncate: The max block of buffers truncated in one go
>
> heap_blks_ntruncate: The number of times truncate was called on this
> relation
>
> I can come up with a patch (already have one) for the above. Any
> thought/comments?
Do you have a use-case for this info? I can see where it might be neat
to know, but I'm not sure how you'd actually use it in the real world.
<Nikhils>
The use-case according to me is that these stats help prove the effectiveness of autovacuum/vacuum operations. By varying some autovac guc variables, and doing subsequent (pgbench e.g.) runs, one can find out the optimum values for these variables using these stats.
<Nikhils>
Regards,
Nikhils
