MySql Database randomly crashing


I’ve got a wordpress website hosted on a vps running virtualmin, here the configuration:
Operating system CentOS Linux 7.7.1908
Webmin version 1.940
Memory: 3.6 GiB total
MySQL version 5.5.64
PHP versions 5.4.16, 7.0.27
Apache version 2.4.6

The database started crashing frequently and randomly 2 days ago (it crashed like one month ago too). There has been no modification or update in this time frame that could have started the crashes. It crashed 2 times yesterday at 13:30 14:25 and this morning at 00:55. Each time I restart the database but I need to find the source of these crashes so I can fix it.

Here is the error log:

If you need more info let me know I’ll gladly provide it.

I don’t know if it is a server issue or a wordpress issue, I would need a little help to at least know in with direction I need to investigate.

Thank you for your help

This log just tells us MySQL (or Mariadb, more likely, as that’s the default on CentOS 7) went away.

You’ll want to look for the MySQL/Mariadb error log, which is probably in /var/lib/mysql/hostname.err

Where hostname is your system hostname. That may or may not have clues.

Mariadb (and MySQL) are generally extremely stable, so if it’s shutting down I tend to suspect it’s being killed rather than crashing, maybe by the OOM (out-of-memory) killer in the kernel. Check dmesg for indications that memory is being exhausted. Make sure you aren’t allocating more RAM than is reasonable for your various processes (in my.cnf and in your PHP configuration, in particular).

Thanks I’ll look info the Database error log and tell you what I found.

Sorry I didn’t find an error log in the folder you indicated. I used dmesg command but I don’t really know what I’m looking at and what I’m looking for. Maybe this will help ?

Out of memory: Kill process 1597 (mysqld) score 51 or sacrifice child
[ 63.328501] Killed process 1597 (mysqld), UID 27, total-vm:1485796kB, anon-rss:194356kB, file-rss:0kB, shmem-rss:0kB
[ 72.805013] httpd invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0
[ 72.805018] httpd cpuset=/ mems_allowed=0
[ 72.805022] CPU: 0 PID: 2074 Comm: httpd Kdump: loaded Not tainted 3.10.0-1062.9.1.el7.x86_64 #1

In PHP configuration I have this:

Maximum memory allocation 256M
Maximum HTTP Post Size 64M
Max exection and input parsing time 120 seconds

I hope that this information can help you

I also found this in var/log/mariadb/mariadb.log exactly at the time of a previous crash 00:55

I just found in th same log that after each reboot this message is displayed

200114 6:32:18 [ERROR] mysqld: Table ‘./…/wp_mail_bank_logs’ is marked as crashed and should be repaired
200114 6:32:18 [Warning] Checking table: ‘./…/wp_mail_bank_logs’

I checked it in the SQL console but it said the table was ok, I repaired it nonetheless

How much more clear can it be? Out of memory, so the kernel killed mysqld to free some up.

You need more RAM, or add swap, or you need to reduce your memory usage.

Thanks for your feedback, i followed the tutorial to configure virtualmin for a low memory config but everything has already been done. Could a WordPress plugin like wordfence (wich is known to be memory consuming) be the origin of the problem (even though it has been installed on the site for some time now without any issue) ?

Lots of things could be the culprit, or any combination of things could be the culprit. You can see memory usage (roughly) with top or by looking at the process list module in Webmin.

Many services have memory limits and cache settings that can cause higher or lower memory usage (trading memory usage for performance, generally).

Adding a swap file should get you running reliably again, and then you can spend time figuring out what to get rid of or reduce in size.

I checked the “running processes” tab in virtualmin and here’s the result

Do you see anything wrong with it considering that there’s one wordpress (WooCommerce) website hosted on the VPS with an average of 20-30 users daily ?
Thank you

root 1.47 GiB /usr/bin/python -s /usr/bin/fail2ban-server -xf start
mysql 1.44 GiB /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/li …
polkitd 598.64 MiB /usr/lib/polkit-1/polkitd --no-debug
root 560.74 MiB /usr/bin/python2 -Es /usr/sbin/tuned -l -P
root 537.15 MiB /usr/sbin/NetworkManager --no-daemon
hostname 507.82 MiB /opt/rh/rh-php70/root/usr/bin/php-cgi
hostname 496.35 MiB /opt/rh/rh-php70/root/usr/bin/php-cgi
[16966hostname 492.35 MiB /opt/rh/rh-php70/root/usr/bin/php-cgi
hostname 483.93 MiB /opt/rh/rh-php70/root/usr/bin/php-cgi
hostname 482.35 MiB /opt/rh/rh-php70/root/usr/bin/php-cgi
apache 474.19 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.8 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.76 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.75 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.75 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.74 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.74 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.74 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.67 MiB /usr/sbin/httpd -DFOREGROUND
apache 473.66 MiB /usr/sbin/httpd -DFOREGROUND
root 473.08 MiB /usr/sbin/httpd -DFOREGROUND
root 354.49 MiB /usr/bin/python2 -Es /usr/sbin/firewalld --nofork --nopid
root 320.64 MiB /usr/sbin/rsyslogd -n
apache 262.33 MiB /usr/sbin/httpd -DFOREGROUND
nobody 196.46 MiB proftpd: (accepting connections)
root 195.73 MiB /usr/libexec/webmin/virtual-server/
root 186.52 MiB /usr/lib/systemd/systemd --switched-root --system --deserialize 22
named 165.61 MiB /usr/sbin/named -u named -c /etc/named.conf
root 123.32 MiB /usr/sbin/crond -n
chrony 115.04 MiB /usr/sbin/chronyd
postfix 115.01 MiB smtpd -n submission -t inet -u -o stress= -s 2 -o smtpd_sasl_auth_enable=yes
postfix 115.01 MiB smtpd -n submission -t inet -u -o stress= -s 2 -o smtpd_sasl_auth_enable=yes
postfix 115.01 MiB smtpd -n smtp -t inet -u -o stress= -s 2 -o smtpd_sasl_auth_enable=yes
postfix 115.01 MiB smtpd -n smtp -t inet -u -o stress= -s 2 -o smtpd_sasl_auth_enable=yes
postfix 115.01 MiB smtpd -n submission -t inet -u -o stress= -s 2 -o smtpd_sasl_auth_enable=yes
mysql 110.66 MiB /bin/sh /usr/bin/mysqld_safe --basedir=/usr
root 110.27 MiB /usr/sbin/sshd -D
root 107.52 MiB /sbin/agetty --noclear tty1 linux
root 100.48 MiB /sbin/dhclient -d -q -sf /usr/libexec/nm-dhcp-helper -pf /var/run/dhclient-eth0. …
root 92.54 MiB /usr/bin/perl /usr/libexec/webmin/ /etc/webmin/miniserv.conf
root 89.71 MiB /usr/bin/perl /usr/libexec/usermin/ /etc/usermin/miniserv.conf
postfix 87.87 MiB qmgr -l -t unix -u
postfix 87.71 MiB tlsmgr -l -t unix -u
postfix 87.69 MiB pickup -l -t unix -u
postfix 87.69 MiB anvil -l -t unix -u
root 87.59 MiB /usr/libexec/postfix/master -w
root 74.78 MiB /usr/sbin/saslauthd -m /run/saslauthd -a pam -r
root 74.78 MiB /usr/sbin/saslauthd -m /run/saslauthd -a pam -r
root 74.78 MiB /usr/sbin/saslauthd -m /run/saslauthd -a pam -r
oot 74.78 MiB /usr/sbin/saslauthd -m /run/saslauthd -a pam -r
root 74.78 MiB /usr/sbin/saslauthd -m /run/saslauthd -a pam -r
rpc 67.65 MiB /sbin/rpcbind -w
dbus 56.94 MiB /usr/bin/dbus-daemon --system --address=systemd: --nofork --nopidfile --systemd- …
root 54.55 MiB /usr/lib/systemd/systemd-journald
root 54.22 MiB /sbin/auditd
root 50.53 MiB ps --cols 2048 -eo user:80,ruser:80,group:80,rgroup:80,pid,ppid,pgid,pcpu,vsz,ni …
root 43.98 MiB /usr/lib/systemd/systemd-udevd
root 25.76 MiB /usr/lib/systemd/systemd-logind
root 15.7 MiB /usr/sbin/dovecot
root 13.25 MiB dovecot/config
root 11.41 MiB sh -c ps --cols 2048 -eo user:80,ruser:80,group:80,rgroup:80,pid,ppid,pgid,pcpu, …
root 9.64 MiB dovecot/log
dovecot 9.51 MiB dovecot/anvil

Actually, that probably doesn’t tell us anything useful (I guess the Webmin PS output doesn’t show RES vs. Virtual memory usage…this is probably wildly over-representing memory usage of each of these process). Check top (you’ll need to login via ssh) instead…look at the RES field. That’s how much resident memory is in use for a given process.

But, it doesn’t matter what I think is “wrong”. Obviously you’re running out of memory. That’s not debatable. The question is what are you going to do about it. Your options are to shrink usage (MySQL could be shrunk by reducing its buffer/cache settings, at the expense of performance; if you’ve already gone through the “Virtualmin on Low Memory Systems” guide and followed those steps, you’ve already done pretty much everything I can tell you to do), increase memory, or add a swap file. All but adding memory will probably make performance worse.

Oh, one other thing, you could shutdown fail2ban. It’s kinda big, and not strictly mandatory. As long as you have very strong passwords and otherwise good security practices, you should be OK. You could also install something smaller, like sshguard (which is more limited, but very small).

1 Like

Thanks for the informations

I will upgrade the server from 4 to 8 Gigs of RAM and see if the crashes keep happening

I ran MySQL Tuner and have more infos on the running out of memory subject. You’re obviously right but the metrics that the tuner gave me seems ridiculously high making me question the relevancy to upgrade the vps. Here are the metrics

[–] Physical Memory : 3.6G
[–] Max MySQL memory : 153.5G
[–] Other process memory: 0B
[–] Total buffers: 704.0M global + 1.0G per thread (151 max threads)
[!!] Maximum reached memory usage: 36.1G (1001.18% of installed RAM)
[!!] Maximum possible memory usage: 153.5G (4256.23% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory


Variables to adjust:
*** MySQL’s maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
SET innodb_stats_on_metadata = OFF
query_cache_size (=0)
query_cache_type (=0)
query_cache_size (> 32M)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.

There’s obviously an over consumption of RAM by the database but it seems that it would happen even on an upgraded vps.

  • well - to be honest with you, have look what are you running at the first place - all those wp optimisation things does not need to be there, if your wp is clean and perfect code like mine - you should not have and single problem, however I can see you do have an problem - since you are running on centos plus you do some plugins which is not need it - please refer to centos and wp docs to use your wp install… virtualmin have nothing to do with wp - it only your own know-how…

virtualmin does servers not issues with wordpress nor wordpress plugins :slight_smile: