Thread: Stopping vacuum
Hi, We have a very big db that was not vacuumed for a long time. We started vacuum 3 days ago and it has not finished yet. There are some urgent processes that have to be run against the db. Is it safe to kill vacuum? It was started with 'vacuum full analyze' Is there anyway to speed it up at next start? TIA, -- Werner Bohl <wbohl@deepdata.com> Infutor de Costa Rica
It is safe to kill the vacuum (but not a favored thing to do). I have done it many times when the vacuums have run to long and have started to affect my online production performance. Be sure to just do a kill, as a kill -9 will cause the db to reset all connections to it. Since you have not vacuumed this db in so long, I would probably suggest vacuuming it table by table so you can have a finer level of control over the vacuum. HTH, Chris ------------------( Forwarded letter 1 follows )--------------------- Date: Tue, 29 Jun 2004 11:09:29 -0600 To: .ADMIN.[pgsql-admin]@postgresql.org.comp From: Werner.Bohl[wbohl]@deepdata.com.comp Sender: pgsql-admin-owner+m14031@postgresql.org.comp Reply-To: wbohl@deepdata.com.comp Subject: [ADMIN] Stopping vacuum Hi, We have a very big db that was not vacuumed for a long time. We started vacuum 3 days ago and it has not finished yet. There are some urgent processes that have to be run against the db. Is it safe to kill vacuum? It was started with 'vacuum full analyze' Is there anyway to speed it up at next start? TIA, -- Werner Bohl <wbohl@deepdata.com> Infutor de Costa Rica ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
To speed up, consider to increase vacuum_mem if you have free RAM. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of CHRIS HOOVER Sent: Tuesday, June 29, 2004 10:30 AM To: wbohl@deepdata.com; pgsql-admin@postgresql.org Subject: Re: [ADMIN] Stopping vacuum It is safe to kill the vacuum (but not a favored thing to do). I have done it many times when the vacuums have run to long and have started to affect my online production performance. Be sure to just do a kill, as a kill -9 will cause the db to reset all connections to it. Since you have not vacuumed this db in so long, I would probably suggest vacuuming it table by table so you can have a finer level of control over the vacuum. HTH, Chris ------------------( Forwarded letter 1 follows )--------------------- Date: Tue, 29 Jun 2004 11:09:29 -0600 To: .ADMIN.[pgsql-admin]@postgresql.org.comp From: Werner.Bohl[wbohl]@deepdata.com.comp Sender: pgsql-admin-owner+m14031@postgresql.org.comp Reply-To: wbohl@deepdata.com.comp Subject: [ADMIN] Stopping vacuum Hi, We have a very big db that was not vacuumed for a long time. We started vacuum 3 days ago and it has not finished yet. There are some urgent processes that have to be run against the db. Is it safe to kill vacuum? It was started with 'vacuum full analyze' Is there anyway to speed it up at next start? TIA, -- Werner Bohl <wbohl@deepdata.com> Infutor de Costa Rica ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
IIRC killing vacuums can cause some minor problems - i think it may have been something like index bloat. Tom said something about it once before. Otherwise, as I understand it, it's "safe" to kill vacuums. Cheers Iain ----- Original Message ----- From: "Werner Bohl" <wbohl@deepdata.com> To: "[ADMIN]" <pgsql-admin@postgresql.org> Sent: Wednesday, June 30, 2004 2:09 AM Subject: [ADMIN] Stopping vacuum > Hi, > > We have a very big db that was not vacuumed for a long time. We started > vacuum 3 days ago and it has not finished yet. There are some urgent > processes that have to be run against the db. > > Is it safe to kill vacuum? It was started with 'vacuum full analyze' > Is there anyway to speed it up at next start? > > > TIA, > > -- > Werner Bohl <wbohl@deepdata.com> > Infutor de Costa Rica > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Werner Bohl <wbohl@deepdata.com> writes:
> We have a very big db that was not vacuumed for a long time. We started
> vacuum 3 days ago and it has not finished yet. There are some urgent
> processes that have to be run against the db.
> Is it safe to kill vacuum? It was started with 'vacuum full analyze'
> Is there anyway to speed it up at next start?
It is safe in the sense that your database won't be corrupted (were it
not safe, vacuum would be too dangerous to use at all --- think of power
failures for instance).
However, whatever work vacuum has done on the current table will be lost
and need to be done over when you run it again. If the thing is stuck
on a particularly large and messy table then killing it could well be
counterproductive --- you might just be buying yourself an even longer
vacuum run next time.
I'd suggest identifying exactly which table it is currently working on
before you kill it. Then look into alternate methods of compacting that
table. It's entirely possible that CLUSTER would be a faster cleanup
method than VACUUM FULL.
regards, tom lane