Re: Additional stats for Relations

Also how many times a relation has been vacuumed (which puts all the
other numbers in more perspective... good catch Simon). And I think
number of pages that could not be added to the FSM would also be
extremely valuable.

On Wed, Oct 18, 2006 at 11:27:39AM +0530, NikhilS wrote:
> 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@2ndquadrant.com> wrote:
> >
> >On Sat, 2006-10-14 at 11:32 +0530, NikhilS wrote:
> >
> >> On 10/13/06, Jim C. Nasby <jim@nasby.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.

-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)