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

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


Graeme.

> On 8 Jul 2015, at 16:22, Andres Freund <andres@anarazel.de> wrote:
>=20
> On 2015-07-08 09:56:51 -0400, Tom Lane wrote:
>> Andres Freund <andres@anarazel.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.
>>=20
>> What size of machine are you testing on?
>=20
> 2xE5520 (=3D> 2 x 4 sockets, 8 threads); numa.
>=20
> (note that I intentionally did not fix the volatility of the function)
>=20
>> 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.
>=20
>> 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.
>>=20
>> You might be seeing the same kind of effect, or something different.
>> It's hard to tell without knowing more about your machine.
>=20
> I think it's likely to be some scheduler effect. The number of cpu
> migrations between 4 and 8 is very different:
>=20
> 4:
>=20
>            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:
>=20
>           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
>=20
> There's a pretty large increase in the number of migrations between 4
> and 8, but none between 8 and 16.
>=20
> My guess is that the kernel tries to move around processes to idle =
nodes
> too aggressively.
>=20
> 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
>=20
> so at -j8 we're routinely much faster than later.
>=20
> 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...
>=20
>=20
> --=20
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs