Know WordPress? Need some advice.

This post is more than 2 years old.

Ok, time to throw in the towel and ask for help. ;) After upgrading the virtual server this blog runs on to a better level of hardware (1.7 gigs of RAM versus 0.6), my uptime improved quite a bit. But I still get - a few times a week - the infamous "Error Establishing Database Connection" issue. I've got a monitor set up for it now so I can reboot quickly, but last night it happened about an hour and a half after I went to bed so it was down for hours.

I've Googled quite a bit but most of what I've found focuses on the issue happening immediately and focus on your authentication values for MySQL. Obviously that isn't the problem. Other items I found focus on using caching plugins to help with performance. I'm using WP Super Cache so I've already done that.

So - any ideas? All I can think of is to try to find out if MySQL isn't using as much RAM as it can. Maybe there is a setting where I can tweak that higher.

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by davidosomething posted on 1/14/2015 at 3:55 PM

Try setting up a custom error page for the db error and have it email you some more useful information, like the DB host and its status, connection parameters, server load, etc.

This still works to get the error page: http://digwp.com/2009/11/cu...

But you'll have to add the PHP to do the mailing and determine what's useful yourself.

Comment 2 (In reply to #1) by Raymond Camden posted on 1/14/2015 at 4:01 PM

Hmm, that sounds ok, but I'd have to get email working. Email w/ Google Compute Engine is non-trivial. You have to sign up with SendGrid and all that. That's why I'm using a third party service even for my contact forms. This may be the issue that makes me get it working finally.

As for server load - I did look at CPU utilization and it was 40-50%. I see some spikes, but never over 50-60%. Seems reasonable, not 'red-lining' if that makes sense.

Comment 3 by Dan Vega posted on 1/14/2015 at 7:18 PM

I am still seeing this error here and there but not as often as you.

Comment 4 by Ben posted on 1/15/2015 at 12:25 AM

Could just be running out of connections of the MySql instance.

Do you have any logs running for the mysql service that could give some insight?

The scaling php book (https://www.scalingphpbook.... has some pretty good tips on tuning Mysql if you're interested.

Comment 5 (In reply to #4) by Raymond Camden posted on 1/15/2015 at 2:02 AM

No idea on the logs. I'm going to log in tomorrow during our company meeting and try to find both the config and the logs.

Comment 6 (In reply to #5) by Ben posted on 1/15/2015 at 2:09 AM

Depending on how it is setup you may need to tweak the mysql config to enable the correct log level (and restart the service).

Comment 7 (In reply to #6) by Raymond Camden posted on 1/15/2015 at 3:11 AM

Found the folder where log files should be -they are not. Now to find where the conf file should be.

Comment 8 (In reply to #7) by Raymond Camden posted on 1/15/2015 at 3:16 AM

Found it... but don't see a connections setting or a max memory even.

Comment 9 (In reply to #8) by Raymond Camden posted on 1/15/2015 at 3:19 AM

Ok, ran mysqladmin variables, and from what I see, I've got a max connections of 151 which seems pretty darn high.

Comment 10 (In reply to #9) by Raymond Camden posted on 1/15/2015 at 3:21 AM

I increased it to 200. (Sorry for all the comments folks.)

Comment 11 by eterps posted on 1/17/2015 at 2:50 PM

I set up Wordpress sites every now and then, and every time I see this error pop up I slap myself because I forgot to turn on page caching. You probably have this covered already, but just in case, I've had good luck with WP Super Cache. Also heard good things about W3 Total Cache.

Comment 12 (In reply to #11) by Raymond Camden posted on 1/17/2015 at 3:49 PM

Yep, I think I mention it above, and in the comments. :) While I'm convinced the issue is *only* MySQL, after this post was written I noticed I had a few settings in SuperCache that was not using the recommended values. I made those changes. Maybe it will help. I've gone a few days now without an issue, but even before that I'd get about the same sometimes.

Comment 13 by kr1000 posted on 1/20/2015 at 4:57 PM

I had some similar issues and figured out that my favorite linux distro on AWS didn't have any swap by default. I added a 2GB swap file for those occasions that I had a memory spike. Worked much better. The swap is rarely over a few MB but is there when needed.

Comment 14 (In reply to #13) by Raymond Camden posted on 1/20/2015 at 5:03 PM

I honestly have no idea if Google Compute has this issue. :)

Comment 15 (In reply to #14) by kr1000 posted on 1/20/2015 at 5:14 PM

I am not familiar with Google Compute but it appears from a quick search that it's not enabled by default. You can type "mount" at the command prompt to see if there is any reference to a swap partition. Swap can significantly reduce performance but can also provide a valuable safety net.

Comment 16 (In reply to #15) by Raymond Camden posted on 1/20/2015 at 5:14 PM

I'll try later if I need. I'm cautiously optimistic that my last set of changes may have helped.

Comment 17 by Nic Raboy posted on 1/20/2015 at 9:37 PM

Is your MySQL instance running on the same server as the web application? Also, what are your metrics?

I'm on a 1GB server and I'm processing 3,000 visits a day. I do get the same problem as you when a spike in traffic happens.

I'm curious to know whether or not it is because we are at the peak of our resources, or if it is a different issue.

Comment 18 (In reply to #17) by Raymond Camden posted on 1/20/2015 at 9:39 PM

Yep, same instance. Metrics? Roughly 4-5k page views per day. If sessions are visits, then it is about the same.

Comment 19 (In reply to #18) by Nic Raboy posted on 1/20/2015 at 9:44 PM

We may just be at our max. I'm not a server expert, but 4-5k sounds pretty high for 1.7GB of RAM.

People keep telling me to split the database from my application server, but I'm too cheap to pay for another server. I can imagine it being less stressful on the server though.

I'll check back on if someone has a magical fix for us. Like you, my server crashes like an hour after I go to bed and it is super annoying.

Comment 20 (In reply to #19) by Raymond Camden posted on 1/20/2015 at 9:52 PM

I don't know man - I mean - even 5k page views per day is just 208 page views per hour. That's 3.5 page views per minute. That should be *trivial* imo. Would be with ColdFusion for sure. :)

Comment 21 (In reply to #20) by Nic Raboy posted on 1/20/2015 at 10:15 PM

That is assuming you get a constant amount of traffic in a 24 hour period. Maybe your traffic is heaviest at night and occasionally it is just too much? What do your hourly Google Analytics say?

As for ColdFusion / PHP / whatever. The base language can always handle a lot. WordPress is just an over-bloated animal.

I ran 'P3 Plugin Profiler' and it said that each page load is making 48 queries on the database. Multiply that by however many visitors you have. The little things can add up.

Comment 22 (In reply to #21) by Raymond Camden posted on 1/20/2015 at 10:45 PM

True - I guess I'm still thinking that even if the hits are focused in around 9-5, if we multiply it by 4 let's say, it still doesn't seem like a lot.

48 hits per view - ugh. Well, that's one thing that SuperCache should help with for sure.

Comment 23 (In reply to #22) by Nic Raboy posted on 1/20/2015 at 10:54 PM

In theory, yes.

If you're up for some wizardry, I have a more majestic approach than just monitoring and quickly restarting. Check the following script:


#!/bin/bash
if (( $(ps -ef | grep -v grep | grep mysqld | wc -l) <= 0 ))
then
echo "MySQL is currently not running and will be restarted!" | mail -s "MySQL may have crashed" -r from@example.com to@example.com
/etc/init.d/mysql restart
else
echo "Running"
fi

Put it under your root cron and have it run every 1 minute or whatever you want. That way it will start back up and you'll get an email so you know it happened.

Comment 24 (In reply to #23) by Raymond Camden posted on 1/20/2015 at 10:58 PM

Actually, I still can't do mail here. :) For Compute, you have to use SendGrid. Apparently they have a good free tier, but I skipped it for now. (Using FormKeep for my contact form.)

Comment 25 (In reply to #24) by Nic Raboy posted on 1/20/2015 at 11:01 PM

Fair enough. The script should still get a restart going.

Comment 26 (In reply to #10) by Ben posted on 1/21/2015 at 9:52 PM

Did it get any better?

Comment 27 (In reply to #26) by Raymond Camden posted on 1/21/2015 at 11:03 PM

So far so good. But it may have been the SuperCache recommendations I changed.

Comment 28 by Raymond Camden posted on 1/23/2015 at 4:58 PM

sigh - well after a good run of no trouble, I've had 2 crashes (technically - unable to connect to db) in 24 hours. In both cases, my CPU usage was pretty high, 80%, which I think means I got some traffic spikes.

Comment 29 (In reply to #28) by Nic Raboy posted on 1/23/2015 at 6:22 PM

I'm assuming your blog makes more money than mine does. The guys at Ionic recommended this to me, but I couldn't afford it.

http://wpengine.com/

If your ads are making you enough to support better hosting, maybe it is worth a shot?

Comment 30 (In reply to #29) by Raymond Camden posted on 1/23/2015 at 6:31 PM

I'm like right in the middle there - one blog, but 130k page views. 100 bucks a month is a bit too much. I'd still turn a profit though. Plus - I've got a modded WP install - I can get around that with a .htaccess file if they would support it.

Comment 31 (In reply to #30) by Nic Raboy posted on 1/23/2015 at 6:33 PM

Yea I hear you. I'm on a $10 VPS right now. Anything else is a huge jump.

Comment 32 (In reply to #31) by Nic Raboy posted on 1/23/2015 at 7:23 PM

Have you seen this too?:

https://ewan.im/900/10-mill...

I've heard people mention Varnish before, but I've never used it.

Comment 33 (In reply to #32) by Raymond Camden posted on 1/23/2015 at 8:42 PM

Ditto. Looks a bit complex.

Comment 34 by Justin Carter posted on 1/25/2015 at 11:33 PM

It sounds swap file related to me too, you should check to see if OOM killer is stopping your MySQL process. You might just need to add swap space (and perhaps tune your MySQL memory settings a bit).

Comment 35 (In reply to #34) by Raymond Camden posted on 1/26/2015 at 3:12 AM

So dumb question - do you know how to add that to a Google Compute instance?

Comment 36 (In reply to #35) by Justin Carter posted on 1/26/2015 at 4:30 AM

I haven't used Google Cloud, but I'd guess the same way you would on most linux distros with mkswap, swapon, fstab etc:
https://www.digitalocean.co...

I run my site on a 512MB instance (Digital Ocean) and for me a swap file was necessary to keep MySQL up, even though I've tuned it down to use as little RAM as possible (and I've disabled InnoDB and just use MyISAM).

Often the recommendation is "don't use swap" but with very small amounts of memory it's almost impossible to avoid really. If I could justify paying for a 4GB RAM instance I'd just do that instead and not worry about it :)

The other alternative is to use Google Cloud SQL, but again, $$!

Comment 37 (In reply to #36) by Nic Raboy posted on 1/26/2015 at 4:38 AM

Hate to ninja in here, but what is your WordPress traffic on your 512MB instance? I'm using a 1GB instance with Digital Ocean, but I believe I have InnoDB enabled.

Curious to know what kind of boost that would do for me.

Thanks,

Comment 38 (In reply to #37) by Justin Carter posted on 1/26/2015 at 4:48 AM

It's not quite an apples-apples comparison and I don't use WordPress, but MySQL + InnoDB would eat up around 400MB RAM with no tuning, and on a 512MB instance that's bad. Tuned with MyISAM it's currently using 120MB RAM, but my traffic is very very low.

On a 1GB+ instance it wouldn't have been a problem for me and I would have just used InnoDB :)

I forgot to re-iterate to Ray though, check to see if OOM killer is the reason your MySQL process is "crashing". It might not be... But if it is, then tuning MySQL and/or adding a swap file could help. Just google: "oom killer mysql" and you'll see what I mean.