From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Brad Nicholson <bnichols@ca.afilias.info>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Cc: Russell Smith <mr-russ@pws.com.au>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jean-David Beyer <jeandavid8@verizon.net>
Subject: Re: Curious about dead rows.
Date: Fri, 16 Nov 2007 16:12:49 -0600
Message-ID: <473DC181.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <b42b73150711161401p13e93e4dn19bc8388a2da9208@mail.gmail.com>
References: <4735DBDB.5050208@verizon.net>
<b42b73150711131628q1469df3el6f557e10de07a05@mail.gmail.com>
<473A5CC0.3060003@verizon.net>
<b42b73150711131936l65b97b9ya7953383673b2710@mail.gmail.com>
<473AE63D.7090305@verizon.net>
<20071114152249.GA16041@crankycanuck.ca>
<473B2A1F.30804@verizon.net>
<473B6C9D.1020804@pws.com.au>
<16999.1195080384@sss.pgh.pa.us>
<1195228608.8966.364.camel@bnicholson-desktop>
<b42b73150711161401p13e93e4dn19bc8388a2da9208@mail.gmail.com>
>>> On Fri, Nov 16, 2007 at 4:01 PM, in message
<b42b73150711161401p13e93e4dn19bc8388a2da9208@mail.gmail.com>, "Merlin Moncure"
<mmoncure@gmail.com> wrote:
> On Nov 16, 2007 10:56 AM, Brad Nicholson <bnichols@ca.afilias.info> wrote:
>> On Wed, 2007-11-14 at 17:46 -0500, Tom Lane wrote:
>> > Russell Smith <mr-russ@pws.com.au> writes:
>> > > It is possible that analyze is not getting the number of dead rows right?
>> >
>> > Hah, I think you are on to something. ANALYZE is telling the truth
>> > about how many "dead" rows it saw, but its notion of "dead" is "not good
>> > according to SnapshotNow". Thus, rows inserted by a not-yet-committed
>> > transaction would be counted as dead. So if these are background
>> > auto-analyzes being done in parallel with inserting transactions that
>> > run for awhile, seeing a few not-yet-committed rows would be
>> > unsurprising.
>> >
>> > I wonder if that is worth fixing? I'm not especially concerned about
>> > the cosmetic aspect of it, but if we mistakenly launch an autovacuum
>> > on the strength of an inflated estimate of dead rows, that could be
>> > costly.
>>
>> Sounds to me like that could result in autovacuum kicking off while
>> doing large data loads. This sounds suspiciously like problem someone
>> on -novice was having - tripping over a windows autovac bug while doing
>> a data load
>>
>> http://archives.postgresql.org/pgsql-novice/2007-11/msg00025.php
>
> I am almost 100% I've seen this behavior in the field...
I know I've seen bulk loads go significantly faster with autovacuum
turned off. It always seemed like a bigger difference than what the
ANALYZE would cause. I bet this explains it.
-Kevin
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: Kevin.Grittner@wicourts.gov, bnichols@ca.afilias.info, mmoncure@gmail.com, mr-russ@pws.com.au, tgl@sss.pgh.pa.us, jeandavid8@verizon.net
Subject: Re: Curious about dead rows.
In-Reply-To: <473DC181.EE98.0025.0@wicourts.gov>
* 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