How do I monitor Amazon RDS Proxy connection pools and optimize database connections?

Resolution

Monitor metrics for RDS Proxy and the target RDS database

Complete the following steps:

1. Open the Amazon CloudWatch console.
2. In the navigation pane, choose Metrics, and then choose All Metrics.
3. On the Browse tab, choose RDS, and then choose Per-Proxy Metrics.
4. Search for the following metrics:
   ClientConnections for the number of incoming connections from your application.
   MaxDatabaseConnectionsAllowed for the maximum number of database connections that RDS proxy allows.
   DatabaseConnections for the current number of connections that RDS Proxy establishes to the target database.
5. On the Browse tab, choose RDS and then choose DBInstanceIdentifier.
6. Search for the DatabaseConnections metric to get the total number of connections that RDS Proxy establishes to the target database and all other connections to the target database.

It's best practice to review these metrics with a 1-minute granularity and use the Sum statistic for more details about connection usage patterns.

Review RDS Proxy log events

Modify the RDS proxy to activate Enhanced logging so that you can get advanced details, such as the connections that are opening or closing. You can also get details for internal operations, such as pinning and borrowing.

Note: Enhanced logging automatically turns off after 24 hours.

To review RDS Proxy log events, complete the following steps:

1. Open the CloudWatch console.
2. In the navigation pane, choose Logs, and then choose Log groups.
3. Select the log group for your proxy /aws/rds/proxy/.
4. On the Log Streams tab, select the log stream to view the log events.

Run CloudWatch Logs Insights queries to detect anomalies

To access the query editor in CloudWatch Logs Insights, complete the following steps:

1. Open the CloudWatch console.
2. In the navigation pane, choose Logs, and then choose Logs Insights.

Use the query editor to run the following queries. In each query, replace prx-000##### with your proxy ID.

To get errors or warnings, run the following query:

fields @timestamp, @message  
| sort @timestamp asc  
| filter @logStream like 'prx-000#####'  
| filter @message like / ERROR: / or @message like / WARN: /

To determine why the client connection closed, run the following query:

fields @message  
| sort @timestamp asc  
| filter @logStream like 'prx-000#####'  
| filter @message like /The client connection closed/ and @message like /CUSTOMER/  
| parse 'Reason: *' as reason  
| stats count() as reasonCount by reason  
| sort by reasonCount desc  
| limit 20

To determine why the database connection closed, run the following query:

fields @message  
| sort @timestamp asc  
| filter @logStream like 'prx-000xxxxx'  
| filter @message like /The database connection closed/ and @message like /CUSTOMER/  
| parse 'Reason: *' as reason  
| stats count() as reasonCount by reason  
| sort by reasonCount desc  
| limit 20

Modify RDS Proxy connect settings

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

MaxConnectionsPercent

If your database reached the maximum connection setting, the you receive the following error message: 

"The database connection closed. Reason: The pool reached the maximum connection setting, and a connection with different properties was required."

To resolve this issue, monitor the DatabaseConnections and MaxDatabaseConnectionsAllowed CloudWatch metrics. If DatabaseConnections is reaching MaxDatabaseConnectionsAllowed, then limit the incoming traffic. Or, run the modify-db-proxy-target-group AWS CLI command or ModifyDBProxyTargetGroup API operation to increase the MaxConnectionsPercent setting. Set the MaxConnectionsPercent to at least 30% above your maximum recent monitored usage.

MaxIdleConnectionsPercent

If there are too many idle connections in the pool, then you receive the following message: 

"The database connection closed. Reason: There were too many idle connections in the connection pool. A new slot in the connection pool is now available."

To resolve this issue, monitor the ClientConnections and DatabaseConnections CloudWatch metrics. If ClientConnections is continually less than DatabaseConnections, then there are idle connections to your target database. By default, the MaxIdleConnectionsPercent setting is 50% of the MaxConnectionsPercent value. To modify MaxIdleConnectionsPercent, run the modify-db-proxy-target-group AWS CLI command or ModifyDBProxyTargetGroup API operation. Keep a high percentage of idle database connections open. Use lower values for consistent workloads to avoid idle resource usage.

IdleClientTimeout

If your database exceeds the client connection idle timeout, then you receive the following message:

"The client connection closed. Reason: The idle timeout was exceeded."

To remove idle connections, reduce IdleClientTimeout. If your workload establishes frequent connections with the proxy, then increase IdleClientTimeout so that you don't need to establish connections. To modify IdleClientTimeout, run the modify-db-proxy AWS CLI command or the ModifyDBProxy API operation.

ConnectionBorrowTimeout

If you get a timeout error, then RDS Proxy can't establish a database connection.

To set the ConnectionBorrowTimeout setting to a value that's lower than the connection timeout value, run the modify-db-proxy-target-group AWS CLI command or ModifyDBProxyTargetGroup API operation.

Manually close open connections

If a database connection is idle for more than 24 hours, then you receive the following error message:

"The database connection closed. Reason: The idle connection exceeded the maximum connection duration."

You can't modify the maximum life of an idle database connection in RDS Proxy. Instead, it's a best practice to manually close connections that your application doesn't need to reuse.

Related information

RDS Proxy concepts and terminology

Monitoring RDS Proxy metrics with Amazon CloudWatch