# Managing an RDS Proxy Managing an RDS Proxy This section provides information on how to manage RDS Proxy operation and configuration. These procedures help your application make the most efficient use of database connections and achieve maximum connection reuse. The more that you can take advantage of connection reuse, the more CPU and memory overhead that you can save. This in turn reduces latency for your application and enables the database to devote more of its resources to processing application requests. **Topics** + [ # Modifying an RDS Proxy ](rds-proxy-modifying-proxy.md) + [ # Adding a new database user when using RDS Proxy ](rds-proxy-new-db-user.md) + [ # Moving from standard IAM authentication to end-to-end IAM authentication for RDS Proxy ](rds-proxy-iam-migration.md) + [ # RDS Proxy connection considerations ](rds-proxy-connections.md) + [ # Avoiding pinning an RDS Proxy ](rds-proxy-pinning.md) + [ # Deleting an RDS Proxy ](rds-proxy-deleting.md) # Modifying an RDS Proxy Modifying an RDS Proxy You can change specific settings associated with a proxy after you create the proxy. You do so by modifying the proxy itself, its associated target group, or both. Each proxy has an associated target group. ## AWS Management Console **Important** The values in the **Client authentication type** and **IAM authentication** fields apply to all Secrets Manager secrets that are associated with this proxy. To specify different values for each secret, modify your proxy by using the AWS CLI or the API instead. **To modify the settings for a proxy** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Proxies**. 1. In the list of proxies, choose the proxy whose settings you want to modify or go to its details page. 1. For **Actions**, choose **Modify**. 1. Enter or choose the properties to modify. You can modify the following: + **Proxy identifier** – Rename the proxy by entering a new identifier. + **Idle client connection timeout** – Enter a time period for the idle client connection timeout. + **IAM role** – Change the IAM role used to retrieve the secrets from Secrets Manager. **Note** You can't create a new IAM role if you set **Default authentication scheme** to **IAM authentication**. + **Secrets Manager secrets** – Add or remove Secrets Manager secrets. These secrets correspond to database user names and passwords. + **Client authentication type** – Change the type of authentication for client connections to the proxy. + **IAM authentication** – Require or disallow IAM authentication for connections to the proxy. + **Default authentication scheme** – Change the default authentication scheme the proxy uses for client connections to the proxy and connections from the proxy to the underlying database. + **Require Transport Layer Security** – Turn the requirement for Transport layer Security (TLS) on or off. + **VPC security group** – Add or remove VPC security groups for the proxy to use. + **Enable enhanced logging** – Enable or disable enhanced logging. 1. Choose **Modify**. If you didn't find the settings listed that you want to change, use the following procedure to update the target group for the proxy. The *target group* associated with a proxy controls the settings related to the physical database connections. Each proxy has one associated target group named `default`, which is created automatically along with the proxy. You can't rename the default target group. You can only modify the target group from the proxy details page, not from the list on the **Proxies** page. **To modify the settings for a proxy target group** 1. On the **Proxies** page, go to the details page for a proxy. 1. For **Target groups**, choose the `default` link. Currently, all proxies have a single target group named `default`. 1. On the details page for the **default** target group, choose **Modify**. 1. Choose new settings for the properties that you can modify: + **Database** – Choose a different Aurora cluster. + **Connection pool maximum connections** – Adjust what percentage of the maximum available connections the proxy can use. + **Session pinning filters** – (Optional) Choose a session pinning filter. This circumvents the default safety measures for multiplexing database connections across client connections. Currently, the setting isn't supported for PostgreSQL. The only choice is `EXCLUDE_VARIABLE_SETS`. Enabling this setting can cause session variables of one connection to impact other connections. This can cause errors or correctness issues if your queries depend on session variable values set outside of the current transaction. Consider using this option after verifying it is safe for your applications to share database connections across client connections. The following patterns can be considered safe: + `SET` statements where there is no change to the effective session variable value, i.e., there is no change to the session variable. + You change the session variable value and execute a statement in the same transaction. For more information, see [Avoiding pinning an RDS Proxy](rds-proxy-pinning.md). + **Connection borrow timeout** – Adjust the connection borrow timeout interval. This setting applies when the maximum number of connections is already being used for the proxy. The setting determines how long the proxy waits for a connection to become available before returning a timeout error. + **Initialization query**. (Optional) Add an initialization query, or modify the current one. You can specify one or more SQL statements for the proxy to run when opening each new database connection. The setting is typically used with `SET` statements to make sure that each connection has identical settings. Make sure that the query you add is valid. To include multiple variables in a single `SET` statement, use comma separators. For example: ``` SET variable1=value1, variable2=value2 ``` For multiple statements, use semicolons as the separator. You can't change certain properties, such as the target group identifier and the database engine. 1. Choose **Modify target group**. ## AWS CLI To modify a proxy using the AWS CLI, use the commands [modify-db-proxy](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-proxy.html), [modify-db-proxy-target-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-proxy-target-group.html), [deregister-db-proxy-targets](https://docs.aws.amazon.com/cli/latest/reference/rds/deregister-db-proxy-targets.html), and [register-db-proxy-targets](https://docs.aws.amazon.com/cli/latest/reference/rds/register-db-proxy-targets.html). With the `modify-db-proxy` command, you can change properties such as the following: + The set of Secrets Manager secrets used by the proxy. + Whether TLS is required. + The idle client timeout. + Whether to log additional information from SQL statements for debugging. + The IAM role used to retrieve Secrets Manager secrets. + The security groups used by the proxy. + The default authentication scheme associated with the proxy. The following example shows how to rename an existing proxy. ``` aws rds modify-db-proxy --db-proxy-name the-proxy --new-db-proxy-name the_new_name ``` To modify connection-related settings or rename the target group, use the `modify-db-proxy-target-group` command. Currently, all proxies have a single target group named `default`. When you work with this target group, you specify the name of the proxy and `default` for the name of the target group. You can't rename the default target group. The following example shows how to first check the `MaxIdleConnectionsPercent` setting for a proxy and then change it, using the target group. ``` aws rds describe-db-proxy-target-groups --db-proxy-name the-proxy { "TargetGroups": [ { "Status": "available", "UpdatedDate": "2019-11-30T16:49:30.342Z", "ConnectionPoolConfig": { "MaxIdleConnectionsPercent": 50, "ConnectionBorrowTimeout": 120, "MaxConnectionsPercent": 100, "SessionPinningFilters": [] }, "TargetGroupName": "default", "CreatedDate": "2019-11-30T16:49:27.940Z", "DBProxyName": "the-proxy", "IsDefault": true } ] } aws rds modify-db-proxy-target-group --db-proxy-name the-proxy --target-group-name default --connection-pool-config ' { "MaxIdleConnectionsPercent": 75 }' { "DBProxyTargetGroup": { "Status": "available", "UpdatedDate": "2019-12-02T04:09:50.420Z", "ConnectionPoolConfig": { "MaxIdleConnectionsPercent": 75, "ConnectionBorrowTimeout": 120, "MaxConnectionsPercent": 100, "SessionPinningFilters": [] }, "TargetGroupName": "default", "CreatedDate": "2019-11-30T16:49:27.940Z", "DBProxyName": "the-proxy", "IsDefault": true } } ``` With the `deregister-db-proxy-targets` and `register-db-proxy-targets` commands, you change which Aurora DB clusters the proxy is associated with through its target group. Currently, each proxy can connect to one Aurora DB cluster. The target group tracks the connection details for all the all the DB instances in an Aurora cluster. The following example starts with a proxy that is associated with an Aurora MySQL cluster named `cluster-56-2020-02-25-1399`. The example shows how to change the proxy so that it can connect to a different cluster named `provisioned-cluster`. When you work with an Aurora DB cluster, you specify the `--db-cluster-identifier` option. The following example modifies an Aurora MySQL proxy. An Aurora PostgreSQL proxy has port 5432. ``` aws rds describe-db-proxy-targets --db-proxy-name the-proxy { "Targets": [ { "Endpoint": "instance-9814.demo.us-east-1.rds.amazonaws.com", "Type": "RDS_INSTANCE", "Port": 3306, "RdsResourceId": "instance-9814" }, { "Endpoint": "instance-8898.demo.us-east-1.rds.amazonaws.com", "Type": "RDS_INSTANCE", "Port": 3306, "RdsResourceId": "instance-8898" }, { "Endpoint": "instance-1018.demo.us-east-1.rds.amazonaws.com", "Type": "RDS_INSTANCE", "Port": 3306, "RdsResourceId": "instance-1018" }, { "Type": "TRACKED_CLUSTER", "Port": 0, "RdsResourceId": "cluster-56-2020-02-25-1399" }, { "Endpoint": "instance-4330.demo.us-east-1.rds.amazonaws.com", "Type": "RDS_INSTANCE", "Port": 3306, "RdsResourceId": "instance-4330" } ] } aws rds deregister-db-proxy-targets --db-proxy-name the-proxy --db-cluster-identifier cluster-56-2020-02-25-1399 aws rds describe-db-proxy-targets --db-proxy-name the-proxy { "Targets": [] } aws rds register-db-proxy-targets --db-proxy-name the-proxy --db-cluster-identifier provisioned-cluster { "DBProxyTargets": [ { "Type": "TRACKED_CLUSTER", "Port": 0, "RdsResourceId": "provisioned-cluster" }, { "Endpoint": "gkldje.demo.us-east-1.rds.amazonaws.com", "Type": "RDS_INSTANCE", "Port": 3306, "RdsResourceId": "gkldje" }, { "Endpoint": "provisioned-1.demo.us-east-1.rds.amazonaws.com", "Type": "RDS_INSTANCE", "Port": 3306, "RdsResourceId": "provisioned-1" } ] } ``` ## RDS API To modify a proxy using the RDS API, you use the operations [ModifyDBProxy](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBProxy.html), [ModifyDBProxyTargetGroup](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBProxyTargetGroup.html), [DeregisterDBProxyTargets](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DeregisterDBProxyTargets.html), and [RegisterDBProxyTargets](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_RegisterDBProxyTargets.html) operations. With `ModifyDBProxy`, you can change properties such as the following: + The set of Secrets Manager secrets used by the proxy. + Whether TLS is required. + The idle client timeout. + Whether to log additional information from SQL statements for debugging. + The IAM role used to retrieve Secrets Manager secrets. + The security groups used by the proxy. With `ModifyDBProxyTargetGroup`, you can modify connection-related settings. Currently, all proxies have a single target group named `default`. When you work with this target group, you specify the name of the proxy and `default` for the name of the target group. You can't rename the default target group. With `DeregisterDBProxyTargets` and `RegisterDBProxyTargets`, you change which Aurora cluster the proxy is associated with through its target group. Currently, each proxy can connect to one Aurora cluster. The target group tracks the connection details for the DB instances in an Aurora cluster. # Adding a new database user when using RDS Proxy Adding a database user In some cases, you might add a new database user to an Aurora cluster that's associated with a proxy. Proceed depending on whether you're using standard authentication with Secrets Manager secrets or end-to-end IAM authentication. If you are using the standard IAM authentication, follow these instructions: 1. Create a new Secrets Manager secret, using the procedure described in [Setting up database credentials for RDS Proxy](rds-proxy-secrets-arns.md). 1. Update the IAM role to give RDS Proxy access to the new Secrets Manager secret. To do so, update the resources section of the IAM role policy. 1. Modify the RDS Proxy to add the new Secrets Manager secret under **Secrets Manager secrets**. 1. If the new user takes the place of an existing one, update the credentials stored in the proxy's Secrets Manager secret for the existing user. If you're using end-to-end IAM authentication, you need to create the database user and configure IAM permissions. To do this, run through the following steps: 1. Create a new database user in your database that matches the IAM user or role name you want to use for authentication. 1. Ensure the database user is configured with IAM authentication plugin at the database. See [Creating a database account using IAM authentication](UsingWithRDS.IAMDBAuth.DBAccounts.md). 1. Update the IAM policy to grant the `rds-db:connect` permission to the IAM user or role, as described in [Creating an IAM policy for end-to-end IAM authentication](rds-proxy-iam-setup.md#rds-proxy-iam-setup-e2e-steps). 1. Ensure your proxy is configured to use IAM authentication as the default authentication scheme. With end-to-end IAM authentication, you don't need to manage database credentials in Secrets Manager secrets, as IAM credentials are used for authentication from the client to the proxy and from the proxy to the database. ## Adding a new database user to a PostgreSQL database when using RDS Proxy When adding a new user to your PostgreSQL database, if you have run the following command: ``` REVOKE CONNECT ON DATABASE postgres FROM PUBLIC; ``` Grant the `rdsproxyadmin` user the `CONNECT` privilege so the user can monitor connections on the target database. ``` GRANT CONNECT ON DATABASE postgres TO rdsproxyadmin; ``` You can also allow other target database users to perform health checks by changing `rdsproxyadmin` to the database user in the command above. ## Changing the password for a database user when using RDS Proxy Changing database passwords In some cases, you might change the password for a database user in an Aurora cluster that's associated with a proxy. If so, update the corresponding Secrets Manager secret with the new password. If you're using end-to-end IAM authentication, you don't need to update any passwords in Secrets Manager secrets. # Moving from standard IAM authentication to end-to-end IAM authentication for RDS Proxy Moving to end-to-end IAM authentication If you currently use standard IAM authentication for RDS Proxy, where clients authenticate to the proxy using IAM but the proxy connects to the database using secrets, you can migrate to end-to-end IAM authentication where both client-to-proxy and proxy-to-database connections use IAM authentication. **To move to end-to-end IAM authentication** 1. **Update RDS Proxy IAM role permissions** Create an updated proxy permission policy that includes both Secrets Manager and `rds:db-connect` permissions: ``` # Create updated proxy permission policy cat > updated-proxy-policy.json ≪ EOF ``` ``` { "Version": "2012-10-17", "Statement": [ { "Sid": "GetSecretsValue", "Action": [ "secretsmanager:GetSecretValue" ], "Effect": "Allow", "Resource": [ "arn:aws:secretsmanager:us-east-1:123456789012:secret:secretName-1234f" ] }, { "Sid": "RdsDBConnect", "Action": [ "rds-db:connect" ], "Effect": "Allow", "Resource": [ "arn:aws:rds-db:us-east-1:123456789012:dbuser:cluster-ABCDEFGHIJKL01234/jane_doe" ] } ] } ``` Update proxy your role policy: ``` aws iam put-role-policy \ --role-name RDSProxyRole \ --policy-name UpdatedProxyPermissions \ --policy-document file://updated-proxy-policy.json ``` 1. Modify your RDS Proxy to enable end-to-end IAM authentication ``` aws rds modify-db-proxy \ --db-proxy-name my-database-proxy \ --default-auth-scheme IAM_AUTH \ --region us-east-1 ``` Verify that RDS Proxy status is **Available** and `DefaultAuthScheme` is `IAM_AUTH` before proceeding to ensure zero downtime during migration. ``` aws rds describe-db-proxies --db-proxy-name my-database-proxy --region us-east-1 ``` Expected output: ``` { "DBProxies": [ { "DBProxyName": "my-database-proxy", "DBProxyArn": "arn:aws:rds:us-east-1:123456789012:db-proxy:prx-0123456789abcdef", "Status": "available", ... "DefaultAuthScheme": "IAM_AUTH" } ] } ``` 1. Enable IAM authentication on database ``` aws rds modify-db-cluster \ --db-cluster-identifier my-database-cluster \ --enable-iam-database-authentication \ --region us-east-1 ``` 1. Configure database user for IAM authentication For Aurora PostgreSQL: ``` GRANT rds_iam TO jane_doe; ``` For Aurora MySQL: ``` ALTER USER 'jane_doe' IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS'; ALTER USER 'jane_doe'@'%' REQUIRE SSL; ``` 1. Your client application code doesn't need to change. The connection process remains the same: For Aurora PostgreSQL: ``` # Generate authentication token export PGPASSWORD=$(aws rds generate-db-auth-token \ --hostname my-database-proxy.proxy-ABCDEFGHIJKL01234.us-east-1.rds.amazonaws.com \ --port 5432 \ --username jane_doe \ --region us-east-1) # Connect to database through proxy psql "host=my-database-proxy.proxy-ABCDEFGHIJKL01234.us-east-1.rds.amazonaws.com port=5432 user=jane_doe dbname=postgres password=$PGPASSWORD sslmode=require sslrootcert=us-east-1-bundle.pem" ``` For Aurora MySQL: ``` # Generate authentication token export MYSQL_PWD=$(aws rds generate-db-auth-token \ --hostname my-database-proxy.proxy-ABCDEFGHIJKL01234.us-east-1.rds.amazonaws.com \ --port 3306 \ --username jane_doe \ --region us-east-1) # Connect to database through proxy mysql -h my-database-proxy.proxy-ABCDEFGHIJKL01234.us-east-1.rds.amazonaws.com \ -P 3306 \ -u jane_doe \ --ssl-ca=us-east-1-bundle.pem \ --enable-cleartext-plugin ``` # RDS Proxy connection considerations RDS Proxy connection considerations ## Configuring connection settings To adjust RDS Proxy's connection pooling, you can modify the following settings: + [IdleClientTimeout](#rds-proxy-connection-pooling-tuning.idleclienttimeout) + [MaxConnectionsPercent](#rds-proxy-connection-pooling-tuning.maxconnectionspercent) + [MaxIdleConnectionsPercent](#rds-proxy-connection-pooling-tuning.maxidleconnectionspercent) + [ConnectionBorrowTimeout](#rds-proxy-connection-pooling-tuning.connectionborrowtimeout) ### IdleClientTimeout IdleClientTimeout You can specify how long a client connection can be idle before the proxy closes it. The default is 1,800 seconds (30 minutes). A client connection is considered *idle* when the application doesn't submit a new request within the specified time after the previous request completed. The underlying database connection stays open and is returned to the connection pool. Thus, it's available to be reused for new client connections. If you want the proxy to proactively remove stale connections, then lowering the idle client connection timeout. If your workload establishes frequent connections with the proxy, then raise the idle client connection timeout to save the cost of establishing connections. This setting is represented by the **Idle client connection timeout** field in the RDS console and the `IdleClientTimeout` setting in the AWS CLI and the API. To learn how to change the value of the **Idle client connection timeout** field in the RDS console, see [AWS Management Console](rds-proxy-modifying-proxy.md#rds-proxy-modifying-proxy.console). To learn how to change the value of the `IdleClientTimeout` setting, see the CLI command [modify-db-proxy](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-proxy.html) or the API operation [ModifyDBProxy](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBProxy.html). ### MaxConnectionsPercent MaxConnectionsPercent You can limit the number of connections that an RDS Proxy can establish with the target database. You specify the limit as a percentage of the maximum connections available for your database. This setting is represented by the **Connection pool maximum connections** field in the RDS console and the `MaxConnectionsPercent` setting in the AWS CLI and the API. The `MaxConnectionsPercent` value is expressed as a percentage of the `max_connections` setting for the Aurora DB cluster used by the target group. The proxy doesn't create all of these connections in advance. This setting allows the proxy to establish these connections as the workload needs them. For example, for a registered database target with `max_connections` set to 1000, and `MaxConnectionsPercent` set to 95, RDS Proxy sets 950 connections as the upper limit for concurrent connections to that database target. A common side-effect of your workload reaching the maximum number of allowed database connections is an increase in overall query latency, along with an increase in the `DatabaseConnectionsBorrowLatency` metric. You can monitor currently used and total allowed database connections by comparing the `DatabaseConnections` and `MaxDatabaseConnectionsAllowed` metrics. When setting this parameter, note the following best practices: + Allow sufficient connection headroom for changes in workload pattern. It is recommended to set the parameter at least 30% above your maximum recent monitored usage. As RDS Proxy redistributes database connection quotas across multiple nodes, internal capacity changes might require at least 30% headroom for additional connections to avoid increased borrow latencies. + RDS Proxy reserves a certain number of connections for active monitoring to support fast failover, traffic routing and internal operations. The `MaxDatabaseConnectionsAllowed` metric does not include these reserved connections. It represents the number of connections available to serve the workload, and can be lower than the value derived from the `MaxConnectionsPercent` setting. Minimal recommended `MaxConnectionsPercent` values are as follows: + db.t3.small: 100 + db.t3.medium: 55 + db.t3.large: 35 + db.r3.large or above: 20 If multiple target instances are registered with RDS Proxy like an Aurora cluster with reader nodes, set the minimum value based on the smallest registered instance. To learn how to change the value of the **Connection pool maximum connections** field in the RDS console, see [AWS Management Console](rds-proxy-modifying-proxy.md#rds-proxy-modifying-proxy.console). To learn how to change the value of the `MaxConnectionsPercent` setting, see the CLI command [modify-db-proxy-target-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-proxy-target-group.html) or the API operation [ModifyDBProxyTargetGroup](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBProxyTargetGroup.html). **Important** If the DB cluster is part of a global database with write forwarding turned on, reduce your proxy's `MaxConnectionsPercent` value by the quota that's allotted for write forwarding. The write forwarding quota is set in the DB cluster parameter `aurora_fwd_writer_max_connections_pct`. For information about write forwarding, see [Using write forwarding in an Amazon Aurora global database](aurora-global-database-write-forwarding.md). For information on database connection limits, see [Maximum connections to an Aurora MySQL DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Performance.html#AuroraMySQL.Managing.MaxConnections) and [Maximum connections to an Aurora PostgreSQL DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html#AuroraPostgreSQL.Managing.MaxConnections). ### MaxIdleConnectionsPercent MaxIdleConnectionsPercent You can control the number of idle database connections that RDS Proxy can keep in the connection pool. By default, RDS Proxy considers a database connection in its pool to be *idle* when there's been no activity on the connection for five minutes. The `MaxIdleConnectionsPercent` value is expressed as a percentage of the `max_connections` setting for the RDS DB instance target group. The default value is 50 percent of `MaxConnectionsPercent`, and the upper limit is the value of `MaxConnectionsPercent`. For example, if `MaxConnectionsPercent`, is 80, then the default value of `MaxIdleConnectionsPercent` is 40. With a high value, the proxy leaves a high percentage of idle database connections open. With a low value, the proxy closes a high percentage of idle database connections. If your workloads are unpredictable, consider setting a high value for `MaxIdleConnectionsPercent`. Doing so means that RDS Proxy can accommodate surges in activity without opening a lot of new database connections. This setting is represented by the `MaxIdleConnectionsPercent` setting of `DBProxyTargetGroup` in the AWS CLI and the API. To learn how to change the value of the `MaxIdleConnectionsPercent` setting, see the CLI command [modify-db-proxy-target-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-proxy-target-group.html) or the API operation [ModifyDBProxyTargetGroup](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBProxyTargetGroup.html). For information on database connection limits, see [Maximum connections to an Aurora MySQL DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Managing.Performance.html#AuroraMySQL.Managing.MaxConnections) and [Maximum connections to an Aurora PostgreSQL DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html#AuroraPostgreSQL.Managing.MaxConnections). ### ConnectionBorrowTimeout ConnectionBorrowTimeout You can choose how long RDS Proxy waits for a database connection in the connection pool to become available for use before returning a timeout error. The default is 120 seconds. This setting applies when the number of connections is at the maximum, and so no connections are available in the connection pool. It also applies when no appropriate database instance is available to handle the request, such as when a failover operation is in process. Using this setting, you can set the best wait period for your application without changing the query timeout in your application code. This setting is represented by the **Connection borrow timeout** field in the RDS console or the `ConnectionBorrowTimeout` setting of `DBProxyTargetGroup` in the AWS CLI or API. To learn how to change the value of the **Connection borrow timeout** field in the RDS console, see [AWS Management Console](rds-proxy-modifying-proxy.md#rds-proxy-modifying-proxy.console). To learn how to change the value of the `ConnectionBorrowTimeout` setting, see the CLI command [modify-db-proxy-target-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-proxy-target-group.html) or the API operation [ModifyDBProxyTargetGroup](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_ModifyDBProxyTargetGroup.html). ## Client and database connections Connections from your application to RDS Proxy are known as client connections. Connections from a proxy to the database are database connections. When using RDS Proxy, client connections terminate at the proxy while database connections are managed within RDS Proxy. Application-side connection pooling can provide the benefit of reducing recurring connection establishment between your application and RDS Proxy. Consider the following configuration aspects before implementing an application-side connection pool: + Client connection max life: RDS Proxy enforces a maximum life of client connections of 24 hours. This value is not configurable. Configure your pool with a maximum connection life less than 24 hours to avoid unexpected client connection drops. + Client connection idle timeout: RDS Proxy enforces a maximum idle time for client connections. Configure your pool with an idle connection timeout of a value lower than your client connection idle timeout setting for RDS Proxy to avoid unexpected connection drops. The maximum number of client connections configured in your application-side connection pool does not have to be limited to the **max\$1connections** setting for RDS Proxy. Client connection pooling results in a longer client connection life. If your connections experience pinning, then pooling client connections might reduce multiplexing efficiency. Client connections that are pinned but idle in the application-side connection pool continue to hold on to a database connection and prevent the database connection to be reused by other client connections. Review your proxy logs to check whether your connections experience pinning. **Note** RDS Proxy closes database connections some time after 24 hours when they are no longer in use. The proxy performs this action regardless of the value of the maximum idle connections setting. # Avoiding pinning an RDS Proxy Avoid pinning RDS Proxy Multiplexing is more efficient when database requests don't rely on state information from previous requests. In that case, RDS Proxy can reuse a connection at the conclusion of each transaction. Examples of such state information include most variables and configuration parameters that you can change through `SET` or `SELECT` statements. SQL transactions on a client connection can multiplex between underlying database connections by default. Your connections to the proxy can enter a state known as *pinning*. When a connection is pinned, each later transaction uses the same underlying database connection until the session ends. Other client connections also can't reuse that database connection until the session ends. The session ends when the client connection is dropped. RDS Proxy automatically pins a client connection to a specific DB connection when it detects a session state change that isn't appropriate for other sessions. Pinning reduces the effectiveness of connection reuse. If all or almost all of your connections experience pinning, consider modifying your application code or workload to reduce the conditions that cause the pinning. For example, your application changes a session variable or configuration parameter. In this case, later statements can rely on the new variable or parameter to be in effect. Thus, when RDS Proxy processes requests to change session variables or configuration settings, it pins that session to the DB connection. That way, the session state remains in effect for all later transactions in the same session. For some database engines, this rule doesn't apply to all parameters that you can set. RDS Proxy tracks certain statements and variables. Thus, RDS Proxy doesn't pin the session when you modify them. In this case, RDS Proxy only reuses the connection for other sessions that have the same values for those settings. For the lists of tracked statements and variables for Aurora MySQL, see [What RDS Proxy tracks for Aurora MySQL databases](#rds-proxy-pinning.mysql-tracked-vars). ## What RDS Proxy tracks for Aurora MySQL databases RDS Proxy tracks the following MySQL statements: + DROP DATABASE + DROP SCHEMA + USE RDS Proxy tracks the following MySQL variables: + `AUTOCOMMIT` + `AUTO_INCREMENT_INCREMENT` + `CHARACTER SET (or CHAR SET)` + `CHARACTER_SET_CLIENT` + `CHARACTER_SET_DATABASE` + `CHARACTER_SET_FILESYSTEM` + `CHARACTER_SET_CONNECTION` + `CHARACTER_SET_RESULTS` + `CHARACTER_SET_SERVER` + `COLLATION_CONNECTION` + `COLLATION_DATABASE` + `COLLATION_SERVER` + `INTERACTIVE_TIMEOUT` + `NAMES` + `NET_WRITE_TIMEOUT` + `QUERY_CACHE_TYPE` + `SESSION_TRACK_SCHEMA` + `SQL_MODE` + `TIME_ZONE` + `TRANSACTION_ISOLATION (or TX_ISOLATION)` + `TRANSACTION_READ_ONLY (or TX_READ_ONLY)` + `WAIT_TIMEOUT` **Note** RDS Proxy tracks changes to the `TRANSACTION_ISOLATION` and `TRANSACTION_READ_ONLY` variables when you set them at the session scope. However, if you set them at the next transaction scope, RDS Proxy pins connections. This behavior applies whether you use a `SET` statement or a `SET TRANSACTION` statement to configure these values. ## Minimizing pinning Performance tuning for RDS Proxy involves trying to maximize transaction-level connection reuse (multiplexing) by minimizing pinning. You can minimize pinning by doing the following: + Avoid unnecessary database requests that might cause pinning. + Set variables and configuration settings consistently across all connections. That way, later sessions are more likely to reuse connections that have those particular settings. However, for PostgreSQL setting a variable leads to session pinning. + For a MySQL engine family database, apply a session pinning filter to the proxy. You can exempt certain kinds of operations from pinning the session if you know that doing so doesn't affect the correct operation of your application. + See how frequently pinning occurs by monitoring the Amazon CloudWatch metric `DatabaseConnectionsCurrentlySessionPinned`. For information about this and other CloudWatch metrics, see [Monitoring RDS Proxy metrics with Amazon CloudWatchMonitoring RDS Proxy with CloudWatch](rds-proxy.monitoring.md). + If you use `SET` statements to perform identical initialization for each client connection, you can do so while preserving transaction-level multiplexing. In this case, you move the statements that set up the initial session state into the initialization query used by a proxy. This property is a string containing one or more SQL statements, separated by semicolons. For example, you can define an initialization query for a proxy that sets certain configuration parameters. Then, RDS Proxy applies those settings whenever it sets up a new connection for that proxy. You can remove the corresponding `SET` statements from your application code, so that they don't interfere with transaction-level multiplexing. For metrics about how often pinning occurs for a proxy, see [Monitoring RDS Proxy metrics with Amazon CloudWatchMonitoring RDS Proxy with CloudWatch](rds-proxy.monitoring.md). ## Conditions that cause pinning for all engine families The proxy pins the session to the current connection in the following situations where multiplexing might cause unexpected behavior: + Any statement with a text size greater than 16 KB causes the proxy to pin the session. ## Conditions that cause pinning for Aurora MySQL For MySQL, the following interactions also cause pinning: + Explicit table lock statements `LOCK TABLE`, `LOCK TABLES`, or `FLUSH TABLES WITH READ LOCK` cause the proxy to pin the session. + Creating named locks by using `GET_LOCK` causes the proxy to pin the session. + Setting a user or system variable (with some exceptions) pins the session to the proxy. If this significantly limits connection reuse, you can configure `SET` operations to avoid pinning. To do this, adjust the session pinning filters property. For more information, see [Creating a proxy for Amazon Aurora](rds-proxy-creating.md) and [Modifying an RDS Proxy](rds-proxy-modifying-proxy.md). + Creating a temporary table causes the proxy to pin the session. That way, the contents of the temporary table are preserved throughout the session regardless of transaction boundaries. + Calling the `ROW_COUNT` and `FOUND_ROWS` functions sometimes causes pinning. The exact circumstances where these functions cause pinning might differ among Aurora MySQL versions that are compatible with MySQL 5.7. + Prepared statements cause the proxy to pin the session. This rule applies whether the prepared statement uses SQL text or the binary protocol. + RDS Proxy does not pin connections when you use SET LOCAL. + Calling stored procedures and stored functions doesn't cause pinning. RDS Proxy doesn't detect any session state changes resulting from such calls. Make sure that your application doesn't change session state inside stored routines if you rely on that session state to persist across transactions. For example, RDS Proxy isn't currently compatible with a stored procedure that creates a temporary table that persists across all transactions. + Queries with executable comments for MySQL (syntax /\$1\$1 ... \$1/) or MariaDB (syntax /\$1M\$1 ... \$1/) cause pinning. RDS Proxy cannot parse SQL embedded in these comments to track session state changes. If you have expert knowledge about your application behavior, you can skip the pinning behavior for certain application statements. To do so, choose the **Session pinning filters** option when creating the proxy. Currently, you can opt out of session pinning for setting session variables and configuration settings. ## Conditions that cause pinning for Aurora PostgreSQL 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`. **Note** RDS Proxy does not pin on transaction level advisory locks, specifically `pg_advisory_xact_lock`, `pg_advisory_xact_lock_shared`, `pg_try_advisory_xact_lock`, and `pg_try_advisory_xact_lock_shared`. + Setting a parameter, or resetting a parameter to its default. Specifically, using `SET` and `set_config` commands to assign default values to session variables. + Calling stored procedures and stored functions doesn't cause pinning. RDS Proxy doesn't detect any session state changes resulting from such calls. Make sure that your application doesn't change session state inside stored routines if you rely on that session state to persist across transactions. For example, RDS Proxy isn't currently compatible with a stored procedure that creates a temporary table that persists across all transactions. + Discarding session state. If you use connection pooling libraries with `DISCARD ALL` query configured as a reset query, RDS Proxy pins your client connection on release. This reduces the proxy's multiplexing efficiency and might lead to unexpected results because the `DISCARD ALL` command can interfere with session state management. # Deleting an RDS Proxy Deleting an RDS Proxy You can delete a proxy when you no longer need it. Or, you might delete a proxy if you take the DB instance or cluster associated with it out of service. ## AWS Management Console **To delete a proxy** 1. Sign in to the AWS Management Console and open the Amazon RDS console at [https://console.aws.amazon.com/rds/](https://console.aws.amazon.com/rds/). 1. In the navigation pane, choose **Proxies**. 1. Choose the proxy to delete from the list. 1. Choose **Delete Proxy**. ## AWS CLI To delete a DB proxy, use the AWS CLI command [delete-db-proxy](https://docs.aws.amazon.com/cli/latest/reference/rds/delete-db-proxy.html). To remove related associations, also use the [deregister-db-proxy-targets](https://docs.aws.amazon.com/cli/latest/reference/rds/deregister-db-proxy-targets.html) command. ``` aws rds delete-db-proxy --name proxy_name ``` ``` aws rds deregister-db-proxy-targets --db-proxy-name proxy_name [--target-group-name target_group_name] [--target-ids comma_separated_list] # or [--db-instance-identifiers instance_id] # or [--db-cluster-identifiers cluster_id] ``` ## RDS API To delete a DB proxy, call the Amazon RDS API function [DeleteDBProxy](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DeleteDBProxy.html). To delete related items and associations, you also call the functions [DeleteDBProxyTargetGroup](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DeleteDBProxyTargetGroup.html) and [DeregisterDBProxyTargets](https://docs.aws.amazon.com/AmazonRDS/latest/APIReference/API_DeregisterDBProxyTargets.html).