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