Re: PATCH: pgbench - merging transaction logs

On 2015-03-21 10:37:05 +0100, Fabien COELHO wrote:
>
> Hello Tomas,
> Let us take this as a worst-case figure and try some maths.
>
> If fprintf takes p = 0.025 (1/40) of the time, then with 2 threads the
> collision probability would be about 1/40 and the delayed thread would be
> waiting for half this time on average, so the performance impact due to
> fprintf locking would be negligeable (1/80 delay occured in 1/40 cases =>
> 1/3200 time added on the computed average, if I'm not mistaken).
>
> With t threads, I would guess that the collision probability in the first
> order is about 0.5 t (t-1) p, and the performance impact would be ~ (0.5 t
> (t-1) p * 0.5 p) (hmmm... this approximation, if not completely stupid, just
> holds for small p and not too large t).
>
> With your worst-case figure and some rounding, it seems to look like:
>
> #threads collision probability performance impact
> 2 1/40 1/3200
> 4 1/7 1/533
> 8 0.7 < 0.01 (about 1%)
>
> This suggest that for a pessimistic (ro load) fprintf overhead ratio there
> would be a small impact even with 8 thread doing 20000 tps each.

I think math like this mostly disregards hardware realities. You don't
actually need to have actual lock contention to notice overhead -
frequently acquiring an *uncontended* lock that resides in another
socket's cache and where the cacheline is dirty requires relatively
expensive cross cpu transfers. That's all besides the overhead of doing
a lock operation itself. A lock; xaddl;, or whatever you end up using,
has a significant cost in itself. It implies a bus lock and cache flush,
which is far from free.

Additionally we're quite possibly talking about more than 8
threads. I've frequently used pgbench with hundreds of threads; for imo
good reasons.

That all said, it's far from guaranteed that there's an actual problem
here. If done right, i.e. the expensive formatting of the string is
separated from the locked output to the kernel, it might end up being
acceptable.

I wonder how bad using unbuffered write + O_APPEND would end up being;
without a lock.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services