Description:
Current mysql documentation is not very clear about how memory usage changes for all different settings. One consequence of this is that it is quite easy to configure by mistake a server to have a potentially much larger memory footprint than available RAM, so it may start and seem to work fine but later may swap or crash unexpectedly.

Most RDBMSes deliberately have settings to control total memory usage of the process so that the DBA
can limit the memory usage to a specific size (usually as much RAM as possible excluding other administrative processes). This is not possible in MySQL.

Even the InnoDB engine appears to have a single buffer pool but its documentation then goes on to say there are overheads (which may be significant) so the total memory usage by InnoDB may be larger than expected.

So please add some sort of new variable to control the total memory usage and only allocate memory from this "buffer". If it becomes exhausted then probably (according to configuration) the thread should wait or be killed, and if no threads are able to continue some sort of deadlock killer should start to kill stuck threads thus allowing the remaining threads to continue.

How to repeat:
Set innodb_buffer_pool_size to 80% of RAM.
Set sort_buffer_size to a large value (500M)
set read_buffer_size to a large value (500M)
set max_connections to a large value (5000).

startup mysqld. It should startup and memory usage may look reasonable.
now start adding threads to the server and make them do things.
you'll see memory usage soar and probably the box swap to death or get killed because it runs out of memory.

The example above is a rather extreme, but something similar recently happened to me.

Suggested fix:
set global_max_memory_usage = "90% of RAM".

other settings stay the same.

The net effect should be that some threads block or die but the server does not crash and keeps responding.

Thank you for the feature request. We had similar/related requests in the past:

- http://bugs.mysql.com/bug.php?id=10139
- http://bugs.mysql.com/bug.php?id=44234
- http://bugs.mysql.com/bug.php?id=9420
- http://bugs.mysql.com/bug.php?id=24757
- maybe more...

We even have (internal) Worklog about these (mentioned in some of the reports above). 

But as a request for simple option this your FR is unique enough.

Simon, we're considering adding warnings for unusual size settings. Please let me know your thoughts on suitable warning values for the variables you've mentioned. Warnings won't prevent the setting, just alert people that it's unusually big, or perhaps small sometimes.

We've improved per-session InnoDB memory allocations with the work described at:

http://blogs.innodb.com/wp/2011/12/improving-innodb-memory-usage/
http://blogs.innodb.com/wp/2011/12/improving-innodb-memory-usage-continued/

While the fragmentation described there is specific to the Linux allocator the deferring of some allocations will help on all operating systems.
 
One place where we describe InnoDB memory use is in the manual at http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_buffer_pool_si... which says in part "you might set this to up to 80% of the machine physical memory size ... InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size". It may be this description that you're remembering. Most of the additional space is for locking information.