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

> On 8 Jul 2015, at 03:13, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> grb(at)skogoglandskap(dot)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.
>
>> The benchmark results & source code are here:
>> https://github.com/gbb/ppppt
>
> First off, thanks for providing a concrete test case! It's always a lot
> easier to investigate when a problem can be reproduced locally.
>
> Having said that ...
>
> 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.

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

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.

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

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