Re: improving concurrent transactin commit rate

Sam Mason <sam(at)samason(dot)me(dot)uk> writes:

>> 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.

Well 2MB/s sounds about right actually:

You have: 8kB / ( 1|7200|2min)
You want: MB/s
* 1.92
/ 0.52083333

Heikki looked at this a while back and we concluded that the existing
algorithm will only get 1/2 the optimal rate unless you have twice as many
sessions as you ought to need to saturate the log i/o.

What happens is that the first backend comes along, finds nobody else waiting
and does an fsync for its own work. While that fsync is happening the rest of
the crowd -- N-1 backends -- comes along and blocks waiting on the lock. The
first backend to get the lock fsyncs the whole N-1 transactions. When it's
done though the whole crowd finds the log already syncs and goes back to work.
The first transaction to commit again finds nobody waiting and syncs alone
again. rinse lather repeat.

But that only kicks in if you don't have enough sessions running enough
transactions.

--
greg