MariaDB Failing Since v12

thekendog

Every since v12 update I'm seeing MariaDB fail every so often across multiple servers. Anybody else seeing this?

https://share.cleanshot.com/PfnxGkwW

cPFence

If you see MariaDB being terminated by the OOM Killer, you may need to increase swap or optimize memory usage.
Check current memory usage:
free -m
Also check disk space:
df -h
du -sh /var/lib/mysql/

DracoBlue

Did you open the ticket?

thekendog

DracoBlue I did. But I know support is overwhelmed right now with stuff so figured I'd ask here as well.

DracoBlue

@xyzulu probably has a similar problem.
Check: https://community.enhance.com/d/2415-enhance-v1200-released/478

thekendog

DracoBlue

"mariadb didn't restart itself like it used to on docker"

That appears to be my issue. In that thread you said this..

"Check the logs /var/log/mariadb/mariadb.log for more details."

There are no logs in that location. What's the fix here?

DracoBlue

check /var/log/mysql/error.log
/var/log/mysql/mariadb.log

thekendog

DracoBlue that file doesn't exist. I see no log files at all for mariadb. No /var/log/mariadb folder at all and nothing in /var/log/mysql

Nickske00

Probably a systemd setting to make a service restart on failure. Maybe @Adam can fix this for everyone in the next patch?

thekendog

Nickske00 yeah that would be nice. Previously, this didn't happen because I think Docker auto restarted stuff. Now I'm having issues on multiple sites across multiple servers. Frustrating.

Adam

The problem with auto restarting mariadb when it's killed for OOM is that it will likely be killed again. Repeated killing of MariaDB will cause data corruption. It's better to address the cause of the OOM - set hard memory limits on your websites, set a sensible innodb_buffer_size or add more RAM to the system.

If you want the old behaviour back, you can add a systemd override for the mariadb service and set Restart=Always. That should do it.

xyzulu

Adam If you want the old behaviour back, you can add a systemd override for the mariadb service and set Restart=Always. That should do it.

Edit: there is also this in /etc/systemd/system/multi-user.target.wants/mariadb.service

# Kernels like killing mariadbd when out of memory because its big.
# Let's temper that preference a little.
# OOMScoreAdjust=-600

...as @thekendog noted above. How is that going @thekendog ? Did you drop in a .conf file to /etc/systemd/system/mariadb.service.d ?

@Adam will this conf file persist now on v12?

thekendog

I ended up just setting the OOMScoreAdjust value for it to -1000. We'll see if that works.

cPFence

Also edit my.cnf by going to Enhance Main CP → Server → Database → Edit my.cnf, then add:

max_user_connections=25

After that, restart MariaDB:

systemctl restart mariadb

You can also utilize this script to analyze your setup and identify areas for optimization. However, it is crucial to keep MariaDB up and running for at least 24 hours to ensure you receive accurate information.
https://gist.github.com/cPFence/98c359cfade030fd62adb6681312a97a

xyzulu

This is an issue to ponder if you ever had OOM kills on v11 to mariadb. The Ubuntu OOM killer will always target mariadb/mysql since it is likely using the largest chunk of server ram. We all also have slightly different setups.. ie swap, no swap.. different ram etc..

cPFence max_user_connections=25

This may be a good way to add some stability in a shared server environment.. I also think that systemd should try to restart mariadb/mysql if it is OOM killed. I have been talking to Adam more in a ticket and he also has some thoughts on this.

cPFence

xyzulu

If you need MariaDB to restart automatically on any failure and ensure persistence, you can set this up with a systemd override file. However, be cautious with such changes to avoid issues like infinite restart loops if a persistent error causes repeated failures.

What Enhance is doing now follows standard practices used by professional control panels like cPanel and DirectAdmin and should not be changed.

What really should be done is to address the root cause of the failures through proper resource allocation, MySQL optimization, and preventing long, inefficient queries from killing your server. And if that’s already done, then adding more RAM to the server is the next step.

xyzulu

cPFence What really should be done is to address the root cause of the failures through proper resource allocation, MySQL optimization, and preventing long, inefficient queries from killing your server. And if that’s already done, then adding more RAM to the server is the next step.

this IS the correct way to attack this issue

xyzulu

Pro tip (since it tripped me up):
If overriding (and it will persist) with files in

/etc/systemd/system/mariadb.service.d/

you need to ensure you have added the correct unit to the .conf file..
ie
[Service]
OOMScoreAdjust=-600

Also, don't forget: systemctl daemon-reload to ensure you override is loaded.. you can check with systemctl show mariadb.service (to see the loaded values) and also systemctl show mariadb.service to see the current status.

Disclaimer: Don't blindly create a .conf file with the sample values I have above.

DracoBlue

xyzulu You can impose a memory limit on MariaDB to prevent it from using all the RAM.
Check: sudo systemctl edit mariadb.service or sudo systemctl edit mariadb
and

[Service]
MemoryMax=1G (your value)

[Service]
OOMScoreAdjust=-1000

Valid values are integers in the range of -1000 to 1000 for the OOM score. The lower the value, the lower the chance that the process is going to be killed by the OOM Killer.

It should help with OOM.

Reload the systemd daemon configuration by executing the following command

systemctl daemon-reload

thekendog

Man, this keeps happening on multiple servers. I'm having to adjust the OOM scores across the board for MariaDB. This didn't happen on v11 and it didn't happen with the same sites/servers when I was using SpinupWP. I see that Enhance is setting the innodb_buffer_pool_size to 512M while the default is 128M. I wonder if that is the issue? I have another server that uses cPanel and it is wayyyyyyyyyy beefier and has way more RAM and CPUs than the ones on Enhance. The default innodb_buffer_pool_size for it is still only 128M.

Kosta

thekendog interesting.
Why enhance doesn’t provide us with all this settings for database servers into the UI. Even with templates where we can make template with settings and apply this settings across all database servers into the cluster or into a group of servers.
I can see this requires a lot of sysadmin work on database servers. To keep them in good performance

twest

thekendog why would you only have a 128mb buffer pool on such a huge server? That makes ZERO sense.

Your problems are definitely related to your lack of even basic database optimization. There's lots of threads about it in this forum, recommend you go for a read and learn about it.

pratik_asabe

thekendog thats low, we're optimizing db for years and we still have to adjust it every now and then, db optimization can be tricky, you have to monitor and adjust alot to make it perfect, OOM happens when db has no room to do its operations like low disk, low ram, etc.

You should try n see increasing your buffer pool size, research more on how to optimize db based on your server resources (vCPU, RAM)

If that doesn't help open ticket, let adam know if this is a random serious bug it should be get fixed..