> 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
> 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
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
> 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
> #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
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.
Aglio Database Solutions
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly