Re: Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?

From: Marc Mamin <M(dot)Mamin(at)intershop(dot)de>
To: "Graeme B(dot) Bell" <graeme(dot)bell(at)nibio(dot)no>, postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?
Date: 2015-07-03 16:15:44
Message-ID: B6F6FD62F2624C4C9916AC0175D56D8828BECB51@jenmbs01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance


>
>Hi everyone,
>
>I've written a new open source tool for easily parallelising SQL scripts in postgres. [obligatory plug: https://github.com/gbb/par_psql ]
>
>Using it, I'm seeing a problem that I've also seen in other postgres projects involving high degrees of parallelisation in the last 12 months.
>
>Basically:
>
>- I have machines here with up to 16 CPU cores and 128GB memory, very fast SSDs and controller etc, carefully configured kernel/postgresql.conf for high performance.
>
>- Ordinary queries parallelise nearly perfectly (e.g. SELECT some_stuff ...), e.g. almost up to 16x performance improvement.
>
>- Non-DB stuff like GDAL, python etc. parallelise nearly perfectly.
>
>- HOWEVER calls to CPU-intensive user-defined pl/pgsql functions (e.g. SELECT myfunction(some_stuff)) do not parallelise well, even when they are independently defined functions, or accessing tables in a read-only way. They hit a limit of 2.5x performance improvement relative to single-CPU performance (pg9.4) and merely 2x performance (pg9.3) regardless of how many CPU cores I throw at them. This is about 6 times slower than I'm expecting.
>
>
>I can't see what would be locking. It seems like it's the pl/pgsql environment itself that is somehow locking or incurring some huge frictional costs. Whether I use independently defined functions, independent source tables, independent output tables, makes no difference whatsoever, so it doesn't feel 'lock-related'. It also doesn't seem to be WAL/synchronisation related, as the machines I'm using can hit absurdly high pgbench rates, and I'm using unlogged tables for output.
>
>Take a quick peek here: https://github.com/gbb/par_psql/blob/master/BENCHMARKS.md
>
>I'm wondering what I'm missing here. Any ideas?
>
>Graeme.
>

auto explain might help giving some insight in what's going on: http://www.postgresql.org/docs/9.4/static/auto-explain.html

Regards,
Marc Mamin

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Mkrtchyan, Tigran 2015-07-05 11:10:51 9.5alpha1 vs 9.4
Previous Message Graeme B. Bell 2015-07-03 14:48:43 Hmmm... why does pl/pgsql code parallelise so badly when queries parallelise fine? Anyone else seen this?