Re: Vacuum questions...

Jim C. Nasby wrote:

>Would it be difficult to vacuum as part of a dump? The reasoning behind
>this is that you have to read the table to do the dump anyway, so it
>would be a good time to be able to piggy-back other operations that need
>to read the entire table on top. I know vacuuming of indexes complicates
>this, so it's probably not as simple as just firing off a vacuum and
>copy at the same time (although that idea is probably worth testing,
>since it might still be a win).
>  
>
This would be a nightmare on a large database. Think of how
long it takes to dump 20 gig, now add how long it is going to
take to vacuum that size of DB, now think about a 500 gig
database.

Actually this also probably would not gain you much in 8.1
as vacuum in theory is already dealing with itself.

>When dropping a table or index, is it's space immediately released in
>the FSM?
>  
>
I would have to double check but I believe you would have to
vacuum to reclaim the space to the FSM because the relationship
is still there just like when you delete (but not truncate).

>Also, would it be possible to add some means to check the status of a
>running vacuum? Even with vacuum verbose, once it starts in on a large
>table you have no way to know how far along it is.
>  
>
That is an interesting thought... Perhaps a quick scan of
the table to see how many dead rows there are? Then check
back every n/10 ? Hmmm... I am not a C guy so I don't know if
that is technically feasible (although probably possible) but it
is interesting from a DBA perspective.

Although that could be an issue on a large table as well I think.

>Finally, if vacuum_delay is enabled, does vacuum_cost_page_miss consider
>a miss as not in the database buffer, or not in the kernel buffer? I
>remember discussions about trying to track IO request times to try and
>determine if something came out of kernel buffers or not, but AFAIK
>that's all vaporware right now...
>  
>
Good question, anyone else?

Sincerely,

Joshua D. Drake





-- 
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/