Help...MYSQL crashing once per day

[americanninja]

So I have had Sentora running on a new server I created through digitalOcean for about 2 months now. I recently moved from shared web hosting on GoDaddy using cpanel.

All was working well up until recently. Basically, it seems about every 1-2 days mysql crashes on my server. I will notice it when I try to open one of my websites and I will see the Drupal server connection error page. This is very bad obviously, as I don't notice the site being down until I check it, which means it can be done for several hours with me knowing (any idea how to get notified of when the mysql server is down? Maybe some cron job I can run to run a .sh script to check if Mysql is actively running?)

A simply service mysql restart command fixes the situation. So basically I know that my issue is mysql is crashing.

root@panel:~# service mysql restart
stop: Unknown instance:
mysql start/running, process 10715
root@panel:~#

I am hoping someone here can help me figure out why mysql is crashing and how I can prevent it. My guess is it's a memory error, such as the server is running out of available memory. But why is that crashing Mysql, shouldn't the site just become less responsive? I don't understand why it's bringing the service down. Is this normal?

Thank you for any help you can all provide?

[Me.B]

Check ressource use and monitor server internal CPU/RAM.

Add more ram as drupal can be heavy and a lot of ressources under spikes.

Kind of instance you use? Don't forget under shared you might have less ressources but you were allowed to use more in spikes.

M B

[americanninja]

here is what htop looks like

CPU[||||||||||||||||| 23.7%] Tasks: 48, 29 thr; 2 running
Mem[||||||||||||||||||||||||||||||||||||||||||||||||||||||||623/994MB] Load average: 0.11 0.18 0.22
Swp[ 0/0MB] Uptime: 1 day, 22:10:39

Looks fine right? CPU usage uses sits around 1% and then spikes for a second or less. Memory seems to just sit around 600 MB constantly (I have 5 drupal sites and 1 wordpress site on this server). All sites except one get less than 100 unique views a month. They are just info sites with no traffic.

So why would mysql be crashing around once every few days?

Where can I find in the logs why mysql is crashing? Is there a log file I can check and search for some keyword to find why and when the crash happened? Is there a way to auto alert me from the server when mysql goes down...this would be very helpful for me to monitor things...anyone have a link to a tutorial on how to do this?

[americanninja]

Does Sentora have such a feature? It seems this is common with cpanel. I would think this would be something we should have as part of core Sentora...

Such a feature already exists within cPanel * WHM.

Documentation: Service Manager

Simply go to: WHM -> Service Manager

Then, make sure both "Enabled" and "Monitor" are checked for "mysql". This will instruct cPanel's chkservd mechanism (which runs onc per 5 minutes) to check on the status of MySQL and restart as necessary. If a restart is necessary, it will also email you about it if you have your email contact setup in WHM for the server.

However, if MySQL is crashing/unavailable that often that you're specifically asking for an auto-restart script, I would be more inclined to suggest you identify the cause of the outages. Applying a bandaid fix by auto-restarting MySQL is fine for the short-term, but you'll want to find out why MySQL is crashing in the first place so that you can prevent that from happening.

What does the MySQL log say during the time of the crash?

Feel free to open a ticket (see my signature for links) if you'd like assistance in attempting to identify the cause of MySQL crashing on your server.

[apinto]

You can currently (with Sentora) see the resource usage on your server by going to phpSysInfo that can be found under the "Admin" menu.

I agree that a module showing the resources under usage (liek the cPanel sidebar) would be cool.

[apinto]

And, by the way, checking your CPU usage of 20%... is that regular? always under 20% load with a Default Sentora Setup?
Because I use a single core 2.6ghz CPU with OpenVZ and the load sits under 5% (most of the time I risk to say under 2%).

[Me.B]

RAM use is main source of such crashes and you are not far from your limits as I guess this is a micro 512 MB ram? Or 1024? That's low .

You should log also slow queries and try to watch MySQL/MariaDB live using MySQL workbench.

Monitoring will only show you if service is running or not and never the internal state inside the service like MySQL Workbench.

M B

[apinto]

I do use Uptime Robot to be alerted (works by default via email and lots of other ways if you want to dig).
https://uptimerobot.com
But like Me.B said, you should detect and fix the issue and not just monitor it to restart the server.

Also, check this: http://www.webtrafficexchange.com/solved...uffer-pool as it is the most common cause of MySQL crash.

[Me.B]

uptime robot can only detect service down... You can with some bash/cron do better and kick the service back.

But again most important remain finding the origin of the crash and most cases I faced it was ram.

M B

[TGates]

This one may not help you, but good to keep in mind:
Another thing to check is HDD storage space. The logs and backups can suck up space quickly. Check them just in case
Also, 'truncate' once a week/month the x_logs database table. This table gets large very fast.
(Looking into a patch to drop oldest entry when new one is created keeping x number of entries.)