Skip site navigation (1) Skip section navigation (2)

Re: Tuning

From: Ron <rjpeace(at)earthlink(dot)net>
To: pgsql-performance(at)postgresql(dot)org
Cc: "John Parnefjord" <John(dot)Parnefjord(at)kib(dot)ki(dot)se>, Josh Berkus <josh(at)agliodbs(dot)com>
Subject: Re: Tuning
Date: 2007-01-29 16:14:43
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
At 06:24 PM 1/28/2007, Josh Berkus wrote:
> > -work_mem
>Depends on the number of concurrent queries you expect to run and what size
>sorts you expect them to do.
EXPLAIN ANALYZE is your friend.  It will tell you how much data each 
query is manipulating and therefore how much memory each query will chew.

The next step is to figure out how many of each query will be running 
Summing those will tell you the maximum work_mem each kind of query 
will be capable of using.

If you have a deep enough understanding of how your pg system is 
working, then you can set work_mem on a per query basis to get the 
most efficient use of the RAM in your system.

> > -maintenance_work_mem - 50% of the largest table?
>Actually, in current code I've found that anything over 256mb 
>doesn't actually
>get used.
Is this considered a bug?  When will this limit go away?  Does 
work_mem have a similar limit?

> > -shared_buffers - max value 50000
>Actually, I need to update that.   On newer faster multi-core 
>machines you may
>want to allocate up to 1GB of shared buffers.
> > -effective_cache_size - max 2/3 of available ram, ie 24GB on the
> > hardware described above
Why?  "max of 2/3 of available RAM" sounds a bit 
hand-wavy.  Especially with 32gb, 64GB, and 128GB systems available.

Is there are hidden effective or hard limit  here as well?

For a dedicated pg machine, I'd assume one would want to be very 
aggressive about configuring the kernel, minimizing superfluous 
services, and configuring memory use so that absolutely as much as 
possible is being used by pg and in the most intelligent way given 
one's specific pg usage scenario.

> > -shmmax - how large dare I set this value on dedicated postgres servers?
>Set it to 2GB and you'll be covered.
I thought  that on 32b systems the 2GB shmmax limit had been raised to 4GB?
and that there essentially is no limit to shmmax on 64b systems?

What are Oracle and EnterpriseDB recommending for shmmax these days?

My random thoughts,
Ron Peacetree 

In response to

  • Re: Tuning at 2007-01-28 23:24:24 from Josh Berkus


  • Re: Tuning at 2007-01-30 10:05:03 from John Parnefjord

pgsql-performance by date

Next:From: Pomarede NicolasDate: 2007-01-29 16:22:22
Subject: int4 vs varchar to store ip addr
Previous:From: ArnauDate: 2007-01-29 15:40:57
Subject: Re: [OT] Very strange postgresql behaviour

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group