Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 3C6179FA304 for ; Fri, 15 Dec 2006 13:55:58 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 92766-08 for ; Fri, 15 Dec 2006 13:55:47 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.237]) by postgresql.org (Postfix) with ESMTP id B70259FA324 for ; Fri, 15 Dec 2006 13:55:47 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so779861wxd for ; Fri, 15 Dec 2006 09:55:47 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=kQKwk8yLCpgiS/TNdNvsEkTnmAYrvjec/ZZu7/wNn6yFpUbL1l7wMsgl+WaFdIuUYmsZa/DqHgRaWV5eBT0GjxALOQsEqYctECTV2rFdWHLgd+8oH3XsmNTqH8drNYu7vQKQSHlumm0Cgymj4uYeY01C35Vn04qu2FQT7p2cW68= Received: by 10.70.40.1 with SMTP id n1mr1542164wxn.1166205346847; Fri, 15 Dec 2006 09:55:46 -0800 (PST) Received: by 10.70.108.15 with HTTP; Fri, 15 Dec 2006 09:55:46 -0800 (PST) Message-ID: <357fa7590612150955g1b9c733duaf000c23d28aa8af@mail.gmail.com> Date: Fri, 15 Dec 2006 12:55:46 -0500 From: "Steven Flatt" To: "Tom Lane" Subject: Re: Insertion to temp table deteriorating over time Cc: pgsql-performance@postgresql.org In-Reply-To: <11651.1166202586@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_175832_4229239.1166205346776" References: <357fa7590612130844q58ef3ca5q31e164f9b006e0fc@mail.gmail.com> <9315.1166030958@sss.pgh.pa.us> <357fa7590612131517q375fc0by50cb4257bcce7a94@mail.gmail.com> <13032.1166052447@sss.pgh.pa.us> <357fa7590612141240t55e70695q770a05a68a67536c@mail.gmail.com> <25209.1166131396@sss.pgh.pa.us> <357fa7590612150821i3eca3f66ge2ced1ee20fa2dac@mail.gmail.com> <11651.1166202586@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/423 X-Sequence-Number: 22479 ------=_Part_175832_4229239.1166205346776 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Our application is such that there is a great deal of activity at the beginning of the hour and minimal activity near the end of the hour. Those 3 vacuums were done at (approximately) 30 minutes past, 40 minutes past, and 50 minutes past the hour, during low activity. Vacuums of pg_class look like they're being done on the hour. So it's not surprising that the first vacuum found a lot of dead rows while the latter two found very few. In fact, I just did another vacuum (about 30 minutes past the hour again) and got: INFO: "pg_class": found 5490 removable, 3263 nonremovable row versions in 171 pages DETAIL: 0 dead row versions cannot be removed yet. ... and clearly a vacuum was done under an hour ago. The truncate and re-fill process is done once per hour, at the end of the high-load cycle, so I doubt that's even a big contributor to the number of removable rows in pg_class. For this particular setup, we expect high load for 10-15 minutes at the beginning of the hour, which is the case when a new connection is initialized. After a day or so (as is happening right now), the high-load period spills into the second half of the hour. Within 3-4 days, we start spilling into the next hour and, as you can imagine, everything gets behind and we spiral down from there. For now, our workaround is to manually kill the connection every few days, but I would like a better solution than setting up a cron job to do this! Thanks again, Steve On 12/15/06, Tom Lane wrote: > > Hm, look at the numbers of rows removed: > > > INFO: "pg_class": found 5680 removable, 3263 nonremovable row versions > in > > 625 pages > > DETAIL: 0 dead row versions cannot be removed yet. > > > INFO: "pg_class": found 24 removable, 3263 nonremovable row versions in > 625 > > pages > > DETAIL: 0 dead row versions cannot be removed yet. > > > INFO: "pg_class": found 0 removable, 3263 nonremovable row versions in > 625 > > pages > > DETAIL: 0 dead row versions cannot be removed yet. > > The lack of unremovable dead rows is good, but why were there so many > dead rows the first time? You didn't say what the cycle time is on your > truncate-and-refill process, but the last two suggest that the average > rate of accumulation of dead pg_class rows is only a couple per minute, > in which case it's been a lot longer than an hour since the previous > VACUUM of pg_class. I'm back to suspecting that you don't vacuum > pg_class regularly. You mentioned having an hourly cron job to fire off > vacuums ... are you sure it's run as a database superuser? > > regards, tom lane > ------=_Part_175832_4229239.1166205346776 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Our application is such that there is a great deal of activity at the beginning of the hour and minimal activity near the end of the hour.  Those 3 vacuums were done at (approximately) 30 minutes past, 40 minutes past, and 50 minutes past the hour, during low activity.  Vacuums of pg_class look like they're being done on the hour.  So it's not surprising that the first vacuum found a lot of dead rows while the latter two found very few.
 
In fact, I just did another vacuum (about 30 minutes past the hour again) and got:
 
INFO:  "pg_class": found 5490 removable, 3263 nonremovable row versions in 171 pages
DETAIL:  0 dead row versions cannot be removed yet.
 
... and clearly a vacuum was done under an hour ago.
 
The truncate and re-fill process is done once per hour, at the end of the high-load cycle, so I doubt that's even a big contributor to the number of removable rows in pg_class.
 
For this particular setup, we expect high load for 10-15 minutes at the beginning of the hour, which is the case when a new connection is initialized.  After a day or so (as is happening right now), the high-load period spills into the second half of the hour.  Within 3-4 days, we start spilling into the next hour and, as you can imagine, everything gets behind and we spiral down from there.  For now, our workaround is to manually kill the connection every few days, but I would like a better solution than setting up a cron job to do this!
 
Thanks again,
Steve
 
 
On 12/15/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hm, look at the numbers of rows removed:

> INFO:  "pg_class": found 5680 removable, 3263 nonremovable row versions in
> 625 pages
> DETAIL:  0 dead row versions cannot be removed yet.

> INFO:  "pg_class": found 24 removable, 3263 nonremovable row versions in 625
> pages
> DETAIL:  0 dead row versions cannot be removed yet.

> INFO:  "pg_class": found 0 removable, 3263 nonremovable row versions in 625
> pages
> DETAIL:  0 dead row versions cannot be removed yet.

The lack of unremovable dead rows is good, but why were there so many
dead rows the first time?  You didn't say what the cycle time is on your
truncate-and-refill process, but the last two suggest that the average
rate of accumulation of dead pg_class rows is only a couple per minute,
in which case it's been a lot longer than an hour since the previous
VACUUM of pg_class.  I'm back to suspecting that you don't vacuum
pg_class regularly.  You mentioned having an hourly cron job to fire off
vacuums ... are you sure it's run as a database superuser?

                       regards, tom lane

------=_Part_175832_4229239.1166205346776--