TCP Keep-Alive - ECONNRESET

[odubuc]

Hi,

My application is going through a load balancer that kills inactive TCP sockets after a few minutes.
This makes my application wait until the 'timeout' setting until it realise the connection is probably at fault.

Is it possible to add a heartbeat configuration to keep the TCP connection alive forever ?

[dougwilson]

There are two ways to call the Node.js set keep alive on the socket (https://nodejs.org/dist/latest-v9.x/docs/api/net.html#net_socket_setkeepalive_enable_initialdelay):

1. Create the TCP socket and provide it to createConnection as the socket option.
2. After calling createConnection, the socket is in the _socket property and you can call the Node.js method on it.

[odubuc]

Ha! Great! Thank you very much :)
So simple.

[odubuc]

just wanted to confirm your suggestion works perfectly.
In my case it was easier to go with your second option.
_socket.setKeepAlive(true, 60000);

Thanks again for the help :)

[st3xupery]

@odubuc May I ask, are you making queries through a single connection or are you utilizing pooled connections? My assumption is the former as I don't know if it's possible to keepAlive each pooled connection, @dougwilson can you confirm this?

As of now, I have implemented the _socket.keepAlive on a single connection, which has worked! To say I am relieved is an understatement as I've combed through every strand of my stack for the past 3 days trying to locate the source of my timeouts.

May I also ask what load balancer you are using? I am using HAProxy with Docker Swarm and having my node application communicate with MySQL over the Docker overlay network. When my app is left idle for ~15 minutes, my subsequent requests can take as long as 100 seconds. These queries all normally take less than a second so I am guessing all the surplus goes into reconnecting. So far no timeouts for 1 hour of idleness, fingers crossed this holds up!

[odubuc]

@st3xupery sorry for the delay, I totally missed your message. I'm using a pool of connection but it is a home made implementation and not the pool contained in this library. But to answer your question, yes, at the initialization of the pool, I initialise all the connections to have their own 60 seconds keep-alive

The load balancer is in Azure so it's the "Azure Loadbalancer" which close any idle sockets (configurable from 4 minutes to 30 minutes).

A little background:

• My pool usually contains 10 connections.
• MySQL timeout is set at 10 seconds.

The behavior I was seeing:
since the socket didn't know the loadbalancer cut the connection, the pool would execute the request on the first connection -> timeout. on the second connection -> timeout, on the third -> timeout. etc, etc, until all the connection timed out so this mean I would wait 10 times 10 seconds (100 seconds) for my application to stop hanging until the pool realised all it's connection had timed out and created new ones. the more connection, the worse the hanging was.

Now that all the connections in the pool got their own keep-alive, not a single one gets killed by the load balancer so no more hanging

[dougwilson]

My assumption is the former as I don't know if it's possible to keepAlive each pooled connection, @dougwilson can you confirm this?

You can alter new connection objects as the pool creates them in the connection pool event (documentation: https://github.com/mysqljs/mysql#connection)

[st3xupery]

Much thanks @odubuc and @dougwilson , your help has been immensely useful.