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