Question

MySQL on Ubuntu Keeps Crashing

Posted June 17, 2013 89.3k views
I've got an Ubuntu 12.x Droplet running, and for some reason MySQL keeps crashing to where I need to go in and restart the process ('sudo service mysql restart'). I'm not even sure how to start debugging this - could anyone give me some guidance on how to start? My MySQL logs are empty in /var/log/ Thanks!
6 comments

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

×
Submit an Answer
26 answers

Finally this helped, none of other solution did any change (add swap, monitor for banned iP’s, upgrade droplet):

## Edit /etc/my.cnf, and add the following line under the [mysqld] heading.
[mysqld]
innodb_buffer_pool_size=64M

Then restart: $ service mysql restart

  • I configured mine to be 32M and it’s not crashed since; thanks!

  • Hi,
    I just put this line in. Can you advise that 64M can be given in the basic 512MB server? Will that cause an issue or should I put 32M like suggested below?

    thx

  • Thanks @juanbrujo. I have followed your advice and time will tell if it works. But one good indication was that I got this in my error log for mysql (located for me at /var/log/mysql/error.log (or one of the older zipped logs if logrotate is working))

    160405 2:12:52 InnoDB: The InnoDB memory heap is disabled
    160405 2:12:52 InnoDB: Mutexes and rw_locks use GCC atomic builtins
    160405 2:12:52 InnoDB: Compressed tables use zlib 1.2.8
    160405 2:12:52 InnoDB: Using Linux native AIO
    160405 2:12:52 InnoDB: Initializing buffer pool, size = 128.0M
    InnoDB: mmap(137363456 bytes) failed; errno 12
    160405 2:12:52 InnoDB: Completed initialization of buffer pool
    160405 2:12:52 InnoDB: Fatal error: cannot allocate memory for the buffer pool
    160405 2:12:52 [ERROR] Plugin ‘InnoDB’ init function returned error.
    160405 2:12:52 [ERROR] Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.
    160405 2:12:52 [ERROR] Unknown/unsupported storage engine: InnoDB
    160405 2:12:52 [ERROR] Aborting

    After adding the innodb_buffer_pool_size=64M as suggested I still get this line in my log

    160405 20:09:05 InnoDB: The InnoDB memory heap is disabled

    but the other error, i.e. Fatal “error: cannot allocate memory for the buffer pool” and “Plugin 'InnoDB’ registration as a STORAGE ENGINE failed.” have not yet raised their heads.

    Looks like I can add this line to my.cnf to get rid of that error:

    innodb_use_sys_malloc = 0

    from here: https://xuri.me/2014/11/10/fix-error-innodb-the-innodb-memory-heap-is-disabled.html

    but not sure If i need to do this yet

  • OK i will try, keep in touch....

  • for me, that file is located in /etc/mysql/my.conf

MySQL could be running out of memory - try adding swap to your droplet:

https://www.digitalocean.com/community/articles/how-to-add-swap-on-centos-6
https://www.digitalocean.com/community/articles/how-to-add-swap-on-ubuntu-12-04
by Etel Sverdlov
Linux swaps allow a system to harness more memory than was originally physically available. Here's how to set up a linux swap file on Ubuntu 12.04
One rule of thumb I use when configuring a droplet with MySQL is I get all my processes running, then use top or landscape-sysinfo to see how much memory is free. Then I divide my free memory in half, that number is usually about 8-10M. Then I set the InnoDB buffer size to that amount to avoid having MySQL use up too much memory. This has cleared up 99% of my problems with MySQL crashing.
innodb_buffer_pool_size = 10M

It can also help to use swap space, but it's best to increase the size of your droplet as it degrades Digital Ocean's hardware to use swap.

This is a pretty common issue with WordPress, certainly not the fault of the DO preconfigured instance as I’ve experienced the same spinning up my own DO instances as well as back when I used to use AWS EC2. Moving up to an instance with more memory may help temporarily, but you’ll likely still eventually come face to face with this issue.

My sites will typically work flawlessly for several months until eventually they start to become plagued with MySQL crashes. My solution almost always ends up being spinning up a new instance and migrating over where the 2-4 month cycle just repeats itself. I’ve yet to be able to pin down the root cause or a solution.

grep for "OOM" in /var/log/syslog & /var/log/dmesg, it's usually a out of memory issue.
You might want to optimize your configuration as well, take a look at MySQLTuner: http://mysqltuner.com
i've got the same issue like your with a 512mb ram droplet

after adding a 512mb swap to my droplet the SQL crash seem like went away
Thanks. I was having the same problem - we'll see if this resolves the issue. Also. Can you give me an example of grepping for "OOM" in /var/log/syslog specifically?
Adding swap to your droplet (first comment) solving this problem.
i have added a 512 swap but face the same issue, my swap is on but is it possible that its not working fine?
@waqas464: Check MySQL's error logs to see why it's crashing.
Previous 1 2 3 Next