Spring Boot and AWS RDS Read Replica

Question

Here I am trying to achieve the below in AWS RDS. I have a MySQL database instance running. I am thinking of creating a read replica so that I will have some extra load sharing capabilities.

I have a Spring Boot application running on EC2. Currently the way I connect to the database is by adding the below properties in the application.yml:

datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:mysql://DB_HOSTNAME:3306/DB_DATABASE?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC
    username: DB_USERNAME
    password: DB_PASSWORD

My question is:

• If I create a read replica, do I need to write some special code to connect to it?
• Do I need multiple connection pools one for each instance of the database?
• How is this scalable from code perspective, if I have 5 read replicas, how do I manage this in code?
• How do I direct my database calls to different replicas? What is the basis of this decision?

If there is any link/video/documentation you can point me to. Spring boot is not a necessity, I need to understand what is a good way to utilize my read replicas from a Java application.

Thanks

Answer 1

Spring Cloud via "spring-cloud-aws-jdbc" provides the support for directly configuring read replica for RDS instance. More info is provided in the documentation https://cloud.spring.io/spring-cloud-aws/2.1.x/single/spring-cloud-aws.html#_data_access_with_jdbc

Answer 2

If we use spring-cloud-aws-jdbc Then

Point 1 How to connect to Main and read replica instances

cloud.aws.rds.<DB-Instance-ID>.username=admin1
cloud.aws.rds.<DB-Instance-ID>.password=Admin123
cloud.aws.rds.<DB-Instance-ID>.databaseName=employee
cloud.aws.rds.employee-db.readReplicaSupport=true

Point 2

No Need to define a separate connection pool

Point 3

Spring Cloud AWS will search for any read-replica that is created for the master database and route the read-only transactions to one of the read-replicas that are available.

Point 4

You can redirect read transactions by

@Transactional(readOnly = true)

You can find a working example and details explanation here