Re: Maintenance question / DB size anomaly...

On Jun 19, 2007, at 7:26 PM, Tom Lane wrote:

> Kurt Overberg <kurt(at)hotdogrecords(dot)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?
>

Unlikely- the database has been stopped and restarted, which I think
closes
out transactions? Or could that cause the problems?

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

Yep, this 8.0.4. It has been running for over a year, fairly heavy
updates, so
I would guess its possible.

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

Okay, I've grabbed pg_filedump and got it running on the appropriate
server.
I really have No Idea how to read its output though. Where does the
ctid from sl_log_1
appear in the following listing?

Block 0 ********************************************************
<Header> -----
Block Offset: 0x00000000 Offsets: Lower 20 (0x0014)
Block: Size 8192 Version 2 Upper 8176 (0x1ff0)
LSN: logid 949 recoff 0xae63b06c Special 8176 (0x1ff0)
Items: 0 Free Space: 8156
Length (including item array): 24

BTree Meta Data: Magic (0x00053162) Version (2)
Root: Block (1174413) Level (3)
FastRoot: Block (4622) Level (1)

<Data> ------
Empty block - no items listed

<Special Section> -----
BTree Index Section:
Flags: 0x0008 (META)
Blocks: Previous (0) Next (0) Level (0)

.../this was taken from the first page file (955960160.0 I guess you
could
call it). Does this look interesting to you, Tom?

FWIW- this IS on my master DB. I've been slowly preparing an upgrade
to 8.2, I guess
I'd better get that inta gear, hmmm? :-(

/kurt

> regards, tom lane