From: Chris Browne <cbbrowne@acm.org>
Subject: Re: Scaling up PostgreSQL in Multiple CPU / Dual Core
Date: Fri, 24 Mar 2006 13:21:23 -0500
Organization: cbbrowne Computing Inc
Lines: 67
Message-ID: <60zmjfsnz0.fsf@dba2.int.libertyrms.com>
References: <65f9ddfb0603222219r1742c2aevda37acd55ca845be@mail.gmail.com>
	<1143131901.17999.67.camel@state.g2switchworks.com>
	<4422D033.2030407@commandprompt.com>
	<1143133364.17999.77.camel@state.g2switchworks.com>
	<87hd5olgyd.fsf@wolfe.cbbrowne.com>
	<20060324101440.GP90527@pervasive.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux)
Cancel-Lock: sha1:s1CFl16A80RDOICVG+i/YB62+wE=
To: pgsql-performance@postgresql.org

jnasby@pervasive.com ("Jim C. Nasby") writes:
> On Thu, Mar 23, 2006 at 09:22:34PM -0500, Christopher Browne wrote:
>> Martha Stewart called it a Good Thing when smarlowe@g2switchworks.com (Scott Marlowe) wrote:
>> > On Thu, 2006-03-23 at 10:43, Joshua D. Drake wrote:
>> >> > Has someone been working on the problem of splitting a query into pieces
>> >> > and running it on multiple CPUs / multiple machines?  Yes.  Bizgress has
>> >> > done that.  
>> >> 
>> >> I believe that is limited to Bizgress MPP yes?
>> >
>> > Yep.  I hope that someday it will be released to the postgresql global
>> > dev group for inclusion.  Or at least parts of it.
>> 
>> Question: Does the Bizgress/MPP use threading for this concurrency?
>> Or forking?
>> 
>> If it does so via forking, that's more portable, and less dependent on
>> specific complexities of threading implementations (which amounts to
>> non-portability ;-)).
>> 
>> Most times Jan comes to town, we spend a few minutes musing about the
>> "splitting queries across threads" problem, and dismiss it again; if
>> there's the beginning of a "split across processes," that's decidedly
>> neat :-).
>
> Correct me if I'm wrong, but there's no way to (reasonably) accomplish
> that without having some dedicated extra processes laying around that
> you can use to execute the queries, no? In other words, the cost of a
> fork() during query execution would be too prohibitive...

Counterexample...

The sort of scenario we keep musing about is where you split off a
(thread|process) for each partition of a big table.  There is in fact
a natural such partitioning, in that tables get split at the 1GB mark,
by default.

Consider doing a join against 2 tables that are each 8GB in size
(e.g. - they consist of 8 data files).  Let's assume that the query
plan indicates doing seq scans on both.

You *know* you'll be reading through 16 files, each 1GB in size.
Spawning a process for each of those files doesn't strike me as
"prohibitively expensive."

A naive read on this is that you might start with one backend process,
which then spawns 16 more.  Each of those backends is scanning through
one of those 16 files; they then throw relevant tuples into shared
memory to be aggregated/joined by the central one.

That particular scenario is one where the fork()s would hardly be
noticeable.

> FWIW, DB2 executes all queries in a dedicated set of processes. The
> process handling the connection from the client will pass a query
> request off to one of the executor processes. I can't remember which
> process actually plans the query, but I know that the executor runs
> it.

It seems to me that the kinds of cases where extra processes/threads
would be warranted are quite likely to be cases where fork()ing may be
an immaterial cost.
-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/languages.html
TECO Madness: a moment of convenience, a lifetime of regret.
-- Dave Moon