Thread: safety of vacuum verbose analyze on active tables
i have always been cautious, and have tried to vacuum my tables when there was little or no activity. i now have a situation where that may not be possible any more. what is the relative safety of doing a vacuum verbose analyze on a 24Gb table while there are selects and updates/inserts happening on it? would it be safer to disable the update/insert processes? -- [ Jim Mercer jim@reptiles.org +1 416 506-0654 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
> what is the relative safety of doing a vacuum verbose analyze on a 24Gb > table while there are selects and updates/inserts happening on it? As far as I know, the table is locked completely during a Vacuum. Any transactions attempting to do inserts/updates will be paused safely. So go ahead and schedule your vacuums for whenever you need to. - Andrew
On Tue, Apr 18, 2000 at 12:58:58AM +1000, Andrew Snow wrote: > > what is the relative safety of doing a vacuum verbose analyze on a 24Gb > > table while there are selects and updates/inserts happening on it? > > As far as I know, the table is locked completely during a Vacuum. Any > transactions attempting to do inserts/updates will be paused safely. So go > ahead and schedule your vacuums for whenever you need to. ah. that sounds about right. however, my insert/update processes are autonomous, and as such, if i had a 3 hour vacuum, i might end up with quite a queue of insert/update processes. is there a way to determine if there is a lock before i start an insert/update? also, i have, as a habit, done: - use pg_dump to get a list of indexes - nuke indexes - vacuum table - recreate indexes since i have control over the various processes, i enforce a quiet period for this. i have noticed that the linear time for: nuke index ; vacuum ; recreate index is much less than for a normal "vacuum table"; -- [ Jim Mercer jim@reptiles.org +1 416 506-0654 ] [ Reptilian Research -- Longer Life through Colder Blood ] [ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]
Andrew Snow wrote: > > > what is the relative safety of doing a vacuum verbose analyze on a 24Gb > > table while there are selects and updates/inserts happening on it? > > As far as I know, the table is locked completely during a Vacuum. Any > transactions attempting to do inserts/updates will be paused safely. So go > ahead and schedule your vacuums for whenever you need to. There have been reports of problems (corruption, etc.) when trying to do this. See the archive for a discussion along these lines a few months ago. Regards, Ed Loehr
> > > what is the relative safety of doing a vacuum verbose analyze > on a 24Gb > > > table while there are selects and updates/inserts happening on it? > > > > As far as I know, the table is locked completely during a Vacuum. Any > > transactions attempting to do inserts/updates will be paused > safely. So go > > ahead and schedule your vacuums for whenever you need to. > There have been reports of problems (corruption, etc.) when trying to do > this. See the archive for a discussion along these lines a few > months ago. You've got to be joking. Is the table locking mechanism in Postgresql broken??
Jim Mercer <jim@reptiles.org> writes:
> what is the relative safety of doing a vacuum verbose analyze on a 24Gb
> table while there are selects and updates/inserts happening on it?
VACUUM obtains an exclusive lock on the table it's working on, so there
won't *be* any selects or updates happening on that table ;-). Anyone
else who wants to touch the table will be blocked until VACUUM finishes.
A disadvantage of running VACUUM concurrently with other activity is
that VACUUM cannot remove tuples that are committed dead, but were
deleted by a transaction more recent than the oldest still-active
transaction. (If that oldest transaction chooses to come look at the
table after VACUUM finishes, it should see those dead tuples as still
live, if it is running fully serializable. So VACUUM has to leave
the tuples there, just in case.) So, if you have long-running
transactions happening in parallel with VACUUM, you probably won't get
as much space reclaimed as you'd like.
We have also heard some reports suggesting there are hard-to-duplicate
bugs in the VACUUM logic that tries to deal with these not-quite-dead
tuples. Tripping over one of those is probably the major risk factor
involved in running VACUUM under full-load conditions. I do not recall
hearing any reports of actual data corruption from such a bug, but you
might see weird error messages out of VACUUM. (BTW, if you can get
a reproducible example of a problem like this, we'd definitely like
to hear about it.)
regards, tom lane
Andrew Snow wrote: > > > > > what is the relative safety of doing a vacuum verbose analyze > > on a 24Gb > > > > table while there are selects and updates/inserts happening on it? > > > > > > As far as I know, the table is locked completely during a Vacuum. Any > > > transactions attempting to do inserts/updates will be paused > > safely. So go > > > ahead and schedule your vacuums for whenever you need to. > > > There have been reports of problems (corruption, etc.) when trying to do > > this. See the archive for a discussion along these lines a few > > months ago. > > You've got to be joking. > > Is the table locking mechanism in Postgresql broken?? I have no idea (but I doubt it). I simply recall this question being asked a few months back and a couple folks said something like "Hey, we tried this and had problems." That was with 6.5.* or earlier. Maybe one of those folks can pipe up again. I couldn't find them in the archive... Regards, Ed Loehr
> Andrew Snow wrote: > > > > > > > what is the relative safety of doing a vacuum verbose analyze > > > on a 24Gb > > > > > table while there are selects and updates/inserts happening on it? > > > > > > > > As far as I know, the table is locked completely during a Vacuum. Any > > > > transactions attempting to do inserts/updates will be paused > > > safely. So go > > > > ahead and schedule your vacuums for whenever you need to. > > > > > There have been reports of problems (corruption, etc.) when trying to do > > > this. See the archive for a discussion along these lines a few > > > months ago. > > > > You've got to be joking. > > > > Is the table locking mechanism in Postgresql broken?? > > I have no idea (but I doubt it). I simply recall this question being asked > a few months back and a couple folks said something like "Hey, we tried > this and had problems." That was with 6.5.* or earlier. Maybe one of > those folks can pipe up again. I couldn't find them in the archive... > > Regards, > Ed Loehr > I've had problems with vacuum on postgres 6.5.3 The didn't occur in 6.3.2 or 7.0beta1. The problems occured after many revoke and grant statements on the database. And after and/or during that running a vacuum twice. The second would start saying something like: NOTICE: Rel pg_class: TID 294/3: InsertTransactionInProgress 20065 - can't shrink relation NOTICE: Rel pg_class: TID 294/4: InsertTransactionInProgress 20065 - can't shrink relation NOTICE: Index pg_class_relname_index: NUMBER OF INDEX' TUPLES (1615) IS NOT THE SAME AS HEAP' (1587) NOTICE: Index pg_class_oid_index: NUMBER OF INDEX' TUPLES (1615) IS NOT THE SAME AS HEAP' (1587) VACUUM But it works in 7.0 Cheers Wim.