Re: Additional stats for Relations

Hi Jim,

On 10/18/06, Jim C. Nasby <jim(at)nasby(dot)net> wrote:
>
> 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.

By the above, do you mean the number of pages that could not be added to the
FSM because they had freespace which was less than the threshold for this
particular relation?

Regards,
Nikhils
EnterpriseDB http://www.enterprisedb.com

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(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.
>
> --
> Jim Nasby jim(at)nasby(dot)net
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>

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