Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 2BCD99FB1DD;
	Thu, 11 Jan 2007 19:12:57 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024)
	with ESMTP id 70905-04; Thu, 11 Jan 2007 19:12:45 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from myemma.com (db1.e2ma.net [66.179.147.163])
	by postgresql.org (Postfix) with ESMTP id 15F419FA31C;
	Thu, 11 Jan 2007 19:12:49 -0400 (AST)
Received: from localhost (localhost.localdomain [127.0.0.1])
	by myemma.com (Postfix) with ESMTP id C60CB23CE1;
	Thu, 11 Jan 2007 17:12:47 -0600 (CST)
Received: from myemma.com ([127.0.0.1])
	by localhost (velma [127.0.0.1]) (amavisd-new, port 10024) with ESMTP
	id 09323-10; Thu, 11 Jan 2007 17:12:47 -0600 (CST)
Received: from [10.0.23.48] (unknown [209.149.59.218])
	by myemma.com (Postfix) with ESMTP id 19D9E23CCD;
	Thu, 11 Jan 2007 17:12:47 -0600 (CST)
Message-ID: <45A6C46E.3020702@myemma.com>
Date: Thu, 11 Jan 2007 17:12:46 -0600
From: Kim <kim@myemma.com>
User-Agent: Mozilla Thunderbird 1.0.6 (Windows/20050716)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, pgsql-hackers@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>
	<6871.1168554415@sss.pgh.pa.us>
In-Reply-To: <6871.1168554415@sss.pgh.pa.us>
Content-Type: multipart/alternative;
	boundary="------------030503080901000104050109"
X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at myemma.com
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.418 tagged_above=0 required=5 tests=AWL, BAYES_50,
	FORGED_RCVD_HELO, HTML_MESSAGE, SPF_HELO_PASS, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200701/192
X-Sequence-Number: 22793

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

Our pgstats.stat file is 40M for 8.2, on 8.1 it was 33M. Our schema size 
hasn't grown *that* much in the two weeks since we upgraded

I'm not sure if this sheds any more light on the situation, but in 
scanning down through the process output from truss, it looks like the 
first section of output was a large chunk of reads on pgstat.stat, 
followed by a larger chunk of reads on the global directory and 
directories under base - this whole section probably went on for a good 
6-7 minutes, though I would say the reads on pgstat likely finished 
within a couple of minutes or so. Following this there was a phase were 
it did a lot of seeks and reads on files under pg_clog, and it was while 
doing this (or perhaps it had finished whatever it wanted with clogs) it 
dropped into the send()/SIGUSR1 loop that goes for another several minutes.

Kim


Tom Lane wrote:

>I wrote:
>  
>
>>(2) Reconsider whether last-vacuum-time should be sent to the collector
>>unconditionally.
>>    
>>
>
>Actually, now that I look, the collector already contains this logic:
>
>    /*
>     * Don't create either the database or table entry if it doesn't already
>     * exist.  This avoids bloating the stats with entries for stuff that is
>     * only touched by vacuum and not by live operations.
>     */
>
>and ditto for analyze messages.  So my idea that the addition of
>last-vac-time was causing an increase in the statistics file size
>compared to 8.1 seems wrong.
>
>How large is your $PGDATA/global/pgstat.stat file, anyway?
>
>			regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend
>
>  
>

--------------030503080901000104050109
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">
Our pgstats.stat file is 40M for 8.2, on 8.1 it was 33M. Our schema
size hasn't grown *that* much in the two weeks since we upgraded<br>
<br>
I'm not sure if this sheds any more light on the situation, but in
scanning down through the process output from truss, it looks like the
first section of output was a large chunk of reads on pgstat.stat,
followed by a larger chunk of reads on the global directory and
directories under base - this whole section probably went on for a good
6-7 minutes, though I would say the reads on pgstat likely finished
within a couple of minutes or so. Following this there was a phase were
it did a lot of seeks and reads on files under pg_clog, and it was
while doing this (or perhaps it had finished whatever it wanted with
clogs) it dropped into the send()/SIGUSR1 loop that goes for another
several minutes. <br>
<br>
Kim<br>
<br>
<br>
Tom Lane wrote:
<blockquote cite="mid6871.1168554415@sss.pgh.pa.us" type="cite">
  <pre wrap="">I wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">(2) Reconsider whether last-vacuum-time should be sent to the collector
unconditionally.
    </pre>
  </blockquote>
  <pre wrap=""><!---->
Actually, now that I look, the collector already contains this logic:

    /*
     * Don't create either the database or table entry if it doesn't already
     * exist.  This avoids bloating the stats with entries for stuff that is
     * only touched by vacuum and not by live operations.
     */

and ditto for analyze messages.  So my idea that the addition of
last-vac-time was causing an increase in the statistics file size
compared to 8.1 seems wrong.

How large is your $PGDATA/global/pgstat.stat file, anyway?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

--------------030503080901000104050109--