Stopping vacuum

[Werner Bohl]

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,

[CHRIS HOOVER]

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+***@postgresql.org.comp
Reply-To: ***@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 <***@deepdata.com>
Infutor de Costa Rica


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Lee Wu]

To speed up, consider to increase vacuum_mem if you have free RAM.

-----Original Message-----
From: pgsql-admin-***@postgresql.org
[mailto:pgsql-admin-***@postgresql.org] On Behalf Of CHRIS HOOVER
Sent: Tuesday, June 29, 2004 10:30 AM
To: ***@deepdata.com; pgsql-***@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+***@postgresql.org.comp
Reply-To: ***@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 <***@deepdata.com>
Infutor de Costa Rica


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Iain]

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" <***@deepdata.com>
To: "[ADMIN]" <pgsql-***@postgresql.org>
Sent: Wednesday, June 30, 2004 2:09 AM
Subject: [ADMIN] Stopping vacuum

Post by Werner Bohl
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,
--
Infutor de Costa Rica
---------------------------(end of broadcast)---------------------------

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Post by Werner Bohl
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings