Hi All ,

Can you please help me out with the following questions .

Our application is running on Postgres 7.4.X . I agree that this is a very
old version of Postgres and we should have upgraded . The issue that we
faced is that

1 . There was a system crash due to a hardware failure .

2 . When the system came up , we tried to insert a few records into the
database . However at this point in time we saw that Postgres was taking a
lot of CPU & memory .

Around 42% CPU consumption . This was a cause of concern .

3 . We re-indexed the database and it helped reduce the cpu & memory
consumption .

My question is

A ) Isn’t Postgres database resilient enough to handle hardware system
failure ? or it sometime results in a corrupt index for its tables ? I read
on the Postgres site that hardware failure can cause corrupt indexes .
Besides this are there any other scenario which may result in such
corruption .

B) If there has been improvement / enhancements done by Postgres regarding
the way it handles corrupt indexes can you please pass me more information
about the bug Id or some documentation on it ? Our application does not do
any REINDEXING . I am in a dilemma if we should seriously incorporate it in
our application .

I ideally want to push to a higher version of Postgres . If I can prove that
there will be significant performance benefits and that crashes won’t occur
then I will be able to present a strong case .

Since my question is related to Performance & Data corruption i saw on the
Postgres site that i should provide the following information

Addition Info :

CPU manufacturer and model : Intel's Itanium Processor

Do you use a RAID controller? yes

PCIe SAS SmartArray P410i RAID Controller

PCIe SAS SmartArray P411 RAID Controller

Is is Write back caching enabled ?

Total Cache Size (MB)............... 144
Read Cache........................ N/A

Write Cache....................... N/A

No of disks : 4

Have you *ever* set fsync=off in the postgresql config file?

#fsync = true # turns forced synchronization on or off

I never changed it .

Have you had any unexpected power loss lately? Replaced a failed RAID disk?
Had an operating system crash? Yes system crashed had occured .

Hope this information helps .



Search Discussions

  • Craig Ringer at Aug 10, 2011 at 6:06 am

    On 4/08/2011 1:14 AM, Sumeet Jauhar wrote:

    1 . There was a system crash due to a hardware failure .
    A ) Isn’t Postgres database resilient enough to handle hardware system
    failure ? or it sometime results in a corrupt index for its tables ? I
    You should *always* be able to pull the plug out of a box running Pg at
    any point and have it come up just fine. If you can't, it's a bug. They
    do turn up, but quite rarely and usually relating to odd corner cases in
    newly introduced features.

    If you've done something like turn off fsync, of course, you've just
    told PostgreSQL "I don't care about my data, don't bother keeping it
    crash safe" and you get garbage if there's a system crash. But that's
    your choice to enable if your use case doesn't require data durability.
    You haven't done that, so this isn't the cause of your issue.

    The only other known case (in a current version) where index corruption
    is expected after a crash is if you are using hash indexes. Hash indexes
    are NOT CRASH SAFE, as per the documentation, and WILL need to be
    reindexed after a crash. Don't use them unless you really know you need
    them (you don't).

    Of course, if you're using 7.4.2 or something ancient, you're missing a
    lot of bug fixes, and some of them DID relate to data durability issues.

    Craig Ringer

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-admin @
postedAug 3, '11 at 5:14p
activeAug 10, '11 at 6:06a

2 users in discussion

Craig Ringer: 1 post Sumeet Jauhar: 1 post



site design / logo © 2023 Grokbase