Re: Utilizing multiple cores in a function call.

On Mon, 2009-06-29 at 14:42 -0400, Greg Smith wrote:

> -Write a "worker server" that you prompt to pick up work from a table and
> write its output to another that you can ask to handle part of the job.
> You might communicate with the worker using the LISTEN/NOTIFY mechanism in
> the database.
>
> -Some combination of these two techniques. One popular way to speed up
> things that are running slowly is to run some part of them in a C UDF, so
> that you could use "select my_big_computation(x,y,z)" and get faster
> execution.

The trouble here is that the backend may not like having threads
suddenly introduced into its execution environment.

If properly written, I don't really see why a C UDF that used pthreads
couldn't spawn two worker threads that _NEVER_ touched _ANY_ PostgreSQL
APIs, talked to the SPI, etc, and let them run while blocking the main
thread until they complete.

Then again, I know relatively little about Pg's guts, and for all I know
initing the pthread environment could completely mess up the backend.

Personally I'd want to do it out-of-process, using a SECURITY DEFINER
PL/PgSQL function owned by a role that also owned some otherwise private
queue and result tables for your worker server. As Greg Smith noted,
LISTEN/NOTIFY would allow your worker server to avoid polling and
instead sleep when there's nothing in the queue, and would also let your
waiting clients avoid polling the result table.

> For example, I've seen >10:1 speedups just be rewriting one small portion
> of a computationally expensive mathematical function in C before, keeping
> the rest of the logic on the database side. You don't necessarily have to
> rewrite the whole thing.

A useful dirty trick is to use Psyco in Python. It's a specializing
compiler that can get massive performance boosts out of Python code
without any code changes, and it seems to work with PL/Python. Just:

try:
import psyco
psyco.full()
except:
# Enabing Pysco failed; don't care
pass

in your function should get you a pretty serious boost. This will NOT,
however, allow your code to use two cores at once; you'll need threading
or multiple processes for that.

--
Craig Ringer