Add expiration timeout that limits max life-time of a connection

[fishcakez]

This timeout should be randomized to prevent sync disconnects.

[fishcakez]

We can do this as part of #108, and just for that pool because for poolboy/connection it is awkward to handle.

[jeffrom]

Hello, I'd like to reopen this. It would be nice to recycle connections so we can better handle when our replica connections are unbalanced.

[ymtszw]

Pinging, since this is an ongoing issue when we use cloud database service with automatic failover feature, such as Aurora (provisioned cluster) in AWS RDS.

In RDS doc: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Aurora.Overview.Endpoints.html#Aurora.Endpoints.Cluster

The physical IP address pointed to by the cluster endpoint changes when the failover mechanism promotes a new DB instance to be the read-write primary instance for the cluster. If you use any form of connection pooling or other multiplexing, be prepared to flush or reduce the time-to-live for any cached DNS information. Doing so ensures that you don't try to establish a read-write connection to a DB instance that became unavailable or is now read-only after a failover.

With db_connection 2.1.0 (with Ecto 3.1.7 and MyXQL 0.2.6), failing over an Aurora cluster causes the sequence below.
(Assumption: Elixir applications are connecting to an Aurora cluster via Writer (primary) endpoint with its DNS name. Reader endpoints innately work as reverse proxies so should not be a problem)

• Connections are closed by the remote (the Writer endpoint) on the moment of failover
• DBConenction tries to reconnect with backoff
• After 10~20 seconds, it reconnects, but to the old (now read-only) endpoint, not to the newly promoted Writer endpoint
• As long as the connection is healthy, it won't try to reconnect to the new Writer endpoint
• Forcing rolling-update of application instances (i.e. restarting Elixir applications) after a few minutes will properly connects to the new Writer endpoint, which is understandable since new instances can likely grab a new IP address

(Figure: Connection counts. The orange line is for an initial Writer instance and the blue line is for an initial Reader instance. In this setup Reader endpoints are NOT used, the Elixir application is connecting to the cluster via Writer endpoint only. Drop-off of the orange line after 16:40 mark is the failover. Re-connections are targeting to the initial Writer instance as you can see. Application rolling-update at 17:00 "corrects" the situation, after that newly started applications are connecting to the new Writer instance)

This I believe is the same problem reported in xerions/mariaex#180.

After several minutes after failover, reconnections can definitely target newly promoted Writers with DNS record being updated.
But at the moment just after the failover, reconnections will likely grab the same IP address, since DNS record is not yet updated, OR updated but not yet recognized by application-running instances.

An obvious workaround for now, is to force application restart or trigger deploy several minutes after the failover, as shown above.

BTW, I haven't tried to issue write requests toward the reconnected (now read-only) endpoint. Does it causes exception & trigger reconnections? I might try that when I have time.

[josevalim]

Have you tried using this option? https://github.com/elixir-ecto/myxql/blob/b8848ba128fb9310aefff0965352373721e0801f/lib/myxql.ex#L104

That's what James proposes as the last suggestion in the linked issue. It exists on the new driver as well as Postgrex. This means you will continue running and reconnect automatically when a write is attempted.

As James also said, if you are reconnecting to the reader even after a disconnect, then it is most likely a DNS cache issue at the OS level. Have you configured those in any way? We don't have any application local cache.

[ymtszw]

@josevalim Thanks, I have certainly read that in the past but haven't tried :P

It looks like it takes an approach of "if it fails in a certain manner on write request, reconnect" right?

If so, it answers my own question in the last comment,

I haven't tried to issue write requests toward the reconnected (now read-only) endpoint. Does it causes exception & trigger reconnections?

...and the answer seems to be "yes, with MyXQL configuration," which is a good news. Will definitely try out.

[ymtszw]

BTW I do understand that the DNS cache is at the OS level, nowhere within applications.

The issue probably boils down to whether proactive reconnection via connection TTL is required in some way, or "lazily reconnect on failed writes" approach suffices.