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

> On 8 Jul 2015, at 03:13, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> grb@skogoglandskap.no writes:
>> pl/pgsql doesn't scale properly on postgres 9.3/9.4 with multiple =
processors
>> beyond 2-3 processors for me, regardless of the machine I use or the
>> benchmark/project.=20
>=20
>> The benchmark results & source code are here:
>> https://github.com/gbb/ppppt
>=20
> First off, thanks for providing a concrete test case!  It's always a =
lot
> easier to investigate when a problem can be reproduced locally.
>=20
> Having said that ...
>=20
> plpgsql is really designed as a glue language for SQL queries, not for
> heavy-duty computation, so these examples aren't exactly showing it at
> its best.  It would be worth your while to consider using some other
> convenient programming language, perhaps plperl or plpython or plv8,
> if you want to do self-contained calculations on the server side.

I'm very interested in trying out plv8, but it is a problem for projects =
when you have a team of people who know plpgsql and a lot of plpgsql =
legacy code.

It would be interesting to know what happens when these functions are =
ported into those other languages.=20

> But I think that the main problem you are seeing here is from snapshot
> acquisition contention.  By default, plpgsql acquires a new snapshot
> for each statement inside a function, and that results in a lot of
> contention for the ProcArray if you're maxing out a multicore machine.
> Depending on what you're actually doing inside the function, you might
> well be able to mark it stable or even immutable, which would suppress
> the per-statement snapshot acquisitions.  On my machine (admittedly =
only
> 8 cores), the scalability problems in this example pretty much vanish
> when I attach "stable" to the function definitions.

Thanks for testing it on your own machine and looking into a possible =
cause.
Do you think that explains both problems (one occurs with table data; =
one occurs with work)?

Several of the functions that were killing me last year were dynamic SQL =
with some internal state, so declaring them harmless and predictable =
probably isn't possible. I will need to go back and check.=20

It sounds like marking up functions may help (at least as far as 8 =
cores), but there must be a lot of people out there running plpgsql they =
have ported over from oracle, (postgis? pgrouting? not sure about their =
internals) etc, and various libraries where the functions aren't marked =
up and can't easily be marked up. A broader solution in the far off =
future would be awesome, if it is ever possible. I am not in a position =
currently to provide it, I regret.=20

> There is some discussion going on about improving the scalability of
> snapshot acquisition, but nothing will happen in that line before 9.6
> at the earliest.

Thanks, this is interesting to know.

Thanks again for your time looking into this and for the =
stable/immutable tip.

Graeme.

>=20
>             regards, tom lane
>=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