Read/Write Splitting Plugin + RDS Proxy

[fkjaekel]

I have configured the RDS Proxy with a read/write endpoint and a read only endpoint. I'd like to know if the Read/Write Splitting Plugin is compatible with this setup.

[karenc-bq]

Hi @fkjaekel, we have not fully tested the Read/Write Splitting plugin with RDS Proxy and we do not recommend using it in production.

RDS Proxy transparently re-routes requests to a single database instance, and it decides which database instance to use based on its criteria. Since RDS Proxy is handling the switching between different instances, it reduces the impact of using the Read/Write Splitting plugin and its several host selection strategies.

Since you have a read/write endpoint and a read-only endpoint similar to the reader and writer cluster endpoints, using just the Read/Write Splitting plugin may work for basic workflows. Using other plugins on top of this set up may cause unpredictable side effects, therefore we do not recommend using it in production.

[vnephologist]

Based on my understanding, I don’t think this answer is quite right. Although RDS Proxy can distribute across the readers effectively, it can’t perform the actual read/write split, correct? Ideally, we want to utilize the r/w splitting plugin for this capability. Seems that if we could have the reader selection strategy target the proxy’s single read endpoint, this would work, no?

[karenc-bq]

Hi @vnephologist, just want to make sure we understood you correctly, do you just want to use the Read/Write Splitting plugin to switch between the writer and the reader, and have RDS Proxy handle all the reader load distribution?

The wrapper constructs the cluster topology based on the wrapperDialect, if you want to override how cluster topology is fetched you would need to specify a non cluster-aware wrapper dialect, such as pg or mysql. This idea would work if you are only using the Read/Write Splitting plugin.

To do so you would need to:

• set the singleWriterConnectionString variable
• use a multi-instances JDBC url
• set the wrapperDialect to pg or mysql, you will not be able to use Aurora dialects

Here is a simple example

  public static void main(String[] args) throws SQLException {
    final Properties properties = new Properties();
    properties.setProperty("user", "user");
    properties.setProperty("password", "pass");

    properties.setProperty("wrapperPlugins", "readWriteSplitting");
    properties.setProperty("wrapperDialect", "pg");
    properties.setProperty("singleWriterConnectionString", "true");

    try (Connection conn = DriverManager.getConnection(
        "jdbc:aws-wrapper:postgresql://writer.XYZ.us-east-2.rds.amazonaws.com,reader.proxy-XYZ.us-east-2.rds.amazonaws.com/db",
        properties);
         Statement stmt = conn.createStatement();
         ResultSet rs = stmt.executeQuery("SELECT aurora_db_instance_identifier()")) {
      rs.next();
      System.out.println(rs.getString(1));
    }
  }

Please note that you will not be able to take advantage of he failover and EFM features, and will not be able to utilize any other features requiring cluster topology.

[vnephologist]

Thanks, @karenc-bq. This is exactly right. We've seen a great deal of success using the wrapper F0 profile to split across our reader instances after adding @transactional(readOnly = True) annotations similar to SpringHibernateBalancedReaderOneDataSourceExample, but we're seeing a less than ideal amount of CPU utilization from idle connections form our ~100 micro services. We've tried tuning hikari.maximum-pool-size down with little success. We're hoping RDS Proxy may help. Do you see any issues using the above config with Spring?