Spring Boot App using Spring Cloud AWS RDS Aurora - how to distribute read queries

Question

I've got a Spring Boot app that's using an AWS RDS DB.

I'm trying to get a RDS Aurora / Postgres DB working with read replicas. I've got the app running, however I cannot see any traffic going to the reader nodes.

Set up is Spring boot (2.2.7), spring cloud aws 2.2.4-RELEASE, spring data (JpaRepository).

RDS Aurora cluster with 2 nodes, 1 reader and 1 writer, which looks like this:

a controller GET method annotated with @Transactional(readOnly = true)

    @RequestMapping(
            value = "/counters",
            produces = {"application/json"},
            method = RequestMethod.GET
    )
    @ResponseBody
    @Transactional(readOnly = true)
    public ResponseEntity<List<Counter>> getCounters()  {
        return ResponseEntity.of(Optional.of(this.counterDAO.findAll()));
    }

finally spring cloud aws configuration

cloud:
  aws:
    region:
      static: ap-southeast-2
    credentials:
      accessKey: ${ACCESS_KEY}
      secretKey: ${SECRET_KEY}
    rds:
      rds-db-cluster-instance-1:
        password: ${POSTGRES_PASSWORD}
        username: postgres
        readReplicaSupport: "true"
        databaseName: postgres
    stack:
      auto: false

At this point I can read and write from the DB, however I do not see any evidence that it is using the reader nodes at all.

The app only starts when I use the writer instance name as the cloud.aws.rds.<instance-id>, It does not work using the cluster name (using the writer id may be the right way to configure it, the docs are not really clear) and if I use the read only instance id, it fails with methods that update the DB.

Monitoring on the AWS console shows connections only on the Writer node (I can get this to spike to 10 - 15 connections with 2 threads calling GET and POST methods), however the Reader node is always 0.

Secondly I've set breakpoints with the debugger on the ReadOnlyRoutingDataSource class, which are never hit, so I am pretty sure it's not using the read only functionality.

I fairly certain it's due to the instance id I am using and perhaps something to do with the underlying RDS config, but I can't for the life of me work it out.

Answer 1

You need to configure explicitly configure 2 Datasources. In AWS read replica will have separate url with -ro added as visible in your screenshot.

Information on how to configure 2 Datasources is available in spring boot documentation section 8.2. Configure Two DataSources

https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-access.configure-two-datasources

Once you have 2 Datasources configured you'll need to have have to create separate EntityManager per RO and RW repositories as described in section 8.10. Using Multiple EntityManagerFactories

https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.data-access.use-multiple-entity-managers

Then in services you'll need to provide fields to include RO and/or RW repository to make queries.