Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 7307064FC60
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 30 Oct 2008 14:37:29 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 63808-07 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 30 Oct 2008 14:37:25 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from yx-out-2324.google.com (yx-out-2324.google.com [74.125.44.28])
	by postgresql.org (Postfix) with ESMTP id 5020F64FC5C
	for <pgsql-performance@postgresql.org>;
	Thu, 30 Oct 2008 14:37:25 -0300 (ADT)
Received: by yx-out-2324.google.com with SMTP id 8so280823yxb.73
	for <pgsql-performance@postgresql.org>;
	Thu, 30 Oct 2008 10:37:24 -0700 (PDT)
Received: by 10.142.232.20 with SMTP id e20mr4842553wfh.138.1225388243554;
	Thu, 30 Oct 2008 10:37:23 -0700 (PDT)
Received: by 10.143.155.5 with HTTP; Thu, 30 Oct 2008 10:37:23 -0700 (PDT)
Message-ID: <a1ec7d000810301037iec3e6c5pec2af7a4e3a0c3af@mail.gmail.com>
Date: Thu, 30 Oct 2008 10:37:23 -0700
From: "Scott Carey" <scott@richrelevance.com>
To: "Christiaan Willemsen" <cwillemsen@technocon.com>
Subject: Re: Configuring for maximum memory usage
Cc: pgsql-performance@postgresql.org
In-Reply-To: <4909E730.5010400@technocon.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_11132_18420304.1225388243559"
References: <4909CF9C.80701@technocon.com>
	<a1ec7d000810300918w666f705eva3f8fb720dfd7aaf@mail.gmail.com>
	<4909E730.5010400@technocon.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200810/129
X-Sequence-Number: 31710

------=_Part_11132_18420304.1225388243559
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

>
>  If you do very large aggregates, you may  need  even 1GB on work_mem.
> However, a setting that high would require very careful tuning and reduction
> of space used by shared_buffers and the ZFS ARC.  Its dangerous since each
> connection with a large aggregate or sort may consume a lot of memory.
>
> Well, some taks may need a lot, but I guess most wil do fine with the
> settings we used right now.
>
> So It looks like I can tune the ARC to use more memory, and also increase
> shared_mem to let postgres cache more tables?
>

I would recommend tuning one upwards, and leaving the other smaller.  The
worst case is when they are both similarly sized, it leaves the most
opportunity for duplication of data, and produces the worst feedback on
checkpoint writes.
You may want to compare the performance with:
 larger ARC and smaller shared_buffers
vs
 smaller ARC and larger shared_buffers

The results will be rather dependent on how you use postgres, the types of
queries you do, and for writes, how you tune checkpoints and such.

------=_Part_11132_18420304.1225388243559
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br><div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div bgcolor="#ffffff" text="#000000"><br><div class="Ih2E3d">
<blockquote type="cite">
  <div class="gmail_quote">If you do very large aggregates, you may&nbsp;
need&nbsp; even 1GB on work_mem.&nbsp; However, a setting that high would require
very careful tuning and reduction of space used by shared_buffers and
the ZFS ARC.&nbsp; Its dangerous since each connection with a large
aggregate or sort may consume a lot of memory.<br>
  </div>
</blockquote></div>
Well, some taks may need a lot, but I guess most wil do fine with the
settings we used right now.<br>
<br>
So It looks like I can tune the ARC to use more memory, and also
increase shared_mem to let postgres cache more tables?<br>
</div>

</blockquote></div><br>I would recommend tuning one upwards, and leaving the other smaller.&nbsp; The worst case is when they are both similarly sized, it leaves the most opportunity for duplication of data, and produces the worst feedback on checkpoint writes.<br>
You may want to compare the performance with:<br>&nbsp;larger ARC and smaller shared_buffers <br>vs<br>&nbsp;smaller ARC and larger shared_buffers<br><br>The results will be rather dependent on how you use postgres, the types of queries you do, and for writes, how you tune checkpoints and such.<br>

------=_Part_11132_18420304.1225388243559--