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

Greg Smith wrote:
> 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.
>
What are the two best platforms for PostgreSQL in your opinion?

Bill