Re: BUG #13493: pl/pgsql doesn't scale with cpus (PG9.3, 9.4)

By the way,

It may be worth increasing the length of the for loop in the pl/pgsql by a factor of 100-1000x and seeing if you get the same kind of behaviour. I intentionally left it very low (10000 iterations of nothing is not that much work) to ensure people would see a non-zero number there for the TPS the first time they ran the bench. However in practice the functions that have the worst behaviour tend to be longer running ones.

Graeme.

> On 8 Jul 2015, at 16:22, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> On 2015-07-08 09:56:51 -0400, Tom Lane wrote:
>> Andres Freund <andres(at)anarazel(dot)de> writes:
>>> So there's an interesting "dip" between 4 and 8 clients. A perf profile
>>> doesn't show any actual lock contention on master. Not that surprising,
>>> there shouldn't be any exclusive locks here.
>>
>> What size of machine are you testing on?
>
> 2xE5520 (=> 2 x 4 sockets, 8 threads); numa.
>
> (note that I intentionally did not fix the volatility of the function)
>
>> I ran Graeme's tests on a 2-socket, 4-core-per-socket, no-hyperthreading
>> machine, which has separate NUMA zones for the 2 sockets. What I saw
>> (after fixing the "stable" issue) was that all the 8-client and 16-client
>> cases were about 8x faster than 1-client, and 2-client was generally
>> within hailing distance of 2x faster, but 4-client was often noticeably
>> worse than the expected 4x faster.
>
>> I figured this was likely some weird NUMA effect, possibly compounded
>> by brutally stupid scheduling on the part of my kernel. But I didn't
>> have time to look closer.
>>
>> You might be seeing the same kind of effect, or something different.
>> It's hard to tell without knowing more about your machine.
>
> I think it's likely to be some scheduler effect. The number of cpu
> migrations between 4 and 8 is very different:
>
> 4:
>
> 64,599 context-switches # 0.003 M/sec (100.00%)
> 172 cpu-migrations # 0.007 K/sec (100.00%)
> 537 page-faults # 0.023 K/sec
> 8:
> 381,383 context-switches # 0.002 M/sec (100.00%)
> 1,279 cpu-migrations # 0.008 K/sec (100.00%)
> 3,869 page-faults # 0.024 K/sec
> 16:
>
> 514,426 context-switches # 0.003 M/sec (100.00%)
> 1,166 cpu-migrations # 0.007 K/sec (100.00%)
> 6,308 page-faults # 0.039 K/sec
>
> There's a pretty large increase in the number of migrations between 4
> and 8, but none between 8 and 16.
>
> My guess is that the kernel tries to move around processes to idle nodes
> too aggressively.
>
> second-by-second pgbench is quite interesting:
> progress: 1.0 s, 22915.3 tps, lat 0.346 ms stddev 0.078
> progress: 2.0 s, 15596.8 tps, lat 0.512 ms stddev 0.185
> progress: 3.0 s, 15519.2 tps, lat 0.514 ms stddev 0.499
> progress: 4.0 s, 15535.7 tps, lat 0.512 ms stddev 0.306
> progress: 5.0 s, 15494.3 tps, lat 0.515 ms stddev 0.162
>
> so at -j8 we're routinely much faster than later.
>
> Comparing perf stat pgbench -j8 -T 1 and -T 8:
> -T 1
> 46 cpu-migrations
> -T 8
> 534 cpu-migrations
> so indeed the number of migration rises noticeably after the first
> second...
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs