Mysql server stops very frequently

January 3, 2015 69.7k views

Recently I installed Zpanel on an Unbuntu 12.04.5 x64 droplet. All gose web, but i have a problem.
Mysql stops working and I have no idea why. Can someone help me with this problem?
I tried to check if mysql is up and make cronjob to restart it but that does not work.
I need to restart manually from command line. That is not a problem, but if i'm not at home, how can i restart the mysql?

21 comments
  • Did the log file indicate warnings before it died. For mariadb on Ubuntu, do

    tail /var/log/mariadb/mariadb.log
    

    For mysql on Ubuntu, do

    tail /var/log/mysql/error.log
    

    Look for shutdown messages or errors.

  • Yeah it's ridiculous. It happens to my site all the time. And I never know when it goes down. No one at digital ocean seems to have any idea why. Definitely considering switching because of it.

  • A power cycle seems to work to load everything back up, but that doesn't work all the time.

  • Show 18 more comments
6 Answers

A better solution is to check why your MySQL needs so much memory, try to optimize its resources before you invest on more, use this to optimize: Percona tools. Also check if your site/application/whatever is running smoothly or is causing heavy traffic peaks to your db.

If your mysql and application/website are running on the same droplet maybe you should consider something bigger but again check if is something you can solve before you invest on resources.

use "top" or better yet get a free newrelic account and monitor your server resources so you can be sure which process are consuming memory.

This is my error.log:

150103 16:15:26 InnoDB: Completed initialization of buffer pool
150103 16:15:26 InnoDB: highest supported file format is Barracuda.
150103 16:15:26  InnoDB: Waiting for the background threads to start
150103 16:15:27 InnoDB: 5.5.40 started; log sequence number 2230560
150103 16:15:27 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
150103 16:15:27 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
150103 16:15:27 [Note] Server socket created on IP: '127.0.0.1'.
150103 16:15:27 [Note] Event Scheduler: Loaded 0 events
150103 16:15:27 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.40-0ubuntu0.12.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

  • Unfortunately this log looks like you've restarted since the failure. Is that right? Otherwise look for an older timestamped log file to analyze, or you may need to wait till it happens again (with a saved copy of the log saved before restarting mysqld). If this is the logfile before you restarted, then you've got more work to do.

This is my error.log after crash:

root@AdultZone:~# tail /var/log/mysql/error.log
InnoDB: mmap(137363456 bytes) failed; errno 12
150106 18:04:02 InnoDB: Completed initialization of buffer pool
150106 18:04:02 InnoDB: Fatal error: cannot allocate memory for the buffer pool
150106 18:04:02 [ERROR] Plugin 'InnoDB' init function returned error.
150106 18:04:02 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
150106 18:04:02 [ERROR] Unknown/unsupported storage engine: InnoDB
150106 18:04:02 [ERROR] Aborting

150106 18:04:02 [Note] /usr/sbin/mysqld: Shutdown complete

The key error here is:

Fatal error: cannot allocate memory for the buffer pool

This shows that MySql has tried to use more memory than is available. As a result, it is crashing. Adding swap file can give you some flexibility when it comes to memory issues. Check out this tutorial for all the information:

by Justin Ellingwood
Swap space can be used as an "overflow" area for your system when you run out of RAM. The operating system can store data that would normally be kept in RAM on the hard drive in a specially formatted file. In this guide, we'll demonstrate how to create and use one of these files in Ubuntu 14.04.
  • I added Swap but same result.
    My droplet is 512MB and i have added a 256 Swap. Is that good or i should increase Swap to 512?

  • @zotacatalin As that tutorial points out, especially on lower memory systems:

    Generally, an amount equal to or double the amount of RAM on your system is a good starting point.

    On my personal 512MB droplets, I always add a 1GB swap file. If that's not enough, you might need to consider scaling up to a larger server. Either way, you should take a closer look at your memory consumption to better understand the problem.

I found that MySQL gets restarted due to automatic unattended upgrades that seems to be included in the later (I believe) Ubuntu releases.

Can be stopped by adding a blacklist entry to: /etc/apt/apt.conf.d/50unattended-upgrades

Have another answer? Share your knowledge.