Обсуждение: 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?

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.

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 ]

> 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

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

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

> 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

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.

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.

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