On Jun 19, 2007, at 7:26 PM, Tom Lane wrote:
Kurt Overberg <kurt@xxxxxxxxxxxxxxxxx> 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
