Re: Vacuum questions...

From: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
To: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Vacuum questions...
Date: 2005-09-25 03:25:30
Message-ID: 433618AA.4050705@commandprompt.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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/

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2005-09-25 04:29:54 Re: Vacuum questions...
Previous Message Bruce Momjian 2005-09-25 01:20:03 Re: Discarding relations from FSM