Re: How to make a REALLY FAST db server?

> I'm not sure how much a 2+ way system will help.  Most of the db work
> will be done in one long serialized processes and those can't be spread
> out over multiple processors (with current day postgresql).

  That's assuming that only one query will ever be executed at once.  As a
new backend is spawned for each connection, extra CPU's are very helpful
if the database will see more than occasional use.

  Also, even if there's only one query, dual-CPU machines are generally
much more responsive, especially under load, as one CPU can be handling
interrupts, kernel code, and other system processes while the other sits
there doing your task.

> >To really eek out as much speed as possible here, you'll want 10k RPM
> >Ultra-160 Fibre Channel SCSI drives with a dedicated hardware raid
> >controller.  If have more reads than writes, you may want to use Raid 5
> >instead.
>
> Why 5?  1+0 is far better and faster.  I was planning on doing a
> hardware RAID controller (just need to find the one that FBSD likes the
> best).

  If you have enough RAM, disk speed isn't terribly important, so RAID 5
gives you the redundancy without as many disks.  Throw in an extra gig of
RAM for your disk cache, turn of fsync(), and you're likely to see a lot
bigger speed-up than any disk upgrade will give you.  There are cases
where that isn't the case (such as updating every row in a multi-gigabyte
table), but unless you're involved in those specialized cases, it's not as
important.

  So, why did I say that I don't use IDE for high-performance machines?
IDE has limitations.  For example, say I wanted 10 drives in my array.
Finding a 5-channel IDE RAID controller is probably not as easy (and not
as cheap) as a dual-channel SCSI RAID controller.  Also, SCSI buses are
much better about sharing bandwidth than IDE, as IDE doesn't have some of
the "nifty" features that SCSI does.  And to go one further, hot-swappable
SCA bays are pretty common in server chassis.  I simply plugged the RAID
controller into the SCA backplanes, and was done.  Had I gone IDE, there
would have been additional cost in obtaining the hot-swap IDE bays.

  As an aside, if you do go with a 3ware card, you might NOT want to use
RAID 5.  The processors on the card are not up to the computing demands of
RAID 5, you might want to take a look at:

http://marc.theaimsgroup.com/?l=linux-xfs&m=99970690219042&w=2


> >No reason not to go 2GB.  Ram is cheap these days, and you can always
> >increase shared buffers and caches to actually fill the server memory
> >up with as much quick-fetch info as possible.
>
> But then why not 4G?  I would love some real numbers rather than 'a
> lot'.  With oracle,  you can plug in some numbers and a real extimate
> will be spit out.  I've worked with DB servers w/ 14G of ram that were
> killing that,  so "get a lot" isn't really good enough.

  We run 1.5 gigs, and that's plenty for us.  I increased the shared
buffers until it didn't help any more, then doubled it, I believe that it
came out to around 128 gigs.  I did the same with sort memory, that came
out to around 64 megs.  The machine right now uses about 860 megs of disk
cache, but took a few months to ge that high.  It hasn't used swap at all.
If it ever hits swap, we'll add more.  Luckily, with the 4-way interleaved
memory, it'll take up to 16 gigs, and with 16 slots, there's a lot of room
to add more. : )

steve