Re: Possible causes for database corruption and solutions

Hi Greg, thanks for the reply!

On Tue, Dec 15, 2009 at 10:52 PM, Greg Smith <greg(at)2ndquadrant(dot)com> wrote:

> Michael Clark wrote:
>
>>
>> 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.
>
>
That sounds like an interesting setting, I will look into that further,
thanks!

> 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.
>
>
That is a lot less of a problem, for us anyways. Faster (and safe) is
always better though. The real concern is explaining why a 2 second restore
now takes almost 4 minutes!
Not that there is anything that can be done (except for some tuning), but
that is not really an issue for here.

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

Thanks for those tips as well. Much appreciated,

Michael.