regards,
Carl
2011/5/30 Toby Corkindale <toby.corkindale@xxxxxxxxxxxxxxxxxxxx>
On 28/05/11 18:42, Carl von Clausewitz wrote:
a few months ago, when I installed my first PostgreSQL, I have had the[snip]
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):
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
