Thread: VACUUM locking for 21 hours and counting

VACUUM locking for 21 hours and counting

From
Halford Dace
Date:
Hi all,

context:

PostgreSQL 7.0.3
Database containing one table with ca. 500,000 records.  It's vacuumed
nightly, but normally doesn't change much.  The table is, arguably,
overindexed, but it took a lot to get acceptable performance on the
queries which it runs.

I delete 120,000 records from the big table.

I run VACUUM.

21 hours later, I'm still waiting for it to finish.

What on earth can I do?

Oh yes, I _did_ run a backup just before doing the DELETE, but would
really rather not have to restore that right now.

Thanks,

Hal



Re: VACUUM locking for 21 hours and counting

From
Halford Dace
Date:
>
> I run VACUUM.
>
> 21 hours later, I'm still waiting for it to finish.
>

For what it's worth, I sorted this out.  Here's the recipe for anyone
stuck in a similar situation.  (I'm not sure if it's orthodox or safe, but
it worked today)

1. Stop the postmaster with pg_ctl
2. Send a TERM signal to the nasty postgres process which has been eating
95-99% of one of your CPUs for all the past day.
3. Send TERM signals to any others which suddenly start processing because
they were waiting on locks.

4. Start up as normal with pg_ctl.
5. DROP all teh indices on the table in question
6. VACUUM again - it just takes a couple of minutes.

Am currently busy rebuilding the indices.

Hal



Re: VACUUM locking for 21 hours and counting

From
Tom Lane
Date:
Halford Dace <hal@stowe.co.za> writes:
> PostgreSQL 7.0.3

I believe I see your problem ;-)

Try updating to 7.2 or later.  There are many pressing reasons besides
performance why you should not be running anything older than 7.2.4.
I was just helping someone last night whose 7.1 database had gone south
due to XID wraparound :-( ... so will yours, eventually.

            regards, tom lane