simple-read-write-splitting-plugin.md

Simple Read/Write Splitting Plugin for the AWS Advanced ODBC Wrapper

The Simple Read/Write Splitting Plugin adds functionality to switch between endpoints via the connection's read-only attribute. Based on the values provided in the connection parameters, upon setting the connection to read-only, the plugin will connect to the specified endpoint for read operations. When read-only is set to false, the plugin will connect to the specified endpoint for write operations. Future changes to the read-only attribute will switch between the established writer and reader connections accordingly.

The plugin will use the current connection, which may be the writer or initial connection, as a fallback if the reader connection is unable to be established, or if connection verification is enabled and the connection is not to a reader host.

The plugin does not rely on cluster topology. It relies purely on the provided endpoints and their DNS resolution.

Plugin Availability

The Simple Read/Write Splitting Plugin is available for use with Aurora clusters, RDS instances, and other database endpoints where you want to direct read and write traffic to different endpoints.

Enabling the Simple Read/Write Splitting Plugin

The Simple Read/Write Splitting Plugin is not enabled by default. To enable the plugin, set the ENABLE_SRW_SPLIT connection parameter to 1 and provide the SRW_WRITE_ENDPOINT and SRW_READ_ENDPOINT parameters.

Configuration Parameters

Field	Connection Option Key	Value	Default Value	Sample Value
Enable Simple Read/Write Splitting	ENABLE_SRW_SPLIT	Set to 1 to enable the Simple Read/Write Splitting Plugin.	0	1
Write Endpoint	SRW_WRITE_ENDPOINT	The endpoint to connect to for write operations (when read-only is set to false).	nil	<cluster-name>.cluster-<XYZ>.<region>.rds.amazonaws.com
Read Endpoint	SRW_READ_ENDPOINT	The endpoint to connect to for read operations (when read-only is set to true).	nil	<cluster-name>.cluster-ro-<XYZ>.<region>.rds.amazonaws.com
Verify New SRW Connections	SRW_VERIFY_CONNS	Enables writer/reader verification for new connections made by the Simple Read/Write Splitting Plugin. When enabled, a query is sent to new connections to verify their role. If the connection cannot be verified as having the correct role, the plugin will retry the connection up to the time limit of SRW_CONN_TIMEOUT_MS.	1	0
Verify Initial Connection Type	SRW_VERIFY_INITIAL_CONN_TYPE	If SRW_VERIFY_CONNS is set to 1, this parameter will verify the initial opened connection to be either a writer or a reader. Set to WRITER or READER accordingly. When connecting with an RDS writer cluster or reader cluster endpoint, the plugin will retry the initial connection up to SRW_CONN_TIMEOUT_MS until it has verified the intended role of the endpoint.	nil	WRITER, READER
Connection Retry Timeout	SRW_CONN_TIMEOUT_MS	If SRW_VERIFY_CONNS is set to 1, this parameter sets the maximum allowed time in milliseconds for retrying connection attempts when verifying the connection role.	60000	30000
Connection Retry Interval	SRW_CONN_INTERVAL_MS	If SRW_VERIFY_CONNS is set to 1, this parameter sets the time delay in milliseconds between each retry of opening a connection when verifying the connection role.	1000	2000
Cached Reader Keep-Alive Timeout	CACHED_READER_KEEP_ALIVE_TIMEOUT_MS	Timeout value in milliseconds for the cached reader connection. Once the reader has expired, the next switch to read-only mode will create a new reader connection using the SRW_READ_ENDPOINT. The default value of 0 means the wrapper will keep reusing the same cached reader connection for the entire lifetime of the connection.	0	600000

How the Simple Read/Write Splitting Plugin Verifies Connections

The property SRW_VERIFY_CONNS is enabled by default (1). This means that when new connections are made with the Simple Read/Write Splitting Plugin, a query is sent to the new connection to verify its role. If the connection cannot be verified as having the correct role — that is, a write connection is not connected to a writer, or a read connection is not connected to a reader — the plugin will retry the connection up to the time limit of SRW_CONN_TIMEOUT_MS.

The values of SRW_CONN_TIMEOUT_MS and SRW_CONN_INTERVAL_MS control the timing and aggressiveness of the plugin's retries.

Additionally, to consistently ensure the role of connections made with the plugin, the plugin also provides role verification for the initial connection. When connecting with an RDS writer cluster or reader cluster endpoint, the plugin will retry the initial connection up to SRW_CONN_TIMEOUT_MS until it has verified the intended role of the endpoint. If it is unable to return a verified initial connection, it will log a message and continue with the normal workflow of the other plugins. When connecting with custom endpoints and other non-standard URLs, role verification on the initial connection can also be triggered by providing the expected role through the SRW_VERIFY_INITIAL_CONN_TYPE parameter. Set this to WRITER or READER accordingly.

The AWS Advanced ODBC Wrapper supports verifying the role of connections to PostgreSQL and MySQL databases through the following queries:

DB Type	Query
PostgreSQL	SELECT pg_catalog.pg_is_in_recovery()
Aurora MySQL	SELECT @@innodb_read_only

Role verification can be disabled by setting the SRW_VERIFY_CONNS parameter to 0. The Simple Read/Write Splitting Plugin will continue to function, relying purely on the endpoints from the SRW_WRITE_ENDPOINT and SRW_READ_ENDPOINT parameters.

Using the Simple Read/Write Splitting Plugin with RDS Proxy

RDS Proxy provides connection pooling and management that improves application scalability by reducing database connection overhead and enabling concurrent connections through connection multiplexing. By providing the read/write endpoint and a read-only endpoint to the Simple Read/Write Splitting Plugin, the AWS Advanced ODBC Wrapper will connect using these endpoints any time the read-only attribute is changed.

To take full advantage of the benefits of RDS Proxy, it is recommended to only connect through RDS Proxy endpoints.

Limitations

Failover

Immediately following a failover event, due to DNS caching, an RDS cluster endpoint may connect to the previous writer, and the read-only endpoint may connect to the new writer instance.

To avoid stale DNS connections, enable SRW_VERIFY_CONNS, as this will retry the connection until the role has been verified. Service for Aurora clusters is typically restored in less than 60 seconds, and often less than 30 seconds. RDS Proxy endpoints to Aurora databases can update in as little as 3 seconds. Depending on your configuration and cluster availability, SRW_CONN_TIMEOUT_MS and SRW_CONN_INTERVAL_MS may be set to customize the timing of the retries.

Following failover, endpoints that point to specific instances will be impacted if their target instance was demoted to a reader or promoted to a writer. The Simple Read/Write Splitting Plugin always connects to the endpoint provided in the initial connection properties when the read-only attribute is changed. We suggest using endpoints that return connections with a specific role such as cluster or read-only endpoints, or using the Read/Write Splitting Plugin to connect to instances based on the cluster's current topology.