MySQL Bugs: #105207: Deallocate memory used by idle connections

Bug #105207	Deallocate memory used by idle connections
Submitted:	13 Oct 2021 13:02	Modified:	13 Oct 2021 21:20
Reporter:	Carlo Alberto Ferraris	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Connection Handling	Severity:	S4 (Feature request)
Version:		OS:	Debian
Assigned to:		CPU Architecture:	x86

Description:
To minimize the memory usage due to having many open network connections it would be ideal if the unused memory allocated to service connections that remain idle for a certain period of time T (ideally configurable) were to be deallocated/decommitted, and allocated/committed back when the connections go back to being active. Memory that could be decommitted include the unused part of the thread stack, and the connection and result buffers.

This would be complementary to the existing wait_timeout in that it would allow to close connections less aggressively, thereby minimizing tail latencies due to reconnections, while still minimizing the amount of memory wasted on idle connections. Just as an example, a reasonable value for T could be 1 second (while wait_timeout could be in the minutes range) and would basically allow connections that remain idle for 1 second to consume almost no memory.

How to repeat:
- Open a significant number of connections to the server, each sending queries infrequently

Suggested fix:
This could be done on linux with madvise(MADV_DONTNEED) by e.g. setting the connection read timeout to T: if this timeout is reached, decommit the memory used for this connection, set the connection read timeout to (wait_timeout-T), and resume waiting for data.

Note that MADV_DONTNEED does not have the same effect on other OS: these either provide different hints to achieve the same thing, or dedicated functions (e.g. DiscardVirtualMemory on windows)

Hi Mr. Ferraris,

Thank you for your feature request.

However, we do not think that this feature is feasible. First of all, stack can not be moved between threads. The only way to implement this would be to end the thread entirely and based on the thread data, create a new thread with smaller stack. Regarding the deallocation of the memory used by the thread, it is also not feasible, since a large amount of info is kept about the connection, user, host, encryption type, all options used and all changed local variables, then data on the running transaction and many others.

More important than the above set of reasons is that this feature is already implemented. Within our Enterprise Edition offer we do have a thread pool manager that saves lots of memory by decreasing significantly the number of running threads.

We hope that we have been clear enough.

Hi,

I completely understand and accept the business argument. But just for the record let me correct the technical ones, as they seem to be missing the point.

> However, we do not think that this feature is feasible. First of all, stack can not be moved between threads. The only way to implement this would be to end the thread entirely and based on the thread data, create a new thread with smaller stack.

Or, as I suggested, you could simply call madvise on the _unused_ parts of the stack.

> Regarding the deallocation of the memory used by the thread, it is also not feasible, since a large amount of info is kept about the connection, user, host, encryption type, all options used and all changed local variables, then data on the running transaction and many others.

I obviously wasn't suggesting to deallocate memory that is still needed by the thread, as that would be rather silly and would almost certainly cause mysql to misbehave. If you only decommit memory whose *contents* we don't need anymore, something that again is possible as described above, we don't cause any misbehavior and just decrease memory usage.

Anyway, as mentioned above I accept the business argument. Feel free to close this issue.

Thank you, Mr. Ferraris.