Re: Possible causes for database corruption and solutions

Is changing the OS/X wal_sync_method default something we should
consider?

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

Greg Smith wrote:
> Michael Clark wrote:
> > 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 surprised recently to discover the default wasn't
> fsync_writethrough on that platform, because it probably should be.
> There is no other safe mode to run PostgreSQL in OS X with. If you
> don't invoke the write-through cache flushing code, you can expect
> databases to get regularly corrupted if people do things like lose power
> in the middle of writing something, exactly as you're seeing.
>
> > 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.
>
> For the restore case, you might get a good sized boost in performance
> without introducing a risk of corruption by turning off the
> synchronous_commit parameter. That will put you in a position where you
> can have a committed transaction not actually be on disk if there's a
> crash or sudden power outage, but you won't get an actual corruption in
> that case. So fsync_writethough plus synchronous_commit=off should be
> no less safe than what you've got now, but probably not as fast as what
> you're used to. As already pointed out, there is a trade-off here you
> can't bargain with: you can either have your data completely safe, or
> you can execute quickly, but you can't do both. Robust data integrity
> slows things down and there's little you can do about it without buying
> hardware targeted to improve on that.
>
> The database creation issue just came up on one of the lists here the
> other day as being particularly slow in the situation you're in, and
> that parameter change doesn't help there. There's been some design
> change suggestions around that to improve the situation, but you're not
> likely to see those in the server code for a year or more.
>
> > I should note here that we have not tuned PG at all.
> You could probably see a good sized performance increase just from
> increasing checkpoint_segments a bit from its default (3). Since it
> sounds like you're trying to keep your product's disk space footprint
> under control, increasing that to around 10 would probably as high as
> you want to go. You can't really increase shared_buffers a lot on your
> platform lest your users get stuck with weird problems where the server
> won't start, from what I hear OS X is fairly hostile to the kernel
> adjustments you need to do in order to support that.
>
> There's a general intro to things you might tune in the postgresql.conf
> at http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
>
> None of those are going to help you out with slow database creation, you
> might be able to pull down the restore times by tweaking some of the
> parameters there upwards. A large number of the tunables recommend to
> tweak there mainly impact query execution time.
>
> --
> Greg Smith 2ndQuadrant Baltimore, MD
> PostgreSQL Training, Services and Support
> greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +