User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Wed, 16 Nov 2005 09:49:28 -0800
Subject: Re: Hardware/OS recommendations for large databases (
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Scott Marlowe" <smarlowe@g2switchworks.com>,
	"William Yu" <wyu@talisys.com>
cc: pgsql-performance@postgresql.org
Message-ID: <BFA0B128.13D50%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Hardware/OS recommendations for large databases
 (
Thread-Index: AcXq0SzleDDBZmahTU6pSm6FUY2s3wABKFlIAAASLus=
In-Reply-To: <BFA0B0AE.13D4E%llonergan@greenplum.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary=B_3214979369_23551637

> 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_3214979369_23551637
Content-Type: text/plain;
 charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Oops,

Last point should be worded: =B3All CPUs on all machines used by a parallel
database=B2

- Luke=20


On 11/16/05 9:47 AM, "Luke Lonergan" <llonergan@greenplum.com> wrote:

> Scott,
>=20
> On 11/16/05 9:09 AM, "Scott Marlowe" <smarlowe@g2switchworks.com> wrote:
>=20
>> The biggest gain is going from 1 to 2 CPUs (real cpus, like the DC
>> Opterons or genuine dual CPU mobo, not "hyperthreaded").  Part of the
>> issue isn't just raw CPU processing power.  The second CPU allows the
>> machine to be more responsive because it doesn't have to context switch
>> as much.
>>=20
>> While I've seen plenty of single CPU servers start to bog under load
>> running one big query, the dual CPU machines always seem more than just
>> twice as snappy under similar loads.
>>=20
> I agree, 2 CPUs are better than one in most cases.
>=20
> The discussion was kicked off by the suggestion to get 8 dual core CPUs t=
o
> process a large database with postgres.  Say your decision support query =
takes
> 15 minutes to run with one CPU.  Add another and it still takes 15 minute=
s.
> Add 15 and the same ...
>=20
> OLTP is so different from Business intelligence and Decision Support that=
 very
> little of this thread=B9s discussion is relevant IMO.
>=20
> The job is to design a system that can process sequential scan as fast as
> possible and uses all resources (CPUs, mem, disk channels) on each query.
> Sequential scan is 100x more important than random seeks.
>=20
> Here are the facts so far:
> * Postgres can only use 1 CPU on each query
> * Postgres I/O for sequential scan is CPU limited to 110-120 MB/s on the
> fastest modern CPUs
> * Postgres disk-based sort speed is 1/10 or more slower than commercial
> databases and memory doesn=B9t improve it (much)
>=20
> These are the conclusions that follow about decision support / BI system
> architecture for normal Postgres:
> * I/O hardware with more than 110MB/s of read bandwidth is not useful
> * More than 1 CPU is not useful
> * More RAM than a nominal amount for small table caching is not useful
>=20
> In other words, big SMP doesn=B9t address the problem at all.  By contrast,
> having all CPUs on multiple machines, or even on a big SMP with lots of I=
/O
> channels, solves all of the above issues.
>=20
> Regards,
>=20
- Luke=20


--B_3214979369_23551637
Content-Type: text/html;
 charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<HTML>
<HEAD>
<TITLE>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'>Oops,=
<BR>
<BR>
Last point should be worded: &#8220;All CPUs on all machines used by a para=
llel database&#8221;<BR>
<BR>
- Luke <BR>
<BR>
<BR>
On 11/16/05 9:47 AM, &quot;Luke Lonergan&quot; &lt;llonergan@greenplum.com&=
gt; wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYL=
E=3D'font-size:14.0px'>Scott,<BR>
<BR>
On 11/16/05 9:09 AM, &quot;Scott Marlowe&quot; &lt;smarlowe@g2switchworks.c=
om&gt; wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYL=
E=3D'font-size:14.0px'>The biggest gain is going from 1 to 2 CPUs (real cpus, =
like the DC<BR>
Opterons or genuine dual CPU mobo, not &quot;hyperthreaded&quot;). &nbsp;Pa=
rt of the<BR>
issue isn't just raw CPU processing power. &nbsp;The second CPU allows the<=
BR>
machine to be more responsive because it doesn't have to context switch<BR>
as much.<BR>
<BR>
While I've seen plenty of single CPU servers start to bog under load<BR>
running one big query, the dual CPU machines always seem more than just<BR>
twice as snappy under similar loads.<BR>
<BR>
</SPAN></FONT></BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STY=
LE=3D'font-size:14.0px'>I agree, 2 CPUs are better than one in most cases.<BR>
<BR>
The discussion was kicked off by the suggestion to get 8 dual core CPUs to =
process a large database with postgres. &nbsp;Say your decision support quer=
y takes 15 minutes to run with one CPU. &nbsp;Add another and it still takes=
 15 minutes. &nbsp;Add 15 and the same ...<BR>
<BR>
OLTP is <B>so</B> different from Business intelligence and Decision Support=
 that very little of this thread&#8217;s discussion is relevant IMO.<BR>
<BR>
The job is to design a system that can process sequential scan as fast as p=
ossible and uses all resources (CPUs, mem, disk channels) on each query. &nb=
sp;Sequential scan is 100x more important than random seeks.<BR>
<BR>
Here are the facts so far:<BR>
</SPAN></FONT><UL><LI><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'f=
ont-size:14.0px'>Postgres can only use 1 CPU on each query=20
</SPAN></FONT><LI><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-=
size:14.0px'>Postgres I/O for sequential scan is CPU limited to 110-120 MB/s=
 on the fastest modern CPUs=20
</SPAN></FONT><LI><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-=
size:14.0px'>Postgres disk-based sort speed is 1/10 or more slower than comm=
ercial databases and memory doesn&#8217;t improve it (much)<BR>
</SPAN></FONT></UL><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font=
-size:14.0px'><BR>
These are the conclusions that follow about decision support / BI system ar=
chitecture for normal Postgres:<BR>
</SPAN></FONT><UL><LI><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'f=
ont-size:14.0px'>I/O hardware with more than 110MB/s of read bandwidth is no=
t useful=20
</SPAN></FONT><LI><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-=
size:14.0px'>More than 1 CPU is not useful=20
</SPAN></FONT><LI><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font-=
size:14.0px'>More RAM than a nominal amount for small table caching is not u=
seful<BR>
</SPAN></FONT></UL><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYLE=3D'font=
-size:14.0px'><BR>
In other words, big SMP doesn&#8217;t address the problem at all. &nbsp;By =
contrast, having all CPUs on multiple machines, or even on a big SMP with lo=
ts of I/O channels, solves all of the above issues.<BR>
<BR>
Regards,<BR>
<BR>
</SPAN></FONT></BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STY=
LE=3D'font-size:14.0px'>- Luke <BR>
</SPAN></FONT>
</BODY>
</HTML>


--B_3214979369_23551637--