"fburgess@radiantblue.com" <fburgess@radiantblue.com> wrote:
> We have 64GB of Memory on RHEL 6.4
>
> shared_buffers = 8GB
> work_mem = 64MB
> maintenance_work_mem = 1GB
> effective_cache_size = 48GB
> Do these settings look correct for 9.3.2?
Maybe.
What is your max_connections setting?
I find that a good place to start with work_mem is to ignore the
factors you quoted, and to set it somewhere near 25% of machine RAM
divided by max_connections. It might be possible to go up from
there, but monitor closely for peaks of activity which cause enough
memory allocation to flush the OS cache and cause high disk read
rates, killing performance until the cache repopulates. The can
take a while since the high disk read rates slow queries, causing
more of them to compete, leading to higher total work_mem
allocations, and thus preventing recovery from the performance
degradation. In other words, setting this too high leads to
unstable performance. It looks better than a lower setting until
too many users hit Enter at about the same time, causing
performance to collapse for a while.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
