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, b=
ut=20
with users waiting ....

One thing I am puzzled by is the "D" status on the VACUUM process.  That wo=
uld=20
seem to indicate that VACUUM was waiting for some other process ... but I=
=20
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=20
the tables that are holding up VACUUM are the permanent ones, which are=20
experiencing up to 900,000 updates every night.=20=20

> 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 an=
d=20
in 7.2.4 FSM doesn't help me.  Unfortunately, dropping the indexes during t=
he=20
data transformation isn't really an option, because the indexes support som=
e=20
of the data transform steps.

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

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco