[Cialug] Multicore and multiproc Postgres

[Daniel.Juliano at wellsfargo.com]

Thanks for the responses!

I have no access to performance monitoring for the server being
replaced, but I know the harddisk side to the server is in serious need
of replacement.  I also can't attest to how the budget is being worked
out.

Couldn't tell you why they seem to think they need so many CPU's, as
I've seen a similar "hard disk speed stomps extra cpu's" story with our
Teradata installation.  Oh, and RAM is king.

I've read a couple of places where SMP for 8.1 is supposed to rock.
Can't find any performance benches tho.

As for Ramaley, who stated:
"i'd strongly recommend reading all you can about tuning PostgreSQL"

I'll be googling for tuning, may fire some q's back here at a later
date.

=Dan

-----Original Message-----
From: cialug-bounces at cialug.org [mailto:cialug-bounces at cialug.org] On
Behalf Of Matthew Nuzum
Sent: Tuesday, August 22, 2006 9:07 AM
To: Central Iowa Linux Users Group
Subject: Re: [Cialug] Multicore and multiproc Postgres

On Tue, 2006-08-22 at 07:36 -0500, Daniel.Juliano at wellsfargo.com wrote:
> Anyone here familiar with running multicore and/or multiproc Postgres?
> If so, how many cores/procs, what OS, and how pesky was the install?
> And how did the performance compare to other db's considered?
> 
> I heard my dept is looking at an 8 dualcore proc installation for MS 
> SQL Server 2K5, and the licensing fee for unlimited connections is 
> just below a cool $100K.  I'd like to at least offer management some 
> info on a free-as-in-beer solution.

I've been running on PostgreSQL for years now. The concensus on the
performance list is that after 2 cores the roi drops when you add more
cores for most jobs. The big win after 2 cores is often in i/o
investment.

Running raid 10 with more spindles on an excellent controller will
usually provide a much greater return than adding more cpu cores after
2.

For example, two 8 channel 3ware sata raid controllers each with 8
raptor drives and battery backed ram cache using 256k, 512k or maybe
even 1mb blocks for the striping on a dual core box will often stomp
quad or 8 way boxes using 3-5 drives in a raid5.

The benefit of multiple cores comes into play when all of the below are
true:
entire database (+ indexes) fit into ram database is mostly read heavy
use of aggragate functions or stored procedures

If you are doing oltp then you'll often benefit from both heavy i/o and
multi-core investments and you should ask specific questions about board
chipsets, controllers and etc on the postgres-perf mailing list before
making a purchase decision. Look into greenplum's bizgres mpp.

By the way, the above is true for MS SQL server as well, although MS SQL
makes better use of additional cores than postgres.

--
Matthew Nuzum
newz2000 on freenode

_______________________________________________
Cialug mailing list
Cialug at cialug.org
http://cialug.org/mailman/listinfo/cialug