Configuring Optimal MySQL Memory UsageIn this blog post, we’ll discuss some of the best practices for configuring optimal MySQL memory usage.

Correctly configuring the use of available memory resources is one of the most important things you have to get right with MySQL for optimal performance and stability. As of MySQL 5.7, the default configuration uses a very limited amount of memory – leaving defaults is one of the worst things you can do. But configuring it incorrectly can result in even worse performance (or even crashes).

The first rule of configuring MySQL memory usage is you never want your MySQL to cause the operating system to swap. Even minor swapping activity can dramatically reduce MySQL performance. Note the keyword “activity” here. It is fine to have some used space in your swap file, as there are probably parts of the operating system that are unused when MySQL is running, and it’s a good idea to swap them out. What you don’t want is constant swapping going on during the operation, which is easily seen in the “si” and “so” columns in the vmstat output.

Optimal MySQL Memory Usage

Example: No Significant Swapping

Optimal MySQL Memory Usage

Example:  Heavy Swapping Going

If you’re running Percona Monitoring and Management, you can also look into the Swap Activity graph in System Overview Dashboard.

Optimal MySQL Memory Usage

If you have spikes of more than 1MB/sec, or constant swap activity, you might need to revisit your memory configuration.

MySQL Memory allocation is complicated. There are global buffers, per-connection buffers (which depend on the workload), and some uncontrolled memory allocations (i.e., inside Stored Procedures), all contributing to difficulties in computing how much memory MySQL will really use for your workload. It is better to check it by looking at the virtual memory size (VSZ) that MySQL uses. You can get it from “top”, or by running ps aux | grep mysqld.

The 5th column here shows VSZ usage (about 11GB).

Note that the VSZ is likely to change over time. It is often a good idea to plot it in your monitoring system and set an alert to ping you when it hits a specified threshold. Don’t allow the mysqld process VSZ exceed 90% of the system memory (and less if you’re running more than just MySQL on the system).

It’s a good idea to start on the safe side by conservatively setting your global and per connections buffers, and then increase them as you go. Many can be set online, including innodb_buffer_pool_size in MySQL 5.7.

How much memory to allocate to MySQL

So how do you decide how much memory to allocate to MySQL versus everything else? In most cases, you shouldn’t commit more than 90% of your physical memory to MySQL, as you need to have some reserved for the operating system and things like caching binary log files, temporary sort files, etc.

There are cases when MySQL should use significantly less than 90% of memory:

  • If there are other important processes running on the same server, either all the time or periodically. If you have heavy batch jobs run from cron, which require a lot of memory, you’ll need to account for that.
  • If you want to use OS caching for some storage engines. With InnoDB, we recommend innodb_flush_method=O_DIRECT  in most cases, which won’t use Operating System File Cache. However, there have been cases when using buffered IO with InnoDB made sense. If you’re still running MyISAM, you will need OS cache for the “data” part of your tables.
  • If your workload has significant demands, Operating System Cache – MyISAM on disk temporary tables, sort files, some other temporary files which MySQL creates the need to be well-cached for optimal performance.

Once you know how much memory you want the MySQL process to have as a whole, you’ll need to think about for what purpose the memory should be used inside MySQL.The first part of memory usage in MySQL is workload related – if you have many connections active at the same time that run heavy selects using a lot of memory for sorting or temporary tables, you might need a lot of memory (especially if Performance Schema is enabled). In other cases this amount of memory is minimal. You’ll generally need somewhere between 1 and 10GB for this purpose.

Another thing you need to account for is memory fragmentation. Depending on the memory allocation library you’re using (glibc, TCMalloc, jemalloc, etc.), the operating system settings such as Transparent Huge Pages (THP) and workload may show memory usage to grow over time (until it reaches some steady state). Memory fragmentation can also account for 10% or more of additional memory usage.

Finally, let’s think about various global buffers and caching. In typical cases, you mainly only have innodb_buffer_pool_size to worry about. But you might also need to consider key_buffer_size, query_cache_size  as well as table_cache and table_open_cache. These are also responsible for global memory allocation, even though they are not counted in bytes. Performance _Schema may also take a lot of memory, especially if you have a large number of connections or tables in the system.

When you specify the size of the buffers and caches, you should determine what you’re specifying. For innodb_buffer_pool_size, remember there is another 5-10% of memory that is allocated for additional data structures – and this number is larger if you’re using compression or set innodb_page_size smaller than 16K.

For systems with large amounts of memory, the database cache is going to be by far the largest memory consumer, and you’re going to allocate most of your memory to it. When you add extra memory to the system, it is typically to increase the database cache size.

Let’s do some math for a specific example. Assume you have a system (physical or virtual) with 16GB of memory. We are only running MySQL on this system, with an InnoDB storage engine and use innodb_flush_method=O_DIRECT, so we can allocate 90% (or 14.4GB) of memory to MySQL. For our workload, we assume connection handling and other MySQL connection-based overhead will take up 1GB (leaving 13.4GB). 0.4GB is likely to be consumed by various other global buffers ( innodb_log_buffer_size, Table Caches, other miscellaneous needs, etc.), which now leaves 13GB. Considering the 5-7% overhead that the InnodB Buffer Pool has, a sensible setting is innodb_buffer_pool_size=12G – what we very commonly see working well for systems with 16GB of memory.

Now that we have configured MySQL memory usage, we also should look at the OS configuration. The first question to ask is if we don’t want MySQL to swap, should we even have the swap file enabled?  In most cases, the answer is yes – you want to have the swap file enabled (strive for 4GB minimum, and no less than 25% of memory installed) for two reasons:

  • The operating system is quite likely to have some portions that are unused when it is running as a database server. It is better to let it swap those out instead of forcing it to keep it in memory.
  • If you’ve made a mistake in the MySQL configuration, or you have some rogue process taking much more memory than expected, it is usually a much better situation to lose performance due to a swap then to kill MySQL with an out of memory (OOM) error – potentially causing downtime.

As we only want the swap file used in emergencies, such as when there is no memory available or to swap out idle processes, we want to reduce Operating System tendency to swap   ( echo 1 >  /proc/sys/vm/swappiness). Without this configuration setting, you might find the OS swapping out portions of MySQL just because it feels it needs to increase the amount of available file cache (which is almost always a wrong choice for MySQL).

The next thing when it comes to OS configuration is setting the Out Of Memory killer. You may have seen a message like this in your kernel log file:

Apr 24 02:43:18 db01 kernel: Out of memory: Kill process 22211 (mysqld) score 986 or sacrifice child

When MySQL itself is at fault, it’s a pretty rational thing to do. However, it’s also possible the real problem was some of the batch activities you’re running: scripts, backups, etc. In this case, you probably want those processes to be terminated if the system does not have enough memory rather than MySQL.

To make MySQL a less likely candidate to be killed by the OOM killer, you can adjust the behavior to make MySQL less preferable with the following:

echo '-800' > /proc/$(pidof mysqld)/oom_score_adj

This will make the Linux kernel prefer killing other heavy memory consumers first.

Finally, on a system with more than one CPU socket, you should care about NUMA when it comes to MySQL memory allocation. In newer MySQL versions, you want to enable innodb_numa_interleave=1. In older versions, you can either manually run numactl --interleave=all  before you start MySQL server, or use the numa_interleave configuration option in Percona Server.

More resources:

Posts

Webinars

Presentations

Free eBooks

Tools

Download Percona Server for MySQL Today!
7 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments

Hi Peter,

What an excellent post!

About the swap, we typically set it to the exact size of the RAM. So, if a server has 64GB of RAM, then the swap is also 64GB. The thing is with SSDs, the swap is now more efficient than ever. Do you think that’s too much, given the fact that we have a generous amount of disk space? (Note: We did have a client who set the swap to 0, and the moment MySQL ran out of memory, the whole server crashed, so I see your point about the necessity of the swap).

About “innodb_buffer_pool_size” – you said to set it at around 12GB on a MySQL dedicated server with 16GB of RAM. Now, assuming an application with a heavy write activity, will there be a performance hit? I am assuming that the innodb_buffer_pool_size works the same way as the query_cache_size, which causes a performance hit when there is a table change because of invalidation.

doublemarket

Thank you for the useful post!

Do we have a way to know the overhead of the InnoDB buffer pool to retain additional data structure?

And since this is good, I translated this into Japanese https://yakst.com/ja/posts/3983.
Please let me know if it’s a problem.

I think InnoDB Fulltext Index has different strategics to using Memory and If someone use Fulltext on Big table, he should control fultext variables to avoid eat Memory

Moll

Nice post, Peter!

I think making MySQL a less likely candidate to be killed by the OOM killer should be done with cautious though. Typically, on the dedicated database machines MySQL will be consuming about 90% of the memory and no other applications should be running there except for the operating system. Killing an OS process won’t release so much memory and it might corrupt the filesystem (I had a case where memory pressure corrupted the system bus).
In other words, killing MySQL is not the worst scenario in all cases!

Rajesh

Hi, I have a dual core with 256 GB virtual MySQL running on it. At times the machine runs very slow even to process a simple count query, Any reason…

Eimantas

Stroed procedures/functions and triggeres – are couse of high memory usage (all affected MySQL, Percona, MariaDB)
scripts and description here: https://jira.percona.com/projects/PS/issues/PS-6800

Any ideas how to control it? Config tips?