Re: improving concurrent transactin commit rate

[ 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/