User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Fri, 18 Nov 2005 07:13:42 -0800
Subject: Re: Hardware/OS recommendations for large databases (
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Dave Cramer" <pg@fastcrypt.com>
cc: "Greg Stark" <gsstark@mit.edu>, "Joshua Marsh" <icub3d@gmail.com>,
	pgsql-performance@postgresql.org
Message-ID: <BFA32FA6.14027%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Hardware/OS recommendations for large databases
 (
Thread-Index: AcXsQAi9HGMh4pFwSiam1ZsLnxnClAAEqB0s
In-Reply-To: <0F3B99E2-4575-42B8-8AAC-3FE4B231348C@fastcrypt.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary=B_3215142822_2162763

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

Dave,

On 11/18/05 5:00 AM, "Dave Cramer" <pg@fastcrypt.com> 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.

Part 2: The answer

System A:
> This system is running RedHat 3 Update 4, with a Fedora 2.6.10 Linux kern=
el.
>=20
> On a single table with 15 columns (the Bizgres IVP) at a size double memo=
ry
> (2.12GB), Postgres 8.0.3 with Bizgres enhancements takes 32 seconds to sc=
an
> the table: that=B9s 66 MB/s.  Not the efficiency I=B9d hope from the onboard =
SATA
> controller that I=B9d like, I would have expected to get 85% of the 100MB/s=
 raw
> read performance.
>=20
> So that=B9s $1,200 / 66 MB/s (without adjusting for 2003 price versus now) =
=3D
> 18.2 $/MB/s
>=20
> Raw data:
> [llonergan@kite4 IVP]$ cat scan.sh
> #!/bin/bash
>=20
> time psql -c "select count(*) from ivp.bigtable1" dgtestdb
> [llonergan@kite4 IVP]$ cat sysout1
>   count  =20
> ----------
>  10000000
> (1 row)
>=20
>=20
> real    0m32.565s
> user    0m0.002s
> sys     0m0.003s
>=20
> Size of the table data:
> [llonergan@kite4 IVP]$ du -sk dgtestdb/base
> 2121648 dgtestdb/base
>=20
System B:
> This system is running an XFS filesystem, and has been tuned to use very =
large
> (16MB) readahead.  It=B9s running the Centos 4.1 distro, which uses a Linux
> 2.6.9 kernel.
>=20
> Same test as above, but with 17GB of data takes 69.7 seconds to scan (!)
> That=B9s 244.2MB/s, which is obviously double my earlier point of 110-120MB=
/s.
> This system is running with a 16MB Linux readahead setting, let=B9s try it =
with
> the default (I think) setting of 256KB =AD AHA! Now we get 171.4 seconds or
> 99.3MB/s.
>=20
> So, using the tuned setting of =B3blockdev =8Bsetra 16384=B2 we get $6,000 / 24=
4MB/s
> =3D 24.6 $/MB/s
> If we use the default Linux setting it=B9s 2.5x worse.
>=20
> Raw data:
> [llonergan@modena2 IVP]$ cat scan.sh
> #!/bin/bash
>=20
> time psql -c "select count(*) from ivp.bigtable1" dgtestdb
> [llonergan@modena2 IVP]$ cat sysout3
>   count  =20
> ----------
>  80000000
> (1 row)
>=20
>=20
> real    1m9.875s
> user    0m0.000s
> sys     0m0.004s
> [llonergan@modena2 IVP]$ !du
> du -sk dgtestdb/base
> 17021260        dgtestdb/base

Summary:

<cough, cough> OK =AD you can get more I/O bandwidth out of the current I/O
path for sequential scan if you tune the filesystem for large readahead.
This is a cheap alternative to overhauling the executor to use asynch I/O.

Still, there is a CPU limit here =AD this is not I/O bound, it is CPU limited
as evidenced by the sensitivity to readahead settings.   If the filesystem
could do 1GB/s, you wouldn=B9t go any faster than 244MB/s.

- Luke

--B_3215142822_2162763
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'>Dave,=
<BR>
<BR>
On 11/18/05 5:00 AM, &quot;Dave Cramer&quot; &lt;pg@fastcrypt.com&gt; wrote=
:<BR>
<FONT COLOR=3D"#0000FF">&gt; <BR>
&gt; Now there's an interesting line drawn in the sand. I presume you have =
<BR>
&gt; numbers to back this up ?<BR>
&gt; <BR>
&gt; This should draw some interesting posts.<BR>
</FONT><BR>
Part 2: The answer<BR>
<BR>
System A:<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYL=
E=3D'font-size:14.0px'>This system is running RedHat 3 Update 4, with a Fedora=
 2.6.10 Linux kernel.<BR>
<BR>
On a single table with 15 columns (the Bizgres IVP) at a size double memory=
 (2.12GB), Postgres 8.0.3 with Bizgres enhancements takes 32 seconds to scan=
 the table: that&#8217;s 66 MB/s. &nbsp;Not the efficiency I&#8217;d hope fr=
om the onboard SATA controller that I&#8217;d like, I would have expected to=
 get 85% of the 100MB/s raw read performance.<BR>
<BR>
So that&#8217;s $1,200 / 66 MB/s (without adjusting for 2003 price versus n=
ow) =3D 18.2 $/MB/s<BR>
<BR>
Raw data:<BR>
</SPAN></FONT><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Courier New">[llon=
ergan@kite4 IVP]$ cat scan.sh <BR>
#!/bin/bash<BR>
<BR>
time psql -c &quot;select count(*) from ivp.bigtable1&quot; dgtestdb<BR>
[llonergan@kite4 IVP]$ cat sysout1<BR>
&nbsp;&nbsp;count &nbsp;&nbsp;<BR>
----------<BR>
&nbsp;10000000<BR>
(1 row)<BR>
<BR>
<BR>
real &nbsp;&nbsp;&nbsp;0m32.565s<BR>
user &nbsp;&nbsp;&nbsp;0m0.002s<BR>
sys &nbsp;&nbsp;&nbsp;&nbsp;0m0.003s<BR>
<BR>
Size of the table data:<BR>
[llonergan@kite4 IVP]$ du -sk dgtestdb/base<BR>
2121648 dgtestdb/base<BR>
</FONT><FONT FACE=3D"Verdana, Helvetica, Arial"><BR>
</FONT></SPAN></BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verda=
na, Helvetica, Arial">System B:<BR>
</FONT></SPAN><BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Verdan=
a, Helvetica, Arial">This system is running an XFS filesystem, and has been =
tuned to use very large (16MB) readahead. &nbsp;It&#8217;s running the Cento=
s 4.1 distro, which uses a Linux 2.6.9 kernel.<BR>
<BR>
Same test as above, but with 17GB of data takes 69.7 seconds to scan (!) &n=
bsp;That&#8217;s 244.2MB/s, which is obviously double my earlier point of 11=
0-120MB/s. &nbsp;This system is running with a 16MB Linux readahead setting,=
 let&#8217;s try it with the default (I think) setting of 256KB &#8211; AHA!=
 Now we get 171.4 seconds or 99.3MB/s.<BR>
<BR>
So, using the tuned setting of &#8220;blockdev &#8212;setra 16384&#8221; we=
 get $6,000 / 244MB/s =3D 24.6 $/MB/s<BR>
If we use the default Linux setting it&#8217;s 2.5x worse.<BR>
<BR>
Raw data:<BR>
</FONT><FONT FACE=3D"Courier New">[llonergan@modena2 IVP]$ cat scan.sh <BR>
#!/bin/bash<BR>
<BR>
time psql -c &quot;select count(*) from ivp.bigtable1&quot; dgtestdb<BR>
[llonergan@modena2 IVP]$ cat sysout3<BR>
&nbsp;&nbsp;count &nbsp;&nbsp;<BR>
----------<BR>
&nbsp;80000000<BR>
(1 row)<BR>
<BR>
<BR>
real &nbsp;&nbsp;&nbsp;1m9.875s<BR>
user &nbsp;&nbsp;&nbsp;0m0.000s<BR>
sys &nbsp;&nbsp;&nbsp;&nbsp;0m0.004s<BR>
[llonergan@modena2 IVP]$ !du<BR>
du -sk dgtestdb/base<BR>
17021260 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dgtestdb/base<BR>
</FONT></SPAN></BLOCKQUOTE><SPAN STYLE=3D'font-size:14.0px'><FONT FACE=3D"Couri=
er New"><BR>
</FONT><FONT FACE=3D"Verdana, Helvetica, Arial">Summary:<BR>
<BR>
&lt;cough, cough&gt; OK &#8211; you can get more I/O bandwidth out of the c=
urrent I/O path for sequential scan if you tune the filesystem for large rea=
dahead. &nbsp;This is a cheap alternative to overhauling the executor to use=
 asynch I/O.<BR>
<BR>
Still, there is a CPU limit here &#8211; this is not I/O bound, it is CPU l=
imited as evidenced by the sensitivity to readahead settings. &nbsp;&nbsp;If=
 the filesystem could do 1GB/s, you wouldn&#8217;t go any faster than 244MB/=
s.<BR>
<BR>
- Luke</FONT></SPAN>
</BODY>
</HTML>


--B_3215142822_2162763--