Bug #24757 No way to limit memory consumption of some server subsystems.
Submitted: 1 Dec 2006 15:21 Modified: 1 Dec 2006 20:01
Reporter: Konstantin Osipov (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: General Severity:S4 (Feature request)
Version: OS:Any
Assigned to: CPU Architecture:Any

[1 Dec 2006 15:21] Konstantin Osipov
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.
[1 Dec 2006 20:01] Sveta Smirnova
Thank you for the reasonable feature request.
[14 Sep 2008 15:55] Valeriy Kravchuk
Bug #39442 was marked as a duplicate of this one.
[10 Jan 2009 2:57] Chris Calender
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).
[10 Jan 2009 5:20] MySQL Verification Team
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.
[28 Feb 2009 15:29] Peter Gulutzan
[28 Jan 2014 17:59] Sveta Smirnova
Bug #71497 was marked as duplicate of this one.