Re: Don't Thread On Me (PostgreSQL related)

On Fri, Jan 27, 2012 at 00:32, Chris Travers <chris(dot)travers(at)gmail(dot)com> wrote:
>
>
> On Thu, Jan 26, 2012 at 3:02 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>
>> On Thu, Jan 26, 2012 at 3:52 PM, Rodrigo E. De León Plicet
>> <rdeleonp(at)gmail(dot)com> wrote:
>> > Quote:
>> >
>> > ======================================================================
>> >
>> > This thread
>> >
>> >
>> > http://postgresql.1045698.n5.nabble.com/Multithread-Query-Planner-td5143643.html
>> >
>> > was mentioned in a performance sub-group posting. Give it a read.
>> >
>> > Back? It means, so far as I can see, that PG is toast. It will fall
>> > down to being the cheap and dirty alternative to MySql, which even
>> > has, at least two, multi-threaded engines. DB2 switched it's *nix
>> > engine to threads from processes with release 9.5. Oracle claims it
>> > for releases going back to 7 (I haven't tried to determine which parts
>> > or applications; Larry has bought so many tchochtkes over the
>> > years...). SQL Server is threaded.
>> >
>> > Given that cpu's are breeding threads faster than cores,
>> > PG will fall into irrelevance.
>>
>> The author of that post apparently doesn't understand that even though
>> postgresql hasn't 'switched to threads', it can still do more than one
>> thing at once.  Each process is itself an execution thread.  A
>> multi-threaded query planner is perfectly possible in postgresql
>> architecture -- however each one must reside in it's own process and
>> you have to use shared memory instead instead of pthreads and locking.
>>  Big whoop.  The only thing at stake with a multi threaded planner is
>> optimizing single user tasks which is, while important, a niche
>> optimization.  PostgreSQL is for more scalable than mysql for
>> multi-user loads and the gap is increasing.
>>
>>
> There are cases where intraquery parallelism would be helpful.  As far as I
> understand it, PostgreSQL is the only major, solid (i.e. excluding MySQL)
> RDBMS which does not offer some sort of intraquery parallelism, and when
> running queries across very large databases, it might be helpful to be able
> to, say, scan different partitions simultaneously using different threads.
>  So I think it is wrong to simply dismiss the need out of hand.  The thing
> though is that I am not sure that where this need really comes to the fore,
> it is typical of single-server instances, and so this brings me to the
> bigger question.

Intraquery parallelism is certainly something PostgreSQL is in need
of, and it's going to get more and more obvious over the next couple
of years.

Whether it uses threads or not is an implementation detail, just like
processing of regular queries on threads or processes or pools is an
implementation detail.

So the lack of threads isn't a problem - the lack of intraquery parallelism is.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/