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

Tom,

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

OK, that's reasuring. I would have liked to give the process more time, but
with users waiting ....

One thing I am puzzled by is the "D" status on the VACUUM process. That would
seem to indicate that VACUUM was waiting for some other process ... but I
can't imagine what it could be. Suggestions?

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

There are several "holding" tables which are truncated and then re-built. But
the tables that are holding up VACUUM are the permanent ones, which are
experiencing up to 900,000 updates every night.

> 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

Well, the holdup is the indexes, which are recycling about 500,000 pages and
in 7.2.4 FSM doesn't help me. Unfortunately, dropping the indexes during the
data transformation isn't really an option, because the indexes support some
of the data transform steps.

I'm wondering if I need to REINDEX more often; I think I'll try that next.

--
-Josh Berkus
Aglio Database Solutions
San Francisco