5

When the system runs out of memory, ubuntu 12.04 kills the mysql process:

Out of memory: Kill process 17074 (mysqld) score 146 or sacrifice child

So the process ends up killed. This happens at peaks of server load and mainly because of apache getting wild and eating the remaining available memory. Possible approaches could be:

  • Change somewhere somehow the priority of mysql, so it's not killed (probably a bad fix as something else will be killed)
  • Monitor the status of mysql and restart automatically whenever it's killed (the one I'm thinking about, but don't know how to do it).

How do you see it?

O. Jones
  • 103,626
  • 17
  • 118
  • 172
Alex
  • 53
  • 1
  • 3

2 Answers2

8

Abrupt termination of a database server is a very serious crash. You need to avoid this in a production system, because it may not restart cleanly.

The database server is a shared resource, and should almost never terminate in an unplanned fashion in production. The only thing that should cause unplanned termination is a catastrophic hardware or power failure. Most properly configured production data base servers have an unplanned termination once every ten years or less frequently. Seriously.

What to do?

Fix your apache configuration. Limit the number of worker threads and processes it can use, so it can't run wild. Learn how to do this. It's vital. See here: http://httpd.apache.org/docs/current/mod/mpm_common.html#maxrequestworkers

Fix the defects in your web app that are causing your apache to run wild.

If you can, move your mysqld server to a different server machine from apache, so the two don't contend for the same hardware resources.

Configure your mysqld to limit the number of connections it will accept from apache worker threads or other clients. Your web app probably handles the situation where a worker thread needs to wait for a connection. See here. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_max_connections

Are you on an EC2 micro instance? You need to do some serious tuning. See here: http://ubuntuforums.org/showthread.php?t=1979049

O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 4
    Thank you Ollie! Yes, you are right, I'm trying to squeeze an EC2 micro instance. Then I'll keep tuning apache and mysql and start looking into scale up and scale down. But is there a way to detect that mysql went down and reboot it automatically? Just in case if it happens whenever I'm not monitoring the server and while I've not optimized it more. – Alex Jun 25 '13 at 19:21
3

You can check mysql status every minute (with cron) and restart if it is crashed:

* * * * * service mysql status | grep running || service mysql restart
torayeff
  • 9,296
  • 19
  • 69
  • 103
  • thanks for this. it worked for me. just that /var/spool/mail/root becomes big after sometime due to this. – sulabh Sep 14 '16 at 07:55
  • 1
    You should add the full path to `service` as I was having [this issue](https://stackoverflow.com/questions/8127433/unable-to-run-a-service-command-via-cron): `* * * * * /sbin/service mysql status | grep running || /sbin/service mysql restart` – PapyDanone Mar 15 '18 at 21:54