Message-ID: <45A6B197.3010301@myemma.com>
Date: Thu, 11 Jan 2007 15:52:23 -0600
From: Kim <kim@myemma.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: unusual performance for vac following 8.2 upgrade
References: <45A671CD.1050706@myemma.com> <27939.1168539652@sss.pgh.pa.us>
	<45A68C74.4080405@myemma.com> <28949.1168544710@sss.pgh.pa.us>
In-Reply-To: <28949.1168544710@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------090300040307080907010600"

This is a multi-part message in MIME format.
--------------090300040307080907010600
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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

--------------090300040307080907010600
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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 :) <br>
<br>
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...<br>
<br>
Thanks so much!<br>
<br>
Kim<br>
<br>
<br>
Tom Lane wrote:
<blockquote cite="mid28949.1168544710@sss.pgh.pa.us" type="cite">
  <pre wrap="">Kim <a class="moz-txt-link-rfc2396E" href="mailto:kim@myemma.com">&lt;kim@myemma.com&gt;</a> writes:
  </pre>
  <blockquote type="cite">
    <pre wrap="">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.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
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

  </pre>
</blockquote>
</body>
</html>

--------------090300040307080907010600--