Google cloud sql instance infinite loading on restart

Question

We have google cloud sql instance with around 10.000 databases. Time to time mysql instance becomes unresponsive, so all we can do is just restart from the google console. (The reasons why it becomes unresponsive are not known currently :( )

Today we had similar issue, and we tried to restart it and now it is already restarting for more than an hour (usually it takes around 2 minutes to restart)

What can be done ???

Answer 1

Google cloud sql has limitation of 600 seconds on a query to run, after which a deadlock was caused. The deadlock can be opened by their technicians in 24 hours (this is in sane if you are on production with lots of users ...)

Their answer

Your CloudSQL instance is now back up and running, it appears that the server crashed and caused corruption at 8:02:22 PST on Feb 10th (Today).

The main reason given is: 2017-02-10T16:02:22.555549Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.

It appears that the instance had a lock that was held for more than 5 minutes and innodb crashed the server because of that. It's possible there was a deadlock happening.

In order to prevent this in the future we recommend looking over your current queries to see if any could cause a deadlock.