From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven Flatt <steven.flatt@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Insertion to temp table deteriorating over time
Date: Fri, 15 Dec 2006 12:09:46 -0500
Message-ID: <11651.1166202586@sss.pgh.pa.us> (raw)
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>
"Steven Flatt" <steven.flatt@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, steven.flatt@gmail.com
Subject: Re: Insertion to temp table deteriorating over time
In-Reply-To: <11651.1166202586@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox