Re: Autovacuum improvements

On Monday 15 January 2007 15:23, Joshua D. Drake wrote:
> 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
> >
> > 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 ?
>
> My original thought with Vacuum summary was that it would only give me
> the information I need from vacuum verbose. Vacuum Verbose is great if
> you want all the info, but normally you just want the last 5 lines :)
>
> If there were functions along with the log table that would give me the
> same info that would be great! Something like:
>
> select show_omg_vacuum_now_tables() ;)
>
> Seriously though...
>
> select show_fsm_summary() which would show information over the last 12
> hours, 24 hours or since last vacuum or something.

If it's only fsm you are thinking of then the contrib module is probably good
enough for you.

>
> Sincerely,
>
> Joshua D. Drake
>
> >> (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