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 I've seen in other postgres projects involving parallelisation in the last 12 months.
Basically:
- I have machines here with up to 16 CPUs and 128GB memory, very fast SSDs and controller etc, carefully configured
kernel/postgresql.conffor high performance.
- Ordinary queries parallelise nearly perfectly (e.g. SELECT some_stuff ...), e.g. almost up to 16x performance
improvement.
- Calls to CPU-intensive user-defined pl/pgsql functions (e.g. SELECT myfunction(some_stuff)) do not parallelise well,
evenwhen they are independent or accessing tables in a read-only way. They hit a limit at 2.5x performance improvement
relativeto single-CPU performance (pg9.4) and 2x performance (pg9.3). This is about 6 times slower than I'm expecting.
- Can't see what would be locking. It seems like it's the pl/pgsql environment itself that is somehow locking or
incurringsome huge frictional costs. Whether I use independently defined functions, independent source tables,
independentoutput tables, makes no difference whatsoever, so it doesn't feel 'locky'. It also doesn't seem to be
WAL/synchronisationrelated, as the machines I'm using can hit absurdly high pgbench rates, and I'm using unlogged
tables.
Curious? Take a quick peek here: https://github.com/gbb/par_psql/blob/master/BENCHMARKS.md
Wondering what I'm missing here. Any ideas?
Graeme.