From: | Nikhil Sontakke <nikhil(dot)sontakke(at)enterprisedb(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | index corruption in PG 8.3.13 |
Date: | 2011-03-09 12:42:22 |
Message-ID: | AANLkTi=NPR9iRMFhojKAUwHOvVk+xjxv8H53KzNvrFmG@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
I am currently looking at a physical file belonging to a corrupt
index. This is with PG 8.3.13 on an ext3 filesystem on suse linux with
a RAID1 setup. The sequence of operation seems nothing suspicious.
Create table, create a bunch of indexes on that table and do
insert/update activity. One difference here is that VACUUM FULL might
be run in between along with the usual autovacuum mechanism when the
inserts/updates are going on.
So one of the vacuum calls errored out with a corrupt (completely
zeroed out in this case) block with the usual hint to reindex.
This is a single machine setup with no slaves. Some analysis using
pg_filedump comes up with the following:
There are a total of 525 blocks (numbered 0 to 524). The root page is
block #3. The corrupt or zeroed out page is block #523.
The basic issue is that item2 in root block points to block 523 which
is completely zeroed out and this has broken the index chain
completely!
Other peculiarity in the index file is that we found a lot of zeroed
out pages. Blocks from #279 to #518 are all completely zeroed out
without any signs of even a page header. Any ideas on how we can get
so many zeroed out blocks? Apart from the extend code path, I fail to
see any other. And this is an unusually large number of zero pages...
Blocks 519 to 521 are DELETED. They do not have the LEAF flag set,
meaning they could be internal pages, but that is strange since ROOT
page is at level 1. Also importantly their next XID is set FrozenXid,
meaning VACUUM FULL was at play. Maybe due to deletes, we reduced the
hierarchy level or something?
I am attaching the data file, the binary and the 8.3 compliant
pg_filedump sources with this mail. Just copy the pg_filedump
directory into contrib and use Makefile.contrib to compile. Cannot
attach the output of running pg_filedump since my email will cross the
attachment limits. Am also attaching my dotty attempts via a png file
to visually show the scenario, hopefully that should make things
clearer.
Feedback, comments, insights appreciated. Appears to be a tough nut to
crack :( :), and although blaming the hardware for the zero pages
might come to mind, still the deletion activity done by Vacuum/Vacuum
Full seems a bit suspicious.
Regards,
Nikhils
Attachment | Content-Type | Size |
---|---|---|
index_corruption.tar.gz | application/x-gzip | 303.9 KB |
index_corruption.png | image/png | 131.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2011-03-09 12:54:32 | Re: [webmaster] Beginner question: Hacking environment? |
Previous Message | Adrian von Bidder | 2011-03-09 12:13:04 | Re: [webmaster] Beginner question: Hacking environment? |