Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5507A64FDF3 for ; Thu, 30 Oct 2008 13:43:20 -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 52817-07 for ; Thu, 30 Oct 2008 13:43:16 -0300 (ADT) X-Greylist: delayed 00:24:50.807396 by SQLgrey-1.7.6 Received: from yw-out-2324.google.com (yw-out-2324.google.com [74.125.46.31]) by postgresql.org (Postfix) with ESMTP id 39AEE64FDF1 for ; Thu, 30 Oct 2008 13:43:16 -0300 (ADT) Received: by yw-out-2324.google.com with SMTP id 3so263052ywj.73 for ; Thu, 30 Oct 2008 09:43:15 -0700 (PDT) Received: by 10.142.212.19 with SMTP id k19mr2459496wfg.13.1225383503588; Thu, 30 Oct 2008 09:18:23 -0700 (PDT) Received: by 10.143.155.5 with HTTP; Thu, 30 Oct 2008 09:18:23 -0700 (PDT) Message-ID: Date: Thu, 30 Oct 2008 09:18:23 -0700 From: "Scott Carey" To: "Christiaan Willemsen" Subject: Re: Configuring for maximum memory usage Cc: pgsql-performance@postgresql.org In-Reply-To: <4909CF9C.80701@technocon.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9148_10133896.1225383503583" References: <4909CF9C.80701@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/123 X-Sequence-Number: 31704 ------=_Part_9148_10133896.1225383503583 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. 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. > > > The filesystem is configured using ZFS, and I think I have found a > configuration that performs fairly well. > > I installed the standard PostgreSQL that came with the OpenSolaris disk > (8.3), and later added support for PostGIS. All fime. > I also tried to tune postgresql.conf to maximize performance and also > memory usage. > > Since PostgreSQL is the only thing running on this machine, we want it to > take full advantage of the hardware. For the ZFS cache, we have 8 GB > reserved. The rest can be used by postgres. > What setting reserves (but does not limit) ZFS to a memory size? I am not familiar with one that behaves that way, but I could be wrong. Try setting this to 48GB (leaving 16 for the db and misc). > > The problem is getting it to use that much. At the moment, it only uses > almost 9 GB, so by far not enough. The problem is getting it to use more... > I hope you can help me with working config. > 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). > > Here are the parameters I set in the config file: > > shared_buffers = 8192MB You probably want shared_buffers + the ZFS ARC cache ("advanced" file system cache for those unfamiliar with ZFS) to be about 56GB, unless you have a lot of connections and heavily use temp tables or work_mem. In that case make the total less. I recommend trying: shared_buffers = 48GB , ZFS limited to 8GB and shared_buffers = 8GB, ZFS limited to 48GB > > work_mem = 128MB > maintenance_work_mem = 2048MB > max_fsm_pages = 204800 > max_fsm_relations = 2000 > 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. > > Database is about 250 GB in size, so we really need to have as much data as > possible in memory. > > I hope you can help us tweak a few parameters to make sure all memory will > be used. > > > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > ------=_Part_9148_10133896.1225383503583 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. 

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.
 


The filesystem is configured using ZFS, and I think I have found a configuration that performs fairly well.

I installed the standard PostgreSQL that came with the OpenSolaris disk (8.3), and later added support for PostGIS. All fime.
I also tried to tune postgresql.conf to maximize performance and also memory usage.

Since PostgreSQL is the only thing running on this machine, we want it to take full advantage of the hardware. For the ZFS cache, we have 8 GB reserved. The rest can be used by postgres.

What setting reserves (but does not limit) ZFS to a memory size?  I am not familiar with one that behaves that way, but I could be wrong.  Try setting this to 48GB (leaving 16 for the db and misc).
 

The problem is getting it to use that much. At the moment, it only uses almost 9 GB, so by far not enough. The problem is getting it to use more... I hope you can help me with working config.

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).
 

Here are the parameters I set in the config file:

shared_buffers = 8192MB
You probably want shared_buffers + the ZFS ARC cache ("advanced" file system cache for those unfamiliar with ZFS) to be about 56GB, unless you have a lot of connections and heavily use temp tables or work_mem.  In that case make the total less.
I recommend trying:
shared_buffers = 48GB , ZFS limited to 8GB and
shared_buffers = 8GB, ZFS limited to 48GB

work_mem = 128MB
maintenance_work_mem = 2048MB
max_fsm_pages = 204800
max_fsm_relations = 2000

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.
 

Database is about 250 GB in size, so we really need to have as much data as possible in memory.

I hope you can help us tweak a few parameters to make sure all memory will be used.




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

------=_Part_9148_10133896.1225383503583--