Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxx  www.2ndQuadrant.com


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux