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 🙂
Hi Peter,
Some time ago, I wrote an incomplete proposal for this:
http://jcole.us/blog/mysql/proposal-for-constraining-memory-usage/
It might be an interesting read. 🙂
Regards,
Jeremy
It’s not quite at the “SHOW STATUS” level, but Domas and I were talking about this area a bit at the MySQL User’s conference. Perhaps you could use the umem memory allocator in some of these cases? It can be LD_PRELOADed and help you, if you set the right environment variable options, determine where the memory allocations have come from. It’s also designed to not be usable in production.
https://labs.omniti.com/trac/portableumem
It was a user space port of the Solaris kernel memory allocator (now copied in Linux/BSD/Windows and others) along with many of the debugging tools. The OmniTI guys liked it so much on OpenSolaris, they ported it to other platforms and use it when building their software after it was released as OpenSource.
One good overview is here: http://blogs.sun.com/ahl/entry/solaris_10_top_11_20
Thanks Matt,
Do I read you correctly it is “not be usable in production” ? or is it a typo.
I think with Dtrace at Sun you guys understand it is profiling in production which is very important. It is often not an option to install other version with some debugging enabled and try to repeat the same situation.
Though I have a rough idea I’d love more insight into where memory is going, right now its just this big blob I hope never runs into swap 🙂
I’ve only had to do it once but the only value I’ve had luck in reducing memory consumption was lowering innodb_buffer_pool_size.