I am looking to beef up a PostgreSQL database by moving it to a Sun
Enterprise or an Alpha ES-40 or some other multi-CPU platform.  My
questions are;

  - What suggestions do people have for a good PostgreSQL platform.
  - How well does PostgreSQLtake advantage of multiple CPUs?

Thanks.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 8 Mar 2001 07:50:15 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
PostgreSQL on multi-CPU systems
On Thu, Mar 08, 2001 at 07:50:15AM -0500, D'Arcy J.M. Cain wrote:
> I am looking to beef up a PostgreSQL database by moving it to a Sun
> Enterprise or an Alpha ES-40 or some other multi-CPU platform.  My
> questions are;
> 
>   - What suggestions do people have for a good PostgreSQL platform.
>   - How well does PostgreSQLtake advantage of multiple CPUs?

i have postgres running on a couple dual-processor intel boxes running
freebsd.

under freebsd, the win is that the various backend processes will flit
between the CPU's, thus increasing the CPU utilization.

the win was not overly huge in our case, but it was in fact a win.

in one case, the system had numerous volitile multi-million record tables,
and was not performing adequately on a single cpu.

the system was migrated to a dual cpu solution (mind you the two CPU's were
each twice as fast as the previous single CPU) and things have been quite
manageable since.

another factor is to get your data on fast/striped disk.

-- 
[ Jim Mercer          jim@pneumonoultramicroscopicsilicovolcanoconiosis.ca ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[ aka                        jim@reptiles.org              +1 416 410-5633 ]

Mon, 12 Mar 2001 15:10:06 -0500
Jim Mercer <jim@reptiles.org>
Re: PostgreSQL on multi-CPU systems
> I am looking to beef up a PostgreSQL database by moving it to a Sun
> Enterprise or an Alpha ES-40 or some other multi-CPU platform.  My
> questions are;
> 
>   - What suggestions do people have for a good PostgreSQL platform.
>   - How well does PostgreSQLtake advantage of multiple CPUs?

I have tested PostgreSQL with 2-4 CPU linux boxes. In summary, 2 CPU
was a big win, but 4 was not. I'm not sure where the bottle neck is
though.
--
Tatsuo Ishii

Tue, 13 Mar 2001 09:40:01 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: PostgreSQL on multi-CPU systems
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> I have tested PostgreSQL with 2-4 CPU linux boxes. In summary, 2 CPU
> was a big win, but 4 was not. I'm not sure where the bottle neck is
> though.

Our not-very-good implementation of spin locking (using select() to
wait) might have something to do with this.  Sometime soon I'd like to
look at using POSIX semaphores where available, instead of spinlocks.

			regards, tom lane

Mon, 12 Mar 2001 19:45:16 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL on multi-CPU systems 
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
>> I have tested PostgreSQL with 2-4 CPU linux boxes. In summary, 2 CPU
>> was a big win, but 4 was not. I'm not sure where the bottle neck is
>> though.

> Our not-very-good implementation of spin locking (using select() to
> wait) might have something to do with this.  Sometime soon I'd like to
> look at using POSIX semaphores where available, instead of spinlocks.

did anyone from here follow the discussion about postgresql on
smp machines on the linux kernel malinglist in the last days?
(just as an info)

t

-- 
thomas.graichen@innominate.com
                                                         innominate AG
                                                  the linux architects
tel: +49-30-308806-13   fax: -77             http://www.innominate.com

13 Mar 2001 07:24:20 GMT
Thomas Graichen <news-list.pgsql.hackers@innominate.de>
Re: PostgreSQL on multi-CPU systems
> did anyone from here follow the discussion about postgresql on
> smp machines on the linux kernel malinglist in the last days?
> (just as an info)

I didn't. Do you have a synopsis or references?

                  - Thomas

Tue, 13 Mar 2001 15:31:02 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: PostgreSQL on multi-CPU systems
On Tue, 13 Mar 2001, Thomas Lockhart wrote:

> > did anyone from here follow the discussion about postgresql on
> > smp machines on the linux kernel malinglist in the last days?
> > (just as an info)
> 
> I didn't. Do you have a synopsis or references?

The thread starts here:

http://www.mail-archive.com/linux-kernel%40vger.kernel.org/msg29798.html


Matthew.


Tue, 13 Mar 2001 17:09:03 +0000 (GMT)
Matthew Kirkwood <matthew@hairy.beasts.org>
Re: Re: PostgreSQL on multi-CPU systems
Thus spake Tom Lane
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > I have tested PostgreSQL with 2-4 CPU linux boxes. In summary, 2 CPU
> > was a big win, but 4 was not. I'm not sure where the bottle neck is
> > though.
> 
> Our not-very-good implementation of spin locking (using select() to
> wait) might have something to do with this.  Sometime soon I'd like to
> look at using POSIX semaphores where available, instead of spinlocks.

One thing I notice is that a single query can seem to block other queries,
at least to some extent.  It makes me wonder if we effectively have a
single threaded system.  In fact, I have some simple queries that if
I send a bunch at once, the first one can take 15 seconds while the
others zip through.  Is this related to what you are talking about?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 15 Mar 2001 07:53:17 -0500 (EST)
darcy@druid.net (D'Arcy J.M. Cain)
Re: PostgreSQL on multi-CPU systems
darcy@druid.net (D'Arcy J.M. Cain) writes:
> One thing I notice is that a single query can seem to block other queries,
> at least to some extent.

It's not supposed to, except with certain specific features (for
example, I don't think any of the index types other than btree allow
concurrent insertions).  Can you give a concrete example?

			regards, tom lane

Thu, 15 Mar 2001 09:58:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL on multi-CPU systems 