Re: Autovacuum improvements

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.

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
> 


-- 
     === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997            http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/