Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 3F9409FA433 for ; Fri, 15 Dec 2006 13:09:53 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 69967-04 for ; Fri, 15 Dec 2006 13:09:47 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id E5C589FA42F for ; Fri, 15 Dec 2006 13:09:47 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBFH9kYR011652; Fri, 15 Dec 2006 12:09:46 -0500 (EST) To: "Steven Flatt" cc: pgsql-performance@postgresql.org Subject: Re: Insertion to temp table deteriorating over time In-reply-to: <357fa7590612150821i3eca3f66ge2ced1ee20fa2dac@mail.gmail.com> 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> Comments: In-reply-to "Steven Flatt" message dated "Fri, 15 Dec 2006 11:21:35 -0500" Date: Fri, 15 Dec 2006 12:09:46 -0500 Message-ID: <11651.1166202586@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/421 X-Sequence-Number: 22477 "Steven Flatt" writes: > Here's the output of "VACUUM VERBOSE pg_class". I think it looks fine. I > even did it three times in a row, each about 10 minutes apart, just to see > what was changing: 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