User-Agent: Microsoft-Entourage/11.2.1.051004
Date: Wed, 23 Nov 2005 18:29:49 -0800
Subject: Re: Hardware/OS recommendations for large databases (
From: "Luke Lonergan" <llonergan@greenplum.com>
To: "Mark Kirkwood" <markir@paradise.net.nz>
cc: pgsql-performance@postgresql.org,
	"eng@intranet.greenplum.com" <eng@intranet.greenplum.com>
Message-ID: <BFAA659D.145E5%llonergan@greenplum.com>
Thread-Topic: [PERFORM] Hardware/OS recommendations for large databases
 (
Thread-Index: AcXvIxdwfLDwWrP8Q669kHxJ85F1twBe9mid
In-Reply-To: <4382A840.3030401@paradise.net.nz>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary=B_3215615389_18058204

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

Mark,

This is an excellent idea =AD unfortunately I=B9m in Maui right now (Mahalo!)
and I=B9m not getting to testing with this.  My first try was with 8.0.3 and
it=B9s an 8.1 function I presume.

Not to be lazy =AD but any hint as to how to do the same thing for 8.0?

- Luke


On 11/21/05 9:10 PM, "Mark Kirkwood" <markir@paradise.net.nz> wrote:

> Luke Lonergan wrote:
>=20
>> > So that leaves the question - why not more than 64% of the I/O scan ra=
te?
>> > And why is it a flat 64% as the I/O subsystem increases in speed from
>> > 333-400MB/s?
>> >
>=20
> It might be interesting to see what effect reducing the cpu consumption
>   entailed by the count aggregation has - by (say) writing a little bit
> of code to heap scan the desired relation (sample attached).
>=20
> Cheers
>=20
> Mark
>=20
>=20
>=20
>=20
>=20
>=20
> /*
>  * fastcount.c
>  *
>  * Do a count that uses considerably less CPU time than an aggregate.
>  */
>=20
> #include "postgres.h"
>=20
> #include "funcapi.h"
> #include "access/heapam.h"
> #include "catalog/namespace.h"
> #include "utils/builtins.h"
>=20
>=20
> extern Datum fastcount(PG_FUNCTION_ARGS);
>=20
>=20
> PG_FUNCTION_INFO_V1(fastcount);
> Datum
> fastcount(PG_FUNCTION_ARGS)
> {
>  text    *relname =3D PG_GETARG_TEXT_P(0);
>  RangeVar   *relrv;
>  Relation rel;
>  HeapScanDesc scan;
>  HeapTuple tuple;
>  int64  result =3D 0;
>=20
>  /* Use the name to get a suitable range variable and open the relation. =
*/
>  relrv =3D makeRangeVarFromNameList(textToQualifiedNameList(relname));
>  rel =3D heap_openrv(relrv, AccessShareLock);
>=20
>  /* Start a heap scan on the relation. */
>  scan =3D heap_beginscan(rel, SnapshotNow, 0, NULL);
>  while ((tuple =3D heap_getnext(scan, ForwardScanDirection)) !=3D NULL)
>  {
>   result++;
>  }
>=20
>  /* End the scan and close up the relation. */
>  heap_endscan(scan);
>  heap_close(rel, AccessShareLock);
>=20
>=20
>  PG_RETURN_INT64(result);
> }


--B_3215615389_18058204
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'>Mark,=
<BR>
<BR>
This is an excellent idea &#8211; unfortunately I&#8217;m in Maui right now=
 (Mahalo!) and I&#8217;m not getting to testing with this. &nbsp;My first tr=
y was with 8.0.3 and it&#8217;s an 8.1 function I presume.<BR>
<BR>
Not to be lazy &#8211; but any hint as to how to do the same thing for 8.0?=
<BR>
<BR>
- Luke<BR>
<BR>
<BR>
On 11/21/05 9:10 PM, &quot;Mark Kirkwood&quot; &lt;markir@paradise.net.nz&g=
t; wrote:<BR>
<BR>
</SPAN></FONT><BLOCKQUOTE><FONT FACE=3D"Verdana, Helvetica, Arial"><SPAN STYL=
E=3D'font-size:14.0px'>Luke Lonergan wrote:<BR>
<BR>
&gt; So that leaves the question - why not more than 64% of the I/O scan ra=
te?<BR>
&gt; And why is it a flat 64% as the I/O subsystem increases in speed from<=
BR>
&gt; 333-400MB/s?<BR>
&gt;<BR>
<BR>
It might be interesting to see what effect reducing the cpu consumption<BR>
&nbsp;&nbsp;entailed by the count aggregation has - by (say) writing a litt=
le bit<BR>
of code to heap scan the desired relation (sample attached).<BR>
<BR>
Cheers<BR>
<BR>
Mark<BR>
<BR>
<BR>
<BR>
<BR>
<BR>
<HR ALIGN=3DCENTER SIZE=3D"3" WIDTH=3D"95%"></SPAN></FONT><FONT SIZE=3D"2"><FONT FA=
CE=3D"Monaco, Courier New"><SPAN STYLE=3D'font-size:12.0px'>/*<BR>
&nbsp;* fastcount.c<BR>
&nbsp;*<BR>
&nbsp;* Do a count that uses considerably less CPU time than an aggregate.<=
BR>
&nbsp;*/<BR>
<BR>
#include &quot;postgres.h&quot;<BR>
<BR>
#include &quot;funcapi.h&quot;<BR>
#include &quot;access/heapam.h&quot;<BR>
#include &quot;catalog/namespace.h&quot;<BR>
#include &quot;utils/builtins.h&quot;<BR>
<BR>
<BR>
extern Datum fastcount(PG_FUNCTION_ARGS);<BR>
<BR>
<BR>
PG_FUNCTION_INFO_V1(fastcount);<BR>
Datum<BR>
fastcount(PG_FUNCTION_ARGS)<BR>
{<BR>
&nbsp;text&nbsp; &nbsp;&nbsp;*relname =3D PG_GETARG_TEXT_P(0);<BR>
&nbsp;RangeVar &nbsp;&nbsp;*relrv;<BR>
&nbsp;Relation&nbsp;rel;<BR>
&nbsp;HeapScanDesc scan;<BR>
&nbsp;HeapTuple&nbsp;tuple;<BR>
&nbsp;int64&nbsp;&nbsp;result =3D 0;<BR>
<BR>
&nbsp;/* Use the name to get a suitable range variable and open the relatio=
n. */<BR>
&nbsp;relrv =3D makeRangeVarFromNameList(textToQualifiedNameList(relname));<B=
R>
&nbsp;rel =3D heap_openrv(relrv, AccessShareLock);<BR>
<BR>
&nbsp;/* Start a heap scan on the relation. */<BR>
&nbsp;scan =3D heap_beginscan(rel, SnapshotNow, 0, NULL);<BR>
&nbsp;while ((tuple =3D heap_getnext(scan, ForwardScanDirection)) !=3D NULL)<BR=
>
&nbsp;{<BR>
&nbsp;&nbsp;result++;<BR>
&nbsp;}<BR>
<BR>
&nbsp;/* End the scan and close up the relation. */<BR>
&nbsp;heap_endscan(scan);<BR>
&nbsp;heap_close(rel, AccessShareLock);<BR>
<BR>
<BR>
&nbsp;PG_RETURN_INT64(result);<BR>
}<BR>
</SPAN></FONT></FONT></BLOCKQUOTE><FONT SIZE=3D"2"><FONT FACE=3D"Monaco, Courie=
r New"><SPAN STYLE=3D'font-size:12.0px'><BR>
</SPAN></FONT></FONT>
</BODY>
</HTML>


--B_3215615389_18058204--