Thread: One or more processor ?
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
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/
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
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
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
> -----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.
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
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
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)
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
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
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
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
> 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.