MySql restarts every 6 days exactly at 12:03 pm
I have a problem with my production server. MySql restarts every 6 days at exactly the same time. Than it recovers but it lasts 20 minutes. In the mysql log i have nothing before the restart, only the line where it starts again /usr/sbin/mysqld (mysqld 5.7.44) starting as process...
I sometimes get OOM message, sometimes don't. I think that OOM is false, because it happens at the exact same time always. It even happened during the holidays, when the load on the server was almost nothing. I checked everything on the server in that time period, cron, scripts, load, nothing seems wrong.
Server has 128 GB of ram.
This is the mysql failed email i get:
Service Name mysql
Service Status failed ⛔
Notification The service “mysql” appears to be down.
Service Check Method The system’s command to check or to restart this service failed.
Number of Restart Attempts 1
Service Check Raw Output (XID kdwrz8) The “mysql” service is down.
The subprocess “/usr/local/cpanel/scripts/restartsrv_mysql” reported error number 2 when it ended.
Restart Message Stuck on restart of mysql
Startup Log No startup log
Memory Information Used 10.52 GB
Available 114.92 GB
Installed 125.45 GB
Load Information 8.93 8.58 8.76
Uptime 5 days, 22 hours, 23 minutes, and 1 second
IOStat Information avg-cpu: %user %nice %system %iowait %steal %idle 18.37 0.02 4.41 0.20 0.00 76.99 Device: tps kB_read/s kB_wrtn/s kB_read kB_wrtn sda 290.82 2662.79 2301.30 1364892426 1179598160
Top Processes PID Owner CPU % Memory % Command
7856 mysql 13.17 1.91 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
54596 kancelarko 6.36 0.07 php-fpm: pool kancelarko_rs
51640 kancelarko 6.03 0.03 php-fpm: pool kancelarko_rs
77591 kancelarko 5.92 0.08 php-fpm: pool kancelarko_rs
48558 kancelarko 5.76 0.07 php-fpm: pool kancelarko_rs
Thanks for any help, will provide any additional info on request!
-
I assume you've checked /var/log/messages and Apache's logs (in case there is high demand at that time)?
I also take it that network activity is nominal/normal, no backups are running at the time, no recent crons have been kicked off (/var/log/cron) etc? You may need to "backtrack" in the logs around 30 minutes before the event as MySQL "issues" do tend to have an initial stage which then grows into the issue.
Can you try manually restarting MySQL via WHM/command line after 5 days (i.e. nearly a day before it "auto-restarts") and see if the problem reoccurs at the same point in time (i.e. it restarts 22 hours later) or after the same sort of time delay (i.e. it auto-restarts 5hrs 22days later), It might give a clue as to what is causing it.
1 -
Hi, yes i checked everything you mentioned. My first thought was it was backup or cron, but nothing in that time frame is happening.
I restarted mysql manually, and even the whole server, and mysql restarted 6 days later in both cases.
The weird thing is that this server had a lot more traffic months ago, and we moved bunch of databases to other servers etc. When it was under heavy load, mysql was working fine.
I am looking at the mysql logs but all i have 30,40 minutes before is some random user denied.
Every time i check it has bunch of memory waiting to be called, i am thinking that maybe there is some bug with releasing standby ram memory.0 -
If it was memory, there should be an out of memory message in dmesg ( dmesg | grep -i oom ) - and the fact you are only occasionally seeing that does make it appear deceiving - and with the logs not showing any other activity, it is a puzzler.
Could you post your current /etc/my.cnf file just in case (feel free to "anonymise" server hostname etc) - just to see if there is anything unexpected set there which might be causing it.
You might get some luck running something like MySQLTuner :
mkdir /root/mysqltuner
cd /root/mysqltuner
wget https://github.com/major/MySQLTuner-perl/archive/refs/tags/v2.2.12.tar.gz
tar -xvf v2.2.12.tar.gz
cd MySQLTuner-perl-2.2.12
perl mysqltuner.plIt should pickup the root MySQL login credentials automatically. Things like the MyISAM metrics ("InnoDB migration request for...") can be safely ignored. I wouldn't actually make any of the recommended changes at this point, but it might highlight things to check/investigate.
Percona's toolkit is another group of handy tools to look and perhaps try: pt-mysql-summary ( wget percona.com/get/pt-mysql-summary ; bash ./pt-mysql-summary ) should give a good overview and pt-stalk looks like it might help "capture" data about when MySQL dies - however, I've not tried it myself so I can't comment on how easy/hard/useful it is.
0 -
Okay, i went through every log again. Checked mysql, one to two hours before the last 2 restarts, checked exim, apache logs. Nothing.
This is my my.cnf file:log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
innodb_buffer_pool_size=10485760000
max_allowed_packet=268435456
open_files_limit=80000
default-storage-engine=MyISAM
innodb_file_per_table=1
sql_mode=""
max_connections=500I don't see anything wrong with the setting, i have other servers with 2,3 times the load and the same settings, and they are doing fine.
Cpanel won't even send me an email before it goes out of memory. I received the email only once, and i had like 15 restarts. I just get the mysql failed email.0 -
It sounds like it's time to create a ticket on this one, so either us or your host can take a look.
0
Please sign in to leave a comment.
Comments
5 comments