tostring
By:
tostring

Unsolveable epic MySQL crashes with Wordpress

August 24, 2014 25.1k views

Hi everybody, sorry about the dramatic title, but I'm truly desperate about an issue in my droplet with LAMP and Wordpress installed that seems to be really unsolveable.

It started in the beginning of 2014, when I tried to access my blog I got an "Error Establishing a Database Connection". I searched on Google and here in Digital Ocean community and I found a lot of tutorials and fix strategies.

Basically, something happens on Apache that makes it overload MySQL (as I imagine). Once MySQL can't allocate enough RAM to do whatever Apache wants, it crashes and my blog stays unavailable.

Well, I used to have a 500MB droplet (the cheapest one), and the first try was on the [swap files](http:// https://www.digitalocean.com/community/tutorials/how-to-add-swap-on-ubuntu-12-04). It apparently solved the problem and the blog stayed online for one week without crashing. And then, suddenly, it started again. It started crashing and even when I restarted it all, it crashed again. In this week I had no traffic changes and no new posts/new plugins. Nothing changed on my blog.

A couple of weeks later, I tried a lot of possible solutions, like this and this. I tried also a tunning in MySQL. Nothing helped.

I tried playing with max_connections, thread_cache_size and other MySQL config params... nothing helped. After two months of daily tries, I quit and opened a ticket at Digital Ocean. The only answer? You have to upgrade your droplet for a 1GB droplet. I wasn't believing it was that because how can a droplet without no traffic and only a few posts could demand a 1GB droplet? But I had nothing else to try and I upgraded.

The Wordpress stayed alive for two weeks! Yay! That was the solution, right!? No! It started again with the exactly same error (I'll show the logs on the end of this post).

For the last tries, I installed Wordpress W3 Cache and some plugins to relieve MySQL calls. It wasn't the solution neither.

Now I'm lost and have anything else to do. I won't upgrade to a 2GB RAM because the traffic and the number os posts on my blog didn't increase. What I did yesterday? I created a script in a crontab to restart MySQL and Apache each 15 minutes. I know it's the ugliest thing to do, but I think this problem won't solve.

Wanna know the good news? IT DIDN'T SOLVE ALSO! LOL! MySQL keeps crashing and doesn't come back even with a script doing exactly the steps I do to restart MySQL and Apache2.

So, I believe you understand why I'm desperate, right? Sorry about this long testimonial, but now that I have nothing to work on my blog, since it doesn't work, I have a lot of free time to write.

Here are the logs on Apache2 and MySQL:

Apache2

[Sun Aug 24 08:42:05 2014] [notice] child pid 17841 exit signal Segmentation fault (11)
[Sun Aug 24 08:42:11 2014] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Sun Aug 24 08:46:48 2014] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Sun Aug 24 08:46:59 2014] [notice] child pid 17900 exit signal Segmentation fault (11)
[Sun Aug 24 08:48:36 2014] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Sun Aug 24 08:48:57 2014] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Sun Aug 24 08:49:07 2014] [notice] child pid 17927 exit signal Segmentation fault (11)
[Sun Aug 24 08:49:07 2014] [notice] child pid 17928 exit signal Segmentation fault (11)
[Sun Aug 24 08:49:07 2014] [notice] child pid 17929 exit signal Segmentation fault (11)
[Sun Aug 24 08:49:07 2014] [notice] child pid 17930 exit signal Segmentation fault (11)
[Sun Aug 24 08:55:15 2014] [error] (12)Cannot allocate memory: fork: Unable to fork new process
[Sun Aug 24 08:55:42 2014] [notice] child pid 18022 exit signal Segmentation fault (11)
[Sun Aug 24 08:56:01 2014] [error] (12)Cannot allocate memory: fork: Unable to fork new process

MySQL

140824  9:06:10 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140824  9:06:10 [Note] Plugin 'FEDERATED' is disabled.
140824  9:06:10 InnoDB: The InnoDB memory heap is disabled
140824  9:06:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140824  9:06:10 InnoDB: Compressed tables use zlib 1.2.3.4
140824  9:06:10 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
140824  9:06:10 InnoDB: Completed initialization of buffer pool
140824  9:06:10 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140824  9:06:10 [ERROR] Plugin 'InnoDB' init function returned error.
140824  9:06:10 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140824  9:06:10 [ERROR] Unknown/unsupported storage engine: InnoDB
140824  9:06:10 [ERROR] Aborting

140824  9:06:10 [Note] /usr/sbin/mysqld: Shutdown complete

140824  9:06:10 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140824  9:06:10 [Note] Plugin 'FEDERATED' is disabled.
140824  9:06:10 InnoDB: The InnoDB memory heap is disabled
140824  9:06:10 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140824  9:06:10 InnoDB: Compressed tables use zlib 1.2.3.4
140824  9:06:10 InnoDB: Initializing buffer pool, size = 64.0M
InnoDB: mmap(68681728 bytes) failed; errno 12
140824  9:06:10 InnoDB: Completed initialization of buffer pool
140824  9:06:10 InnoDB: Fatal error: cannot allocate memory for the buffer pool
140824  9:06:10 [ERROR] Plugin 'InnoDB' init function returned error.
140824  9:06:10 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140824  9:06:10 [ERROR] Unknown/unsupported storage engine: InnoDB
140824  9:06:10 [ERROR] Aborting

140824  9:06:10 [Note] /usr/sbin/mysqld: Shutdown complete

Thank you.

3 comments
  • This is low memory problem. Try to add big swap file - 1024MB.

  • I have exactly same problem with same configuration. Current request per day per site is ~40 req. So I wonder what need more memory.

  • I upgraded to a 2gb droplet and set up a 1024mb virtual memory, I'm running low volume websites.

    The problem disappeared after upgrading last year, but has now happened twice this week.

25 Answers

I had a similar problem and was able to finally fix it.

Background

I tried a bunch of tutorials that recommended tuning my MySQL installation and Apache configuration with no luck.

Identifying the Problem

It turns out that my site was being subjected to a brute force attack that targeted the WordPress xmlrpc.php file. H/T @sierracircle.

In order to check for this, you should view your apache2 access logs and look for suspicious POST requests. In my case, I found a huge number of POST requests to the /xmlrpc.php file coming from the same IP address (about 2 or 3 requests per second).

cd /var/log/apache2/
cat access.log

Solution

In order to fix this, I banned the offending IP address via Iptables:

Ban IP the malicious IP address (replace example with whatever IP address you want to ban) :

iptables -A INPUT -s 46.166.139.20 -j DROP

To view blocked IP address:

iptables -L INPUT -v -n

Reference:
http://www.cyberciti.biz/faq/linux-howto-check-ip-blocked-against-iptables/

  • This was going on on my site! Banned that bastard. Thanks!

  • Thank you so much! I was having the same issue.

  • I can't believe it! It seems I was being attacked as well. Banned 3 ips and voilá... thank you so much!

  • Had just the same problem! banned the list of IP (there were 3 ips too) in iptables and got everything running smoothly again. Thanks! :)

  • So I took a bunch of IP's people have been reporting on here and a few that were attacking me as well. I dropped them all in my Iptables, unfortunately the list keeps growing.. but here are a few bad eggs that have been brute force attacking me and others...

    191.96.249.20

    185.130.4.120
    185.103.252.170
    185.130.4.197

    159.122.224.173
    89.248.167.131

    191.96.249.53
    191.96.249.54

  • Thank you SO much. This has been a consistent issue and you nailed it.

  • Awesome ! Been searching for a while now...
    You, sir, have my deepest thanks !

I had a similar problem from some time. I want to say that in my opinion 500MB is and should be enough memory for low traffic. In the past I had almost 200 users at the same time for at least 2 hours and nothing happened. And moreover there was almost nothing cashing and with original settings.

How this it looks like. IT starts with a huge number of processes from apache that consuming a lot of memory and because of that there is 100% CPU and 100% Hard drive usage I even can't do anything in console like restart the system. Because of that mysql crashes and here you are error establishing database connection.

When it started ?, during a lot of months nothing happened so probably it was worpdress update OR wordpress update with some plugins update. I can't tell you now if this is because of plugins but I found jetpack and sitemap creator that using cron. I assume that broken link checker, W3 cache and few other use cron either but currently under investigation. And THIS DATABASE ERROR happens periodically.

I haven't noticed any brute attack on xmlrpc.php but could be something missed ?. Frankly nothing in apache logs.

IT could be that there is no straight answer for that and it is complex problem that include plugins and some kernel problems like cleaning cache from memory. I done some updates described here but currently not sure if that will help me and us ;(. I will write later.

I think there should some solution wrote by administrators, answer is simple it is a common problem and happens almost everyone. So few check points with solutions would be great.

Just signed up to say thanks to markjtalbot
Thanks a ton.

I had this error and it plagued me for weeks. Sorry you got frustrated and switched to managed hosting. I'm posting this answer for others having the same problem.

For me it was a combination of heavy bot traffic and sub-optimal Apache configuration. What happens is amid the high bot traffic, Apache crashes, restarts automatically like it's supposed to, but there isn't enough memory to re-launch MySQL as well, hence the Database Connection Error.

Apache2Buddy is a quick diagnostic tool you can use to evaluate your current Apache settings given the amount of RAM you have and the size of your application and make recommendations:

$ curl -L http://apache2buddy.pl/ | perl

For me the MaxRequestWorkers (MaxClients for older Apache versions) was too high. Simply following the recommendations from Apache2Buddy and restarting Apache worked. (The file where the configuration lived was a little hard to find too. It's at /etc/apache2/mods-available/mpm_prefork.conf).

At the beginning, I set up a stress test with JMeter, with which I was able to reproduce the error. It performed much better under the new Apache configs.

As for the bot traffic, there are a number of WordPress plugins for dealing with that. I like IP Geo Block.

I wrote an article on this as it was such a hassle. For more info on the stress test, plugins for dealing with bots, check it out here

I had the same problem with zero visitor except myself on 500mb with 2 wordpress sites. Like you I followed the same tutorials and steps but in the end I had to change for the next plan and since then I have no problem which is sad. I don't know what's going on with DO!

I am going to guess that one of your plugins is using wp-cron for some task and causing WordPress to crash MySQL . But that is only a guess. You did not mention if you are using any special plugins.

If that is indeed the case, it is possible to change that task and use a cronjob on your server...

but that is all guessing. It might help if you mention what theme you are using and what plugins you have installed.

I am not sure that upgrading to the next level of DigitalOcean is the best approach to trouble-shoot your problem,. I am not sure why anyone suggested that if you are not getting much traffic.. If it were me I would do this:

spin up a new droplet, maybe with Ubuntu LAMP. Maybe use a sub-domain to point to it.
Also, change the default link for phpmyadmin if you use that:

go into /etc/apache2/conf-available and find phpmyadmin.conf. ....change Alias from phpmyadmin to something you can remember easily...

install WordPress (all new, latest version). Use new passwords for everything.
Now add your theme..wait for awhile. is everything working?
If so, one by one start adding whatever plugins you are using. wait an hour or so between each plugin you add to give it a chance to crash WordPress

You might find your new droplet works fine. If so, you could then finally migrate your database over, and your original domain.

Hi adam, thank you for your willingness to help.

The theme I'm using is Knews.

Here are the plugins:

  1. Broken Link Checker - Version 1.9.3 | By Janis Elsts
  2. Disqus Comment Syste - Version 2.77 | By Disqus
  3. Google Web Fonts Customizer (GWFC - Version 1.0.2 | By Chanif Al-Fath
  4. Google XML Sitemaps - Version 4.0.7 | By Arne Brachhold
  5. jQuery lazy load plugin - Version v0.21 | By Andrew Ng
  6. nrelate Related Content - Version 1.2.0 | By nrelate and SlipFire
  7. Post Reading Time - Version 1.1 | By Bostjan Cigan
  8. Shareaholic | share buttons, analytics, related content - Version 7.5.0.2 | By Shareaholic
  9. Simple Google Analytic - Version 2.2.2 | By Jerome Meyer
  10. Tiled Galleries Carousel Without Jetpack - Version 1.4 | By Raja CRN
  11. Video Metabox - Version 1.1.2 | By Jesse Overright
  12. W3 Total Cache - Version 0.9.4 | By Frederick Townes
  13. WordPress SEO - Version 1.5.4.2 | By Joost de Valk
  14. WP-Optimize - Version 1.8.6 | By Ruhani Rabin
  15. WP Google Authorship - Version 2.0 | By Mervin Praison
  16. WP Smush.it - Version 1.6.5.4 | By WPMU DEV

Looking at your list, my first suspect would be Broken Link Checker, since it runs on a schedule, and uses the database.

If it had some issues, it could easily crash MySQL.

You may try to temporarily remove that plugin for a few days and see if you are still getting MySQL crashes.

  • Thank you adam. I disabled the plugin and now I'll be watching the blog to see if the crashes stop.

    If it's the solution, we'll build a church and you'll be our new God.

    Thanks a lot.

It didn't solve the problem.

Is there anyway to check if a plugin uses wp-cron?

Generally speaking: if a plugin needs to do things on a schedule (check things, send emails at certain times, etc.) then it probably uses wp-cron.

Since that plugin was not the culprit, you might consider disabling ALL plugins...waiting..does that solve it?

If so, then you must enable plugins one by one to see which one is causing the crash.
If not, then you try changing the theme and see if that does it.
If not the theme or plugins, then we start looking at your server.

It is tedious work, but it is important to zero in on exactly what is causing the problem, instead of throwing money away on upgrades you might not need.

but to answer your question:
"Is there anyway to check if a plugin uses wp-cron?"

  • ssh into your server
  • cd /var/www/yoursite/wp-content/plugins
  • grep -r "wp-cron"

That should list files where wp-cron is mentioned..

  • I found out that Broken Link Checker and Google XML Sitemaps uses wp-cron. I disabled them both.

    Let's see.

    Thank you!

Just out of curiosity, do you have any backup scripts running on a schedule that backup your database?

  • Yes, but just a Digital Ocean backup schedule.

    I also tried to relate it, but the backups execute at 6:00 am and the droplets are crashing everytime.

    Just to update this thread, I disabled W3 Total Cache and since friday the blog is online. No crashings. I think we found the cause of that.

    I'll keep watching, but I'm almost sure it was because of W3.

    @sierracircle, thanks a lot for your willingness to help me.

I'm dealing with the same problem on two droplets.. It keeps crashing if I keep my finger on the F5 (refresh) button or if I suddenly navigate from one page to another faster that usual..

I tried all the solutions above - swap, cronjob on crash.. all that's left for me is increasing the memory of the droplet, I'll try that too

I'm having the same problem, new droplet, no traffic, underscores theme from WordPress. Just a couple of plugins all very popular, s2member, wordfence, wordpress seo. No answer from DO

Hi Ana... I had to take out my blog from DO to another host since it was really an unsolveable question. Neither the staff could help me.

Dear DO Staff,

I'm having EXACTLY the same problems!!! Please help or I'm afraid there's no other solution except tostring's.. :(

Same problem here! Any solution?

I updated the droplet from 512MB to 1GB, I also added SWAP memory (1GB) and it hadn't crashed in months so far. (with multiple wordpress sites)

I also have the same problem... :(
If the problem persists, I'll have to relocate my site to another provider as well.

My droplet doesn't have epic MySQL crashes, but according to the logs, it does crash a few times per day which is of course not good.

Serverpilot says that the best solution is to increase RAM, even though my droplet only regularly consumes 50% of RAM, so yes - this is an ongoing concern of mine.

It also makes me wonder whether PHP memory limit is a factor. Serverpilot sets PHP memory limit at 128mb per droplet.

If each Wordpress install requires 40mb at minimum, then having several Wordpress installs in one droplet may cause PHP to hit that limit.

Right now, I'm trying to figure out how to increase PHP memory limit. Maybe to 256mb.

Scratch my post directly above. That memory limit set in php.ini is "per script". So, as far as I know (right now), it shouldn't be a factor. If you've got several wordpress sites in one droplet, each of them can use up to the WP memory limit. Plus, whatever plugins you've got installed.

The issue is not in wordpress. I had the same issue with stopped mysql and apache. The issue is in buggy kernel, in my case this was ubuntu 12 3.2.0-24-virtual x86. Usually some there is "used" ram and cache. in normal system caches should be dropped, when any process needs more memory, but in buggy kernels this does not happen.

So there are a few solutions:

  • upgrade kernel to a good one.
  • use 64bit OS instead or 32bit
  • forcefully drop memory caches

The issue might be with apache2. I just got the "error establishing database connection" problem and checked top for the memory usage and there was maybe 11 Mb free of RAM and almost all of it was being used up by apache.

There's a tutorial on DigitalOcean that might help you fix your issue if it's related to apache.

In particular, I had to modify the mpm_prefork.conf to lower the amount of requests apache can handle (MaxClients for Apache 2.2, MaxRequestWorkers for Apache 2.4). The next step is turning on whatever aggressive caching I can for WordPress.

by Matthew Nuzum
If you're running Apache on one of the smaller sizes of droplets, or if you want to maximize your performance on the bigger droplets, here are a few things you should do. I'll be using Ubuntu 12.04 in the examples but the principles I'm demonstrating are applicable to other versions of Linux as well.
Have another answer? Share your knowledge.