MySQL Service Stop although Restart=Always

Question

I've an Amazon t2.small EC2 - 1 Core and 2GB RAM it's more than the minimum requirements for CentOS and Virtualmin

My System:

CentOS 7.5.1804
Virtualmin
MySQL 5.5.60-MariaDB
Apache 2.4.6

The MySQL service was always stop working a month ago after a lot of visits and I can restart it from Webmin again until I read an article in DigitalOcean blog to add the [Restart=Always] in the file [/etc/systemd/system/multi-user.target.wants/mariadb.service] and I test it by killing the process then the service starts again automatically No stop for 22 days

Now the service stopped working again, I opened the [mariadb.service] file I found the line [Restart=Always] still there but the service stopped randomly.

Note: I still can start it from the Webmin no problem with that, But all websites become unavailable because of DB connection.

I need to trace this issue but I've no experience for such issues .. How to solve that?

MySQL configuration: my.conf

symbolic-links=0
innodb_file_per_table = 1
myisam_sort_buffer_size = 8M
read_rnd_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
sort_buffer_size = 512K
table_open_cache = 64
max_allowed_packet = 1M
key_buffer_size = 16M

Some lines from {var/log/messages}

Oct 22 16:49:49 ns1 kernel: Out of memory: Kill process 13092 (mysqld) score 97 or sacrifice child

Oct 22 16:49:49 ns1 kernel: Killed process 13092 (mysqld) total-vm:1065992kB, anon-rss:182972kB, file-rss:0kB, shmem-rss:0kB

Oct 22 16:49:49 ns1 kernel: [12822]    27 12822    28326       73      14        0             0 mysqld_safe


Oct 23 08:58:11 ns1 kernel: [12822]    27 12822    28326       74      14        0             0 mysqld_safe
Oct 23 08:58:11 ns1 kernel: [19703]    27 19703   266425    39874     150        0             0 mysqld

Oct 23 20:04:47 ns1 saslauthd[531]: do_auth         : auth failure: [user=mysql] [service=smtp] [realm=] [mech=pam] [reason=PAM auth error]

Oct 23 22:21:25 ns1 kernel: [12822]    27 12822    28326       74      14        0             0 mysqld_safe
Oct 23 22:21:25 ns1 kernel: [19703]    27 19703   266425    48494     161        0             0 mysqld
Oct 23 22:21:25 ns1 kernel: Out of memory: Kill process 19703 (mysqld) score 103 or sacrifice child
Oct 23 22:21:25 ns1 kernel: Killed process 19703 (mysqld) total-vm:1065700kB, anon-rss:193976kB, file-rss:0kB, shmem-rss:0kB
Oct 23 22:21:25 ns1 kernel: [12822]    27 12822    28326       74      14        0             0 mysqld_safe
Oct 23 22:39:58 ns1 kernel: [12822]    27 12822    28326       76      14        0             0 mysqld_safe
Oct 23 22:39:58 ns1 kernel: [19246]    27 19246   266716    16891      98        0             0 mysqld
Oct 23 22:40:02 ns1 kernel: [12822]    27 12822    28326       76      14        0             0 mysqld_safe
Oct 23 22:40:02 ns1 kernel: [19246]    27 19246   266938    17064      98        0             0 mysqld
Oct 23 22:40:05 ns1 kernel: mysqld invoked oom-killer: gfp_mask=0x201da, order=0, oom_score_adj=0
Oct 23 22:40:05 ns1 kernel: mysqld cpuset=/ mems_allowed=0
Oct 23 22:40:05 ns1 kernel: CPU: 0 PID: 19357 Comm: mysqld Kdump: loaded Not tainted 3.10.0-862.11.6.el7.x86_64 #1

Have you looked at the mysql logs after it shuts down to see WHY it shut down? — longneck, Oct 24 '18 at 14:04
MySQL crashed because it ran out of memory. Run mysqltuner.pl and check its memory recommendations. — Michael Hampton, Oct 24 '18 at 14:30
I've add some lines of error log to the question .. I saw some (do_auth) before (Out of memory) message .. maybe it's related?? I don't know .. but I made a lot of visits and load a lot of pages 300 pages and the service working properly — Hady Shaltout, Oct 24 '18 at 14:31
Actually it's the system that ran out of memory. That's why you see `Out of memory: Kill process 13092`, the system decided to kill your MySQL process to free some memory and survive. — Pierre-Alain TORET, Oct 24 '18 at 14:41
So what should I do? the next level of EC2 t2.medium is doubled price :) :) — Hady Shaltout, Oct 24 '18 at 14:44
I would say it depends what is running on the server, and what kind of tuning has been done already. — Pierre-Alain TORET, Oct 24 '18 at 14:46
I saw the mysqltuner.pl but I didn't run such tuners for mysql or apache before — Hady Shaltout, Oct 24 '18 at 14:57
I review another mysql conf on DigitalOcean droplet (1GB RAM) I noticed that {key_buffer_size} is 16K while my conf is 16M .. is 16M too much?? — Hady Shaltout, Oct 24 '18 at 15:17
I understand from @MichaelHampton that mysqltuner.pl could help — Hady Shaltout, Oct 24 '18 at 15:54

score 1 · Accepted Answer · answered Nov 06 '18 at 02:37

Finally, I found the solution after 12 days of testing the MySQL start after killed automatically with hundreds of page visits.

The answer depends on the question System OS (CentOS) and MySQL Mariadb service

Solution: Just add some seconds before make service start - in my case I've added 45 seconds

Add the following lines under [service] section in {/etc/systemd/system/multi-user.target.wants/mariadb.service} - Of course the path depends on your system OS and MySQL service name (not all have mariadb.service)

Restart=always
RestartSec=45s

Do NOT forget to run the following commands

sudo systemctl daemon-reload

sudo systemctl restart mariadb.service

score 0 · Answer 2 · answered Oct 24 '18 at 13:44

No idea. I would start with a tout of the usual suspects

check the diskspace status just to be safe df -h and the memory free -m uptime (to check just after a crash)
check that the ownership and rights have not been changed on the data directories

score 0 · Answer 3 · answered Oct 24 '18 at 15:20

From this line in your log output : Oct 22 16:49:49 ns1 kernel: Out of memory: Kill process 13092 (mysqld) score 97 or sacrifice child

One can tell that your systemd did ran out of memory and that the Linux OOM (Out of memory) killer did stop your mysqld(MySQL daemon) process.

From the description of Restart= section, it's written that it won't restart if the service is stopped with systemctl stop or an equivalent operation, and maybe OOM killer is an equivalent for systemd, that I don't know.

Dangerous section

It seems that you can disable OOM killing for your process, by adding OOMScoreAdjust=-1000 to disable this functionnality.

You're right bro, That's too dangerous – Hady Shaltout Oct 24 '18 at 16:01 — Hady Shaltout, Oct 24 '18 at 16:01

MySQL Service Stop although Restart=Always

3 Answers3

Dangerous section