I don't have control(or easy control) over unit tests creating/deleting databases since Im using the django framework for this job. Createdb takes 12secs on my system(9.10 pg8.4 and ext4) which is impossibly slow for running 200unittests. Fsync got it to .2secs or so which is blazing but also the speed I expected being used to 8.3 and xfs. This dev box is my laptop and the data is litterally unimportant and doesn't exist longer than 20sec but Im all about good practices. Will definately try synchronous commit tonight once Im done working for the day. I've got some massive copying todo later though so this will probably help in the future as well.
On Fri, Dec 11, 2009 at 5:39 PM, Greg Smith <greg@xxxxxxxxxxxxxxx> wrote:
Scott Mead wrote:Right, but the flip side here is that often the production server will have hardware such as a caching RAID card that vastly improves performance in this area. There's some room to cheat in order to accelerate the dev systems lack of such things, while still not giving a completely unrealistic view of performance.
The other common issue is that developers running with something like 'fsync=off' means that they have completely unrealistic expectations of the performance surrounding something.
As far as I'm concerned, using "fsync=off" is almost never excusable if you're running 8.3 or later where "synchronous_commit=off" is a possibility. If you use that, it will usually improve the worst part of commit issues substantially. And it happens in a way that's actually quite similar to how a caching write production server will run: small writes happen instantly, but eventually bigger ones will end up bottlenecked at the disks anyway.
It would improve the average safety of our community members if anytime someone suggests "fsync=off", we strongly suggest "synchronous_commit=off" and potentially tuning its interval instead as a middle ground, while still helping people who need to speed their systems up. Saying "never turn fsync off" without suggesting this alternative is counter-productive. If you're in the sort of position where fsync is killing your performance you'll do anything to speed things up (I've seen a 100:1 speed improvement) no matter how risky. I've ran a production system of 8.2 with fsync off, a TB of data, and no safety net if a crash introduced corruption beyond a ZFS snapshot. It wasn't fun, but it was the only possibility to get bulk loading (there was an ETL step in the middle after COPY) to happen fast enough. Using async commit instead is a much better approach now that it's available.
--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg@xxxxxxxxxxxxxxx www.2ndQuadrant.com
