Maximum Memory Warnings Are Rubbish

Many people use MySQLTuner, as a guide to MySQL Server configuration values and they are alarmed when they see things like this:

[!!] Maximum reached memory usage: 86.6G (138.16% of installed RAM)
[!!] Maximum possible memory usage: 5085.9G (8110.25% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory

You should be aware that the “maximum memory” warning from MySQLTuner is rubbish. It is a misleading and impractical estimate, because it has no real chance of happening.

The memory usage they base their calculation on has to do with buffers that are allocated conditionally, depending on certain query behavior. Not all queries allocate all those buffers. Even if they do allocate the buffers, they may not allocate the full size of the buffer.

The chance that a given query will allocate all the possible buffers at their maximum size is remotely small.

In addition, there might be up to max_connections clients connected, but it’s typical in a running system that not all connections are executing a query at the same time. On every MySQL Server I’ve supported, even if there are hundreds of clients connected, most of them are idle (i.e. not running a query). I would expect the number of threads running a query at any given moment to be 10-20 at most. The others are connected, but if you view SHOW PROCESSLIST, they only show “Sleep” as their current state. These are clearly not using query-specific buffers.

The possibility that all threads on a MySQL Server will allocate all the possible query buffers at their maximum size at the same time is so unlikely that you should treat it as complete fiction.

Because of these mistakes in calculating the theoretical maximum memory usage, I ignore those warnings from automatic tuning tools like MySQLTuner. The estimate of “maximum memory” given by MySQLTuner has been many times the size of RAM on every database server I have administered. You don’t need to be alarmed by this.

So how should one estimate memory usage?

By observation!

Monitor the actual memory usage of your mysqld process by using tools like top or ps. Ideally you would have a continuous monitoring system to make graphs over time, so you could observe the trend.

This is much more accurate than relying on MySQL Tuner or any other estimate. Those estimates are not taking into account your database traffic or activity.