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

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.

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.

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.

regards, tom lane