Re: sudden spurt in swap utilization (was:cpu bound postgresql setup.)

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(at)2ndQuadrant(dot)com www.2ndQuadrant.us