Question

How to know the reason MySQL keep crashing?

  • Posted September 10, 2014

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/

Subscribe
Share

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 :)

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.

Besides, shouldn’t it restart automatically after a crash?? Isn’t that what a SERVICE is about?

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

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

@kamaln7 Actually it was enabled from server pilot.

How do you set up swap?!

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

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


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

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.

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

Hello,

What you can also do is to use the MySQLTuner script.

The MySQLTuner is a script written in Perl and allows you to quickly test your MySQL configuration and it gives you suggestions for adjustments to increase performance and stability.

According to the official GitHub page, it supports 300 indicators for MySQL/MariaDB/Percona Server in this last version.

To run the script you could do the following:

  • SSH to your Droplet
  • Download the script:
wget http://mysqltuner.pl/ -O mysqltuner.pl
  • Then execute it:
perl mysqltuner.pl

The script would run multiple checks against your MySQL instance, all checks done by MySQLTuner are documented here.

Also as stated in the official documentation, it is still extremely important for you to fully understand each change you make to a MySQL database server. If you don’t understand portions of the script’s output, or if you don’t understand the recommendations, you should consult a knowledgeable DBA or system administrator that you trust.

As a good practice make sure to always test your changes on staging environments before implementing them on your production database.

On the same note, if you want to have a worry-free MySQL hosting and focus on your application, I would recommend trying out the DigitalOcean Managed Databases:

https://www.digitalocean.com/products/managed-databases-mysql/

This was mini tutorial was posted from @bobbyiliev in this question in our community: https://www.digitalocean.com/community/questions/how-to-tweak-mysql-mariadb-configuration-for-increased-performance-and-stability

Hope that this helps! Regards, Alex

I had mysql crashing every 30 minutes exactly. Turns out mysql installs on Windows still default the flush_time to 1800 seconds (on nix boxes this value defaults to 0). Normally this is not a problem, but because I was using federated tables it was a problem. I guess mysql doesn’t know how to flush federated tables, therefore crash. So I set flush_time = 0 in my.ini and the problem went away.

This bug has been reported here: https://bugs.mysql.com/bug.php?id=47525&thanks=3&notify=87

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

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.

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.

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.

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.