Description:
It should be a nice feature to have a shared limit on per process memory usage.
Now, with the current implementation per process memory limits (sort and read buffers) have to be set on the worst case event (all processes using all their memory). If not, when the worst case occurs the server starts hyperpaging and goes down.
But when all proccesses aren't using all their memory a lots of memory become unused and this affects seriously to the server performance.
So it will be a nice feature that the process's private memory don't be limited on the worst case, and in the same time prevent that hyperpagging takes the server down. Instead if that some processes aren't using their memory they can share it with others.
As private memory can be freed after each query, before a query starts the process could look if there are some memory unused by other proccesses and take it for one query (all within some limits min_memory_per_process, max_memory_per_process, max_memory_per_server).
I think this feature isn't dificult to implement and will provide better resource usage. At low loads lower query times and at high load the same performance. Lower query times are very important in lamp stacks as the web user could see more pages in less time when the server aren't high loaded.
How to repeat:
Its a feature request. Nothing to repeat.
Suggested fix:
Some ideas in the description.