8

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

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
dharam
  • 7,882
  • 15
  • 65
  • 93
  • 1
    "How do I direct my database calls to different replicas? What is the basis of this decision?" If you configure Spring cloud any data access method @Transaction(readOnly=true) will be directed to the read replicas. The basis is : Ifyou are reading only read which you can live will bit state data (eg: not used for read&update kind of senarios, you can live with it. – srikanth Nutigattu Sep 23 '21 at 16:34

1 Answers1

3

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

DharmanBot
  • 1,066
  • 2
  • 6
  • 10
Niraj Sonawane
  • 10,225
  • 10
  • 75
  • 104
  • does spring always pick a read replica that is in the same availability zone as the spring boot application? I'm asking because you mention spring could use _any_ of the available replicas. I have a read replica in us-east-a1 and in eu-central-a1, will the spring boot application pick the read replica in eu-central-a1 when the ec2 server that has the spring application is also in that AZ zone? Or is there a chance that it might pick the one in us-east-a1 as well? – Maurice Jul 28 '22 at 22:23
  • I have followed same link @Niraj https://nirajsonawane.github.io/2022/04/24/Spring-Boot-AWS-RDS-Read-Replica-Part-2/ but I am getting sometime profile not set errors, and token url connection issue, Reported issue under the same above your git link. https://github.com/nirajsonawane/spring-boot-aws-postgresql-read-replica/issues/2 – Dnyaneshwar Jadhav Sep 21 '22 at 04:58