How to Prevent MySQL from Crashing Due to OOM Killer

Post Reply
User avatar
isscbta
Team Member
Posts: 131
Joined: Mon Jul 19, 2021 1:41 am
Has thanked: 16 times
Been thanked: 3 times

Unlike MySQL, many other processes have an unpredictable memory footprint. Take PHP as an example when run with php-fpm; its memory consumption is quite dynamic. Optimizing your system to ensure that PHP doesn't exceed memory limits, without compromising overall throughput, is challenging. As a result, it's often recommended to allocate excess memory for PHP to maximize performance consistently. Nonetheless, this approach has a downside. There may come a time when PHP demands significantly more memory than usual, prompting the system to activate the OOM (Out of Memory) killer. Given MySQL's substantial memory usage, the kernel often targets it when addressing memory shortages. This action is less than ideal because terminating MySQL affects all requests, as opposed to shutting down a few errant PHP processes which would only impact the problematic requests. So, the lingering question is: how can we prevent MySQL from being the primary target?

Here is the answer you may apply on Debian.

In your SSH, as root, run:

Code: Select all

service=''
count=$(systemctl list-unit-files |  grep -c 'mysql\.service.*enabled')
if [ $count -gt 0 ]; then
    service='mysql'
fi
count=$(systemctl list-unit-files |  grep -c 'mariadb\.service.*enabled')
if [ $count -gt 0 ]; then
    service='mariadb'
fi

if [ "$service" != "" ]; then
    echo "== Preventing oom-killer to kill $service"
    folder="/etc/systemd/system/$service.service.d"
    file="$folder/oomadjust.conf"
    mkdir -p $folder
    echo "[Service]" > $file
    echo "OOMScoreAdjust=-500" >> $file
    systemctl daemon-reload
    systemctl restart $service
    systemctl status $service | grep -A 1 'Drop-In'
else
    echo "== mysql and mariadb not found"
fi
Post Reply