Thread: Is vacuum full lock like old's vacuum's lock?
Recently moved to 7.2 and have been mainly doing "vacuum analyze". I have only run "vacuum full" on the weekend when nobody is using the server. I am wondering if "vacuum full" locks the tables like the old vacuum did.
Francisco Reyes <lists@natserv.com> writes: > Recently moved to 7.2 and have been mainly doing "vacuum analyze". I have > only run "vacuum full" on the weekend when nobody is using the server. I > am wondering if "vacuum full" locks the tables like the old vacuum did. AFIAK "vacuum full" is exactly the same as the old "vacuum". -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
On 1 Mar 2002, Doug McNaught wrote: > Francisco Reyes <lists@natserv.com> writes: > > > Recently moved to 7.2 and have been mainly doing "vacuum analyze". I have > > only run "vacuum full" on the weekend when nobody is using the server. I > > am wondering if "vacuum full" locks the tables like the old vacuum did. > > AFIAK "vacuum full" is exactly the same as the old "vacuum". Thanks. So I will continue to do it "off hours".
On 1 Mar 2002, Neil Conway wrote: > Why do you need 'vacuum full' rather than just 'vacuum'? Read the docs, > it's usually not necessary. Because every night I delete/reload a big chunk of my data and then once a week I delete/reload the entire dataset... about 7 million records. I don't truncate because I want the users to see a "Stable" view of the system at any point so I have the delete/reload inside of a commit statement.
On Sat, Mar 02, 2002 at 01:23:59AM -0500, Francisco Reyes wrote:
> On 1 Mar 2002, Neil Conway wrote:
>
> > Why do you need 'vacuum full' rather than just 'vacuum'? Read the docs,
> > it's usually not necessary.
>
> Because every night I delete/reload a big chunk of my data and then once a
> week I delete/reload the entire dataset... about 7 million records.
This is one thing that is slightly confusing (to me) about the new
vacuum -- perhaps someone more familiar with the internals can
clarify?
I thought that, in the case Mr Reyes is talking about, Postgres would
again use the freed disk space. It's just that the space would not
be available to other applications. I thought what VACUUM FULL did
was just free the disk space _absolutely_.
If I'm right, does that also mean that performance is actually
(marginally) _better_ in these types of cases, because the system
doesn't need to request new disk blocks from the OS?
A
--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M6K 3E3
+1 416 646 3304 x110
Andrew Sullivan <andrew@libertyrms.info> writes:
> I thought that, in the case Mr Reyes is talking about, Postgres would
> again use the freed disk space. It's just that the space would not
> be available to other applications. I thought what VACUUM FULL did
> was just free the disk space _absolutely_.
> If I'm right, does that also mean that performance is actually
> (marginally) _better_ in these types of cases, because the system
> doesn't need to request new disk blocks from the OS?
For situations where your turnover between vacuums is a small part
of the table (say up to 10% or so), I think VACUUM is a clear win
over VACUUM FULL. As you say, there's little percentage in doing
a lot of tuple-shuffling in order to return some disk blocks to
the OS, if you're only going to need the space back again soon.
Might as well accept some steady-state space overhead.
However, in Francisco's case he wants to completely replace the
table contents --- and if he wants to maintain service to clients
while he does it, then there's no way around the fact that the
peak space consumption is going to be twice the nominal table size.
(Can't invalidate the old tuples till you've loaded all the new
ones.) So if he just does VACUUMs then he's going to have a
steady-state space consumption 2x larger than minimum, not a few
percent larger than minimum. That might be annoying --- particularly
if he's got queries that do sequential scans of the table. Might be
worth a VACUUM FULL to knock the space usage back down.
(On the other hand, if the goal is "continuous service" then I
think VACUUM FULL is out of the question anyway; it'll lock down
the table for too long.)
regards, tom lane
On Sat, 2 Mar 2002, Tom Lane wrote: Catching up with the lists. > However, in Francisco's case he wants to completely replace the > table contents Not only that is correct, but now that we are "near" production I am pushing even more data. --- and if he wants to maintain service to clients > while he does it, then there's no way around the fact that the > peak space consumption is going to be twice the nominal table size. Space is not a problem in my case. > ones.) So if he just does VACUUMs then he's going to have a > steady-state space consumption 2x larger than minimum, not a few > percent larger than minimum. That might be annoying --- particularly > if he's got queries that do sequential scans of the table. Might be > worth a VACUUM FULL to knock the space usage back down. Do sequential scans go over the entire space, including the space not in use? It would be great if there was some kind of optimization that could move the empty space towards the end. It would probably be an expensive operation, but it may be very helpfull on databases with a big turnaround. > (On the other hand, if the goal is "continuous service" then I > think VACUUM FULL is out of the question anyway; it'll lock down > the table for too long.) I am doing VACUUM FULL weekly, but I am thinking whether to try daily. I am only a bit concerned about how long it is going to take. Does vacuum full locks only a table or the entire DB? Specially if I did VACUUM FULL <table>. I am thinking maybe scatter the VACUUM FULLs accross the week and doing one table daily instead of trying the whole DB.
> Do sequential scans go over the entire space, including the space not in > use? It would be great if there was some kind of optimization that could > move the empty space towards the end. It would probably be an expensive > operation, but it may be very helpfull on databases with a big turnaround. The only difference between doing that and doing a VACUUM FULL would be that the disk usage would remain the same. > Does vacuum full locks only a table or the entire DB? I'm sure someone will correct me if I'm wrong (as Tom Lane has had to do on many an occassion <g>), but I think it just locks the table that's being vacuumed. > Specially if I did VACUUM FULL <table>. > I am thinking maybe scatter the VACUUM FULLs accross the week and doing > one table daily instead of trying the whole DB. Personally, I would continue do the entire database once a week, and select a few key tables that have a lot of UPDATEs/DELETEs to do daily (or however frequently is necessary). That way the tables that need it get more frequent attention, but you don't have to go over the entire database frequently. Of course, I haven't been following the thread carefully enough to understand your specific needs... Greg
On Fri, 8 Mar 2002, Gregory Wood wrote: > > Do sequential scans go over the entire space, including the space not in > > use? It would be great if there was some kind of optimization that could > > move the empty space towards the end. It would probably be an expensive > > operation, but it may be very helpfull on databases with a big turnaround. > > The only difference between doing that and doing a VACUUM FULL would be that > the disk usage would remain the same. There is one other extremely important difference. VACUUM FULL locks the table/database. > Personally, I would continue do the entire database once a week, and select > a few key tables that have a lot of UPDATEs/DELETEs to do daily thanks like a reasonable thing to do.
> > > Do sequential scans go over the entire space, including the space not in > > > use? It would be great if there was some kind of optimization that could > > > move the empty space towards the end. It would probably be an expensive > > > operation, but it may be very helpfull on databases with a big turnaround. > > > > The only difference between doing that and doing a VACUUM FULL would be that > > the disk usage would remain the same. > > There is one other extremely important difference. VACUUM FULL locks the > table/database. But to move around records, you *would* have to lock the table. This could be an incorrect assumption, but I believe that you would need to aquire an AccessExclusiveLock to rearrange the contents of the table, and that's the same lock aquired by VACUUM FULL. To put it another way, when you delete (or update) the first record in a particular table, to move that record to the end would require moving *all* the records up by one. This would destroy the existing MVCC system. You would essentially be VACUUM FULLing every time you did a DELETE or UPDATE. Greg
On Fri, Mar 08, 2002 at 03:46:11PM -0500, Gregory Wood wrote: > > > > Do sequential scans go over the entire space, including the space not in > > > > use? It would be great if there was some kind of optimization that could > > > > move the empty space towards the end. It would probably be an expensive > > > > operation, but it may be very helpfull on databases with a big turnaround. > > > > > > The only difference between doing that and doing a VACUUM FULL would be that > > > the disk usage would remain the same. > > > > There is one other extremely important difference. VACUUM FULL locks the > > table/database. > > But to move around records, you *would* have to lock the table. This could > be an incorrect assumption, but I believe that you would need to aquire an > AccessExclusiveLock to rearrange the contents of the table, and that's the > same lock aquired by VACUUM FULL. Surely you only need to lock the actual pages being changed. Actually, you have this tiny little problem with sequential and index scan currently in progress. > To put it another way, when you delete (or update) the first record in a > particular table, to move that record to the end would require moving *all* > the records up by one. This would destroy the existing MVCC system. You > would essentially be VACUUM FULLing every time you did a DELETE or UPDATE. Wouldn't you only need to move the last record to be the first one. Destroys order, but does pack the database. Actually, it seems to me you could get this to play with MVCC by treating the move as a UPDATE that does nothing. Copy the tuple from the end to the beginning, mark the end one as deleted and the beginning one as new. Voila, tuple moved and everything will eventually use the new one and ignore the old one. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > If the company that invents a cure for AIDS is expected to make their > money back in 17 years, why can't we ask the same of the company that > markets big-titted lip-syncing chicks and goddamn cartoon mice?
Martijn van Oosterhout <kleptog@svana.org> writes:
> Actually, it seems to me you could get this to play with MVCC by treating
> the move as a UPDATE that does nothing. Copy the tuple from the end to the
> beginning, mark the end one as deleted and the beginning one as new. Voila,
> tuple moved and everything will eventually use the new one and ignore the
> old one.
... except for scans in progress ...
VACUUM FULL is indeed like an UPDATE as far as crash recovery goes:
either the original tuple or the moved one is good, never both. The
problem is to ensure that concurrent scans will see the tuple correctly.
regards, tom lane