How do I troubleshoot connection pinning issues in Amazon RDS Proxy?

Resolution

Review CloudWatch metrics

Complete the following steps:

1. Open the Amazon CloudWatch console.
2. In the navigation pane, choose Metrics and then All Metrics.
3. In the Browse tab, choose RDS and then Per-Proxy Metrics.
4. Search for the DatabaseConnectionsCurrentlySessionPinned metric.

The DatabaseConnectionsCurrentlySessionPinned metric shows the number of database connections that RDS proxy pins every 60 seconds. When operations in the client request change the state of the session, RDS Proxy pins the connections.

Note: The DatabaseConnectionsCurrentlySessionPinned metric starts to record when RDS Proxy detects the first pinned connection and stops when the pinned connection has NULL values.

Review RDS Proxy log events

To get detailed information about the SQL statements and RDS Proxy internal operations, modify the proxy to activate enhanced logging.

Note: Enhanced logging automatically turns off after 24 hours.

To troubleshoot connection pinning issues, 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, such as /aws/rds/proxy/name of your proxy.
4. In the Log Streams tab, select the log stream to view the log events.

Run CloudWatch Log 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 Log Insights.

Use the query editor to run the following queries. In each query, replace proxy-name with your proxy name.

To determine why an individual connection pinned, run the following query:

fields @message  
| sort @timestamp asc  
| filter @logStream like '{proxy-name}'  
| filter @message like /The client session was pinned to the database connection/

To get the top pinned connections by count, run the following query:

fields @message  
| sort @timestamp asc  
| filter @logStream like '{proxy-name}'  
| filter @message like /The client session was pinned to the database connection/  
| parse 'Reason: \\\*' as reason  
| stats count() as reasonCount by reason  
| sort by reasonCount desc  
| limit 20

Resolve session setting changes

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.

When the client connection changes the session-level variable settings, RDS Proxy pins the connection because it can't reuse it.

Then, you receive the following error message:

"The client session was pinned to the database connection [dbConnection=xxxx] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query."

All database connections must have identical settings. To resolve this issue, add the Initialization query setting when you create a proxy. Specify SQL statements for the proxy to run when the proxy opens each new database connection. To modify an existing proxy, you can use the Amazon Aurora and RDS console or run the modify-db-proxy-target-group AWS CLI command.

Important: Don't add sensitive data, such as passwords or long-lived encryption keys, to the initialization query. Authentication or cryptographic methods don't protect it because anyone with access to your proxy target group configuration can view the initialization query.

Typically, you use the Initialization query setting with SET statements so that each connection has identical settings. To include multiple variables in a single SET statement, use comma separators.

For example, you can run the following command to set a time zone variable in the initialization query:

aws rds modify-db-proxy-target-group --target-group-name default --db-proxy-name proxy --connection-pool-config '{ > "InitQuery": "SET time_zone = \"+00:00\";" > }' 

Note: Replace proxy with your proxy name.

Resolve parse message and protocol-level prepared statement errors

When specific libraries, such as asyncpg/mysql.connector, internally use protocol-level prepared statements, you receive one of the following error messages: 

• "The client session was pinned to the database connection [dbConnection=xxxx] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: A parse message was detected."
• "The client session was pinned to the database connection [dbConnection=xxxx] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: A protocol-level prepared statement was detected."

If the client uses the prepared statements, then RDS Proxy pins the connection.

To resolve this issue, close all connections that use prepared statements after the proxy explicitly used the connections.

Resolve large SQL queries

You might receive the following error message:

"The client session was pinned to the database connection [dbConnection=xxxx] for the remainder of the session. The proxy can't reuse this connection until the session ends. Reason: The connection ran a SQL query which exceeded the 16384 byte limit."

For all DB engines that RDS proxy supports, RDS Proxy pins a session when it encounters SQL statements that are larger than 16 KB. It's best practice to reduce the size of SQL statements. For example, you can remove comments or limit alias usage.

Reduce connection pins

To avoid unnecessary database requests that can cause the proxy to pin a connection, take the following actions:

• Remove database operations that initiate a connection pin.
• Use batch operations to combine related requests into a single query.

To standardize connection settings, take the following actions:

• Maintain consistent variable and configuration settings across connections to manage transaction-level reuse.
• For Amazon RDS for PostgreSQL, set the variables on the database side. When you set the variables on the client side, RDS Proxy pins the database connection.
• For Amazon RDS for MySQL databases, use session pinning filters to specify the database operations that can safely avoid the session pinning requirements.

It's best practice to move common SET statements to the proxy's initialization query for identical initialization across all connections and to maintain transaction-level reuse.

Related Information

RDS Proxy concepts and terminology

Monitoring RDS Proxy metrics with Amazon CloudWatch