Re: Additional stats for Relations

Hi,

So:
heap_blks_reused (with Jim's semantics), heap_blks_extend,
heap_blks_truncate are the "interesting" stats? Will try to work up a patch
for this.

Regards,
Nikhils
EnterpriseDB http://www.enterprisedb.com
On 10/15/06, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> On Sat, 2006-10-14 at 11:32 +0530, NikhilS wrote:
>
> > On 10/13/06, Jim C. Nasby <jim(at)nasby(dot)net> wrote:
>
>
> > 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>
>
> This makes sense for indexes, but only makes sense for heaps when we
> know that the backend will keep re-accessing the block until it is full
> - so only of interest in steady-state workloads.
>
> IMHO Jim's proposal makes more sense for general use.
>
> > > heap_blks_extend: The number of times file extend was
> > invoked on the
> > > relation
>
> Sounds good
>
> > > heap_blks_truncate: The total number of blocks that have
> > been truncated due
> > > to vacuum activity e.g.
>
> Sounds good
>
> > > 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
>
> Those last 2 sound too complex for normal use and ntruncate is most
> likely the same as number of vacuums anyway. Hmmm...Perhaps nvacuums is
> a more interesting metric? We've got last vacuum date, but no indication
> of how frequently a vacuum has run.
>
> > 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>
>
> This should be useful for tuning space allocation/deallocation. If we
> get this patch in early it should help get feedback on this area.
>
> --
> Simon Riggs
> EnterpriseDB http://www.enterprisedb.com
>
>
>

--
All the world's a stage, and most of us are desperately unrehearsed.