VPS keeps crashing, any solutions?

TacoCat

TacoCat

Bueno...
BuSo Pro
Joined
Apr 2, 2015
Messages
502
Likes
558
Degree
2
So two days ago I migrated from my shared Hostgator hosting to Knownhost VPS and now I'm having some troubles.

It keeps crashing and sending me this: The service “mysql” appears to be down. As I'm completely retarded when it comes down to this kind of technicality, I'm reaching out to you guys, maybe someone will know what to do. Because as soon as I reach 300+ users, it fails and shuts down for 5 minutes, then it recovers and works fine.

Any suggestions?
 
Sounds like you could have a memory leak or some corruption in your table.

Knownhost is 100% managed. If you open a ticket with them in the support section, you'll get a response back very fast (maybe not as fast on Thanksgiving day), and they will investigate, keep you informed, and eventually present a solution and ask for your permission to act on that solution.

I'd definitely hit them up pronto and let them be figuring that out while you eat copious amounts of turkey.
 
Sounds like mysql reached its maximum connection settings limit in /etc/mysql/my.cnf.

Try increasing "max_connections = 100" in /etc/mysql/my.cnf(if on debian/ubuntu) to higher.

I think it's on /etc/my.cnf on centos/rhel.

then debian/ubuntu needs a restart after that: sudo service mysql restart
no idea about centos/rhel

TacoCat said:
Because as soon as I reach 300+ users
Click to expand...
 
Last edited:
You need to tuner your mysql to your new settings. If you just upgraded but didn't change settings then you aren't using the maximum of your capabilities. MYSQL Tuner...
 
qwianghomingh said:
Sounds like mysql reached its maximum connection settings limit in /etc/mysql/my.cnf.

Try increasing "max_connections = 100" in /etc/mysql/my.cnf(if on debian/ubuntu) to higher.

I think it's on /etc/my.cnf on centos/rhel.

then debian/ubuntu needs a restart after that: sudo service mysql restart
no idea about centos/rhel
Click to expand...

CCarter said:
You need to tuner your mysql to your new settings. If you just upgraded but didn't change settings then you aren't using the maximum of your capabilities. MYSQL Tuner...
Click to expand...

If only I would know how to do any of those things. :D Is there any good guides out there that pops right into your mind? Because at this point I don't even know how to open the command prompt.
 
Okay, ask your host's technical support if you are on managed service. Otherwise, contract someone.

I wish my webserver crashed from so many visitors

TacoCat said:
If only I would know how to do any of those things. :D Is there any good guides out there that pops right into your mind? Because at this point I don't even know how to open the command prompt.
Click to expand...
 
You need to upgrade your VPS to VPS-2, VPS-3, etc. You are running out of RAM/memory for your plan. I experienced same issues with Knownhost before upgrading earlier.

300 users shouldn't be tapping out your VPS-1 plan (or whichever you're on) so you should check into server optimization later.
 
Last edited:
qwianghomingh said:
Sounds like mysql reached its maximum connection settings limit in /etc/mysql/my.cnf.

Try increasing "max_connections = 100" in /etc/mysql/my.cnf(if on debian/ubuntu) to higher.

I think it's on /etc/my.cnf on centos/rhel.

then debian/ubuntu needs a restart after that: sudo service mysql restart
no idea about centos/rhel
Click to expand...

Today I asked about the connections, it turns out that I'm not using the maximum, the maximum that I've used was 8 out of 50. So that is not the issue. It probably was the memory amount. We will see today, I've upgraded from VPS - 2 to VPS - 4.

I think something is fucked up in my Wordpress theme as @Ryuzaki said, but I'm not a tech guy, so I don't really know what to look for. Anyways, I will find someone who can fix it in the nearest future.
 
Well, if your WP install is maxing out memory (2GB at VPS-2) with 8 connections, then yes, I'd look at what the theme is doing

::emp::
 
Could also be a plugin eating up the RAM
 
PM me with link to the site or place where you bought the theme if you'd like, I can review. I don't need a login or anything for a quick peak.

It's definitely something from front end making too many DB calls. The same error you're getting was the error I was experiencing with a heavier site and the live chat feature eating away. The upgrade was what fixed it for me.

The site I have at VPS-3 is over 200k users, live chat, etc, and heavier traffic. If VPS-4 doesn't work for then you should put optimization as a higher priority because a 300 user site should not kill your VPS-4 server.
 
Ok, I got the site PM'd to me from @TacoCat and he said it was ok for me to put my reply here in case anyone wanted to add to it or others run into this issue.

Problem: His site fails after traffic spike and he wants to make sure next time there's an influx of traffic it doesn't overload WP to fail (different problem than I originally thought).

Solution: I put all the solutions below laid out so it's easier to follow for non-devs. Not all these solutions are to directly related to the problem of DB failing for WP but just making your site quicker/less strain on the server overall that high traffic spikes would lead to.

Quick/easy:
  • New theme = Avoid anything off Themeforest. They have developers add a lot of junk in order to sell there (perfect for someone who is new though). What you showed me is an incredibly easy design/dev that you have a lot of options, cheap to recreate too. The theme you're using is bad dev wise.
  • Nginx = Contact Knownhost to put Nginx mod install on your server, they'll do it for free and only takes like 15-20 minutes, it will help a lot.
  • Gzip compression = Contact Knownhost about setting up Gzip compression on your server, again all free and will help.
  • Add Cloudflare. = Use Cloudflare to minify scripts/styles, check out Rocketloader in the settings too (still on free version).
  • Simplify font choices = The theme you're using is loading a high amount of fonts for no good reason, also check if they're needed on every page (cover this more in next step).
  • Global JS/CSS = You currently have plugins loading CSS/JS on every single page when they're only needed on 1 template (ex. just show on single post). Contact plugin developers about how to fix this (usually just a quick line you have to add to functions.php) or replace with new/no plugin.
  • 2 social share plugins = This is a small one but this added JS does east up time and I believe one of these is issue for JS loaded on all pages.
  • CDN = Jetpack on WordPress has a free CDN called Photon that is easy to setup but loads things through their domain, I personally haven't seen SEO issues from it if the site wasn't image heavy. Check it out, otherwise setup MaxCDN which isn't so bad to setup. This will save you a lot of bandwidth and help.
Cleaning the DB:
From plugins and any dev on your side this can fill your DB with a ton of crap, more relevant to your DB issue. This takes up memory on your host and can make any queries from the DB take more time. More time means possibility for timeouts and longer time in general to process any queries (which WP does a lot).
  • Clean post meta = Upload the plugin WP CSV. This pretty much gives you a CSV phpmyadmin dump of the wp_posts DB table. From the plugin export page there's checkboxes for the option of what post meta to include, highlight and copy all those checkboxes to a text file and go through each one seeing what is useless (ex. from a plugin you don't use anymore). Also take note of what plugins are adding post meta that shouldn't. Put the following code in phpmyadmin SQL to delete the useless post meta.
    DELETE FROM wp_postmeta WHERE meta_key = 'name from CSV plugin here';
  • Clean user meta = Upload Export Users to CSV. Same as clean post meta step but instead we want to clean the wp_usermeta table. Export portion of users of to spreadsheet and take note of the user meta columns in spreadsheet. Put the following code in phpmyadmin SQL to delete the useless user meta.
    DELETE FROM wp_usermeta WHERE meta_key = 'column name from CSV plugin here';
  • Optimize/clean DB = Upload WP-Optimize. From the settings make sure to select 'delete transients' which is unchecked by default before clicking button to optimize. Deleting transients will make your site slow until loaded/viewed again, but after whatever transients are loaded and saved again it will be fine (WP transients are similar to caching).
    WP optimize also gives you a list of all your DB Tables and their size. Check each one and see if there's anything weird. Also compare your tables to the default WP tables to see if there's anything added.
This is kind of what happens with huge traffic spikes though if your server can't handle it, VPS-4 seems like a good choice to me and can handle a lot. Cloudflare can help with displaying a cached version if it goes down. There's more to do with WP AJAX and heartbeat api, putting database on another server, etc. but that's not so user-friendly.

Might write a bit more and turn this into a longer blog post if I have time (different site example though).
 
Last edited:
I feel guilty for installing shit on 128mb kvm plans.
 
I asked them to install this on my vps:
Now it runs at light speed and can handle up to 4k user at once. I have an SSD4 package. I figured out that their PHP config eats to much memory so the above one fixed it for me. Just open a support ticket and they will take care of everything. If this doesn't work for you then start changing you theme and everything else that was suggested above..
 
You must log in or register to reply here.
Back