Thread: VACUUM locking for 21 hours and counting

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

>
> 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

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