MySQL Bugs: #24757: No way to limit memory consumption of some server subsystems.

Bug #24757	No way to limit memory consumption of some server subsystems.
Submitted:	1 Dec 2006 16:21	Modified:	1 Dec 2006 21:01
Reporter:	Konstantin Osipov
Status:	Verified
Category:	Server: General	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		Target Version:
Triage:	Triaged: D5 (Feature request)

Description:
There is no way to put a limit on the total memory consumed by 
- user variables, 
- stored procedure cache, 
- temporary in-memory tables, 
- prepared statements (although there is a limit on the total number of statements, every
single statement can occupy arbitrary amount of memory).

There might be other subsystems not mentioned in this list.

How to repeat:
See Bug#23443: user-defined variables can consume too much memory in the server
for instructions how to repeat out-of-memory situation with use of user variables.

See Bug#24751: Possible infinite loop in init_io_cache() when insufficient memory
out-of-memory situation in IO caching subsystem.

As there is no grand limit and heap memory is used by all server components, it is not
possible to enumerate all the scenarios that can lead to an out-of-memory situation.

Suggested fix:
The server needs to introduce a number of limits:
- a limit for the total amount of memory allocated to global shared resources (table
cache, stored procedure cache, prepared statements)
- a limit for the total amount consumed by a user connection
- a limit for the total amount of memory that can be allocated to resources of a given
user (across-connections total the previous item).
There may be alternative solutions to this problem.

Thank you for the reasonable feature request.

Bug #39442 was marked as a duplicate of this one.

Other suggestions in addition to the above:

user-based or process-based Solaris10 groups would be welcome ... (i.e., project /
user_attr / exec_attr, or newtask / projects / prctl / rctladm).

Something that might help some folks, is a little know option of mysqld:

If you want to be sure no users are allocating per-session buffers too high, you may
start the server with options: --maximum-sort_buffer_size=5M
--maximum_read_buffer_size=2M --maximum_tmp_table_size=5M etc ....  

In the above cases, this limits session values to the specified limits.