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

Stephen Tyler wrote:
> So a "typical" checkpoint is around 200K buffers (1.5GBytes, 40% of
> max), taking around 150 seconds to write (10MByte/second, 1300
> buffers/second), and around 150 seconds to sync.
Your problem may very well be plain old lack of disk I/O throughput, and
fsync forcing some caches to clear is just a symptom rather than the
root problem. 1.5GB per checkpoint spread out over ~5 minutes is still
a pretty high load of writes even for a SSD to deal with, given that
it's got to compute with foreground activity too. I try to aim for less
than 250MB of checkpoint writes per 5 minute period with regular disks,
so even with your theoretically good random I/O I think you're having
checkpoints at least twice as often as your system can tolerate them.

> Why then does it take postgresql 200seconds to sync a checkpoint (or
> DTrace report 20 to 60 second fsync calls)? The drives themselves
> have only smallish 32MB buffers. I can write a 1.5GByte file (the
> size of the average checkpoint) in only 10 seconds, if I do it from
> outside postgresql.
The information being written out to the database has a large random I/O
component to it, so you can't compare it to sequential write speed at
all. You said you can get 4K random writes/second, right? That might
only be as little as 4K IOPS * 8K block = 32MB/s worth of write speed.
At that speed, 1.5GB will take over 45 seconds, not 10.

I don't really have any great suggestions for you from here. You're
using not very well understood hardware on the 2nd worst of the popular
platforms for PostgreSQL from a performance perspective (with Windows by
far the worst), trying to push through what would be a stressful
workload even on the best of them. I'm not too familiar with this area
because recommendation #1 if I ran into this situation would be "Don't
try that on OS X with HFS+". Maybe there's some way to get more
performance out of there by tweaking the OS, I haven't had to do so
myself enough to know the details off the top of my head.

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