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

On the machine used for this set of benchmarks, THP is set to never. I've seen this on several machines with slight variations in their configuration.

I spent days poring over best practice guides from google and others when setting these up, and I try to stay up to date with discussion on pgsql-performance for kernel and postgresql.conf.

- Don't have any spare machines currently, or time, to test on pg9.5. Going into hospital soon and then to a conference. Wanted to get this published first.
- may not be able to provide perf data just now. Lots of work, little time, bad health. Will try if i can though!

Graeme Bell

> On 8 Jul 2015, at 02:16, Andres Freund <andres(at)anarazel(dot)de> wrote:
>
> On July 8, 2015 12:27:39 AM GMT+02:00, grb(at)skogoglandskap(dot)no wrote:
>> The following bug has been logged on the website:
>>
>> Bug reference: 13493
>> Logged by: Graeme Bell
>> Email address: grb(at)skogoglandskap(dot)no
>> PostgreSQL version: 9.3.9
>> Operating system: Linux
>> Description:
>>
>> Hi,
>>
>> 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.
>>
>> I discovered this painfully during a project here where we were running
>> a
>> small 'forest simulator' in pl/pgsql on different datasets
>> simultaneously.
>>
>> To highlight the problem, I've provided a benchmark that demonstrates
>> two
>> problems (on my machines).
>>
>> 1. The first problem is scaling when you have lots of pl/pgsql code
>> running
>> and doing real work.
>>
>> 2. The second problem is scaling when you have a table column as an
>> input
>> parameter.
>>
>> The benchmark results & source code are here:
>>
>> https://github.com/gbb/ppppt
>>
>> Another set of benchmarks showing the same phenomena on PG9.3 and PG9.4
>> can
>> be found here, under 'BENCHMARKS.md'
>>
>> https://github.com/gbb/par_psql
>>
>> I would be grateful if others could run the benchmark and
>> confirm/disconfirm
>> the result.
>>
>> If confirmed, the result may be of special interest to e.g. the postgis
>> &
>> pgrouting communities.
>>
>> This result is from a 16-core machine with 128GB of memory and a lot of
>> random I/O capacity (there's no writing involved though other than a
>> bit of
>> WAL, and not much data, so this shouldn't matter).
>
> That sounds odd.. Could you provide perf profiles with this running? Also have you reproduced on 9.5?
>
> Do you have transparent huge pages disabled?
>
> ---
> Please excuse brevity and formatting - I am writing this on my mobile phone.
>
>
> --
> 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