Re: Any issues with my tuning... - Mailing list pgsql-performance

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