[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Curious about dead rows.
From:       "Kevin Grittner" <Kevin.Grittner () wicourts ! gov>
Date:       2007-11-16 22:12:49
Message-ID: 473DC181.EE98.0025.0 () wicourts ! gov
[Download RAW message or body]

>>> 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
 


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

[prev in list] [next in list] [prev in thread] [next in thread]