In-Reply-To: <0F3B99E2-4575-42B8-8AAC-3FE4B231348C@fastcrypt.com>
References: <BFA2AFBA.13FC4%llonergan@greenplum.com>
	<0F3B99E2-4575-42B8-8AAC-3FE4B231348C@fastcrypt.com>
Mime-Version: 1.0 (Apple Message framework v623)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <d555b0eedb008ce34e18463cceb73b04@bfccomputing.com>
Content-Transfer-Encoding: 7bit
Cc: pgsql-performance@postgresql.org
From: Bill McGonigle <bill@bfccomputing.com>
Subject: Re: Hardware/OS recommendations for large databases (
Date: Fri, 18 Nov 2005 10:55:19 -0500
To: Dave Cramer <pg@fastcrypt.com>

On Nov 18, 2005, at 08:00, Dave Cramer wrote:

>> A $1,000 system with one CPU and two SATA disks in a software RAID0 
>> will
>> perform exactly the same as a $80,000 system with 8 dual core CPUs 
>> and the
>> world's best SCSI RAID hardware on a large database for decision 
>> support
>> (what the poster asked about).
>
> Now there's an interesting line drawn in the sand. I presume you have 
> numbers to back this up ?
> This should draw some interesting posts.

There is some truth to it.  For an app I'm currently running (full-text 
search using tsearch2 on ~100MB of data) on:

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

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

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.

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'.

-Bill
-----
Bill McGonigle, Owner           Work: 603.448.4440
BFC Computing, LLC              Home: 603.448.1668
bill@bfccomputing.com           Mobile: 603.252.2606
http://www.bfccomputing.com/    Pager: 603.442.1833
Jabber: flowerpt@gmail.com      Text: bill+text@bfccomputing.com
Blog: http://blog.bfccomputing.com/