The scenario:
I'm testing out a project written by another engineer in Go to interface with a mysql database. It came to my attention that in Go, whenever a new mysql connection is created, the defaults for this connection are set such that connections never expire.
We recently had a bug in our code that resulted in the proposed statement queue in mysql being completely filled up, resulting in all database requests being denied. The proposed solution for this bug is to clear out all mysql connections every 24 hours to prevent the proposed statement queue from being filled up.
It is my understanding that our code spawns new connections automatically as needed, and hence clearing out all connections every 24 hours should have no unintended consequences.
The underlying reason why this queue is being filled up seems to be a disconnect between GO language cancelling a connection or request; whereas in mysql, the connection still exists but is only idle. So the proposed solution is to clear out these idle requests and allow them to timeout, using the following code:
conn.SetConnMaxLifetime(24 * time.Hour)
conn.SetConnMaxIdleTime(1 * time.Hour)
conn.SetMaxOpenConns(30)
conn.SetMaxIdleConns(1)
What I seek to understand:
What advantage is there for keeping idle connections, and could there be a situation where more than 1 idle connection is needed?
Are there any unintended consequences that haven't been considered; that could result in a future issue where all database requests get denied?