Avoid session pinning with RDS proxy with Glue connection

Question

Hello,

I need some guidance on avoiding session pinning when using RDS Proxy with a Glue connection. In my Glue job (using Glue 5), data is exported to an RDS Aurora Postgres database via an RDS Proxy. Here are the connection pool configurations for the target group:

```
connection_pool_config {
    connection_borrow_timeout    = 120
    init_query                   = ""
    max_connections_percent      = 100
    max_idle_connections_percent = 10
    session_pinning_filters      = ["EXCLUDE_VARIABLE_SETS"]
}
```

When I run the Glue job, the number of database connections increases significantly (by at least 100) compared to running the job without RDS Proxy and connecting directly to the RDS endpoint. Additionally, the `DatabaseConnectionsCurrentlySessionPinned` count reaches 118 during the job's execution. The RDS Proxy logs show messages indicating that sessions are being pinned:

```
2024-12-24T16:34:19.049Z [WARN] [proxyEndpoint=default] [clientConnection=493046057] The client session was pinned to the database connection [dbConnection=650571780] 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. Digest: "set datestyle=$1; set client_min_messages=$2; select set_config($3,$4,$5) from pg_show_all_settings() where name = $6; set client_encoding=$7;".
```

Despite adding `session_pinning_filters = ["EXCLUDE_VARIABLE_SETS"]` in the target group configuration, sessions still get pinned. 
According to [AWS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html#rds-proxy-pinning:~:text=RDS%20Proxy%20doesn%27t%20support%20session%20pinning%20filters%20for%20PostgreSQL) session pinning filters are not supported for PostgreSQL. 
However, I found [this article](https://aws.amazon.com/blogs/database/amazon-rds-proxy-multiplexing-support-for-postgresql-extended-query-protocol/) suggesting that this issue has been addressed using the Extended Query Protocol. Since I am using JDBC driver version 42.7.3 with Glue 5, I believe it defaults to the Extended Query Protocol.

Here is the statement I use to populate tables in RDS from the Glue job:

```
glueContext.write_dynamic_frame.from_options(
    frame=asset_mapped,
    connection_type="postgresql",
    connection_options={
        "useConnectionProperties": "true",
        "connectionName": f"rds_{env_name}",
        "dbtable": "table_name",
        "bulkSize": 1000
    },
    transformation_ctx="asset_table_postgresql"
)
```

Could you please guide me on any configuration changes needed to avoid session pinning while using RDS Proxy with Glue jobs?

Neisha · Answer

Hi @rePost-User-1308834!
I appreciate your guidance.

I added `&prepareThreshold=0` in the connection URL, but still the number of `DatabaseConnectionsCurrentlySessionPinned` and `DatabaseConnectionsCurrentlyBorrowed` is exactly the same when the glue job runs.

The glue job is using Glue 5, and `glueContext.write_dynamic_frame.from_options` method to write to a table in RDS. 
I am not sure which inherited library in Glue 5 is making the listed interactions you mentioned. 
Could you please guide me how to fix this?

Neisha · Answer

I tried updating the URL to include parameters `assumeMinServerVersion` and `ApplicationName` but still the sessions are getting pinned with this log message

`If this prepared statement runs, the client session will be pinned to the database connection [dbConnection=25...]. Reason: SQL changed session settings that the proxy doesn't track. Consider moving session configuration to the proxy's initialization query. Digest: "set session characteristics as transaction isolation level $1 uncommitted".`

rePost-User-1308834 · Answer

For PostgreSQL, the following interactions also cause pinning:

* Using SET commands.
* Using PREPARE, DISCARD, DEALLOCATE, or EXECUTE commands to manage prepared statements.
* Creating temporary sequences, tables, or views.
* Declaring cursors.
* Discarding the session state.
* Listening on a notification channel.
* Loading a library module such as auto_explain.
* Manipulating sequences using functions such as nextval and setval.
* Interacting with locks using functions such as pg_advisory_lock and pg_try_advisory_lock.

Using prepared statements will cause pinning.

To avoid pinning, you will have to turn off prepared statements.

re:Post Agent · Answer

To minimize session pinning when using RDS Proxy with a Glue connection to an Aurora PostgreSQL database, there are several strategies you can employ:

1. JDBC Connection Parameters: When using PostgreSQL with JDBC, set two important connection parameters to avoid pinning:
   - Set `assumeMinServerVersion` to at least '9.0'. This prevents an extra round trip during connection startup for setting extra_float_digits.
   - Set `ApplicationName` to 'any/your-application-name'. This avoids an extra round trip for setting the application name.

2. Consistent Variable Settings: Ensure that variables and configuration settings are consistent across all connections. This increases the likelihood of connection reuse.

3. Avoid Unnecessary Database Requests: Minimize database operations that might trigger pinning, such as setting session-specific variables or using certain PostgreSQL-specific features.

4. Monitor Pinning: Keep track of pinning frequency using the Amazon CloudWatch metric `DatabaseConnectionsCurrentlySessionPinned`. This can help you identify when pinning occurs and adjust your strategy accordingly.

5. Connection Pool Configuration: While you've already set some connection pool configurations, consider adjusting the following:
   - Increase `max_idle_connections_percent` if possible, to allow more connections to remain available for reuse.
   - Set an appropriate `init_query` if there are any session-level configurations that all connections should have.

6. Glue Job Configuration: In your Glue job, when writing to PostgreSQL, try to minimize the number of separate write operations. Batch writes where possible to reduce the number of individual connections needed.

7. Extended Query Protocol: Since you're using a recent JDBC driver (42.7.3) with Glue 5, you're likely already benefiting from the Extended Query Protocol support. This should help with multiplexing for PostgreSQL, but it's not a silver bullet for all pinning scenarios.

8. Review Application Logic: Examine your Glue job to see if there are any operations that might be causing unnecessary session state changes. For example, avoid setting session-level variables if possible.

Remember that while these strategies can help reduce pinning, some level of pinning may still occur due to the nature of PostgreSQL and certain operations that require session-specific states. Continue monitoring your CloudWatch metrics and RDS Proxy logs to fine-tune your approach based on your specific workload characteristics.  
**Sources**  
[Avoiding pinning an RDS Proxy - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-pinning.html)  
[Connecting to a database through RDS Proxy - Amazon Aurora](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/rds-proxy-connecting.html)  
[Connecting to a database through RDS Proxy - Amazon Relational Database Service](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy-connecting.html)

Avoid session pinning with RDS proxy with Glue connection

Add your answer

Relevant content