Re: Parallel threads in query

On 31.10.2018 22:07, Darafei "Komяpa" Praliaskouski wrote:
> Hi,
>
> I've tried porting some of PostGIS algorithms to utilize multiple 
> cores via OpenMP to return faster.
>
> Question is, what's the best policy to allocate cores so we can play 
> nice with rest of postgres?
>
> What I'd like to see is some function that I can call and get a number 
> of threads I'm allowed to run, that will also advise rest of postgres 
> to not use them, and a function to return the cores back (or do it 
> automatically at the end of query). Is there an infrastructure for that?

I do not completely understand which PostGIS algorithms  you are going 
to make parallel.
So may be you should first clarify it.
There are three options to perform parallel execution of the single 
query in Postgres now:

1. Use existed Postgres parallel capabilities. For example if there is 
some expensive function f() which you are going to execute concurrently,
then  you do not need to do anything: parallel seq scan will do it for 
you. You can configure arbitrary number of parallel workers and so 
control level of concurrency.
The restriction of the current Postgres parallel query processing 
implementation is that
- parallel workers are started for each query
- it is necessary to serialize and pass to parallel workers a lot of 
things from coordinator
- in case of seqscan, workers will compete for pages to scan, so 
effective number of workers should be < 10, while most powerful modern 
servers have hundreds of COU cores.

2. Implement you own parallel plan nodes using existed Postgres parallel 
infrastructure. Such approach has most chances to be committed in 
Postgres core.
But disadvantages are mostly the same as in 1) Exchange of data between 
different process is much more complex and expensive than access to 
common memory in case of threads. Mostly likely you will have to use 
shared message queue and dynamic shared memory, implemented in Postgres 
specially for interaction of parallel workers .

3. Use multithreading to provide concurrent execution of your particular 
algorithm (s[awn threads within backend). You should be very careful 
with this approach, because Postgres code is not thread safe. So you 
should not try to execute in thread any subplan or call any postgres 
functions (unless you are 100% sure that them are thread safe).
This approach may be easy to implement and provide better performance 
than 1). But please notice its limitations. I have used such approach in 
my IMCS extension (In-Memory-Columnar-Store).

You can look at pg_strom extension as an example of providing parallel 
query execution (in this case using parallel capabilities of video cards).

-- 

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company