Re: Autovacuum improvements

On Monday 15 January 2007 15:13, Darcy Buskermolen wrote:
> On Sunday 14 January 2007 08:45, Joshua D. Drake wrote:
> > > While we are talking autovacuum improvements, I'd like to also see some
> > > better logging, something that is akin to the important information of
> > > vacuum verbose being logged to a table or baring that the error_log.
> > > I'd like to be able to see what was done, and how long it took to do
> > > for each relation touched by av. A thought, having this information
> > > may even be usefull for the above thought of scheduler because we may
> > > be able to build some sort of predictive scheduling into this.
> >
> > This plays back to the vacuum summary idea that I requested:
> >
> > http://archives.postgresql.org/pgsql-hackers/2005-07/msg00451.php
>
> Well the fsm information is available in the pg_freespace contrib module,
> however it does not help with the "how long does it take to maintian XZY,
> or vacuum of relfoo did ABC".
>
> I'm thinking a logtable of something like the following:
>
> relid
> starttime
> elapsed_time
> rows
> rows_removed
> pages
> pages_removed
> reusable_pages
> cputime

I suppose that we may also want to track if FULL was done or not.

>
> This information then could be statisticaly used to ballance N queues to
> provide optimal vacuuming performance.
>
> Josh, is this more of what you were thinking as well ?
>
> > (Man our new search engine is so much better than the old one :))
> >
> > Joshua D. Drake
> >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 7: You can help support the
> > > PostgreSQL project by donating at
> > >
> > > http://www.postgresql.org/about/donate
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match