Possible causes for database corruption and solutions - Mailing list pgsql-general

Hello all,

Over the past 6 months or so I have posted to the list a couple times looking for information regarding recovering databases from corruption.  At the time the incidents of corruption among our users was very low, but the frequency is starting to increase, most likely due to the increased user base that has upgraded to the version of our software that uses Postgres.

I have a couple questions I am hoping to get some feedback on.

The first, likely causes of the corruption.  In response to my emails I mentioned above, I was told that generally these sort of corruptions are caused by faulty hardware/hard drives of some sort.  Which seems reasonable.  When we started to see the number of corruptions per week on the rise we did some more digging and came across some information that points directly to hard drives, specifically their write buffers.

(Specifically this page: http://www.mail-archive.com/sqlite-users@sqlite.org/msg06502.html)

The solution to the problem seemed to be to change the value for the wal_sync_method setting to fsync_writethrough from the default of fsync.

I was curious if there were perhaps any other reasons that we should look at?  Or if there may be other alternatives to changing the wal_sync_method setting.

I should note, our product runs on OS X, and I would say about 95% of the corruptions happen in a bytea column in a given table which tends to hold largish data (like email bodies which may or may not have embedded attachments).

Secondly, I ask about an alternative solution to the corruption problem because with preliminary testing we have seen a significant degradation in performance.  So far the two operations we have noted are database creation and database restores.  We haven't tried some other things that will likely suffer, such as bulk imports into the database.  But to give an idea, I am using a 590K sized dump created with the command:

pg_dump -F c DB > ./backup

I then restore the dump into a newly created DB (createdb -E UTF8 Test1) with the command:

pg_restore -F c -d Test1 pgbackup

With wal_sync_method set to fsync it takes 2 seconds.

With wal_sync_method set to fsync_writethrough it takes 3 minutes and 51 seconds.

Similarly, the createdb command:

With fsync it takes .3 seconds.

With fsync_writethrough it takes 13 seconds.

This is quite worrying from a performance point of view, especially given that many databases out there are in the gigabytes.  (Our own database is 5.6GB)

I should note here that we have not tuned PG at all.

The only things we have changed are the max connections (set to 200), shared_buffers was adjusted to 4800kB and max_fsm_pages is set to 20000.

I guess that is pretty laughable, but even with these settings and the rest at default Postgres was performing better than our previous database engine, and so tuning dropped down the priority list quite a bit.

I know this is a bit long winded, but I hope that someone can shed some light for us.  

I am hoping there may be an alternative to fsync_writethrough and/or we are barking up the wrong tree w.r.t. the cause of the corruptions.

Thanks very much in advance,

Michael.