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 6BC05DB835 for ; Fri, 18 Nov 2005 11:25:48 -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 39852-04 for ; Fri, 18 Nov 2005 15:25:51 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from net2.micro-automation.com (net2.micro-automation.com [64.7.141.29]) by svr1.postgresql.org (Postfix) with SMTP id 42E45DBAAE for ; Fri, 18 Nov 2005 11:25:44 -0400 (AST) Received: (qmail 16600 invoked from network); 18 Nov 2005 15:25:49 -0000 Received: from dcdsl.ebox.com (HELO ?192.168.1.2?) (davec@64.7.143.116) by net2.micro-automation.com with SMTP; 18 Nov 2005 15:25:49 -0000 In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v746.2) Content-Type: multipart/alternative; boundary=Apple-Mail-55--598469618 Message-Id: Cc: "Greg Stark" , "Joshua Marsh" , pgsql-performance@postgresql.org From: Dave Cramer Subject: Re: Hardware/OS recommendations for large databases ( Date: Fri, 18 Nov 2005 10:25:52 -0500 To: Luke Lonergan X-Mailer: Apple Mail (2.746.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.129 required=5 tests=[AWL=-0.128, HTML_FONT_BIG=0.256, HTML_MESSAGE=0.001] X-Spam-Score: 0.129 X-Spam-Level: X-Archive-Number: 200511/379 X-Sequence-Number: 15636 --Apple-Mail-55--598469618 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed Luke, Interesting numbers. I'm a little concerned about the use of blockdev =20= =97setra 16384. If I understand this correctly it assumes that the =20 table is contiguous on the disk does it not ? Dave On 18-Nov-05, at 10:13 AM, Luke Lonergan wrote: > Dave, > > On 11/18/05 5:00 AM, "Dave Cramer" wrote: > > > > Now there's an interesting line drawn in the sand. I presume you =20 > have > > numbers to back this up ? > > > > 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 =20 >> Linux kernel. >> >> On a single table with 15 columns (the Bizgres IVP) at a size =20 >> double memory (2.12GB), Postgres 8.0.3 with Bizgres enhancements =20 >> takes 32 seconds to scan the table: that=92s 66 MB/s. Not the =20 >> efficiency I=92d hope from the onboard SATA controller that I=92d =20 >> like, I would have expected to get 85% of the 100MB/s raw read =20 >> performance. >> >> So that=92s $1,200 / 66 MB/s (without adjusting for 2003 price =20 >> versus now) =3D 18.2 $/MB/s >> >> Raw data: >> [llonergan@kite4 IVP]$ cat scan.sh >> #!/bin/bash >> >> time psql -c "select count(*) from ivp.bigtable1" dgtestdb >> [llonergan@kite4 IVP]$ cat sysout1 >> count >> ---------- >> 10000000 >> (1 row) >> >> >> real 0m32.565s >> user 0m0.002s >> sys 0m0.003s >> >> Size of the table data: >> [llonergan@kite4 IVP]$ du -sk dgtestdb/base >> 2121648 dgtestdb/base >> > System B: >> This system is running an XFS filesystem, and has been tuned to =20 >> use very large (16MB) readahead. It=92s running the Centos 4.1 =20 >> distro, which uses a Linux 2.6.9 kernel. >> >> Same test as above, but with 17GB of data takes 69.7 seconds to =20 >> scan (!) That=92s 244.2MB/s, which is obviously double my earlier =20= >> point of 110-120MB/s. This system is running with a 16MB Linux =20 >> readahead setting, let=92s try it with the default (I think) setting =20= >> of 256KB =96 AHA! Now we get 171.4 seconds or 99.3MB/s. >> >> So, using the tuned setting of =93blockdev =97setra 16384=94 we get =20= >> $6,000 / 244MB/s =3D 24.6 $/MB/s >> If we use the default Linux setting it=92s 2.5x worse. >> >> Raw data: >> [llonergan@modena2 IVP]$ cat scan.sh >> #!/bin/bash >> >> time psql -c "select count(*) from ivp.bigtable1" dgtestdb >> [llonergan@modena2 IVP]$ cat sysout3 >> count >> ---------- >> 80000000 >> (1 row) >> >> >> real 1m9.875s >> user 0m0.000s >> sys 0m0.004s >> [llonergan@modena2 IVP]$ !du >> du -sk dgtestdb/base >> 17021260 dgtestdb/base > > Summary: > > OK =96 you can get more I/O bandwidth out of the =20 > current I/O path for sequential scan if you tune the filesystem for =20= > large readahead. This is a cheap alternative to overhauling the =20 > executor to use asynch I/O. > > Still, there is a CPU limit here =96 this is not I/O bound, it is CPU =20= > limited as evidenced by the sensitivity to readahead settings. If =20= > the filesystem could do 1GB/s, you wouldn=92t go any faster than =20 > 244MB/s. > > - Luke --Apple-Mail-55--598469618 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=WINDOWS-1252 Luke,

Interesting numbers. I'm a little concerned = about the use of=A0blockdev =97setra 16384. If I understand this correctly = it assumes that the table is contiguous on the disk does it not = ?


Dave
On 18-Nov-05, at = 10:13 AM, Luke Lonergan wrote:

Dave,

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

Part 2: The answer

System A:
=
This system is = running RedHat 3 Update 4, with a Fedora 2.6.10 Linux kernel.

= 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=92s 66 MB/s. =A0Not the efficiency I=92d = hope from the onboard SATA controller that I=92d like, I would have = expected to get 85% of the 100MB/s raw read performance.

So = that=92s $1,200 / 66 MB/s (without adjusting for 2003 price versus now) = =3D 18.2 $/MB/s

Raw data:
[llonergan@kite4 = IVP]$ cat scan.sh
#!/bin/bash

time psql -c "select = count(*) from ivp.bigtable1" dgtestdb
[llonergan@kite4 IVP]$ cat = sysout1
=A0=A0count =A0=A0
----------
=A010000000
(1 = row)


real =A0=A0=A00m32.565s
user =A0=A0=A00m0.002s
= sys =A0=A0=A0=A00m0.003s

Size of the table data:
= [llonergan@kite4 IVP]$ du -sk dgtestdb/base
2121648 = dgtestdb/base

=
System B:
=
This = system is running an XFS filesystem, and has been tuned to use very = large (16MB) readahead. =A0It=92s running the Centos 4.1 distro, which = uses a Linux 2.6.9 kernel.

Same test as above, but with 17GB of = data takes 69.7 seconds to scan (!) =A0That=92s 244.2MB/s, which is = obviously double my earlier point of 110-120MB/s. =A0This system is = running with a 16MB Linux readahead setting, let=92s try it with the = default (I think) setting of 256KB =96 AHA! Now we get 171.4 seconds or = 99.3MB/s.

So, using the tuned setting of =93blockdev =97setra = 16384=94 we get $6,000 / 244MB/s =3D 24.6 $/MB/s
If we use the = default Linux setting it=92s 2.5x worse.

Raw data:
= [llonergan@modena2 IVP]$ cat scan.sh =
#!/bin/bash

time psql -c "select count(*) from = ivp.bigtable1" dgtestdb
[llonergan@modena2 IVP]$ cat sysout3
= =A0=A0count =A0=A0
----------
=A080000000
(1 row)

=
real =A0=A0=A01m9.875s
user =A0=A0=A00m0.000s
sys = =A0=A0=A0=A00m0.004s
[llonergan@modena2 IVP]$ !du
du -sk = dgtestdb/base
17021260 =A0=A0=A0=A0=A0=A0=A0dgtestdb/base
=

Summary:

<cough, cough> OK =96 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. =A0This is a cheap alternative = to overhauling the executor to use asynch I/O.

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

- Luke =

= --Apple-Mail-55--598469618--