Connection timeouts? Might they the cause, how to debug this?

[Jaap Taal]

Sometimes, I see that request to my webserver take slightly over 10 seconds, which coincidentally is the default connection timeout in node-mysql.

How would I go about debugging to whether connection timeouts might be the cause?

I've already set the connectionTimeout to a lower value: 5000, I hope to see them in the logs.

Are there events we can listen to on the database or pool level?

I'm using Google Cloud SQL on a Compute Engine instance.

The code for setting up connection is very straight forward, but I can include it if needed...

Thanks,

Jaap

[Jaap Taal]

I have a bit more info,

I've set connectTimeout to 5000 and acquireTimeout to 7500.

Testing this I hit a slower response of 7652. It seems that acquiring a connection in the pool is taking a while.

I've enabled debug on the pool, and this is what happend:

// The request is received by my application, PingPacket immediately responds

--> ComPingPacket

ComPingPacket { command: 14 }

// waiting...

// After 7.5 seconds, the request is completely served rather quickly, the first packet after the ping is a HandshakeInitializationPacket

<-- HandshakeInitializationPacket

HandshakeInitializationPacket {

  protocolVersion: 10,

  serverVersion: '5.6.26-log',

  threadId: 47,

  scrambleBuff1: <Buffer ...>,

  filler1: <Buffer 00>,

  serverCapabilities1: 65535,

  serverLanguage: 33,

  serverStatus: 2,

  serverCapabilities2: 49279,

  scrambleLength: 21,

  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,

  scrambleBuff2: <Buffer ...>,

  filler3: <Buffer 00>,

  pluginData: 'mysql_native_password',

  protocol41: true }

I'm using SSL, the application is almost always idle atm.

[Jaap Taal]

Upgraded to node-mysql 2.10.2, I still have this problem.

I've noticed that my mysql client also doesn't respond anymore to query's when this happens. It would seem this might also be a server issue, but I'm not sure how to rule it out.

Any suggestions on how to debug are most welcome!

Jaap

[Jaap Taal]

I got a friendly mail by Karl, he said to set wait_timeout to a high value. It's already set to 8 hours. So that didn't help :(

[Jaap Taal]

It looks like Compute Engine is dropping TCP connections. It's too bad this isn't detected by node-mysql, but I guess there only so much you can do.

Or is there? I'd love to help out, but I'm not into these kind of details...

https://cloud.google.com/compute/docs/troubleshooting#communicatewithinternet

Let me know if you're interested in digging deeper?

Jaap

On Friday, January 22, 2016 at 3:33:45 PM UTC+1, Jaap Taal wrote:

[Felix Geisendörfer]

I think connectTimeout is not relevant here, as it just defines the timeout for creating new connections.

aquireTimeout might be relevant, as it’s the time after which the driver will consider an unresponsive idle connection to be dead and tries to create a new one.

That being said, the root cause is probably the server side TCP timeout you linked to.

To avoid that, you need to make sure connections aren’t idle for more than 10 minutes. You can accomplish this by using TCP keepalive, but that’s probably going to be a little difficult.

You could try something like:

setTimeout(function() {

  conn.ping();

}, 60*1000);

But I think at the end of the day, it seems like the mysql pool could use a feature that automatically does ping keepalive for idle connections, or terminates them after a while. Neither seems to be implemented right now I think.

Cheers

Felix

--
You received this message because you are subscribed to the Google Groups "node-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-mysql+...@googlegroups.com.
To post to this group, send email to node-...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-mysql.
For more options, visit https://groups.google.com/d/optout.

[Jaap Taal]

Hi Felix,

AccuireTimeout is definitely the delay I'm seeing. I aggree it is probably a TCP problem, and not with node-mysql.

I've sprinkled console.logs around in the code, and after ComPingPacket is sent, nothing comes in.

I've contacted Google about this and I will update this thread if any news comes out of it.

Thanks for your suggestions on how to work arround this. I think doing a ping every minute should be a reasonable workaround.

The code you proposed seems simple enough, although, closed connections (because of errors) should stop pinging.

Once we've implemented a workaround, I'll post the code here as well.

Thanks,

Jaap

[Felix Geisendörfer]

On 23.01.2016, at 23:01, Jaap Taal <ja...@q42.nl> wrote:

Hi Felix,

AccuireTimeout is definitely the delay I'm seeing. I aggree it is probably a TCP problem, and not with node-mysql.

No. My understanding is that the current pool implementation is the root cause of this problem. TCP is not at fault.

I've sprinkled console.logs around in the code, and after ComPingPacket is sent, nothing comes in.

Probably because the connection was already dead at that point.

I've contacted Google about this and I will update this thread if any news comes out of it.

I don't there there is anything they could or should do here.

Thanks for your suggestions on how to work arround this. I think doing a ping every minute should be a reasonable workaround.

The code you proposed seems simple enough, although, closed connections (because of errors) should stop pinging.

Once we've implemented a workaround, I'll post the code here as well.

Let me know if the workaround helps. It might not be possible to create a proper fix for this without changing node-mysql.