On Thu, 2008-02-07 at 20:22 +0100, Dawid Kuroczko wrote: > On Feb 5, 2008 10:54 PM, Ron Mayer <[EMAIL PROTECTED]> wrote: > > Decibel! wrote: > > > > > > Yes, this problem goes way beyond OOM. Just try and configure > > > work_memory aggressively on a server that might see 50 database > > > connections, and do it in such a way that you won't swap. Good luck. > > > > That sounds like an even broader and more difficult problem > > than managing memory. > > > > If you have 50 connections that all want to perform large sorts, > > what do you want to have happen? > > > > a) they each do their sorts in parallel with small amounts > > of memory for each; probably all spilling to disk? > > b) they each get a big chunk of memory but some have to > > wait for each other? > > c) something else? > > Something else. :-) > > I think there could be some additional parameter which would > control how much memory there is in total, say: > process_work_mem = 128MB # Some other name needed... > process_work_mem_percent = 20% # Yeah, defenately some other name... > total_work_mem = 1024MB # how much there is for you in total. > > > Your postgres spawns 50 processes which initially don't > use much work_mem. They would all register their current > work_mem usage, in shared memory. > > Each process, when it expects largish sort tries to determine > how much memory there is for the taking, to calculate is own > work_mem. work_mem should not exceed process_work_mem, > and not exceed 20% of total available free mem. > > So, one backend needs to make a huge sort. Determines the > limit for it is 128MB and allocates it. > > Another backend starts sorting. Deletermines the current free > mem is about (1024-128)*20% =~ 179MB. Takes 128MB > > Some time passes, 700MB of total_work_mem is used, and > another backend decides it needs much memory. > It determines its current free mem to be not more than > (1024-700) * 20% =~ 64MB, so it sets it work_mem to 64MB > and sorts away. > > Noooow, I know work_mem is not "total per process limit", but > rather per sort/hash/etc operation. I know the scheme is a bit > sketchy, but I think this would allow more memory-greedy > operations to use memory, while taking in consideration that > they are not the only ones out there. And that these settings > would be more like hints than the actual limits.
I like the sketch and I think we need to look for a solution along those lines. > ....while we are at it -- one feature would be great for 8.4, an > ability to shange shared buffers size "on the fly". I expect > it is not trivial, but would help fine-tuning running database. > I think DBA would need to set maximum shared buffers size > along the normal setting. Perhaps we might go for a mechanism that allows us to increase but not decrease memory. Perhaps that might be easier. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly