From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kurt Overberg <kurt@hotdogrecords.com>
Cc: Chris Browne <cbbrowne@acm.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Maintenance question / DB size anomaly...
Date: Tue, 19 Jun 2007 19:26:10 -0400
Message-ID: <18495.1182295570@sss.pgh.pa.us> (raw)
In-Reply-To: <A0763165-9B49-42C7-BBE7-4A670D74B7EE@hotdogrecords.com>
References: <A6D35557-74A3-47CF-B426-143C8CE01A13@hotdogrecords.com>
<607iq0dlns.fsf@dba2.int.libertyrms.com>
<1FF54A1D-9085-4C9B-81F3-D860FB132D44@hotdogrecords.com>
<16441.1182288819@sss.pgh.pa.us>
<A0763165-9B49-42C7-BBE7-4A670D74B7EE@hotdogrecords.com>
Kurt Overberg <kurt@hotdogrecords.com> writes:
> That's the thing thats kinda blowing my mind here, when I look at
> that table:
> db1=# select count(*) from _my_cluster.sl_log_1 ;
> count
> -------
> 6788
> (1 row)
Well, that's real interesting. AFAICS there are only two possibilities:
1. VACUUM sees the other 300k tuples as INSERT_IN_PROGRESS; a look at
the code shows that these are counted the same as plain live tuples,
but they'd not be visible to other transactions. I wonder if you could
have any really old open transactions that might have inserted all those
tuples?
2. The other 300k tuples are committed good, but they are not seen as
valid by a normal MVCC-aware transaction, probably because of
transaction wraparound. This would require the sl_log_1 table to have
escaped vacuuming for more than 2 billion transactions, which seems a
bit improbable but maybe not impossible. (You did say you were running
PG 8.0.x, right? That's the last version without any strong defenses
against transaction wraparound...)
The way to get some facts, instead of speculating, would be to get hold
of the appropriate version of pg_filedump from
http://sources.redhat.com/rhdb/ and dump out sl_log_1 with it
(probably the -i option would be sufficient), then take a close look
at the tuples that aren't visible to other transactions. (You could
do "select ctid from sl_log_1" to determine which ones are visible.)
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, kurt@hotdogrecords.com, cbbrowne@acm.org
Subject: Re: Maintenance question / DB size anomaly...
In-Reply-To: <18495.1182295570@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