Message-ID: <4C24E212.2020504@2ndquadrant.com>
Date: Fri, 25 Jun 2010 13:06:26 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
CC: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
	Yeb Havinga <yebhavinga@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: sudden spurt in swap utilization (was:cpu bound 	postgresql
	setup.)
References: <AANLkTinDBPQL-SjLaCr7AVgzouWbRrjwA6MAgBih_ciy@mail.gmail.com>
	<4C2492F9.2020706@gmail.com>
	<AANLkTikUDoXMF-7N7csuYPWIzn9gSKm_g4NnmjvAYu8N@mail.gmail.com>
	<4C2477A20200002500032B03@gw.wicourts.gov>
	<AANLkTimPhkr2nvuLo-fpBfUdN08KsDFTwzL9ubJP5VG2@mail.gmail.com>
	<AANLkTikJEyqi_8WRI6FjH3OBnnIj4SV9EbTBLl2EGCZw@mail.gmail.com>
In-Reply-To: <AANLkTikJEyqi_8WRI6FjH3OBnnIj4SV9EbTBLl2EGCZw@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Rajesh Kumar Mallah wrote:
>      default_statistics_target = 50 # pgtune wizard 2010-06-25  
> (current 100 via default)
> (*) effective_cache_size = 22GB # pgtune wizard 2010-06-25 (18GB , 
> specified)
>      checkpoint_segments = 16 # pgtune wizard 2010-06-25 (30 , specified)

You probably want to keep your existing values for all of these.  Your 
effective_cache_size setting may be a little low, but I wouldn't worry 
about changing that right now--you have bigger problems right now.

> (*) maintenance_work_mem = 1GB # pgtune wizard 2010-06-25 (16MB via 
> default)
> (*) wal_buffers = 8MB # pgtune wizard 2010-06-25 ( 64kb , via default)
>      checkpoint_completion_target = 0.9 # pgtune wizard 2010-06-25 
> (0.5 via default)
>      shared_buffers = 7680MB # pgtune wizard 2010-06-25 ( 4096 MB , 
> specified)

These are all potentially better for your system, but you'll use more 
RAM if you make these changes.  For example, if you're having swap 
trouble, you definitely don't want to increase maintenance_work_mem.

I suspect that 8GB of shared_buffers is probably the most you want to 
use.  Most systems stop gaining any more benefit from that somewhere 
between 8GB and 10GB, and instead performance gets worse; it's better to 
be on the low side of that drop.  You can probably support 8GB just fine 
if you sort out the work_mem issues.

> (*) max_connections = 80 # pgtune wizard 2010-06-25 ( 300 , ;-)  
> specified )
>      work_mem = 192MB # pgtune wizard 2010-06-25  (256MB , specified)

pgtune makes a guess at how many connections you'll have based on 
specified workload.  If you know you have more connections than that, 
you should specify that on the command line:

pgtune -c 300 ...

It will then re-compute the work_mem figure more accurately using that 
higher connection count.  Right now, it's guessing 192MB based on 80 
connections, which is on the high side of reasonable.  192MB with *300* 
connections is way oversized.  My rough computation says that if you 
tell it the number of connections correctly, pgtune will suggest to you 
around 50MB for work_mem.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us