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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
