Connections are not balanced across readers mapped to a RDS Custom endpoint

[KamleshAtTray]

We added 3 readers to our RDS cluster for reporting queries, created a custom endpoint for them, and configured this endpoint in our datasource with:

customEndpoint plugin along with the loadBalancing, hostMonitoring, and failover plugins with relevant configs

Current Issue:
Connections aren't load balancing properly - all connections go to one reader instance, even under concurrent API load.

Key Constraints:

We cannot use the writer endpoint + setReadOnly approach

We maintain two separate reader groups (transactional reads vs reporting reads)

Questions:

1. Why isn't the driver distributing connections across readers via the custom endpoint?
2. Does load balancing only work when -

• Connecting to the writer endpoint AND
• Using setReadOnly(true) for selects?
• ReaderWriteSplit plugin os enabled

3. Must we use ReaderWriteSplit plugin when routing all queries through the writer endpoint?
4. Is there any way to configure uniform load distribution across readers when using a custom endpoint?
5. Would AWS RDS Proxy provide better load balancing for readers under a custom endpoint?

The datasource configurations from the application -

dataSources:
    reports:
        dbCreate: none
        pooled: true
        #driverClassName: "com.mysql.jdbc.Driver"
        driverClassName: "software.amazon.jdbc.Driver"
        url: "jdbc:aws-wrapper:mysql://reporting-cluster.cluster-custom-ciamq9aqkvio.us-west-1.rds.amazonaws.com:3306/db?noAccessToProcedureBodies=true"
        username: "app_db_user"
        password: "xxxxxxxxxxxxxxxxxxxxxxx"
        properties:            
            # AWS Driver Configuration : Custom Endpoint with weighted round-robin load balancing
            # ==========================================================================
            wrapperPlugins: "customEndpoint,loadBalancing,hostMonitoring,failover"
            customEndpointRegion: "us-west-2"
            waitForCustomEndpointInfo: true
            customEndpointInfoRefreshRateMs: 5000
            failoverMode: "strict-reader"
 
            # Weighted Round-Robin
            loadBalanceStrategy: "roundRobin"
            roundRobinHostWeightPairs: "rep-1:1,rep-2:1,rep-3:1"
            roundRobinDefaultWeight: "1"
            
            # Health checks
            hostMonitoringFrequencySec: "10"        # Check reader health every 10s
            failureDetectionTimeMs: "30000"         # Mark host as down after 30s of failures
            failureDetectionIntervalMs: "5000"      # Check every 5s during failures
            
            # Connection pool properties
            # ============================================================================
            initialSize: 5
            maxActive: 100
            maxIdle: 10
            minIdle: 2
            maxWait: 10000
            minEvictableIdleTimeMillis: 120000
            timeBetweenEvictionRunsMillis: 10000
            testOnBorrow: true
            testWhileIdle: true
            testOnReturn: false
            #validationQuery: "SELECT 1"
            validationQuery: "select sleep(5),1"
            validationInterval: 15000
            abandonWhenPercentageFull: 10
            removeAbandonedTimeout: 60
            removeAbandoned: true

[aaron-congo]

Hi @KamleshAtTray, thank you for opening this discussion. The driver does not currently support the behavior you are describing according to your constraints. Using the read/write plugin, connecting to the writer, and calling setReadOnly(true) should give you the load balancing you are looking for, but you mentioned you cannot use this approach; can you share more details about why this isn't feasible for you?

Note 1: I see you are passing loadBalancing in your wrapperPlugins parameter, please note that this is not a valid wrapper plugin. The read/write plugin provides some load balancing functionality but we do not have a "loadBalancing" plugin.

Note 2: The custom endpoint plugin does not provide load balancing functionality on its own. The primary purpose of the custom endpoint plugin is to ensure that hosts selected by the failover and/or read/write plugin are part of the custom endpoint in use.

1. Why isn't the driver distributing connections across readers via the custom endpoint?

• see my notes above, there is no "loadBalancing" plugin, and the custom endpoint plugin does not provide load balancing functionality on its own

2. Does load balancing only work when -
   Connecting to the writer endpoint AND
   Using setReadOnly(true) for selects?
   ReaderWriteSplit plugin os enabled

• the other load-balancing scenarios we provide are:
  • reader load balancing when using the "initialConnection" plugin and connecting to a reader cluster URL. I don't think this is relevant for you since you are using a custom endpoint URL
  • load balancing when connecting to limitless databases, again I don't think this is relevant for you

3. Must we use ReaderWriteSplit plugin when routing all queries through the writer endpoint?

• with the current driver, the only way to get the behavior you describe is to use the custom endpoint and read write splitting plugin, connect to the writer, and then call setReadOnly(true) to connect to a load-balanced reader.

4. Is there any way to configure uniform load distribution across readers when using a custom endpoint?

• see my answer to question 3

5. Would AWS RDS Proxy provide better load balancing for readers under a custom endpoint?

• I am not very familiar with RDS proxy but if I'm not mistaken I do not think RDS proxy provides load balancing restricted to a subset of available readers like you are describing