Re: Excessive (and slow) fsync() within single transaction

Stephen Tyler wrote:
> So firstly, why are there so many calls to fsync()?
Every time a transaction commits, you get a fsync to the WAL file.
Then, during the periodic database checkpoints, you get more fsync'd
writes. The latter are more likely to be your problem.

You should turn on log_checkpoint in the postgresql.conf and confirm the
slowdowns are happening around the same time as the checkpoint report
gets written to the log files. Presuming that turns out to be the case,
you'll want to follow the suggestions at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server to improve
the behavior, and you can get a lot more background about what's
happening at
http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm

We just went through one of these last week with someone else, you might
find the troubleshooting process and surrounding discussion interesting
reading:

http://archives.postgresql.org/pgsql-performance/2009-12/msg00033.php

One useful later stage in troubleshooting problems in this area is to
reduce the amount of caching done by the operating system, so that there
aren't as many blocks backed up when the fsync calls happen. I'm not
sure how to do this on OS X though.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com