[ I'm arbitrarily replying to Greg as his was the most verbose ] On Tue, Mar 24, 2009 at 11:23:36PM -0400, Greg Smith wrote: > On Tue, 24 Mar 2009, Sam Mason wrote: > >The conceptual idea is to have at most one outstanding flush for the > >log going through the filesystem at any one time. > > Quoting from src/backend/access/transam/xlog.c, inside XLogFlush: > > "Since fsync is usually a horribly expensive operation[...]
I think I read that but for some reason thought this was only within one backend. i.e. if several separate transactions are being committed at once then they will all be twiddling their own locks. This seems somewhat silly now and I'm not sure what I was thinking; there wouldn't be any need for locks in that case. > You can see this > most easily by doing inserts into a system that's limited by a slow fsync, > like a single disk without write cache where you're bound by RPM speed. Yes, I did a test like this and wasn't getting the scaling I was expecting--hence my post. I thought I'd need more clients to see any effect so my base line was at 10 clients. I've just redone my tests and am getting the scaling you describe (and lower down than I was expecting). http://samason.me.uk/~sam/scaling.svg Why does it top out so much though? It goes up nicely to around ten clients (I tested with 8 and 12) and then tops out and levels off. The log is chugging along at around 2MB/s which is well above where they are for a single client, but it still seems as though things could go further. Then again, I'm not sure how likely this is to occur in the real-world. > The other idea you'll already find implemented in there is controlled by > commit_delay. If there are more than commit_siblings worth of open > transactions at the point where a commit is supposed to happen, that will > pause commit_delay microseconds in hopes that other transactions will jump > onboard via the mechanism described above. In practice, it's very hard to > tune that usefully. You can use it to help bunch together commits a bit > better into bigger batches on a really busy system (where not having more > than one commit ready is unexpected), it's not much help outside of that > context. OK; that's above and beyond what I was thinking and I can see how this could have a negative impact on performance. How much does it usefully affect things on a busy system? is this mopping up the slack left over that I described above when you've got more than 10 clients? > Check out the rest of the comments in xlog.c, there's a lot in there > that's not really covered in the README. If you turn on WAL_DEBUG and > XLOG_DEBUG you can actually watch some of this happen. I found time spent > reading the source to that file and src/backend/storage/buffer/bufmgr.c to > be really well spent, some of the most interesting parts of the codebase > to understand from a low-level performance tuning perspective are in those > two. Thanks for the pointers; I'll have a read! -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers