From: Jean-David Beyer <jeandavid8@verizon.net>
To: pgsql-performance@postgresql.org
Subject: Re: Curious about dead rows.
Date: Wed, 14 Nov 2007 22:26:02 -0500
Message-ID: <473BBC4A.20304@verizon.net> (raw)
In-Reply-To: <16999.1195080384@sss.pgh.pa.us>
References: <4735DBDB.5050208@verizon.net>
<16627.1194714491@sss.pgh.pa.us>
<4735FA9F.1080805@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>
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,
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
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: jeandavid8@verizon.net
Subject: Re: Curious about dead rows.
In-Reply-To: <473BBC4A.20304@verizon.net>
* 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