Thread: One or more processor ?

One or more processor ?

From
Hervé Piedvache
Date:
Hi,

A simple question about PostgreSQL ... I have a Pentium Xeon Quadri processors
...
If I do a SQL request ... does PostgreSQL use one or more processor ?

And if it use only one ... why ?
Could you explain me this ;o)

Thanks per advance.
--
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Pho. 33-144949901
Fax. 33-144949902


Re: One or more processor ?

From
Jeff
Date:
On Fri, 10 Oct 2003, [iso-8859-15] Herv� Piedvache wrote:

> If I do a SQL request ... does PostgreSQL use one or more processor ?
>

Nope. Just one processor this is because PG is process not thread based.
However, if you opened 4 connections and each issued a sql statement all 4
processors would be used.  Check the -HACKERS archives for lengthy
discussions of this.

--
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



Re: One or more processor ?

From
Dennis Bjorklund
Date:
On Fri, 10 Oct 2003, [iso-8859-15] Hervé Piedvache wrote:

> A simple question about PostgreSQL ... I have a Pentium Xeon Quadri
> processors ... If I do a SQL request ... does PostgreSQL use one or more
> processor ?

Each connection becomes a process, and each process runs on one processor.
So, with only one connection you use only one processor (and the OS might
use an other processor). Most databases has many concurrent users and then
it will use more processors.

--
/Dennis


Re: One or more processor ?

From
Bill Moran
Date:
Hervé Piedvache wrote:
> Hi,
>
> A simple question about PostgreSQL ... I have a Pentium Xeon Quadri processors
> ...
> If I do a SQL request ... does PostgreSQL use one or more processor ?

PostgreSQL uses one processor per connection.  If you have 4 simultaneous
connections, you'll use all four processors (assuming your operating system
is properly designed/configured).

> And if it use only one ... why ?
> Could you explain me this ;o)

The answer to that is beyond my knowledge, but I have a few guesses:
1) Doing so is more complicated than you think.
2) The code was originally written for uniprocessor machines, and nobody
    has volunteered to update it yet.
3) kernel threading isn't as predictable as some people would like to
    think, thus they developers have avoided using it so far.
4) It simply isn't practical to expect a single query to
    execute on multiple processors simultaneously.

Do you know of any RDBMS that actually will execute a single query on
multiple processors?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com


Re: One or more processor ?

From
johnnnnnn
Date:
On Fri, Oct 10, 2003 at 12:42:04PM -0400, Bill Moran wrote:
> 4) It simply isn't practical to expect a single query to
>    execute on multiple processors simultaneously.
>
> Do you know of any RDBMS that actually will execute a single query
> on multiple processors?

Yes, DB2 will do this if configured correctly. It's very useful for
large, complicated queries that have multiple subplans.

-johnnnnnnnn

Re: One or more processor ?

From
Jason Hihn
Date:

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Bill Moran
> Sent: Friday, October 10, 2003 12:42 PM
> To: Hervé Piedvache
> Cc: Postgresql Performance
> Subject: Re: [PERFORM] One or more processor ?
>
>
> Hervé Piedvache wrote:
> > Hi,
> >
> > A simple question about PostgreSQL ... I have a Pentium Xeon
> Quadri processors
> > ...
> > If I do a SQL request ... does PostgreSQL use one or more processor ?
>
> PostgreSQL uses one processor per connection.  If you have 4 simultaneous
> connections, you'll use all four processors (assuming your
> operating system
> is properly designed/configured).
>
> > And if it use only one ... why ?
> > Could you explain me this ;o)
>
> The answer to that is beyond my knowledge, but I have a few guesses:
> 1) Doing so is more complicated than you think.

You need to be able to paralellize the algorithm. Even so, a 99%
paralelizable algorithm over 2 cpus is only 50% faster than 1 cpu. So choose
your poison: 2 processes @100% in 1tu or 1 process at 150% at .66tu (tu=time
unit). This ofcourse is over simplification. I don't think 99% is reasonable
in query processing (though it can depend on the query) so I expect the 2
connection method to be better, unless you only ever have 1 connection.



Re: One or more processor ?

From
Bill Moran
Date:
johnnnnnn wrote:
> On Fri, Oct 10, 2003 at 12:42:04PM -0400, Bill Moran wrote:
>
>>4) It simply isn't practical to expect a single query to
>>   execute on multiple processors simultaneously.
>>
>>Do you know of any RDBMS that actually will execute a single query
>>on multiple processors?
>
> Yes, DB2 will do this if configured correctly. It's very useful for
> large, complicated queries that have multiple subplans.

I expected there would be someone who did (although I didn't know for
sure).

Is DB2 the only one that can do that?

--
Bill Moran
Potential Technologies
http://www.potentialtech.com


Re: One or more processor ?

From
Josh Berkus
Date:
Herve'

> A simple question about PostgreSQL ... I have a Pentium Xeon Quadri
> processors ...
> If I do a SQL request ... does PostgreSQL use one or more processor ?

For your configuration, yes, you want multiple processors.  Postgres (or
rather, the host OS) will distribute active connections over the multiple
processors.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: One or more processor ?

From
Christopher Browne
Date:
herve@elma.fr (=?iso-8859-15?q?Herv=E9=20Piedvache?=) writes:
> A simple question about PostgreSQL ... I have a Pentium Xeon Quadri processors
> ...
> If I do a SQL request ... does PostgreSQL use one or more processor ?

Just one processor.

> And if it use only one ... why ?
> Could you explain me this ;o)

... Because partitioning requests across multiple processors is a
hairy and difficult proposition.

Some musing has been done on how threading might be used to split
processing of queries across multiple CPUs, but it represents a pretty
immense task involving substantial effort for design, implementation,
and testing.

It's tough to make this portable across all the system PostgreSQL
supports, too.

So while musing has been done, nobody has seriously tried implementing
it.
--
output = ("cbbrowne" "@" "libertyrms.info")
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)

Re: One or more processor ?

From
Josh Berkus
Date:
Chris,

> > If I do a SQL request ... does PostgreSQL use one or more processor ?
>
> Just one processor.

For one query, yes.  For multiple queries, PostgreSQL will use multiple
processors, and that's what he's concerned about given his earlier posts.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Re: One or more processor ?

From
Andriy Tkachuk
Date:
On Fri, 10 Oct 2003, Bill Moran wrote:

> johnnnnnn wrote:
> > On Fri, Oct 10, 2003 at 12:42:04PM -0400, Bill Moran wrote:
> >
> >>4) It simply isn't practical to expect a single query to
> >>   execute on multiple processors simultaneously.
> >>
> >>Do you know of any RDBMS that actually will execute a single query
> >>on multiple processors?
> >
> > Yes, DB2 will do this if configured correctly. It's very useful for
> > large, complicated queries that have multiple subplans.
>
> I expected there would be someone who did (although I didn't know for
> sure).
>
> Is DB2 the only one that can do that?

Oracle, i think, on partitioned tables.

regards, andriy

http://www.imt.com.ua


Re: One or more processor ?

From
"Magnus Hagander"
Date:
Actually, even Microsoft SQL Server will do this for you (you can even
chose if it shoudl split it up on all processors or a maximum number).
Will do it on any types of queries, as long as they're big enough (you
can tweak the cost limit, but the general idea is only process
CPU-expensive queries that way)

//Magnus


> -----Original Message-----
> From: Andriy Tkachuk [mailto:ant@imt.com.ua]
> Sent: Monday, October 13, 2003 10:53 AM
> To: Bill Moran
> Cc: johnnnnnn; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] One or more processor ?
>
>
> On Fri, 10 Oct 2003, Bill Moran wrote:
>
> > johnnnnnn wrote:
> > > On Fri, Oct 10, 2003 at 12:42:04PM -0400, Bill Moran wrote:
> > >
> > >>4) It simply isn't practical to expect a single query to
> > >>   execute on multiple processors simultaneously.
> > >>
> > >>Do you know of any RDBMS that actually will execute a
> single query
> > >>on multiple processors?
> > >
> > > Yes, DB2 will do this if configured correctly. It's very
> useful for
> > > large, complicated queries that have multiple subplans.
> >
> > I expected there would be someone who did (although I
> didn't know for
> > sure).
> >
> > Is DB2 the only one that can do that?
>
> Oracle, i think, on partitioned tables.
>
> regards, andriy
>
http://www.imt.com.ua


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: One or more processor ?

From
James Rogers
Date:
On Mon, 2003-10-13 at 01:53, Andriy Tkachuk wrote:
> > >>Do you know of any RDBMS that actually will execute a single query
> > >>on multiple processors?
> > >
> Oracle, i think, on partitioned tables.



This makes a certain amount of sense.  It be much easier to allow this
on partitioned tables than in the general case, since partitioned tables
look a lot like multiple relations hiding behind a view and one could
simply run one thread per partition in parallel without any real fear of
contention on the table.  This is doubly useful since partitioned tables
tend to be huge almost by definition.

Offhand, it would seem that this would be a feature largely restricted
to threaded database kernels for a couple different pragmatic reasons.

Cheers,

-James Rogers
 jamesr@best.com



Re: One or more processor ?

From
"Relaxin"
Date:
> Do you know of any RDBMS that actually will execute a single query on
> multiple processors?

SQL Server does in a sense.  It can split a query onto multiple threads
(which could possible use multiple processors) and then brings the results
from the threads into one and then sends the results to the client.