User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Wed, 16 Nov 2005 09:47:26 -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: <BFA0B0AE.13D4E%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Hardware/OS recommendations for large databases
 (
Thread-Index: AcXq0SzleDDBZmahTU6pSm6FUY2s3wABKFlI
In-Reply-To: <1132160978.3582.64.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary=B_3214979246_23519111

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

Scott,

On 11/16/05 9:09 AM, "Scott Marlowe" <smarlowe@g2switchworks.com> wrote:

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

The discussion was kicked off by the suggestion to get 8 dual core CPUs to
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
minutes.  Add 15 and the same ...

OLTP is so different from Business intelligence and Decision Support that
very little of this thread=B9s discussion is relevant IMO.

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.

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)

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

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.

Regards,

- Luke 

--B_3214979246_23519111
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'>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
</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
</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
</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>
- Luke </SPAN></FONT>
</BODY>
</HTML>


--B_3214979246_23519111--