Re: Sort memory not being released

On Tue, Jun 17, 2003 at 08:08:29PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > Well, there's other ways to do what I'm thinking of that don't rely on
> > getting a free memory number from the OS. For example, there could be a
> > 'total_sort_mem' parameter that specifies the total amount of memory
> > that can be used for all sorts on the entire machine.
>
> How would you find out how many other sorts are going on (and how much
> memory they're actually using)?  And probably more to the point, what do
> you do if you want to sort and the parameter's already exhausted?

There'd need to be a list of sorts kept in the backend, presumably in
shared memory. When a sort started, it would add an entry to the list
specifying how much memory it intended to use. Once it was done, it
could update with how much was actually used. Actually, I guess a simple
counter would suffice instead of a list.

As for when memory runs out, there's two things you can do. Obviously,
you can just sleep until more memory becomes available (presumably the
lock on the shared list/counter would prevent more than one backend from
starting a sort at a time). A more elegant solution would be to start
decreasing how much memory a sort will use as the limit is approached. A
possible algorithm would be:

IF total_sort_mem - active_sort_mem < desired_sort_mem THEN
    desired_sort_mem = (total_sort_mem - active_sort_mem) / 2

So if you can't get all the memory you'd like, take half of whatever's
available. Obviously there would have to be a limit to this... you can't
sort on 100 bytes. If (total_sort_mem - active_sort_mem) drops below a
certain threshold, you would either ignore it and use some small amount
of memory to do the sort, or you'd sleep until memory became available.

I know this might sound like a lot of added complexity, but if it means
you have a much better chance of being able to perform large sorts
in-memory instead of on-disk, I think it's well worth it.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
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?"