Re: How to make a REALLY FAST db server?

> I'm looking to make a DB server for a project I'm working on (using
pgsql)
> and am wondering if people have suggestions?
>
> Thoughts:
>
> - Hardware: dual / quad Intel class

If you're going dual P3, go with Athlons, they have a *much* higher
bandwidth across the front side and memory buses, not to mention being
better processors to start with. If you're thinking a quad Xeon, you can
still consider a dual Athlon. I do run a quad Xeon (SuperMicro 8050
chassis/motherboard), and it's fast, but there are tradeoffs. Dual 1.2
GHz Athlons = 2.4 GHz total, 4x700 MHz Xeons = 2.8 GHz total. Cost
difference = ~$4,000 or more.

Another determining factor is the usage that you expect. If you need
extremely low latency on infrequent queries, the higher-frequency Athlons
will likely be much better, as for a single query you're pitting a 700 MHz
Xeon on a 100 MHz bus against a 1.2 MHz Athlon with a 266 MHz bus.
However, for all-out, slam-the-machine work, the ServerWorks Xeon chips do
shine, as they have 4-way interleaved memory and a crossbar to individual
CPU buses. (I wasn't aware that they used the crossbar until recently.)

If you do go with a quad Xeon, the SuperMicro 8050 is a *superb*
platform to start with. Add RAM, CPU's, RAID card, drives, and video, and
you're off to the races. Triple, redundant power supplies connected to an
intelligent power distribution system, an unearthly number of fans (again,
all in redundant push/pull pairs), 64/66 PCI, and 10 hot-swap, SCA drive
bays across two channels. Tough to beat. It is a very large chassis
(makes two mid-towers look puny), the rack-mountable 8060 looks like it
should be equally sturdy and reliable. The only time ours ever gets
rebooted is for hardware upgrades.

> can do multi proc support
> - Disk: SCSI Raid 1+0
> - Ram: Not really sure here. Is there math somewhere for ram needs for
> pgsql? I imagine is has something to do with # connections, db size,
> etc.

While 1+0 is fast, you can realistically use RAID 5 for redundancy,
saving disks, provided that you're not using fsync(), and have plenty of
RAM. Between the write-cache on the RAID controller and from the kernel,
even a large number of inserts/updates don't actually have to hit the disk
at all.

As for how much RAM to use, you want to have enough that your entire
$PGDATA directory is held in disk cache, a good chunk for shared buffers,
a good chunk for sorts, enough for the backends, and enough spare for
write-caching that you won't hit the disk. Just how much that is depends
on the size of your data, and what type of usage you're going to give the
machine.

steve