MySQL restarts itself when doing "load data"

[jo...@gencat.cat]

Hi all,

I'm in the middle of a Oracle->MySQL migration process, using Google Cloud SQL for the MySQL part (second gen. 5.7). I've found that, for a given database/table, there's something that makes the MySQL server shutdown and restart.

The migration process is simple: Two processes are started and communicate via a named pipe. One reads records from oracle and writes them in CSV form to the pipe. The other one just issues a "load data infile..." reading from the named pipe.

I start the migration process and, after some minutes migrating rows, the writing process stops due to a broken pipe exception, the reading process (that reads from the named pipe and loads data into MySQL) has stopped, being disconnected from the MySQL connection. After some try-and-error, I've found that, when that happens, if I query uptime to the MySQL server ("show global status like 'Uptime'"), it's reset (that is: the service has restarted)

Logfiles (stack driver) also confirm this: seconds after the migration process fails, there are traces of MySQL starting and finding it didn't shut down properly the last time.

Any one else has experienced something like this?

Any hint on how to get this debugged and see if I've stumbled upon some platform/MySQL bug?

This doesn't happen if I replicate the table to a local MySQL or if I replicate another table to the same Cloud SQL MySQL instance. Looks like a specific issue with the MySQL Google Cloud service and some content in this table.

Right now I'm trying to identify whether the issue is due to a specific row/column of the input data source, but any help or experience about this will be welcome.

Best,

Jose

[Fady (Google Cloud Platform)]

Hello Jose,

If MySQL server (mysqld) is crashing, and not the instance itself, it could be a utilization issue. Hence, you may check the corresponding graph in the instance overview page, and for testing purposes you may try migrating with a higher CPU/Memory type machine (you have the ability to downgrade after).

But are you sure that the crash is causing the connection issue (could be not related)? For example, and while you replicated the server locally (and not over the internet), it could also (in addition to crashing) be a connection issue like this communication error, and per this troubleshooting guide , this does not indicate that  you have an issue with the Cloud SQL instance itself, but the “application is not terminating connections properly”, or caused by a network issue.

On the other hand, and while this is affecting one table, and as you have a special case using named pipes, it would be nice to investigate it through issue tracker. Opening a report, make sure it is private, include your project ID, instance name, all error messages for the behavior described above, and detailed reproduction steps.

[jo...@gencat.cat]

Hi Fady,

thanks for your response. I've been doing tests with different machine sizes, testing with high memory machines, and the error is consistent. No matter hoy much memory/CPUs the machine is configured with, there's a point at which it crashes (all I can see is in the MySQL error logs, that the MySQL service restarts, I think there's no way from the web console to check if was only the MySQL service or the machine that crashed)

I've even transformed the load operation to check if that was caused by some limits related to transaction. Instead of doing "load data" of large chunks, I'm splitting them into smaller chuncks, so the operations can be commited in smaller chunks. It didn't work neither.

So, I'm following your advice and I'll open an issue in the issue tracker. Thanks in advance.

[Mhd Mousa Hamad]

Hi,

We are also facing a similar problem with our SQL instance in Google Cloud Platform, it restarts often (2-3) times a day on what we suspect to be a heavy write load. We cannot see enough information in MySQL error logs except the information that MySQL instance was restarted. What could cause instance restarts other than the maintenance and upgrades? Is it possible that a heavy write or even read load causes such restarts?

Many thanks in advance,

Mousa

[Fady (Google Cloud Platform)]

Hello Mousa,

From the description it seems like an over-utilized instance, and provisioning additional resources should resolve the issue. However, the instance (VM) itself should not restart but rather the running MySQL server (mysqld). If the instance itself is restarting please open a private issue tracker report including the instance’s connection name for inspection.

[jo...@gencat.cat]

Hi,

to be honest, if they are suffering the same issue as us, I don't think it can be solved by provisioning additional resources. We've tried with a machine configured as db-n1-highmen-32 (32 cpus and 208 GB of RAM), and this issue is still happening. IMHO It doesn't makes sense, neither, to need to worry with service restarts when a vanila MySQL VMWare instance in our premises, with 4 cpus and 8 GB RAM can handle that parallel import task without any problem...

[Fady (Google Cloud Platform)]

Hello Jose, 

Thank you for the feedback. I believe both cases are different as you are migrating using named pipes/load data infile over the internet, while Mousa is describing heavy write load. In both cases though, the instance itself should not restart. That said, we do appreciate opening the issue tracker for an investigation as it is the platform for discovering defects and improving our products. 

[Mhd Mousa Hamad]

Hi,

Thank you for your replies. I have created an issue as suggested by @Fady, but I sill agree with @Jose that we shouldn't worry about server restarts no matter how heavy the read/write load is. We can live with the server getting slower at heavy load times but not it being restarted. We shouldn't be forced to upgrade the machine type if we expect a heavy load in a relatively very short time of the day especially that it is okay for us if the server gets slower responding at these times being planned.

Best,

Mousa

[George (Cloud Platform Support)]

In fact, Engineering is looking presently at similar Cloud SQL issues. Your issue is likely going to enjoy increased attention, as a consequence. 

[jo...@gencat.cat]

On Wednesday, July 11, 2018 at 3:19:02 AM UTC+2, Fady (Google Cloud Platform) wrote:

Hello Jose, 

Thank you for the feedback. I believe both cases are different as you are migrating using named pipes/load data infile over the internet, while Mousa is describing heavy write load. In both cases though, the instance itself should not restart. That said, we do appreciate opening the issue tracker for an investigation as it is the platform for discovering defects and improving our products. 

Hello Fady,

the fact that we are using pipes in parallel is just the mechanism. This is what generates heavy write load also in our scenario.

Best,

Jose 

[rich mudder]

I can't help you with that so please look elsewhere for help, thanx

--
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsub...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/f0ac28a2-8a52-4f34-9ff7-ee4ccc8c32f3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.