Re: Utilizing multiple cores in a function call.

От: Craig Ringer
Тема: Re: Utilizing multiple cores in a function call.
Дата: ,
Msg-id: 1246341281.22426.73.camel@tillium.localnet
(см: обсуждение, исходный текст)
Ответ на: Re: Utilizing multiple cores in a function call.  (Greg Smith)
Список: pgsql-performance

Скрыть дерево обсуждения

Utilizing multiple cores in a function call.  ("Hartman, Matthew", )
 Re: Utilizing multiple cores in a function call.  (Jean-David Beyer, )
  Re: Utilizing multiple cores in a function call.  ("Hartman, Matthew", )
   Re: Utilizing multiple cores in a function call.  (Joe Conway, )
 Re: Utilizing multiple cores in a function call.  (Greg Smith, )
  Re: Utilizing multiple cores in a function call.  (Craig Ringer, )
 Re: Utilizing multiple cores in a function call.  (Merlin Moncure, )
  Re: Utilizing multiple cores in a function call.  ("Hartman, Matthew", )
   Re: Utilizing multiple cores in a function call.  (Merlin Moncure, )
    Re: Utilizing multiple cores in a function call.  ("Hartman, Matthew", )

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



В списке pgsql-performance по дате сообщения:

От: valgog
Дата:
Сообщение: Re: Terrible Write Performance of a Stored Procedure
От: Bob Lunney
Дата:
Сообщение: Re: Insert performance and multi-column index order