Re: 8.4.1 ubuntu karmic slow createdb

Thanks all this has been a good help.
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(at)2ndquadrant(dot)com> wrote:

> Scott Mead wrote:
>
>> 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.
>>
> 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.
>
> 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(at)2ndQuadrant(dot)com www.2ndQuadrant.com
>
>