Skip site navigation (1) Skip section navigation (2)

Re: Curious about dead rows.

From: Jean-David Beyer <jeandavid8(at)verizon(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Curious about dead rows.
Date: 2007-11-15 03:26:02
Message-ID: 473BBC4A.20304@verizon.net (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Russell Smith <mr-russ(at)pws(dot)com(dot)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,

They are.

> seeing a few not-yet-committed rows would be unsurprising.

That is a very interesting possibility. I can see that it is certainly a
possible explanation, since my insert transactions take between 0.04 to 0.1
minutes (sorry, decimal stopwatch) of real time, typically putting 1700 rows
into about a half dozen tables. And the ANALYZE is whatever autovacuum
chooses to do. So if new not-yet-committed rows are considered dead, that
would be a sufficient explanation.

So I am, retroactively, unsurprised.

> 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.
> 
Well, since I was more interested in the explanation than in the fixing, in
that sense I do not care if it is fixed or not. While it may create a slight
slowdown (if it is an error), the applications run "fast enough."

I would not even get the fix until Red Hat get around to putting it in (I
run postgresql-8.1.9-1.el5 that is in their RHEL5 distribution), that
probably will not be until RHEL6 and the soonest, and I will probably skip
that one and wait until RHEL7 comes out in about 3 years.

But somewhere perhaps a reminder of this should be placed where someone like
me would find it, so we would not have to go through this again for someone
else.

-- 
  .~.  Jean-David Beyer          Registered Linux User 85642.
  /V\  PGP-Key: 9A2FC99A         Registered Machine   241939.
 /( )\ Shrewsbury, New Jersey    http://counter.li.org
 ^^-^^ 22:05:01 up 22 days, 15:23, 0 users, load average: 4.16, 4.22, 4.10

In response to

pgsql-performance by date

Next:From: Jeff TroutDate: 2007-11-15 14:41:59
Subject: Re: dell versus hp
Previous:From: Jean-David BeyerDate: 2007-11-15 03:16:45
Subject: Re: Curious about dead rows.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group