Better Controlling MySQL Memory Usage

MySQL, like a lot of other software, has many knobs you can tweak. Most of these knobs may affect behaviour, but more importantly most affect the memory usage of the server, so getting these settings right is very important.

Most of MySQL’s memory is really used just as a cache, in one form or another, information that otherwise is on disk. So ensuring you have as large a cache as possible is important. However, making these memory sizes too large will trigger the server to start swapping and possibly can cause it to crash or cause the kernel to kill the process when it runs out of memory.  So that’s something we want to avoid.

Certain settings affect memory allocation on a per connection/thread basis, being bounded by thread_cache_size and max_connections.  If you configure for the worst behaviour (max_connections) you may end up not actually using all the memory you have available, memory which normally could be used for other purposes.

Recently a server I managed was configured incorrectly with a large sort_buffer_size (4M to 256M) and larger read_buffer_size (4M to 20M).  The change in configuration on first glance looks quite innocent, and not noticing that these are per-connection settings this got rolled out. max_connections on this server was set to 1000, while normally there were ~40 connections of which only a few were active. The mysqld memory footprint on startup looked fine. In fact under normal usage it also worked fine. However spiky load suddenly changed this nice behaviour: as configured mysqld ramped up the thread count and hence memory usage, resulting in swapping and finally server death…

The fault of course was mine for not noticing, but this has been an issue with MySQL forever.  Most other RDBMSes manage memory slightly differently: you define how much memory you want to use (maximum), this is optinally locked into memory, and further requests for different buffers are taken from this global pool.  That is much safer, avoids unexpected swapping, or memory over-allocation, but does raise the question of what happens when a memory request can not be honoured.

Initially I would expect two different behaviours: either

  1. kill a thread whose memory can not be allocated, or
  2. wait a certain time to allocate that memory, after which it gets killed anyway.

Option (2) is probably saner, and possibly some sort of deadlock detection can kick if in all threads are waiting for memory, perhaps killing the younger thread, or thread which has done least work first. Possibly there are other better ways of doing this?

I can imagine that changing MySQL’s current behaviour to do something like this could be quite hard, especially as ideally the engines would also use the same memory management mechanisms, but I see this as being a good thing and would make MySQL more robust, especially under load, which is after all what counts.  Of course this will not happen in today’s 5.5 GA version, or tomorrow’s 5.6 version which is probably likely to appear some time this year. That’s a major change. It would be nice if Oracle look at this for 5.7 as a way of ensuring that when resource usage does come under pressure MySQL does not go heads up, but attempts to use the allocated resources as best as possible.

In the meantime what would help would be:

  • better documentation so we can see clearly how all mysql memory is allocated. There are several web pages commenting ways to calculate this, but certainly no definitive guide.
  • The InnoDB engine’s documentation talks about memory usage and most people think that the innodb_buffer_pool_size is the main setting. yet read further and there’s talk of an overhead of perhaps 1/8th. I have recently been playing with innodb_buffer_pool_instances settings > 1 (using values in the range of 20-40) and am inclined to think that this increases that overhead somewhat more, yet there’s no documentation on this and whether my guess is right or not. Please InnoDB developers improve your documentation, if only to prove me wrong.
  • Ideally some tools to tell you if you server is possibly misconfigured. Coming from a Sybase environment I’d be tempted to suggest a stored procedure in the mysql database which can tell you total memory usage and how it is broken down as doing this with a single SELECT is going to be tricky.

Then once that is done consider adding some extra variable to enable total memory usage to be controlled. I made a feature request for this at http://bugs.mysql.com/?id=64108. If you think this feature might interest you please let Oracle know.

Tags: , , , , , , , ,

7 Responses to “Better Controlling MySQL Memory Usage”

  1. Simon J Mudd says:

    That might be useful but it does not address the real problem: applications should not expect that they can use infinite amounts of memory, and it should be possible to configure them to control their resource usage. I am not sure the kernel’s place is to do this. Database software wants to be in memory and the administrator wants to be able to tell it how much it can use, and then be sure that it does not go over that limit, and hope the software is smart enough to use the available memory as efficiently as possible amongst the contending threads. Assuming you have that, it would indeed be nice to be able to then assign the right resource limits to different kernel components.

  2. James Day says:

    It helps to think of the history of this. In the old days there was MyISAM and it uses the operating system cache as its data row cache. So all the allocations for buffers would do is reduce the row caching a bit, still leaving plenty of RAM without swapping.

    Enter InnoDB and a fixed buffer pool size and have most people migrating to it because it’s more robust and that model isn’t so good any more. Add in some people using persistent connections when the allocations can stick around instead of being rapidly freed and that set of users get even worse problems of accumulated RAM allocations.

    So that’s how we got here. Now we need to get to somewhere more friendly.

    We know this isn’t good enough and are gradually doing things about it. Not as fast as would be nice for those of us in the Oracle MySQL Support team or those trying to maximally use RAM without swapping.

    One interesting possibility if we could find it is a place that could replace the MyISAM row cache so that the RAM can be used productively but freed on demand. Maybe multiple InnoDB buffer pools could be applicable to this, allocating and dropping based on demand. Not sure.

    For persistent connections we really need to do better. There are already some cases where we decide that a buffer is too big to keep around even within a session but we need to do some more housekeeping when connections are persistent but when many are lightly used or allocate larger than default buffers.

    Thoughts on how we might handle these things are welcome, particularly by me because it’s the sort of thing that I end up trying to push into actually happening.

    James Day, MySQL Senior Principal Support Engineer, Oracle. Views are my own; if you want an official Oracle opinion, seek out a PR person.

  3. Simon J Mudd says:

    James, of course you are right. MySQL has evolved a lot over the last few releases. So have its users. So has hardware. In my current job our main replicated database migrated from MySQL to InnoDB because write contention (from replication) was starting to cause problems with read access from the web users. Hardware has improved, giving us more cores and memory. So contention within MySQL became an issue and that has been improved in 5.1’s InnoDB plugin and now MySQL 5.5. More memory means we want to use it, and to the full. When I used MyISAM the key_buffer_size was nowhere close to total memory, so any unusual settings had much less of an impact. InnoDB’s fixed in memory pool changes that bringing to our attention the issues I outlined before.

    Something I forgot to mention previously. Temporary tables in memory may use up RAM, in a way that varies entirely on the query profile, so sudden changes in this profile, due to code changes, can quite significantly affect memory usage. Controlling that is also important.

    So my comments were not to critise MySQL for not having done this before. I think you rightly point out that this was not really an issue. However, and certainly given the current usage of InnoDB as the default storage engine, this issue just might be worth focusing on.

    Having a better way to measure potential usage is good. Controlling that is even better. It also does not have to be done in one giant step. Memory used by the InnoDB engine is perhaps almost there, my comments were only related to a link I can’t find now where it said that the buffer pool size should be configured to 80% of memory and there was an overhead of 10%. I have noticed in some recent changes by increasing the number of innodb buffer pool instances that memory usage seemed to jump further, something I am guessing is related. I should have noted the link’s URL.

    For other non-InnoDB memory settings hopefully common memory allocation routines can reduce the code changes needed to implement these additional controls. Having said that things always get more complicated than one would hope. I will keep my fingers crossed, as maybe some progress can be made, a small step at a time. :-)

  4. Gavin Towey says:

    This script would have showed you the mistake:
    https://launchpad.net/mysql-tuning-primer

  5. I disagree; I think that script is actually harmful. It promotes apocryphal things like “maximum possible memory consumption equation” and “tuning buffers by the buffer hit ratio” and other nonsense.

    The sample chapter from High Performance MySQL Third Edition is the configuration chapter. It’s available on http://www.highperfmysql.com.

  6. [...] most affect the memory usage of the server, so getting these settings right is very important. Simon Mudd [...]

Leave a Reply