Protecting MySQL from the OOM Killer

In a previous post I mentioned that this site is hosted across two different hosts. One that is dedicated to running MySQL and Redis while the other runs Nginx and PHP. I use this arrangement for a few reasons. First, this is the cheapest way to get two real CPU cores on Digital Ocean. During a web request, multiple processes including Nginx, PHP, MySQL and Redis must run and share CPU time with each other. By using multiple machines, the work is spread across multiple physical CPUs which improves overall performance and throughput. Second, it allows me to configure MySQL to use most of the system memory without fear that it’ll be OOM killed. An OOM kill is what happens on a Linux system when it determines it is out of memory and the biggest user of memory needs to be removed (killed) in order to protect the system from a meltdown. In general, regular triggering of the OOM killer should be considered an error in configuration and capacity planning but know that it is there to protect the system.

In this post, I want to discuss a scenario where you want to host a common LAMP/LEMP stack on a single machine. In this kind of setup, multiple processes will be competing with each other for resources. Without getting too into the weeds about tuning software on this kind of setup, I’m going to assume that you will likely configure MySQL in such a way that it, as a single process, will consume the most memory of any process on the system. Indeed, most distributions when installing MySQL (or MariaDB) will have a default configuration that allows MySQL to use in excess of 1GB.

Unlike MySQL, the amount of memory that many other processes may use is relatively unknown. Looking at just PHP (using php-fpm) the amount of memory is fairly dynamic. It is unlikely that you will be able to tune your system to ensure PHP doesn’t use too much memory without sacrificing total throughput. Therefore, it is necessary to configure PHP in such a way that you over provision available memory in an effort to ensure you get the most performance you can most of the time. However, in this scenario it is likely that you will eventually face a situation where PHP is asking for a lot more memory than usual and the system will invoke the OOM killer to deal with the sudden shortage of memory. MySQL, being the single largest user of memory on the system, will almost always be selected by the kernel to be removed. Allowing MySQL to be OOM killed is far less ideal than killing a rogue PHP process or two because it will disrupt all requests rather than the problem requests. So, how do you avoid MySQL being selected?

Most modern systems ship with systemd. Portions of systemd are not well received but, at least in my opinion, the init system is excellent. Using systemd, we are able to customize the startup routines for MySQL (almost any service, actually) so that we can instruct the kernel’s OOM killer to select a different process when the system is low on memory. Here is how it is done:

  • Create a directory – /etc/systemd/system/mysql.service.d. The directory name must match an existing service. For MariaDB it would be mariadb.service.d. You can determine the name by running systemctl list-unit-files
  • In this directory, create a file called oomadjust.conf with the following in it:
    [Service]
    OOMScoreAdjust=-500
  • Run systemctl daemon-reload
  • Restart MySQL

To confirm your customization was picked up run systemctl status mysql. In the “Drop-In” section you should see your customization was picked up. It’ll look similar to this:

Screenshot showing the oomadjust.conf file was picked up by systemd

This setting adjusts the value the OOM killer will calculate when trying to determine what process is using the most memory. By forcing this value to be lowered for MySQL it is much less likely to be selected. Instead, a problem PHP process will likely be selected first and removed. This will save MySQL and the overall availability of your app. Of course, your mileage may vary and you will need to tune your configuration to reduce if not eliminate the need for the OOM killer.

If you would like to learn more about systemd drop-ins take a look at the documentation by flatcar Linux at https://www.flatcar.org/docs/latest/setup/systemd/drop-in-units/. Many things can be overridden without having to edit files provided by packages (which you should avoid).

Have you used systemd’s drop-in system before? Curious how else you might use it? Leave a comment!

1 comment

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.