pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: David Griffiths <dgriffiths@boats.com>
To: pgsql-performance@postgresql.org
Subject: Re: Any issues with my tuning...
Date: Mon, 13 Oct 2003 14:32:17 -0700
Message-ID: <200310131432.17735.josh@agliodbs.com> (raw)
In-Reply-To: <039801c391c2$4b83d600$6501a8c0@griffiths2>
References: <039801c391c2$4b83d600$6501a8c0@griffiths2>

David,

> shared_buffers = 96000          # min max_connections*2 or 16, 8KB each

This seems a little high to me, even for 2gb RAM.   What % of your available 
RAM does it work out to?

> effective_cache_size = 6000     # typically 8KB each

This is very, very low.  Given your hardware, I'd set it to 1.5GB.

> Note that I've played with all these values; shared_buffers has been as
> low as 5000, and effective_cache_size has been as high as 50000. Sort
> mem has varied between 1024 bytes and 4096 bytes. wal_buffers have been
> between 16 and 128.

If large updates are slow, increasing checkpoint_segments has the largest 
effect on this.

> Tied up in all this is my inability to grasp what shared_buffers do
>  
> From " http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
> <http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html; ":
>  
> "shbufShared buffers defines a block of memory that PostgreSQL will use
> to hold requests that are awaiting attention from the kernel buffer and
> CPU." and "The shared buffers parameter assumes that OS is going to
> cache a lot of files and hence it is generally very low compared with
> system RAM."

This is correct.   Optimal levels among the people on this list who have 
bothered to do profiling have ranged btw. 6% and 12% of available RAM, but 
never higher.

> From " http://www.lyris.com/lm_help/6.0/tuning_postgresql.html
> <http://www.lyris.com/lm_help/6.0/tuning_postgresql.html; "
>  
> "Increase the buffer size. Postgres uses a shared memory segment among
> its subthreads to buffer data in memory. The default is 512k, which is
> inadequate. On many of our installs, we've bumped it to ~16M, which is
> still small. If you can spare enough memory to fit your whole database
> in memory, do so."

This is absolutely incorrect.  They are confusing shared_buffers with the 
kernel cache, or perhaps confusing PostgreSQL configuration with Oracle 
configuration.

I have contacted Lyris and advised them to update the manual.

> Our database (in Oracle) is just over 4 gig in size; obviously, this
> won't comfortably fit in memory (though we do have an Opteron machine
> inbound for next week with 4-gig of RAM and SCSI hard-drives). The more
> of it we can fit in memory the better.

This is done through increasing the effective_cache_size, which encourages the 
planner to use data kept in the kernel cache.

> What about changing these costs - the doc at
> http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html
> <http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.htm
> l>  doesn't go into a lot of detail. I was thinking that maybe the
> optimizer decided it was faster to do a sequential scan rather than an
> index scan based on an analysis of the cost using these values.
>  
> #random_page_cost = 4           # units are one sequential page fetch
> cost
> #cpu_tuple_cost = 0.01          # (same)
> #cpu_index_tuple_cost = 0.001   # (same)
> #cpu_operator_cost = 0.0025     # (same)

That's because nobody to date has done tests on the effect of tinkering with 
these values on different machines and setups.   We would welcome your 
results.

On high-end machines, random_page_cost almost inevatibly needs to be lowered 
to 2 or even 1.5 to encourage the use of indexes.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




view thread (4+ messages)  latest in thread

Message-ID: <200310131432.17735.josh@agliodbs.com>
Permalink:  ../200310131432.17735.josh@agliodbs.com/
Also on:    postgresql.org/message-id/200310131432.17735.josh@agliodbs.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: josh@agliodbs.com, dgriffiths@boats.com
  Subject: Re: Any issues with my tuning...
  In-Reply-To: <200310131432.17735.josh@agliodbs.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox