New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
MYSQL Goes Down Once a Day with a Certain LEB Provider - Anything I can do?
MTUser2012
Member
in Help
About once a day, I am getting notices that the MySQL server on one of my VPSs has gone down. I have to log in via SSH and delete the old mysql.sock file and restart. You cannot simply restart the MySQL service. This VPS has loads of resources available to it. I only have a single WP site on it.
Does anyone know what causes this type of behavior? Is it a provider problem? Is it something that I am doing? I am a little suspicious because I utilize several different providers, all of which run mostly WP websites and I am only having this problem with a single host.
Comments
So what does the log say about the reason?
Check /proc/user_beancounters and especially the last column - see if there are any non-zero numbers there.
Are you sure the whole VPS itself is not going down?
+10000
You might be surprised to find out that you're running out of memory and the process is being killed, for something even as simple as a massive brute force attack.
have you checked your server uptime?
I have the last months seen the same issue at my servers (CentOS 6.x + Virtualmin) when they are stopped suddently. Like a node reboot or something like that.
then I have to manually delete the mysql.sock file before I can start MySQL again.
It was not a issue before, but I have seen allot of it the last months.
But only when the server is shutdown or rebooted suddenly. If I shutdown or reboot the server the normal way, there is no issue with the mysql.sock file at all.
So take a look at your server uptime next time it happens. But of course, maybe there is other reasons like @jarland and @rds100 write about.
Question. Why the mysql.sock is not in the tmp folder?
/Var/log/mysqld.log has nothing except me restarting after the crash. Is there someplace else I should look? This is a Centos 6 installation running Virtualmin.
On my servers I always find the mysql.sock file in my
/var/lib/mysqlfolder.Yes, I use Virtualmin to host and I can login and see VPS status page. The only server down is MySQL, and I cannot restart from Virtualmin.
If MySQL is exceeding memory limits, your host might automatically kill that process. Make sure it's not doing that. Otherwise, I don't see why it'd go down everyday unless it is a misconfiguration.
That's not quite what I meant. If the vps has an unclean shutdown or crash then it's possible your .sock will be locked. When the VPS starts back up other services will start normally; however MySQL will not. Have you checked uptime after MySQL crashes?
I would bet on 2 things:
You have not optimized MySQL's configuration (I suggest http://mysqltuner.pl)
You probably have some crazy large queries without indexes (I suggest activating mysql slow query log to find out which queries are taking so long and creating indexes for the tables which require it)
In both situations it can cause the MySQL server to lock up, especially so with super large queries. To give you an example, I had a customer who had a WordPress site that had a single query taking more than 1 second to execute, this one single query had no indexes and basicly ended up crashing MySQL once the query was no longer cached and under high load.
I hope this helps.
Cheers!
I just asked, I am curious about it.
cat /proc/user_beancounters shows no non-zero numbers in the last column. I can post if you like.
Got it now. Thanks. Yes, the uptime is consistent with the entire server going down. I just have the alert set for an email and text when the MySQL server goes down.
If it goes down again tomorrow as I expect, I will immediately check uptime again.
Based on what you and other say, this maybe the same problem I am experiencing. Would you mind sending me a PM with the provider that you are experiencing this with, if it is a single provider and not something that is happening across multiple providers for you? I'd like to find out if we share the same provider, but don't want start something here if this is all random. Thanks.
It might be MySQL using a large amount of CPU and tripping a CPU abuse prevention script. If the script doesn't kill -15 instead of kill -9, then the old socket would still be in existence. Is MySQL munching CPU?
@MTUser2012 I have seen this with many host the last months. So it is not a single host. I can also replicate the issue on my cloud hosts, when I force shutdown from the control panel. But if your server goes down very often, I would consider to change host. It is not a good thing that your server get rebooted all the time.
Indeed even with iwStack, their clean shutdown is not clean and leads to this issue. I had a ticket open with them about it. After waiting a week for a response after opening the ticket I asked if they made any progress fixing it. They then closed my ticket with no response. That's diminished my interpretation of Prometeus somewhat.
that's weird. they ALWAYS answer my tickets when I post them, and so fast.
@Virtovo first time I have ever seen anybody has any negative to say about Prometeus. But yes, it's a issue when you force a shutdown. The way I'm doing it, is to use SSH and the
shutdown -h 1command, and then go to the cloudstack CP and shutdown my server (but not use force shutdown). Then there is no issue.But I have seen the mysql.sock issue on all kinds of servers, also servers that has nothing on them, just a clean Virtualmin installation, no sites at all. Have used some cheap host for testing, and some of them goes down allot. Since I monitor all my servers (HTTP, FTP and MySQL) I can see right away when my MySQL has gone down.
Are these KVM virtual machines? If so, make sure acpid is installed and running.
My cloud servers is KVM, but the other servers I have seen the issue on is OpenVZ
Do you guys parse your access logs daily to pull out trends? This under the assumption that maybe it's getting rebooted for usage.
@jarland: As I wrote before, I have seen this issue on several servers that don't have any sites on them, just a clean installation of Virtualmin. So how can there be any usage?
My servers at iwStack and Prometeus before that, did never go down, so I have not seen this issue on one of my live servers. Just seen it on iwStack on some test servers, when I force shutdown.
This is a question people often ask before they're surprised. If it can be accessed in any form, it can be used. Some people don't need a reason, or perhaps brute force is their reason. Some IP ranges are on more bot/scan lists than others. As long as the IP is different, you can never say that two things are identical purely on their setup. You don't even have to advertise them.
cPanel made a nice script a while back most people don't think about, works without cPanel. May help diagnose problems. Keep it running, figure out what time something happened, figure out what your VPS was doing at that time.
https://github.com/cPanelTechs/SysSnap
I've had no complaints with Prometeus so far and have always sung their praises (they got 5 of my votes this last poll); however the ticket was left without response for 7 days then closed when I asked for an update.
I am sorry for that. Currently there are 3 people working on tickets and we are sometimes stepping on our toes, like replying same time same ticket, so we are currently reorganizing this so it does not happen as well as tickets remaining unanswered.
Regarding your problem, I think I remember the ticket, or was someone else, I could not replicate the issue, ordering a stop will do the stop cleanly, however, from past experience of making and using templates, I have seen it after a template deployment that it crashes on shutdown for some reason. The issue is not present in the initial template, but after it is deployed, even regular shutdowns from inside will cause it to crash.
We will be upgrading on the 15th this month to 4.2.1, it might be CS related, however, I do not see how.
The next time mysql is down, check the web server logs; I had a similar experienced and noticed that when mysql would go down, I had few web crawlers hitting the server, requesting too many pages per second. At the time, I blocked the crawler and learned about an entry in the robots.txt file.
robots.txt entry
Find a pattern of when the mysql goes down: Your new best friend is the script below:
http://mysqltuner.pl
Also, check the following link to learn how to display tcp/ip connections
http://www.cyberciti.biz/tips/linux-investigate-sockets-network-connections.html
Monitoring tools
http://www.cyberciti.biz/tips/top-linux-monitoring-tools.html
Did you checked the logs and see why the mysql goest down? that is the first step you should do.