X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2E7CEDBA5F for ; Fri, 18 Nov 2005 10:04:59 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25064-01 for ; Fri, 18 Nov 2005 14:05:01 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.mi8.com (d01gw04.mi8.com [63.240.6.44]) by svr1.postgresql.org (Postfix) with ESMTP id 23503DB77A for ; Fri, 18 Nov 2005 10:04:55 -0400 (AST) Received: from 172.16.1.112 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D4)); Fri, 18 Nov 2005 09:04:47 -0500 X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230 Received: from MI8NYCMAIL06.Mi8.com ([172.16.1.175]) by D01SMTP02.Mi8.com with Microsoft SMTPSVC(6.0.3790.211); Fri, 18 Nov 2005 09:04:25 -0500 Received: from 69.181.100.71 ([69.181.100.71]) by MI8NYCMAIL06.Mi8.com ( [172.16.1.219]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.104]) with Microsoft Exchange Server HTTP-DAV ; Fri, 18 Nov 2005 09:04:24 -0500 User-Agent: Microsoft-Entourage/11.2.1.051004 Date: Fri, 18 Nov 2005 06:04:24 -0800 Subject: Re: Hardware/OS recommendations for large databases ( From: "Luke Lonergan" To: "Dave Cramer" cc: "Greg Stark" , "Joshua Marsh" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Hardware/OS recommendations for large databases ( Thread-Index: AcXsQAi9HGMh4pFwSiam1ZsLnxnClAACPIaY In-Reply-To: <0F3B99E2-4575-42B8-8AAC-3FE4B231348C@fastcrypt.com> MIME-Version: 1.0 X-OriginalArrivalTime: 18 Nov 2005 14:04:25.0376 (UTC) FILETIME=[FBA71A00:01C5EC48] X-WSS-ID: 6F6300E531S14414958-23-01 Content-Type: multipart/alternative; boundary=B_3215138664_1920020 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.254 required=5 tests=[AWL=-0.000, HTML_MESSAGE=0.001, RCVD_NUMERIC_HELO=1.253] X-Spam-Score: 1.254 X-Spam-Level: * X-Archive-Number: 200511/352 X-Sequence-Number: 15609 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3215138664_1920020 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Dave, On 11/18/05 5:00 AM, "Dave Cramer" wrote: >=20 > Now there's an interesting line drawn in the sand. I presume you have > numbers to back this up ? >=20 > This should draw some interesting posts. OK, here we go: The $1,000 system (System A): - I bought 16 of these in 2003 for $1,200 each. They have Intel or Asus motherboards, Intel P4 3.0GHz CPUs with an 800MHz FSB. They have a system drive and two RAID0 SATA drives, the Western Digital 74GB Raptor (10K RPM). They have 1GB of RAM. * A test of write and read performance on the RAID0: > [llonergan@kite4 raid0]$ time dd if=3D/dev/zero of=3Dbigfile bs=3D8k count=3D2500= 00 > 250000+0 records in > 250000+0 records out >=20 > real 0m17.453s > user 0m0.249s > sys 0m10.246s > [llonergan@kite4 raid0]$ time dd if=3Dbigfile of=3D/dev/null bs=3D8k > 250000+0 records in > 250000+0 records out >=20 > real 0m18.930s > user 0m0.130s > sys 0m3.590s > So, the write performance is 114MB/s and read performance is 106MB/s. The $6,000 system (System B): * I just bought 5 of these systems for $6,000 each. They are dual Opteron systems with 8GB of RAM and 2x 250 model CPUs, which are close to the fastest. They have the new 3Ware 9550SX SATA RAID adapters coupled to Western Digital 400GB RE2 model hard drives. They are organized as a RAID5= . * A test of write and read performance on the RAID5: > [root@modena2 dbfast1]# time dd if=3D/dev/zero of=3Dbigfile bs=3D8k count=3D20000= 00 > 2000000+0 records in > 2000000+0 records out >=20 > real 0m51.441s > user 0m0.288s > sys 0m29.119s >=20 > [root@modena2 dbfast1]# time dd if=3Dbigfile of=3D/dev/null bs=3D8k > 2000000+0 records in > 2000000+0 records out >=20 > real 0m39.605s > user 0m0.244s > sys 0m19.207s >=20 > So, the write performance is 314MB/s and read performance is 404MB/s (!) = This > is the fastest I=B9ve seen 8 disk drives perform. >=20 So, the question is: which of these systems (A or B) can scan a large table faster using non-MPP postgres? How much faster would you wager? Send your answer, and I=B9ll post the result. Regards, - Luke --B_3215138664_1920020 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Re: [PERFORM] Hardware/OS recommendations for large databases (</TIT= LE> </HEAD> <BODY> <FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-size:14.0px'>Dave,= <BR> <BR> <BR> On 11/18/05 5:00 AM, "Dave Cramer" <pg@fastcrypt.com> wrote= :<BR> <FONT COLOR=3D"#0000FF">> <BR> > Now there's an interesting line drawn in the sand. I presume you have = <BR> > numbers to back this up ?<BR> > <BR> > This should draw some interesting posts.<BR> </FONT><BR> OK, here we go:<BR> <BR> The $1,000 system (System A):<BR> <BR> - I bought 16 of these in 2003 for $1,200 each. They have Intel or Asus mot= herboards, Intel P4 3.0GHz CPUs with an 800MHz FSB.  They have a system= drive and two RAID0 SATA drives, the Western Digital 74GB Raptor (10K RPM).=  They have 1GB of RAM.<BR> <BR> </SPAN></FONT><UL><LI><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'f= ont-size:14.0px'>A test of write and read performance on the RAID0:<BR> </SPAN></FONT></UL><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font= -size:14.0px'><BR> </SPAN></FONT><BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Courie= r New">[llonergan@kite4 raid0]$ time dd if=3D/dev/zero of=3Dbigfile bs=3D8k count=3D= 250000<BR> 250000+0 records in<BR> 250000+0 records out<BR> <BR> real    0m17.453s<BR> user    0m0.249s<BR> sys     0m10.246s<BR> </FONT></SPAN></BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verda= na, Helvetica, Arial"><BR> </FONT></SPAN><BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Courie= r New">[llonergan@kite4 raid0]$ time dd if=3Dbigfile of=3D/dev/null bs=3D8k<BR> 250000+0 records in<BR> 250000+0 records out<BR> <BR> real    0m18.930s<BR> user    0m0.130s<BR> sys     0m3.590s<BR> </FONT></SPAN></BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verda= na, Helvetica, Arial"><BR> </FONT></SPAN><BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verdan= a, Helvetica, Arial">So, the write performance is 114MB/s and read performan= ce is 106MB/s.<BR> </FONT></SPAN></BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verda= na, Helvetica, Arial"><BR> The $6,000 system (System B):<BR> <BR> </FONT></SPAN><UL><LI><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verdana, H= elvetica, Arial">I just bought 5 of these systems for $6,000 each.  The= y are dual Opteron systems with 8GB of RAM and 2x 250 model CPUs, which are = close to the fastest.  They have the new 3Ware 9550SX SATA RAID adapter= s coupled to  Western Digital 400GB RE2 model hard drives.  They a= re organized as a RAID5.<BR> </FONT></SPAN></UL><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verdana, Helv= etica, Arial"><BR> </FONT></SPAN><UL><LI><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verdana, H= elvetica, Arial">A test of write and read performance on the RAID5:<BR> </FONT></SPAN></UL><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verdana, Helv= etica, Arial"><BR> </FONT></SPAN><BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Courie= r New">[root@modena2 dbfast1]# time dd if=3D/dev/zero of=3Dbigfile bs=3D8k count=3D2= 000000<BR> 2000000+0 records in<BR> 2000000+0 records out<BR> <BR> real    0m51.441s<BR> user    0m0.288s<BR> sys     0m29.119s<BR> <BR> [root@modena2 dbfast1]# time dd if=3Dbigfile of=3D/dev/null bs=3D8k<BR> 2000000+0 records in<BR> 2000000+0 records out<BR> <BR> real    0m39.605s<BR> user    0m0.244s<BR> sys     0m19.207s<BR> <BR> </FONT><FONT FACE=3D"Verdana, Helvetica, Arial">So, the write performance is = 314MB/s and read performance is 404MB/s (!)  This is the fastest I̵= 7;ve seen 8 disk drives perform.<BR> <BR> </FONT></SPAN></BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verda= na, Helvetica, Arial">So, the question is: which of these systems (A or B) c= an scan a large table faster using non-MPP postgres?  How much faster w= ould you wager?<BR> <BR> Send your answer, and I’ll post the result.<BR> <BR> Regards,<BR> <BR> - Luke</FONT></SPAN> </BODY> </HTML> --B_3215138664_1920020--