Connection management and pooling

Always close database connections explicitly instead of relying on the development framework or language destructors to do it. There are situations, especially in container based or code-as-a-service scenarios, when the underlying code container isn’t immediately destroyed after the code completes. In such cases, you might experience database connection leaks where connections are left open and continue to hold resources (for example, memory and locks).

If you can’t rely on client applications (or interactive clients) to close idle connections, use the server’s wait_timeout and interactive_timeout parameters to configure idle connection timeout. The default timeout value is fairly high at 28,800 seconds (8 hours). You should tune it down to a value that’s acceptable in your environment. Refer to the MySQL Reference Manual for details.

Consider using connection pooling to protect the database against connection surges. Also, consider connection pooling if the application opens large numbers of connections (for example, thousands or more per second) and the connections are short lived, that is, the time required for connection setup and teardown is significant compared to the total connection lifetime. If your development framework or language doesn’t support connection pooling, you can use a connection proxy instead.

Amazon RDS Proxy is a fully managed, highly available database proxy for Amazon Relational Database Service (Amazon RDS) that makes applications more scalable, more resilient to database failures, and more secure. ProxySQL, MaxScale, and ScaleArc are examples of third-party proxies compatible with the MySQL protocol.

Refer to the Connection scaling section of this document for more notes on connection pools versus proxies.

By using Amazon RDS Proxy, you can allow your applications to pool and share database connections to improve their ability to scale. Amazon RDS Proxy makes applications more resilient to database failures by automatically connecting to a standby DB instance while preserving application connections.

AWS recommends the following for configuring connection pools and proxies:

• Check and validate connection health when the connection is borrowed from the pool. The validation query can be as simple as SELECT 1. However, in Amazon Aurora you can also use connection checks that return a different value depending on whether the instance is a primary instance (read/write) or an Aurora Replica (read-only). For example, you can use the @@innodb_read_only variable to determine the instance role. If the variable value is TRUE, you're on an Aurora Replica.

• Check and validate connections periodically even when they're not borrowed. It helps detect and clean up broken or unhealthy connections before an application thread attempts to use them.

• Don't let connections remain in the pool indefinitely. Recycle connections by closing and reopening them periodically (for example, every 15 minutes), which frees the resources associated with these connections. It also helps prevent dangerous situations such as runaway queries or zombie connections that clients have abandoned. This recommendation applies to all connections, not just idle ones.