[Cialug] Multicore and multiproc Postgres

[Matthew Nuzum]

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