Re: repeatable system index corruption on 7.4.2 (SOLVED)

Joe Conway wrote:
> Simon Riggs wrote:
>>> Joe Conway writes
>>> I'm seeing the following errors after a few hours of fairly aggressive
>>> bulk load of a database running on Postgres 7.4.2:
>>
>>> When I say aggressive, I mean up to 6 simultaneous COPY processes. It is
>>> different from the issue Tom solved the other day in that we don't get
>>> SIGABORT, just corrupt index pages.
>>
>> OK, problem accepted, but why would you run 6 simultaneous COPYs? 
>> Presumably
>> on > 1 CPU? Sounds like you're hitting the right edge of the index really
>> hard (as well as finding a hole in the logic).
> 
> This is fairly high end hardware -- 4 hyperthreaded CPUs (hence 8 CPUs 
> from the OS perspective), 8GB RAM.
> 
> But in any case, since last report we've reproduced the problem with a 
> single COPY at a time.

I just want to close the loop on this thread. In summary, the problem 
turned out to be related to the logical volume, and NOT Postgres. For 
those interested in the gory detail, read on -- others may safely move 
on ;-)

Joe

----------------------
Here's what we had, in terms of storage layout (i.e. the layout 
susceptible to Postgres system catalog corruption). Note that I'm 
neither the unix admin nor the storage expert -- we had our own unix 
admin, a storage expert from the local VAR, and a storage expert from 
the SAN vendor involved. They decided how to lay this all out -- I'll do 
my best to be accurate in my description.

[-------------     jfs filesystem   -------------]
[-------------     logical volume   -------------]
[-------------      volume group    -------------]
+------+------+------+------+------+------+------+
| LUN1 | LUN2 | LUN3 | LUN4 | LUN5 | LUN6 | LUN7 |
+______+______+______+______+______+______+______+

LUN[1-7] are actually each comprised of 14 x 73GB x 15K rpm SCSI drives, 
configured (I think) in a RAID 5 array, totaling just under 1TB of 
useable space. The SAN presents each of these arrays, via fibrechannel, 
to the OS as a single, large SCSI LUN. The LUNs are individually 
partitioned using fdisk, a single primary partition on each, and in this 
case the partition was offset 128MB to allow for stripe alignment (more 
on stripes later).

The volume group creates a block device. We used 128MB physical extent 
size to allow for up to an 8TB logical volume.

The logical volume is then created on top of the volume group. We 
created a single, 6.4TB logical volume using 128MB stripes across the 
LUNs in the volume group.

Finally the filesytem is laid on top. We started out with jfs based on 
vendor recommendations (I believe). I'll represent this topology a bit 
more compactly as (SAN-SCSI->SLV->jfs), where SLV stands for striped 
logical volume.

----------------------

In the above configuration we consistently got data corruption (as 
evidenced by bad system indexes) regardless of the Postgres 
configuration, and with 1 or 6 parallel data loads. We then changed the 
filesystem to xfs. With an xfs filesystem, we got substantially farther 
into our data load, but corruption did eventually occur.

After a variety of successful data loads (i.e. no corruption) on other 
volumes, with different topologies, we decided that the problem was 
related to either the logical volume level, or the SAN hardware itself.

So we deleted the logical volume and the volume group. Then we 
re-partitioned the LUNs -- this time without the offset for stripe 
alignment. The volume group was rebuilt using 128MB extents again, but 
the logical volume was build using concatenated, instead of striped, 
LUNs. Finally we formatted with xfs. That is, (SAN-SCSI->CLV->xfs), 
where CLV is concatenated logical volume.

(SAN-SCSI->CLV->xfs) worked with no data corruption. We're not entirely 
sure why the original configuration was a problem, but the SAN vendor 
has agreed to try to reproduce this scenario in their lab.

----------------------

Finally, some comparitive times on the various volume types that we 
tested while troubleshooting:

fail == system catalog data corruption
pass == no evident corruption

data1 -> (SAN-SCSI->SLV->jfs)  -> fail
data1 -> (SAN-SCSI->SLV->xfs)  -> fail
data3 -> (NFS-mounted-NAS)     -> pass (122 minutes)
data2 -> (SAN-IDE->CLV->jfs)   -> pass (103 minutes)
data1 -> (SAN-SCSI->CLV->xfs)  -> pass (94 minutes)
data1 -> (SAN-SCSI->CLV->xfs)  -> pass (93 minutes)

Times listed are the total clock time to complete the entire data load, 
using 6 parallel processes doing bulk COPY.

Hope someone finds this useful.

Joe