Quite frequently I would log in to customers system and find MySQL using too much memory. I would look at memory consumed by Innodb (it is often higher than innodb_buffer_pool_size) substract memory used by other global buffers such as query_cache_size and key_buffer and will in many cases see some mysterous memory which I can’t really explain. It can be several Gigabytes accounting for over 50% of memory usage of MySQL in some cases, though typically it is much smaller fraction.
Now. There are a lot of guesses I can make. Could it be memory allocated for per connection buffers which was not really freed to operation system ? Could it be some buffers associated with opened tables, prepared statements etc ? Could it be some of the queries currently running is using some of the buffers temporary based on per connection settings ? It also could be memory leak in MySQL or runaway stored procedure consuming too much memory, but there is no way to see what exactly is using memory.
It was long spoken about global resource management for MySQL, ie so I could restrict global temporary space for temporary tables or sort memory. Though I’m not expecting this to come in the near future nor it would solve all problems – Storage Engines for MySQL quite commonly use its own memory allocation policies which may be hard to merry with MySQL control policies.
What however I would like to see is at least memory use information by different object types, similar to what you can find for Linux kernel for example.
I would see it implemented similary to SHOW STATUS – threads could account statistics for their own memory usage which is when merged together to show GLOBAL status – this would allow to implement very simple and lock free statistics plus would allow to see amount of memory allocated for current thread in addition to global memory allocation.
As MySQL is progressing the problem of memory allocation transparency is becoming more and more serious. There are much more objects which MySQL can have allocated these days which can have a lot different sizes, while tools for analyzing memory usage have not improved since MySQL version 3.23, in fact were newer were any significant tools out there