Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CEDD16501AB for ; Thu, 30 Oct 2008 13:57:08 -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 56315-06 for ; Thu, 30 Oct 2008 13:56:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from amidala.server.telavista.net (amidala.server.telavista.net [213.239.138.194]) by postgresql.org (Postfix) with ESMTP id EE937650138 for ; Thu, 30 Oct 2008 13:56:55 -0300 (ADT) Received: from firewall.tecnocon.com (gateway.technocon.com [62.132.195.8]) by amidala.server.telavista.net (Postfix) with ESMTP id B56666D790B; Thu, 30 Oct 2008 17:56:47 +0100 (CET) Received: from [192.168.0.129] (unknown [192.168.0.129]) by firewall.tecnocon.com (Postfix) with ESMTP id 93EF7225212; Thu, 30 Oct 2008 17:56:51 +0100 (CET) Message-ID: <4909E730.5010400@technocon.com> Date: Thu, 30 Oct 2008 17:56:16 +0100 From: Christiaan Willemsen User-Agent: Thunderbird 2.0.0.17 (Windows/20080914) MIME-Version: 1.0 To: Scott Carey Cc: pgsql-performance@postgresql.org Subject: Re: Configuring for maximum memory usage References: <4909CF9C.80701@technocon.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------030300060002000305040201" X-svmilter: B56666D790B: result=CLEAN, action=ACCEPT, virus=-1, spam=-1, rbl=0, nbl=0, pbl=0 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/126 X-Sequence-Number: 31707 This is a multi-part message in MIME format. --------------030300060002000305040201 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Hi Scott, Thaks for the clear answers! Scott Carey wrote: > You must either increase the memory that ZFS uses, or increase > Postgresql shard_mem and work_mem to get the aggregate of the two to > use more RAM. > > I believe, that you have not told ZFS to reserve 8GB, but rather told > it to limit itself to 8GB. That is correct, but since it will use the whole 8 GB anyway, I can just as easily say that it will reseve that memory ;) > > Some comments below: > > On Thu, Oct 30, 2008 at 8:15 AM, Christiaan Willemsen > > wrote: > > Hi there, > > I configured OpenSolaris on our OpenSolaris Machine. Specs: > > 2x Quad 2.6 Ghz Xeon > 64 GB of memory > 16x 15k5 SAS > > If you do much writing, and even moreso with ZFS, it is critical to > put the WAL log on a different ZFS volume (and perhaps disks) than the > data and indexes. I already did that. I also have a separate disk pair for the ZFS intent log. > Are you counting both the memory used by postgres and the memory used > by the ZFS ARC cache? It is the combination you are interested in, > and performance will be better if it is biased towards one being a > good chunk larger than the other. In my experience, if you are doing > more writes, a larger file system cache is better, if you are doing > reads, a larger postgres cache is better (the overhead of calling > read() in 8k chunks to the os, even if it is cached, causes CPU use to > increase). No, the figure I gave is this is without the ARC cache. > 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? --------------030300060002000305040201 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Hi Scott,

Thaks for the clear answers!

Scott Carey wrote:
You must either increase the memory that ZFS uses, or increase Postgresql shard_mem and work_mem to get the aggregate of the two to use more RAM.

I believe, that you have not told ZFS to reserve 8GB, but rather told it to limit itself to 8GB. 
That is correct, but since it will use the whole 8 GB anyway, I can just as easily say that it will reseve that memory ;)

Some comments below:

On Thu, Oct 30, 2008 at 8:15 AM, Christiaan Willemsen <cwillemsen@technocon.com> wrote:
Hi there,

I configured OpenSolaris on our OpenSolaris Machine. Specs:

2x Quad 2.6 Ghz Xeon
64 GB of memory
16x 15k5 SAS
If you do much writing, and even moreso with ZFS, it is critical to put the WAL log on a different ZFS volume (and perhaps disks) than the data and indexes.
I already did that. I also have a separate disk pair for the ZFS intent log.

Are you counting both the memory used by postgres and the memory used by the ZFS ARC cache?  It is the combination you are interested in, and performance will be better if it is biased towards one being a good chunk larger than the other.  In my experience, if you are doing more writes, a larger file system cache is better, if you are doing reads, a larger postgres cache is better (the overhead of calling read() in 8k chunks to the os, even if it is cached, causes CPU use to increase).
No, the figure I gave is this is without the ARC cache.
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?
--------------030300060002000305040201--