pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOne or more processor ?
14+ messages / 12 participants
[nested] [flat]
* One or more processor ?
@ 2003-10-10 16:30 Hervé Piedvache <herve@elma.fr>
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:35 Jeff <threshar@torgo.978.org>
parent: Hervé Piedvache <herve@elma.fr>
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:38 Dennis Bjorklund <db@zigo.dhs.org>
parent: Hervé Piedvache <herve@elma.fr>
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:42 Bill Moran <wmoran@potentialtech.com>
parent: Hervé Piedvache <herve@elma.fr>
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 17:09 Jason Hihn <jhihn@paytimepayroll.com>
parent: Bill Moran <wmoran@potentialtech.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 17:15 johnnnnnn <john@phaedrusdeinus.org>
parent: 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 17:26 Bill Moran <wmoran@potentialtech.com>
parent: johnnnnnn <john@phaedrusdeinus.org>
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 17:43 Josh Berkus <josh@agliodbs.com>
parent: Hervé Piedvache <herve@elma.fr>
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 18:49 Christopher Browne <cbbrowne@libertyrms.info>
parent: Hervé Piedvache <herve@elma.fr>
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 20:42 Josh Berkus <josh@agliodbs.com>
parent: Christopher Browne <cbbrowne@libertyrms.info>
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-10 21:03 Relaxin <me@yourhouse.com>
parent: Bill Moran <wmoran@potentialtech.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-13 08:53 Andriy Tkachuk <ant@imt.com.ua>
parent: Bill Moran <wmoran@potentialtech.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-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
* Re: One or more processor ?
@ 2003-10-13 18:43 James Rogers <jamesr@best.com>
parent: Andriy Tkachuk <ant@imt.com.ua>
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
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