MySQL isn’t too concerned about table handler memory usage – it will allocate row size buffer thrice per each table invocation. There’s a few year old bug discussing UNION memory usage – for each mention in an union one can allocate nearly 200k of unaccounted memory – so a megabyte sized query can consume 7GB of RAM already.
Partitioning though adds even more pain here – it will allocate those three buffers per each partition, so opening a table with 1000 partitions looks like this on memory profile:
Click to enlarge, and you will see 191MB sent to execute a simple single-row fetching query from a table (I filed a bug on this).
There’re multiple real life situations when this is painful (e.g. any kind of server stall may lead to multiple concurrent threads reading from same table, consuming additional gigabytes or tens of gigabytes of memory). It gets even more painful when combined with UNION bug – a megabyte query on an empty table can now consume 7TB of memory and I doubt anyone has that much on their MySQL servers :-)
P.S. Also, check out how much memory can be wasted for malloc overhead, once discussed here.
P.P.S. And here you can see why innodb_max_dirty_pages_pct=0 doesn’t do what you’d expect.
7 thoughts on “Blowing up in memory”
What is the GUI tool in the screen shot?
Instruments, part of XCode – https://dom.as/2009/03/31/on-tools-and-operating-systems/
Domas – very interesting….
Instruments is a pretty cool tool. As always – takes some time to really interpret the numbers. We use it for BlackRay development – btw, I use MacOS for (almost) the same reasons as you, looks nice and just works.
Anyone interested in this should subscribe to or otherwise watch http://bugs.mysql.com/bug.php?id=57480 which is where work on this is being done. Very extensive work has been done. Among other things the work has included:
1. Moving many allocations to the heap instead of malloc.
2. Merging multiple allocations into one in several cases.
3. Better estimates of sizes needed for some allocations.
4. Defer allocation of upd_buff, of the size of the data row, that’s only needed if a row is updated.
Those who have an Oracle MySQL support subscription can also watch its mirror bug 11764622 in that system.
I asked for escalation of your patch for innodb_max_dirty_pages_pct and that’s been assigned to a developer. No ETA, just means it’s likely to get done sometime reasonably soon. Not guaranteed, of course.
This isn’t the official position of Oracle corp; for that consult a PR person.
James Day, Oracle
Is there any howto for creating Xcode projects and using Xcode for not Mac/iOS software?
Can you, please, describe how are you debugging and profiling UNIX programs such as MySQL in XCode?
Wasted a few hours on this today.
Comments are closed.