Re: Vacuum going -D; crash or just impatience?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Vacuum going -D; crash or just impatience?
Date: 2003-07-16 17:53:11
Message-ID: 23721.1058377991@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Does this sound like a crash during VACUUM, or just like it needed more time?

I think it just needed more time. VACUUM goes to great lengths to be
crash-safe. I doubt that a "fast stop" could have left the database
in a corrupted state.

It is entirely likely that an interrupted VACUUM FULL would leave the
next VACUUM FULL with more, not less, work to do --- all of the tuples
the first one tried to move before being killed would now be dead and
need to be cleaned up.

> I've a 7.2.4 report-generation database that has been growing for some time,
> resuting in the nightly VACUUM FULL ANALYZE taking longer and longer. (most
> of the data is copied nightly from other systems, so use of FSM is not very
> effective).

Are you saying that you delete most or all of the rows, then vacuum?
You might consider TRUNCATE if you delete all the rows, or CLUSTER
if you delete most, as a substitute for VACUUM FULL. (You'd still want
to run ANALYZE, after you load fresh data.) VACUUM FULL is really
designed for the case where there are not a huge number of dead rows
--- it gets awfully slow if it has to move lots of data.

Also, I think you have probably not given the FSM enough chance.
If the FSM settings are adequate then it should work fine to do
-- delete unwanted rows
VACUUM (not FULL, not ANALYZE)
-- load new rows
ANALYZE

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Josh Berkus 2003-07-16 19:09:50 Re: Vacuum going -D; crash or just impatience?
Previous Message Tom Lane 2003-07-16 17:33:58 Re: [GENERAL] INSTEAD rule bug?