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 <kim@myemma.com> 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