Re: Hardware/OS recommendations for large databases (

Bill,

On 11/18/05 7:55 AM, "Bill McGonigle" <bill(at)bfccomputing(dot)com> wrote:
>
> There is some truth to it. For an app I'm currently running (full-text
> search using tsearch2 on ~100MB of data) on:

Do you mean 100GB? Sounds like you are more like a decision support
/warehousing application.

> Dev System:
> Asus bare-bones bookshelf case/mobo
> 3GHz P4 w/ HT
> 800MHz memory Bus
> Fedora Core 3 (nightly update)
> 1GB RAM
> 1 SATA Seagate disk (7200RPM, 8MB Cache)
> $800
> worst-case query: 7.2 seconds

About the same machine I posted results for, except I had two faster disks.

> now, the machine I'm deploying to:
>
> Dell SomthingOrOther
> (4) 2.4GHz Xeons
> 533MHz memory bus
> RedHat Enterprise 3.6
> 1GB RAM
> (5) 150000 RPM Ultra SCSI 320 on an Adaptec RAID 5 controller
>> $10000
> same worst-case query: 9.6 seconds

Your problem here is the HW RAID controller - if you dump it and use the
onboard SCSI channels and Linux RAID you will see a jump from 40MB/s to
about 220MB/s in read performance and from 20MB/s to 110MB/s write
performance. It will use less CPU too.

> Now it's not apples-to-apples. There's a kernel 2.4 vs. 2.6 difference
> and the memory bus is much faster and I'm not sure what kind of context
> switching hit you get with the Xeon MP memory controller. On a
> previous postgresql app I did I ran nearly identically spec'ed machines
> except for the memory bus and saw about a 30% boost in performance just
> with the 800MHz bus. I imagine the Opteron bus does even better.

Memory bandwidth is so high on both that it's not a factor. Context
switching / memory bus contention isn't either.

> So the small machine is probably slower on disk but makes up for it in
> single-threaded access to CPU and memory speed. But if this app were to
> be scaled it would make much more sense to cluster several $800
> machines than it would to buy 'big-iron'.

Yes it does - by a lot too. Also, having a multiprocessing executor gets
all of each machine by having multiple CPUs scan simultaneously.

- Luke