Thread: A multi CUP performance problem

A multi CUP performance problem

From
Li YueXin
Date:
I found  postgresql running on the multi CPU machine doesn't have better performance than single CPU machine. Can
youexplain the reason? And how to resolve the problem?
 
  In the Todo list, I read the below message:
Add code to detect an SMP machine and handle spinlocks accordinglyfrom distributted.net,
http://www1.distributed.net/source,inclient/common/cpucheck.cpp 
 
  I want to know how to handle spinlocks even if it can detect an SMP machine.
  Thank you!


Re: A multi CUP performance problem

From
Christopher Browne
Date:
Centuries ago, Nostradamus foresaw when liyuexin@neusoft.com (Li YueXin) would write:
> I found postgresql running on the multi CPU machine doesn't have
> better performance than single CPU machine. Can you explain the
> reason? And how to resolve the problem?

The classic reason for this is that if your system is primarily I/O
bound, adding extra CPUs is likely to have a relatively negligible
effect on system performance.

The far-and-away most enormous increase in transactions-per-second
that people seem to see from hardware changes come from putting in a
SCSI controller with large amounts of battery-backed cache.
Additional disk drives tends to be #2.  More memory also helps.

The "problem" might very well be your expectation that adding CPUs
would necessarily have much effect on performance.
-- 
output = ("cbbrowne" "@" "cbbrowne.com")
http://www3.sympatico.ca/cbbrowne/emacs.html
DSK: STAN.K; ML EXIT -- FILE NOT FOUND


Re: A multi CUP performance problem

From
Neil Conway
Date:
On Tue, 2003-09-23 at 22:45, Li YueXin wrote:
> I found  postgresql running on the multi CPU machine doesn't have
> better performance than single CPU machine. Can you explain the
> reason?

In addition to what Christopher Browne suggests, keep in mind that
PostgreSQL spawns a separate Unix process for each client connection.
Since each process can run on at most one CPU at any given time, if the
# of concurrent queries your application submits is < than the # of CPUs
in the machine, PostgreSQL won't take advantage of all your CPUs.

It would be possible to spread the execution of a single query over
multiple CPUs (say, have different nodes in the query tree execute on
different CPUs concurrently), but I highly doubt PostgreSQL will bother
doing that anytime soon.

-Neil




Re: A multi CUP performance problem

From
Tom Lane
Date:
Li YueXin <liyuexin@neusoft.com> writes:
>    I found  postgresql running on the multi CPU machine doesn't have
>    better performance than single CPU machine. Can you explain the
>    reason?

That's a rather sweeping statement to make without offering any
evidence, I should think.

If you gave details --- "I did x,y,z, on thus-and-such a platform,
and couldn't see any speedup" --- then you might get useful responses.
        regards, tom lane


Re: A multi CUP performance problem

From
"Hu Tian-Lei"
Date:
Not exactly, I think.
It said that postgresql "Add code to detect an SMP machine and handle spinlocks accordingly", so it is an issue of
spinlockhandling, not the distribution of execution tree. 
In SMP enviorment, when using spinlock, when we fail to get the spin lock at a time, we may do a number of null loops
totry to see if another threads on another cpu release the spin lock. It is just like M$'s EnterCriticalSection system
call'slast parameter. 
Am I right?

======= 2003-09-24 00:09:00 You Wrote: =======

>On Tue, 2003-09-23 at 22:45, Li YueXin wrote:
>> I found  postgresql running on the multi CPU machine doesn't have
>> better performance than single CPU machine. Can you explain the
>> reason?
>
>In addition to what Christopher Browne suggests, keep in mind that
>PostgreSQL spawns a separate Unix process for each client connection.
>Since each process can run on at most one CPU at any given time, if the
># of concurrent queries your application submits is < than the # of CPUs
>in the machine, PostgreSQL won't take advantage of all your CPUs.
>
>It would be possible to spread the execution of a single query over
>multiple CPUs (say, have different nodes in the query tree execute on
>different CPUs concurrently), but I highly doubt PostgreSQL will bother
>doing that anytime soon.
>
>-Neil
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: the planner will ignore your desire to choose an index scan if your
>      joining column's datatypes do not match

= = = = = = = = = = = = = = = = = = = =

Yours Sincerely,
        Hu Tian-Lei
        andy_hu@263.net
          2003-09-24