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.
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.
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.
I am still seeing this error here and there but not as often as you.
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.
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.
Depending on how it is setup you may need to tweak the mysql config to enable the correct log level (and restart the service).
Found the folder where log files should be -they are not. Now to find where the conf file should be.
Found it... but don't see a connections setting or a max memory even.
Ok, ran mysqladmin variables, and from what I see, I've got a max connections of 151 which seems pretty darn high.
I increased it to 200. (Sorry for all the comments folks.)
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.
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.
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.
I honestly have no idea if Google Compute has this issue. :)
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.
I'll try later if I need. I'm cautiously optimistic that my last set of changes may have helped.
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.
Yep, same instance. Metrics? Roughly 4-5k page views per day. If sessions are visits, then it is about the same.
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.
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. :)
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.
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.
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:
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.
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.)
Fair enough. The script should still get a restart going.
Did it get any better?
So far so good. But it may have been the SuperCache recommendations I changed.
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.
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.
If your ads are making you enough to support better hosting, maybe it is worth a shot?
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.
Yea I hear you. I'm on a $10 VPS right now. Anything else is a huge jump.
Have you seen this too?:
I've heard people mention Varnish before, but I've never used it.
Ditto. Looks a bit complex.
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).
So dumb question - do you know how to add that to a Google Compute instance?
I haven't used Google Cloud, but I'd guess the same way you would on most linux distros with mkswap, swapon, fstab etc:
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, $$!
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.
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.