운영환경에 이런저런 개발이 계속 진행되고 데이터도 쌓이고 하다보니 점점 무거워지고 있는 와중에 dba쪽에서 slow query 관련하여 문의가 들어왔고 확인하다보니 다른 이슈가 확인되었다. readOnly 트랜잭션으로 묶어놓은 쿼리인데 reader쪽에서 실행되어야 할 쿼리가 writer쪽에서도 실행이 되고 있는 현상이 지속되고 있었다.

현재 애플리케이션은 SpringBoot 2.x + MariaDB connector 2.7.x + HikariPool + MyBatis 사용하도록 구성된 상태.

compile 'org.mybatis.spring.boot:mybatis-spring-boot-starter:2.1.1'
compile 'org.springframework.boot:spring-boot-starter-jdbc'
compile 'org.mariadb.jdbc:mariadb-java-client:2.7.7'
compile 'org.bgee.log4jdbc-log4j2:log4jdbc-log4j2-jdbc4.1:1.16'

db 클러스터는 writer 1개와 reader 1개로 구성되어있다. 클러스터 주소는 writer, reader 용으로 각 1개씩 두 개가 있다.

• my-cluster.cluster-xxx.ap-northeast-2.rds.amazonaws.com 
• my-cluster.cluster-ro-xxx.ap-northeast-2.rds.amazonaws.com

그리고 아래와 같이 writer용과 reader용 주소가 별도로 있다.

spring:
  datasource:
    url: jdbc:mariadb:aurora://my-cluster.cluster-xxx.ap-northeast-2.rds.amazonaws.com

원인추적을 해보기 위해 일단 log4jdbc의 audit 설정을 좀 변경했다. @Transactional(readOnly=true) 설정이 제대로 안먹히는건가?? 싶어서 말이다. 

logging:
  level:
    jdbc:
      audit: debug

확인했을 때는 아래처럼 readOnly 설정은 정상적으로 되고 있었다.

DEBUG jdbc.audit : 1. Connection.setReadOnly(true) returned   com.zaxxer.hikari.pool.ProxyConnection.setReadOnly(ProxyConnection.java:423)
DEBUG jdbc.audit : 1. Connection.getAutoCommit() returned true  com.zaxxer.hikari.pool.HikariProxyConnection.getAutoCommit(HikariProxyConnection.java:-1)
DEBUG jdbc.audit : 1. Connection.setAutoCommit(false) returned   com.zaxxer.hikari.pool.ProxyConnection.setAutoCommit(ProxyConnection.java:414)
DEBUG jdbc.audit : 1. Connection.getAutoCommit() returned false

그럼 왜 이럴까? 다음으로는 jdbc url에 아래와 같이 log 파라미터를 추가하고 로거를 추가하여 mariaDB 쪽 로그를 확인해보았다.

spring:
  datasource:
    url: jdbc:mariadb:aurora://my-cluster.cluster-xxx.ap-northeast-2.rds.amazonaws.com?log=true

<logger name="org.mariadb.jdbc" level="info">
    <appender-ref ref="stdout"/>
</logger>

DEBUG 88239 --- [nio-8080-exec-1] org.mariadb.jdbc.MariaDbConnection       : conn=817636(M) - set read-only to value true
DEBUG 88239 --- [nio-8080-exec-1] o.m.j.i.protocol.AbstractQueryProtocol   : System variable change :  autocommit = OFF
{{쿼리 실행}}
DEBUG 88239 --- [nio-8080-exec-1] o.m.j.i.protocol.AbstractQueryProtocol   : System variable change :  autocommit = ON
DEBUG 88239 --- [nio-8080-exec-1] org.mariadb.jdbc.MariaDbConnection       : conn=53856(S) - set read-only to value false

로그상으로는 도대체 실제로 어느 클러스터 주소에서 쿼리가 실행되는지 알 수가 없었다.

그래서 운영환경에서 사용하고있는 mariaDB 커넥터와 동일한 버전의 소스코드에 로그를 여기저기 추가하면서 추적을 시작했고,

다음과 같은 현상을 확인했다.

1. writer 클러스터 주소를 기준으로 신규 커넥션을 생성된다. 
2. readOnly 쿼리가 writer쪽에서 실행된 뒤에 failover 프로세스가 실행되면서 mariaDB FailoverLoop에서 reader쪽 커넥션이 생성되어 pool에 들어간다.
3. 이후의 readOnly 쿼리들은 정상적으로 reader쪽에서 실행된다.

이제 궁금한 점이 생겼다.

1. 커넥션의 생성주기에 따라 신규 커넥션을 생성하게되면 reader쪽 커넥션은 사라지는지?
2. 신규 커넥션 생성시 reader host가 포함되도록 할 수 있을지..? (이렇게 할 수 있으면 1번과 같은 현상은 없어질테니)
3. 그렇게하면 readOnly 쿼리가 항상 reader로 들어가는지?

1번 케이스의 경우 maxLifeTime 설정값을 30초로 설정해서 테스트해보았고, reader쪽 커넥션은 사라지게된다. readOnly 쿼리가 reader에서 실행되려면 위에 말한 failover 과정이 다시 필요하다. 즉, maxLifeTime이 지나 커넥션을 새로 생성하게 될 경우 failover 프로세스가 실행되어야 reader를 사용할 수 있게 된다. (아래 2번 케이스에 대한 내용까지 확인하면 알겠지만 어디까지나 url 설정에 메인 클러스터 주소만 넣었을 경우이다.)

2번 케이스의 경우 jdbc url 설정에 reader 클러스터의 주소를 추가해주면 된다. 기존 설정과 변경한 설정은 다음과 같다.

spring:
  datasource:
    url: jdbc:mariadb:aurora://my-cluster.cluster-xxx.ap-northeast-2.rds.amazonaws.com

spring:
  datasource:
    url: jdbc:mariadb:aurora://my-cluster.cluster-xxx.ap-northeast-2.rds.amazonaws.com,my-cluster.cluster-ro-xxx.ap-northeast-2.rds.amazonaws.com

이렇게 reader cluster의 주소가 추가된 jdbc url을 사용할 경우 항상 reader 클러스터쪽으로 readOnly 쿼리가 실행되는 것을 확인할 수 있었다. 혹시라도 url 뒤쪽에 파라미터값을 사용한다면 파라미터값들은 제일 마지막에 한번만 붙여주면 된다. 예를들어 파라미터값을 아래처럼 넣게되면 mariaDB 커넥터에서 url 파싱할 때 뒤 쪽 url이 사라지게 되니 주의!!! 즉, 모든 클러스터에 동일한 파라미터값을 사용해야 한다는 것 !!

url: my-cluster.cluster-xxx.ap-northeast-2.rds.amazonaws.com?log=true,my-cluster.cluster-ro-xxx.ap-northeast-2.rds.amazonaws.com?log=true

그냥 얼핏 듣기로는 메인클러스터 주소만 넣어주소 jdbc url에 aurora 옵션을 넣어줄 경우 readOnly 트랜잭션은 자동으로 reader 쪽으로 들어간다고 들었었으나 그렇지 않다는 것을 확인한 디버깅이었다.

결국 결론은 이렇다. 클러스터 주소가 writer, reader가 별도로 있을 경우 (아마 AWS aurora를 클러스터링해서 사용할 경우 다 이럴 것이다) 개발자 입장에서는 writer, reader 두 클러스터 주소를 jdbc url에 넣어주어야 readOnly 트랜잭션이 항상 정상적으로 reader 클러스터에서 실행되게 된다.

참고로...mariaDB connector 3.x 버전에서는 aurora 옵션을 지원하지 않는단다...