Hi Greg, thanks for the reply! On Tue, Dec 15, 2009 at 10:52 PM, Greg Smith <g...@2ndquadrant.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.
