For 8.1, we did have stats_block_level and stats_row_level on, so thats
not it either :-/ However, I did go on to an alternate database of ours
on the same machine, using the same install, same postmaster - that
holds primarily static relations, and not many of those (16 relations
total). The response of running a vac for a 1.3k static table was quick
(6 seconds - but it still did not set the last_vacuum field). Not sure
why we weren't seeing more probs with this on 8.1 for the full db, but
from the looks of things I think your theory on the primary problem with
our vacs is solid. I'm hoping we can fire up our old 8.1 dataset and run
some tests on there to confirm/reject the idea that it was doing any
better, but that will require quieter times on the machine than we've
got right now :)
We are going to try and upgrade to 8.2.1 as soon as we can, and if we
continue to see some of the other problems I mentioned as side-notes,
we'll build some information on those and pass it along...
Thanks so much!
Kim
Tom Lane wrote:
Kim <[EMAIL PROTECTED]> writes:
We were running on 8.1.1 previous to upgrading to 8.2, and yes, we
definitely have a heafty pg_class. The inheritance model is heavily used
in our schema (the results of the group by you wanted to see are down
below). However, no significant problems were seen with vacs while we
were on 8.1.
Odd, because the 8.1 code looks about the same, and it is perfectly
obvious in hindsight that its runtime is about O(N^2) in the number of
relations :-(. At least that'd be the case if the stats collector
output were fully populated. Did you have either stats_block_level or
stats_row_level turned on in 8.1? If not, maybe the reason for the
change is that in 8.2, that table *will* be pretty fully populated,
because now it's got a last-vacuum-time entry that gets made even if the
stats are otherwise turned off. Perhaps making that non-disablable
wasn't such a hot idea :-(.
What I think we need to do about this is
(1) fix pgstat_vacuum_tabstats to have non-O(N^2) behavior; I'm thinking
of using a hash table for the OIDs instead of a linear list. Should be
a pretty small change; I'll work on it today.
(2) Reconsider whether last-vacuum-time should be sent to the collector
unconditionally.
Comments from hackers?
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
