On 10/09/2013 10:45 AM, Bruce Momjian wrote:
On Wed, Oct 9, 2013 at 04:40:38PM +0200, Pavel Stehule wrote:Effectively, if every session uses one full work_mem, you end up with total work_mem usage equal to shared_buffers.We can try a different algorithm to scale up work_mem, but it seems wise to auto-scale it up to some extent based on shared_buffers. In my experience a optimal value of work_mem depends on data and load, so I prefer a work_mem as independent parameter.But it still is an independent parameter. I am just changing the default.
The danger with work_mem especially is that setting it too high can lead to crashing postgres or your system at some stage down the track, so autotuning it is kinda dangerous, much more dangerous than autotuning shared buffers.
The assumption that each connection won't use lots of work_mem is also false, I think, especially in these days of connection poolers.
I'm not saying don't do it, but I think we need to be quite conservative about it. A reasonable default might be (shared_buffers / (n * max_connections)) FSVO n, but I'm not sure what n should be. Instinct says something like 4, but I have no data to back that up.
cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
