Re: Possible causes for database corruption and solutions

* Michael Clark:

> 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.

Fsync and related settings only matter if the operating system (not
just the database) crashes. Does this happen frequently for you?

> 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).

That's not surprising if 95% of the data are stored that way.

> 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.

fsync_writethrough seems to be global in effect (not file specific),
so it's going to hurt if there is other I/O activity on the box.

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99