Re: Dream Server? - Mailing list pgsql-general

> I'm currently running a 4 Gig 11 million row database and am looking
> to build a "dream server" for it.   I am currently running a dual p3
> 1GHz system with 10k rpm scsi drives and 4 gigs of ram (which I have
> configured pgsql to use all of) and I'm concerned about performance
> once the db doubles in size, which should be in 6 mos to a year at
> the latest.  First off, If money was no concern, what would you buy
> as the ultimate postgresql server running linux?

  I don't know if Linux runs on the new version of the Sun Starfires, does
it?

> Second off, on a
> more technical note, does pgsql take advantage of multiple
> processors.

  If you are running multiple connections, then yes, it does.  But having
an extra CPU around to handle kernel code, disk activity, network
activity, etc. does help even for a single connection.

> If I had a 8 way 800 MHz Xeon would the machine blow
> away a 2GHz P4?  How much is CPU a factor compared to memory?

   Both CPU and memory have to be taken into account.  It does you no good
to have a 10 GHz chip if your memory subsystem can't provide the data to
feed the chip, and likewise, having 10 GB/sec throughput from your memory
system does no good if you are running a 386!  If you're using extremely
large tables (which it sounds like you will be), then memory throughput is
a very critical factor.

  As for the 8-Xeon to 2-P4, in total throughput, yes, the Xeon should
blow away the P4, assuming that you were talking about overall throughput
with multiple connections, and the 8-way machine had a memory subsystem
that was up to the task.  If you're talking about a single query on a
single connection, then the P4 may very well beat the Xeon.  Ideally, your
SMP machine should require you to add DIMMS in groups equal to the number
of your processors - in this case, 8 DIMMS at a time.  Otherwise, you
simply can't give each chip the full bandwidth.  One interesting solution
would be to interleave 4 banks of DDR Ram to feed 8 Xeons, but I doubt
we'll ever see that in production.

> Disk
> speed?  I want to be able to do large volume selects on tables with
> more than 5 million rows and not have the server blink at other
> requests put in at the same time.

  If you have enough RAM to keep the entire database in disk cache, then
disk speed becomes much less of a factor, if you turn of fsync().  Like
I've said plenty of times before (and probably bored everyone to tears),
the lights on our DB machine only blink *occasionally*, even when the
machine is literally being slammed with database activity.  A hardware
RAID card with cache on the board and some moderately-decent drives can
give you a VERY fast, responsive disk subsystem.

  In a day or two, I'll be getting the parts to build a dual Athlon MP
1800+ machine, and I'm planning on putting PG on it and testing it against
our 4x700 MHz Xeon machine, to see how it fares.  Once I do, I'm planning
on making a full report to the list.  If anyone has a certain PG benchmark
that they'd like me to run, let me know.

steve