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 <[EMAIL PROTECTED]> wrote: > > > >On Sat, 2006-10-14 at 11:32 +0530, NikhilS wrote: > > > >> On 10/13/06, Jim C. Nasby <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
