Alevsk
By:
Alevsk

How to know the reason MySQL keep crashing?

September 10, 2014 14.8k views

Hi, currently I have a simple droplet (5 USD) with debian, a basic webserver with a wordpress with 100 visits per day, for some reason Mysql crash randomly, at the beginning I started the service manually, this was very annoying because my site was offline for several days until I realized wordpress was down, right now I have monit who monitors mysql and restart the service if it crash, I solved partially the problem but I still dont know why mysql keep crashing.

Note: there is no mysql.log in /var/log/mysql/

9 comments
  • I had the same issue on the $5/month plan before, seemed due to MySQL running out of resources. Have you set up swap?

  • Most likely low RAM and OOM issues. That is why my MYSQL used to crash every couple of days.

    1. Change to the low resource config for mysql (one of the three sample config files provided)
    2. Add a swap file
    3. Remove innodb support (not needed for wordpress... saves tons of ram)....

    Had 0 crashes since I did this :)

  • @adityars Could you share little more detail or provide a tutorial for your setup? Little nervous messing around the my.cnf.

  • How do you set up swap?!

  • @karlonC: Take a look at this tutorial: How To Add Swap on Ubuntu 14.04.

    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.
  • @kamaln7 Actually it was enabled from server pilot.

  • Hi Alevsk, how did you solve this issue? I am having same issue even with 1GB RAM. Could you please let me know if you find any solution? Thank you

  • Hi kothintl, I just add a swap partition in my dropplet and then enable slow queries in mysql to identify in which if my sites was the problem

  • Only remove innodb support if you know none of your plugins need it. I do not know for sure if WordPress blocks innodb, but I use it in some tables in my various other projects so that I can perform certain search functions.

6 Answers

I had the same issue, with a 2GB droplet size. Tried everything and eventually discovered it was an XML-RPC attack (Wordpress related).

Check this article for help:
How To Protect WordPress from XML-RPC Attacks on Ubuntu 14.04

WordPress is a popular and powerful CMS (content management system) platform. Its popularity can bring unwanted attention in the form of malicious traffic specially targeted at a WordPress site. There are many instances where a server that has not been protected or optimized could experience issues or errors after receiving a small amount of malicious traffic. This guide will show you how to protect WordPress from XML-RPC attacks on an Ubuntu 14.04 system.

Most likely this due to running out of memory. Take a look through /var/log/syslog for any messages from the kernel's Out Of Memory (OOM) killer.

MySql logs are sometimes also found in /var/log/mysql.log and /var/log/mysql.err instead of the /var/log/mysql/ directory. They might provide some hints.

If you haven't done so already, you should add a swap file to give yourself some leeway when your memory consumption grows.

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.

Perhaps try specifying a log file in /etc/mysql/my.cnf as follows:

general_log_file      = /var/log/mysql/mysql.log
general_log             = 1

I'm also guessing memory issues.

Currently having this problem as well. Just added swap, but now my 512MB droplet has become seriously slow and my site wont even open.

Will try the other stuff adityars suggested.

Hi,

We are having the same issue here. I find lots of forum posts about the same issue, but no 'official' recommendation... just 'try this', 'could be that', 'perhaps its..'.

PLEASE is there any procedure to follow in order to know 1) what´s causing the issue and 2) take proper action?

thks for your time.

This is definitely caused by the oom kernel killer beacause of mysql using up all your RAM (and swap if any). I solved this by looking at my mysql error log at /var/log/mysql/error.log and reverting changes made to my systems/sites by the time of error.

Turns out a wordpress plugin caused my problem and once it was out. I was back up with lots of free memory. (I deleted the plugin manually since there was no way to access the site without crashing mysql - see Managing Plugins - WordPress)

You could use the free -m command when logged in via ssh to check which specific site is causing mysql to use up so much memory and debug appropriately. Hope this helps

Have another answer? Share your knowledge.