Help...MYSQL crashing once per day

[americanninja]

@apinto: Thank you for the support. No, my CPU is usually sitting around 2-5% with spikes to around 20% every few seconds during busy times of the sites I guess. Regarding cpanel, I was referring to their auto check feature which would relaunch mysql if it failed or monitored that at least. It seems Sentora has no such checking feature.

And thanks for the two links. I am giving uptime robot a look right now. I have been using pingdom's free service but it only lets me add one website, but actually it's all I really need as all my sites are on this single server. But I will create an account with uptime robot to see if it's better than Pingdom. Also, thanks for the link regarding the memory pool issue. I will give that a look too!

@Me.B - Thanks for your response. The htop output pasted above shows I am on a 1GB ram virtual server...memory sits around 500-620 constantly with 400-500 MB of free ram. So I don't think it's that. I never see the ram go above the 620 MB mark, or really fluctuate for that matter. So i don't think that's it. I do see the CPU spike to 100% on occasion. I'll have to give that workbench a look if I can't figure things out here soon.

And yep, I am trying to find the cause. I actually created a script to check if mysql is up every 10 minutes (via cron) and if not, bring it back up and email me a notice as well as document the time of failure and send me details from the system log during that time.


@tgates: thanks for your support. Can you elaborate on this one...Also, 'truncate' once a week/month the x_logs database table. This table gets large very fast. Where is this database table? Or how can I truncate it?

[americanninja]

Here is the bash script I created for my server. In case anyone else comes across this thread and would like to use my script for checking mysql and restarting. This is for Ubuntu with Sentora, but I'm sure it will work for most linux distributions (you may need to make some changes)

I added this line in crontab -e to run this script every 10 minutes
*/10 * * * * /bin/sh /home/root/scripts/mysql-service-check.sh >/dev/null 2>&1

UP=$(pgrep mysql | wc -l)
RFILE="/home/root/scripts/mysql-service-check.txt"
FFILE="/home/root/scripts/mysql-failure-log.txt"
NOW=$(date)
LOAD=$(uptime)

if [ $UP -eq 0 ]
then

#echo

"MySQL is down"
sudo service mysql start
echo "$NOW" >> $FFILE
echo "Subject: SERVER ALERT - Mysql Server was Down" > $RFILE
echo "The mysql server on your server was found to be down by our monitoring script on $NOW. The site was down for a maximum of 10 minutes. Crash logged in mysql-failure-log.txt" >> $RFILE
echo "" >> $RFILE
echo "This email should be proof that the services have been restored and the site is up and running. However, you should also confirm by going here: www.yoursite.com." >> $RFILE
echo "" >> $RFILE
echo "- YOUR SCRIPT MONKEY" >> $RFILE
echo "" >> $RFILE
echo "" >> $RFILE
echo "DIAGNOSTICS:" >> $RFILE
echo "$LOAD" >> $RFILE
echo "" >> $RFILE
echo "Syslog Error Log:" >> $RFILE
tail -100 /var/log/syslog | grep mysql >> $RFILE
echo "" >> $RFILE
echo "" >> $RFILE
echo "" >> $RFILE
echo "" >> $RFILE
echo "Mysql Error Log:" >> $RFILE
tail -100 /var/log/mysql/error.log >> $RFILE
/usr/sbin/sendmail you@youremail.com < $RFILE
fi


Hope this helps someone else. This script works perfectly to get things back up and running quickly and automatically, the most important thing. I would think Sentora should have something like this built in as mysql is critical and a down server means NO websites. But anyway, this script does the job. Now, I need to find out why mysql is crashing in the first place.

[americanninja]

@apinto...amazing! I think your article is spot on describing my issue, including the errors. I am going to give their suggestions a try and then see if I can last a week without the server crashing.

Thank you!!!

[TGates]

You can purge the x_logs table through phpmyadmin as root. sentora_core.x_logs

[apinto]

@apinto...amazing! I think your article is spot on describing my issue, including the errors. I am going to give their suggestions a try and then see if I can last a week without the server crashing.

Thank you!!!

americanninja, great it worked
Actually that is probably the most common cause, especially if we are talking about low ram machines (1gb or even 2gb).

[americanninja]

I thought I would share this tutorial as well. The link you gave me definitely identified the issue I was having. So I definitely thought it would be a good idea to setup a swap file. I came across DigitalOcean's tutorial:

https://www.digitalocean.com/community/t...untu-14-04

It's brilliantly written and got me setup with even more settings than the other article. Actually DigitalOcean did an amazing job of writing up very helpful and commonly needed tutorial documents. In fact, it was the main reason I decided on DigitalOcean. I was using AWS and it was working just fine, but I felt it was way more cluttered and complicated compared to DigitalOcean's system. And I didn't need most of it. But basically because everything I searched on the web to find help lead back to DigitalOcean tutorials, I thought I would give them my money, since they seem to really care about their users to help them administer their VPS servers. They made the jump from shared hosting for the past 10 years to cloud hosting easy as pie, and they continue to support me through these well written tutorials.

Thanks for all the help Apinto!

[americanninja]

Just thought I would come back to this thread and post a few more things. What I did above fixed a lot of my issues by mysql would still crash from time to time. It wasn't a big deal, because i had a script that ran and checked if it was down and would restart it. It only happened once or twice a month probably. Anyway, it's still probably not a good idea to have mysql server crash even if rarely.

Recently, I found some time to look into securing a few things on the server and I came across the prefork and MaxRequestWorkers setting. I thought I would post a link to these two articles for anyone else experiencing memory issues and crashes.

If you leave your web server setup with the defaults, it's probably no good. I found that given the average memory used by the httpd process, I should only allow about 20-30 httpd processes, anything more and my server starts to thrash. I probably should have learned about this sooner. Anyway, since configuring this (based on the website articles pasted below), I haven't had my mysql server or web server crash since.

https://fuscata.com/kb/set-maxclients-apache-prefork
https://www.maketecheasier.com/optimizin...he-part-1/

Hope others will find this useful.