Re: Shared Buffer Size

On 28/05/11 18:42, Carl von Clausewitz wrote:
> a few months ago, when I installed my first PostgreSQL, I have had the
> same problem. I've try to get any information about optimal memory
> config, and working, but there wasn't any "optimal memory setting
> calculator" on the internet, just some guide in the posgre documentation
> (http://www.postgresql.org/docs/9.0/interactive/kernel-resources.html#SYSVIPC).
> I got FreeBSD 8.2 AMD64, with 8 GB of memory (this server is just for
> PostgreSQL and a little PHP app with 2 user), and I have theese setting
> in postgresql.conf (which are not the default):
>
[snip]
> work_mem = 64MB# min 64kB
> maintenance_work_mem = 1024MB# min 1MB
> max_stack_depth = 64MB# min 100kB

Just a warning - but be careful about setting work_mem to high values.
The actual memory used by a query can be many times the value, depending
on the complexity of your query.

In a particular query I saw last week, we were regularly exceeding the
available memory on a server, because the query was requiring 80 times
the value of work_mem, and work_mem had been set to a high value.

Reducing work_mem back to just 4MB reduced memory usage by a couple of
gigabytes, and had almost no effect on the execution time. (Actually, it
was marginally faster - probably because more memory was left for the
operating system's cache)

Toby