pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
One or more processor ?
14+ messages / 12 participants
[nested] [flat]

* One or more processor ?
@ 2003-10-10 16:30 Hervé Piedvache <herve@elma.fr>
  2003-10-10 16:35 ` Re: One or more processor ? Jeff <threshar@torgo.978.org>
  2003-10-10 16:38 ` Re: One or more processor ? Dennis Bjorklund <db@zigo.dhs.org>
  2003-10-10 16:42 ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
  2003-10-10 17:43 ` Re: One or more processor ? Josh Berkus <josh@agliodbs.com>
  2003-10-10 18:49 ` Re: One or more processor ? Christopher Browne <cbbrowne@libertyrms.info>
  0 siblings, 5 replies; 14+ messages in thread

From: Hervé Piedvache @ 2003-10-10 16:30 UTC (permalink / raw)
  To: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
@ 2003-10-10 16:35 ` Jeff <threshar@torgo.978.org>
  4 siblings, 0 replies; 14+ messages in thread

From: Jeff @ 2003-10-10 16:35 UTC (permalink / raw)
  To: Hervé Piedvache <herve@elma.fr>; +Cc: pgsql-performance

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/





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
@ 2003-10-10 16:38 ` Dennis Bjorklund <db@zigo.dhs.org>
  4 siblings, 0 replies; 14+ messages in thread

From: Dennis Bjorklund @ 2003-10-10 16:38 UTC (permalink / raw)
  To: Hervé Piedvache <herve@elma.fr>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
@ 2003-10-10 16:42 ` Bill Moran <wmoran@potentialtech.com>
  2003-10-10 17:09   ` Re: One or more processor ? Jason Hihn <jhihn@paytimepayroll.com>
  2003-10-10 17:15   ` Re: One or more processor ? johnnnnnn <john@phaedrusdeinus.org>
  2003-10-10 21:03   ` Re: One or more processor ? Relaxin <me@yourhouse.com>
  4 siblings, 3 replies; 14+ messages in thread

From: Bill Moran @ 2003-10-10 16:42 UTC (permalink / raw)
  To: Hervé Piedvache <herve@elma.fr>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
  2003-10-10 16:42 ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
@ 2003-10-10 17:09   ` Jason Hihn <jhihn@paytimepayroll.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Jason Hihn @ 2003-10-10 17:09 UTC (permalink / raw)
  To: Bill Moran <wmoran@potentialtech.com>; Hervé Piedvache <herve@elma.fr>; +Cc: pgsql-performance



> -----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.





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
  2003-10-10 16:42 ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
@ 2003-10-10 17:15   ` johnnnnnn <john@phaedrusdeinus.org>
  2003-10-10 17:26     ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
  2 siblings, 1 reply; 14+ messages in thread

From: johnnnnnn @ 2003-10-10 17:15 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
  2003-10-10 16:42 ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
  2003-10-10 17:15   ` Re: One or more processor ? johnnnnnn <john@phaedrusdeinus.org>
@ 2003-10-10 17:26     ` Bill Moran <wmoran@potentialtech.com>
  2003-10-13 08:53       ` Re: One or more processor ? Andriy Tkachuk <ant@imt.com.ua>
  0 siblings, 1 reply; 14+ messages in thread

From: Bill Moran @ 2003-10-10 17:26 UTC (permalink / raw)
  To: johnnnnnn <john@phaedrusdeinus.org>; +Cc: pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
  2003-10-10 16:42 ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
  2003-10-10 17:15   ` Re: One or more processor ? johnnnnnn <john@phaedrusdeinus.org>
  2003-10-10 17:26     ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
@ 2003-10-13 08:53       ` Andriy Tkachuk <ant@imt.com.ua>
  2003-10-13 18:43         ` Re: One or more processor ? James Rogers <jamesr@best.com>
  0 siblings, 1 reply; 14+ messages in thread

From: Andriy Tkachuk @ 2003-10-13 08:53 UTC (permalink / raw)
  To: Bill Moran <wmoran@potentialtech.com>; +Cc: johnnnnnn <john@phaedrusdeinus.org>; pgsql-performance

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




^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
  2003-10-10 16:42 ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
  2003-10-10 17:15   ` Re: One or more processor ? johnnnnnn <john@phaedrusdeinus.org>
  2003-10-10 17:26     ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
  2003-10-13 08:53       ` Re: One or more processor ? Andriy Tkachuk <ant@imt.com.ua>
@ 2003-10-13 18:43         ` James Rogers <jamesr@best.com>
  0 siblings, 0 replies; 14+ messages in thread

From: James Rogers @ 2003-10-13 18:43 UTC (permalink / raw)
  To: pgsql-performance

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





^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
  2003-10-10 16:42 ` Re: One or more processor ? Bill Moran <wmoran@potentialtech.com>
@ 2003-10-10 21:03   ` Relaxin <me@yourhouse.com>
  2 siblings, 0 replies; 14+ messages in thread

From: Relaxin @ 2003-10-10 21:03 UTC (permalink / raw)
  To: pgsql-performance


> 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.






^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
@ 2003-10-10 17:43 ` Josh Berkus <josh@agliodbs.com>
  4 siblings, 0 replies; 14+ messages in thread

From: Josh Berkus @ 2003-10-10 17:43 UTC (permalink / raw)
  To: Hervé Piedvache <herve@elma.fr>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
@ 2003-10-10 18:49 ` Christopher Browne <cbbrowne@libertyrms.info>
  2003-10-10 20:42   ` Re: One or more processor ? Josh Berkus <josh@agliodbs.com>
  4 siblings, 1 reply; 14+ messages in thread

From: Christopher Browne @ 2003-10-10 18:49 UTC (permalink / raw)
  To: pgsql-performance

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)



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
  2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
  2003-10-10 18:49 ` Re: One or more processor ? Christopher Browne <cbbrowne@libertyrms.info>
@ 2003-10-10 20:42   ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 14+ messages in thread

From: Josh Berkus @ 2003-10-10 20:42 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@libertyrms.info>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread

* Re: One or more processor ?
@ 2003-10-13 09:04 Magnus Hagander <mha@sollentuna.net>
  0 siblings, 0 replies; 14+ messages in thread

From: Magnus Hagander @ 2003-10-13 09:04 UTC (permalink / raw)
  To: Andriy Tkachuk <ant@imt.com.ua>; Bill Moran <wmoran@potentialtech.com>; +Cc: johnnnnnn <john@phaedrusdeinus.org>; pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 14+ messages in thread


end of thread, other threads:[~2003-10-13 18:43 UTC | newest]

Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-10 16:30 One or more processor ? Hervé Piedvache <herve@elma.fr>
2003-10-10 16:35 ` Jeff <threshar@torgo.978.org>
2003-10-10 16:38 ` Dennis Bjorklund <db@zigo.dhs.org>
2003-10-10 16:42 ` Bill Moran <wmoran@potentialtech.com>
2003-10-10 17:09   ` Jason Hihn <jhihn@paytimepayroll.com>
2003-10-10 17:15   ` johnnnnnn <john@phaedrusdeinus.org>
2003-10-10 17:26     ` Bill Moran <wmoran@potentialtech.com>
2003-10-13 08:53       ` Andriy Tkachuk <ant@imt.com.ua>
2003-10-13 18:43         ` James Rogers <jamesr@best.com>
2003-10-10 21:03   ` Relaxin <me@yourhouse.com>
2003-10-10 17:43 ` Josh Berkus <josh@agliodbs.com>
2003-10-10 18:49 ` Christopher Browne <cbbrowne@libertyrms.info>
2003-10-10 20:42   ` Josh Berkus <josh@agliodbs.com>
2003-10-13 09:04 Re: One or more processor ? Magnus Hagander <mha@sollentuna.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox