[Bizgres-general] Question: Is 64-bit cache support valuable?

[Jim C. Nasby]

On Thu, Jul 21, 2005 at 11:23:56AM +0100, Simon Riggs wrote:
 
> Most important thing is that the memory management needs to be a Top-
> down Memory Allocation scheme. So, all work_mem requests get allocated
> from a single top-level pool. This is defined as total_work_mem which is
> a SUSET parameter allowing the administrator to change it while the
> server is running. This is possible because work_mem is a *dynamic*
> memory limit, not a pre-allocation, like shared_buffers. We may not use
> all of total_work_mem at any point in time.

Something to consider is how this will interact with buffering/caching.
Right now, with the consensus that it's best to let the OS handle
caching, it's important that we don't hold on to memory that's not being
actively used. In the future, if we end up with a caching scheme that's
better than plane OS caching, it will become important to allow unused
memory to be used for buffering.

> Memory allocation calculations are used in the planner to decide whether
> to perform Sort/Merge joins, materializations, hash joins and hashable
> sub-queries. The performance of those features is very dependent upon
> getting the memory allocation correct. So we need a way of saying "what
> limit can I use for now for planning that you can guarantee me will be
> the minimum I receive when we try to execute this query?"
> If the query is being immediately executed then we can set the dynamic
> memory limit during planning and then begin using it later in the
> executor. ...But the planner does not currently know about
> prepared/immediate plans. 

Something else to ponder... the ultimate goal is to get as much work
done at this given instant as possible. So consider if all running
running backends were able to coordinate in terms of 'if I get x amount
of memory, I can perform this query in y seconds, but if I get f amount
of memory I can perform it in g seconds'. This will become more
important for queries that need to perform large sorts. Depending on the
relationship between sort memory and on-disk sort speed, it could make a
lot of sense to give one query 96% of available memory if it means it
can sort in-memory, even if it means some other query only gets 1%.
Similarly, in the future it might be very useful if long-running on-disk
sorts can dynamically change how much memory they're using; when there's
not much memory demand they can use more (probably more efficiently than
just leaving it to disk buffering), but when a query comes in that needs
less memory it can take memory from the large query where it won't
matter as much.

> Limit action
> What should we do if the requested size is unavailable?
> - Overallocate (current action)
> - Trimming - Give back the maximum available, however much that is...
> - Step down - Return the next smaller unit of size, in a variable scheme
> - Block on a Wait loop until limit request succeeds... but might wait a
> long time depending upon workload
> - Throw error and allow user to resubmit later - if we did that it would
> be important to throw and error quickly, rather than execute for an
> hour, hit a limit and then abort

I think it would probably be good to support multiple options. Another
option that comes to mind in the sort case is to have sorts that end up
spilling to disk release some of their memory. While I'm sure that
memory does impact the speed of disk sorts, the difference would be
nothing compared to the decrease in speed for a sort that should be able
to fit into memory but can't because a single sort is sucking everything
down.

> It should be fairly straightforward to implement a memory limit request
> system using shared memory. We would probably need a system view defined
> for it, to allow diagnostics and monitoring.

Food for thought, this probably doesn't need to be shared memory, since
there's no need to share data between sorts. It should be sufficient to
just keep track of how much total memory is being used.

Also, I agree with Josh that this is probably too much to bite off at
once. I also think that we need to at least offer an option besides
blocking. Since there's so many facets to this problem it might be good
to just prioritize what's important, and what gets done gets done.
-- 
Jim C. Nasby, Database Consultant               decibel at decibel.org 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"