Re: Additional stats for Relations

Hi Jim,

On 10/13/06, Jim C. Nasby <jim(at)nasby(dot)net> wrote:

> 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
EnterpriseDB http://www.enterprisedb.com
--
All the world's a stage, and most of us are desperately unrehearsed.