Allow to customize the interval the connection reaper runs

[nvivo]

Short version: I'd like to set my ConnectionIdleTimeout to 10 seconds, but the reaper currently runs every 60 seconds, which causes lots of connections to being left open for longer than needed.

Long version: I have 900 nodes connecting to a mariadb instance with 64 CPU, and even though each node opens up to 10 connections, and eventually closes them, once we past 3000 connections, CPU started to increase exponentially. 900 nodes had 30% cpu usage, 950 had 70% cpu, 1000 nodes went to 100% and brought down the server. The nodes do the same work, so that didn't make sense.

After spending an hour with AWS support, they recommended closing the idle connections and that solved the problem.

I set a a 10 sec idle timeout, but with 1800 connections, 600 of them are still waiting for longer than necessary. Being able to run the reaper more frequently would allow me to keep using the connection pool, but keep connections open for less time.

The default of 60 seconds is enough for most applications, . But it would be nice to have something like ConnectionIdleReaperInterval=180 by default and be able to change that per app.

[bgrainger]

Did you change wait_timeout on the server (it defaults to 28800s = 8 hours)? Setting that to 10s on the database server might work around the problem by making the server kill idle connections. (It might introduce inefficiencies in the MySqlConnector client due to pooled connections being killed behind the client's back, so perhaps it's not the best long-term solution. But I'd be interested in knowing if it helps your situation or causes any problems.)

Because a connection string setting is per-connection-pool but the reaper is currently global, I'm leaning towards automatically changing the reaper interval based on the lowest ConnectionIdleTimeout from any active connection pool, e.g., automatically setting it to Min(ConnectionIdleTimeout) / 2. In your situation, it would then run automatically every 5s and connections would be closed when they had been idle for 10s-14.9s (depending on exactly when the reaper ran); does that sound like it'd work for you?

[bgrainger]

Setting [wait_timeout] to 10s on the database server might work around the problem by making the server kill idle connections.

Just found an old comment of mine where I tried that and it had negative repercussions on the client-side (so it may not be viable): #211 (comment)

[bgrainger]

Because a connection string setting is per-connection-pool but the reaper is currently global

I'm wondering if this was a premature optimisation.

Since most applications only have one connection pool, it should be fine to have one reaper per pool, and set its frequency based on ConnectionIdleTimeout; this should be much easier to implement.

[bgrainger]

Improved in 0.36.1.

[nvivo]

@bgrainger Just to make sure I understand how it works now:

If ConnectionIdleTimeout >= 60 seconds, it will run every 60 seconds.
If ConnectionIdleTimeout < 60 seconds, it will run in the same interval as ConnectionIdleTimeout, limited to once per second.

Correct?

[bgrainger]

If ConnectionIdleTimeout >= 120 seconds (two minutes), it will run every 60 seconds.
If ConnectionIdleTimeout < 120 seconds, it will run every ConnectionIdleTimeout / 2 seconds, but at most once per second.

Examples:

• ConnectionIdleTimeout = 1, it runs every second
• ConnectionIdleTimeout = 10, it runs every 5 seconds
• ConnectionIdleTimeout = 90, it runs every 45 seconds
• ConnectionIdleTimeout = 180 (the default), it runs every 60 seconds

[nvivo]

Makes sense. Thanks!