테이블 분리 작업에서 발생한 문제들을 해결하며

하나의 테이블로 관리되어지다가 여러가지 문제가 생겨 테이블을 분리하는 작업을 진행하게 되면서 겪은 문제들과 해결책을 정리해보려고 한다.

상황

• 기존 상태: 고객 테이블에는 일반 고객과 제휴 고객의 정보가 함께 저장되고 있었음.
• 해결해야 할 문제
  • 제휴 고객과 일반 고객을 완전히 분리하여 따로 저장해야 함.
  • 기존 고객 테이블을 사용하고 있는 쿼리(join 포함)가 너무 많았기 때문에 최대한 코드를 적게 수정해야한다.
  • 제휴 고객과 일반 고객의 member Id는 그대로 유지되어야 함. 이유는 여러 테이블에서 해당 member Id를 참조하고 있기 때문에 기존 참조 관계를 유지해야 함.

따라서, 기존 데이터의 구조 변경을 통해 제휴 고객과 일반 고객을 분리하지만, 참조 무결성을 유지하는 방향으로 작업이 필요하다.

첫 번째 접근

최소한의 코드 수정으로 테이블 변경 : View

기존 고객 테이블을 참조하는 쿼리를 모두 수정하여 분리된 고객 테이블과 제휴 고객 테이블을 참조하도록 변경하는 작업은 시간과 비용이 너무 많이 들기 때문에, 두 테이블을 하나로 묶는 View를 생성하는 방식으로 해결하기로 하였다. 이 방법을 사용하면, 기존에 고객 테이블을 참조하던 쿼리에서 단순히 Member 대신 MemberView를 사용하기만 하면 된다. 이 View는 UNION을 사용하여 두 테이블을 합쳐 하나의 통합된 뷰로 만들었으며, 이를 통해 기존 쿼리 수정 작업을 최소화할 수 있었다.

CREATE VIEW member_view AS
 SELECT m.uid AS member_id,
     ...
     m.created_at,
     m.updated_at
 FROM member m
 UNION ALL
 SELECT om.member_id AS member_id,
     ...
     om.created_at,
     om.updated_at
 FROM open_member om

참조 무결성을 유지하기 위한 방법 : custom_sequences 테이블 도입

기존의 memberId를 유지해야 하므로, 앞으로 고객과 제휴 고객의 memberId가 절대 중복되지 않도록 관리할 필요가 있었다. 이를 위해 custom_sequences 테이블을 도입하여 ID 생성 과정에서 발생할 수 있는 충돌 문제를 데이터베이스가 처리하도록 설계했다.
uid 값을 순차적으로 증가시키는 과정에서 동시성 문제가 발생할 가능성도 있었지만, MySQL InnoDB의 레코드 락(Record Lock) 기능을 통해 이를 방지할 수 있었다. InnoDB는 UPDATE, DELETE, INSERT 같은 데이터 변경 작업 시 자동으로 레코드에 락을 걸어, 동시에 여러 트랜잭션이 같은 레코드를 수정하는 것을 방지하며 트랜잭션의 일관성을 유지해 준다.
이를 기반으로 increase_value라는 함수를 설계했다. 이 함수는 새로운 고객 또는 제휴 고객이 생성될 때 호출되어 중복되지 않는 uid를 반환하도록 되어 있으며, 이를 통해 uid의 일관성과 유일성을 보장했다.

      CREATE TABLE custom_sequences
       (
         name     VARCHAR(32) unique not null comment '시퀀스 명',
         current_value BIGINT UNSIGNED default 0 comment '현재 시퀀스 값'
       ) ENGINE = InnoDB, comment = '커스텀 시퀀스 테이블';
       
       # 2-3. custom_sequences 테이블에 seq_member_uid 추가
       INSERT INTO custom_sequences
       VALUES ('seq_member_uid', 0);
       
       # 2-4. 시퀀스 값 올리는 함수 생성
       DELIMITER $$
       CREATE FUNCTION `increase_value`(input_name VARCHAR(32))
         RETURNS BIGINT UNSIGNED
         MODIFIES SQL DATA
         DETERMINISTIC
         comment '해당하는 시퀀스의 current_value 를 1 증가시키고, 증가된 값을 반환한다.'
       BEGIN
         DECLARE ret BIGINT UNSIGNED;
         UPDATE custom_sequences SET current_value = current_value + 1 WHERE name = input_name;
         SELECT current_value INTO ret FROM custom_sequences WHERE name = input_name LIMIT 1;
         RETURN ret;
       END $$
       DELIMITER ;

결과

하지만, UNION을 사용한 View는 인덱스를 활용하지 못해 성능이 좋지 않았고, 결국 하나의 API에서 무한 로딩이 발생했다. 이에 따라 기존 작업을 롤백하게 되었다.

이후 다시 실행 계획을 살펴보니, 테이블 풀 스캔이 발생하고 있음을 확인했다. 또한, 부하 테스트 없이 작업을 진행했던 점을 반성하며, 부하 테스트의 중요성을 깨닫게 되었다.

       explain
       select * from member_view where member_id = '68';

두 번째 접근

처음에는 UNION ALL을 사용하는 VIEW를 통해 고객 데이터를 조회했는데, 일부 API의 응답 시간이 dev 환경에서는 10초 이상 걸렸고, prod 환경에서는 약 6초가 소요되었다.

이러한 성능 문제를 확인한 뒤 부하 테스트를 진행했고, dev 환경에서 504 Gateway Timeout이 5건 발생하는 것을 발견했다. 실행계획을 살펴보니 UNION ALL 방식은 가장 큰 테이블인 member에서 Full Table Scan이 발생했고, 이는 인덱스를 활용하지 못하는 구조였다고 판단했다.

이를 해결하기 위해 View를 인덱스를 활용할 수 있는 방식으로 변경하기로 결정했다.

인덱스를 타기 위한 View 설계

UNION ALL 대신 JOIN을 활용하면 View에서도 인덱스를 탈 수 있다는 점에 착안했다. 이를 위해 member와 open_member가 공통된 식별자로 조인 가능해야 했고, 이를 위해 고객과 제휴 고객의 memberId를 하나로 묶는 member_combined_ids 테이블을 생성했다.

아래는 그 구조를 기반으로 구성한 View이다.

         CREATE VIEW member_view AS
        SELECT
               m.created_at as m_created_at,
               m.updated_at as m_updated_at,
               m.uid AS m_member_id,
               ...
               om.member_id AS ref_member_id,
               ci.combined_id as combined_id,
               ...
               om.uid       AS open_member_uid,
               om.created_at AS om_created_at,
               om.updated_at AS om_updated_at
        FROM combined_ids as ci
            left join  open_member as om on ci.combined_id = om.member_id
             left join member as m on ci.combined_id = m.uid

따라서 저 view에 대한 조회쿼리의 결과는 아래 그림과 같을 것이다.

하지만 위와 같이 null인 값들이 존재했기 때문에 이는 코드에서 많은 변경 작업이 필요했다. 해당 view를 사용하는 모든 메서드에서 Dto의 수정을 피할 수 없었다.

COALESCE로 NULL 문제 해결

기존 코드를 수정하지 않고 안정적으로 View를 사용할 수 있도록, View 내부에서 COALESCE를 활용해 NULL 값을 방지했다. 이를 통해 기존 로직에 영향을 주지 않으면서 필요한 값을 반환하도록 처리했다.

create view member_view as
select `mci`.`combined_id`                   as `member_id`,
    (COALESCE(m.user_type, om.user_type))          as user_type,
    (om.uid)                        as open_member_uid,
    (om.client_id)                     as client_id,
    (om.expire_date)                    as expire_date,
    (COALESCE(m.name_masked, om.name_masked))        as name_masked,
    ...
    (COALESCE(m.created_at, om.created_at))         as created_at,
    (COALESCE(m.updated_at, om.updated_at))         as updated_at
from member_combined_ids mci
     left join open_member om on mci.combined_id = om.member_id
     left join member m on mci.combined_id = m.uid

실행계획

실행계획을 보면 아래와 같이 인덱스를 타도록 바뀌었으며 type이 const이므로 primary 또는 유니크 인덱스를 사용하는 것을 확인할 수 있다.

       explain
       select * from member_view where member_id = '68';

새 고객이 생길 때마다 자동으로 연관된 테이블에 식별자 넣기

새로운 고객과 제휴 고객이 생기면 member_combined_ids에도 새로운 member id를 넣어줘야 하기 때문에 이를 트리거를 통해서 해결하였다.

    DELIMITER //
    CREATE TRIGGER member_insert_trigger
        AFTER INSERT
        ON member
        FOR EACH ROW
    BEGIN
        INSERT INTO member_combined_ids (combined_id)
        VALUES (NEW.uid);
    END //
    DELIMITER ;
    
    DELIMITER //
    CREATE TRIGGER open_member_insert_trigger
        AFTER INSERT
        ON open_member
        FOR EACH ROW
    BEGIN
        INSERT INTO member_combined_ids (combined_id)
        VALUES (NEW.member_id);
    END //
    DELIMITER ;

성능 개선 결과

최적화 이후, dev 환경에서 10초 이상 걸리던 쿼리는 0.7초로 단축되었고, 동일한 조건의 부하 테스트에서도 504 오류는 단 한 건도 발생하지 않았다.

이번 개선을 통해 View 설계 시에도 단순한 기능 구현을 넘어서 실행계획과 인덱스 활용 여부를 고려한 구조 설계의 중요성을 체감하게 되었다.

세 번째 접근 - 고객 테이블 분리 작업하며 만난 Lock Wait Time Out?!

겪은 문제

회원 가입하는 고객들이 늘어나면서 아래와 같은 오류가 발생하였습니다. 로그를 통해서 알 수 있는 것은 Lock wait timeout이 발생했다는 것이다

Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:916)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1061)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1009)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1320)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:994)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
        at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
        ... 189 common frames omitted

Lock Wait Timeout은 MySQL이 특정 트랜잭션이 필요한 잠금을 획득하지 못한 채 일정 시간 이상 대기한 경우 발생하는 오류이다. 이 오류는 주로 다른 트랜잭션이 잠금을 보유하고 있거나, 동시에 동일한 자원에 접근하려는 트랜잭션이 많은 경우에 발생한다.

Lock Wait Timeout이 발생하는 상황

1. 동시성 문제로 인한 경합:
   여러 트랜잭션이 동시에 동일한 행이나 자원에 접근하여 업데이트하려고 할 때, 일부 트랜잭션은 잠금을 획득하지 못해 대기 상태로 전환된다. 대기 중인 트랜잭션이 설정된 시간(innodb_lock_wait_timeout)을 초과하면 Lock Wait Timeout 오류가 발생한다.
2. 긴 트랜잭션으로 인한 잠금 유지:
   한 트랜잭션이 오랜 시간 동안 잠금을 유지하는 경우, 다른 트랜잭션이 필요한 잠금을 기다리다 Lock Wait Timeout에 도달할 수 있다. 특히 대량의 데이터 처리나 복잡한 쿼리가 포함된 트랜잭션이 이 문제를 일으킬 수 있다.
3. Deadlock (교착 상태):
   두 개 이상의 트랜잭션이 서로의 자원을 잠그고 대기하는 교착 상태에 빠지면, Lock Wait Timeout이 발생할 수 있다. MySQL은 이를 감지하면 교착 상태를 해결하기 위해 트랜잭션 중 하나를 강제로 종료한다.

원인 분석

기존에 제휴 고객과 일반 고객이 한 테이블에 저장될 때, 이들의 프라이머리 키가 여러 테이블에서 참조되고 있었기 때문에 테이블을 분리해도 참조 무결성을 유지해야 했다. 이를 위해 custom_sequences 테이블을 도입했다.

   CREATE TABLE custom_sequences
      (
        name     VARCHAR(32) unique not null comment '시퀀스 명',
        current_value BIGINT UNSIGNED default 0 comment '현재 시퀀스 값',
        comment    VARCHAR(255) comment '시퀀스 설명'
      ) ENGINE = InnoDB, comment = '커스텀 시퀀스 테이블';

      # 2-3. custom_sequences 테이블에 seq_member_uid 추가
      INSERT INTO custom_sequences
      VALUES ('seq_member_uid', 0, 'member, open_member 에서 공통으로 사용하는 uid');

      # 2-4. 시퀀스 값 올리는 함수 생성
      DELIMITER $$
      CREATE FUNCTION `increase_value`(input_name VARCHAR(32))
        RETURNS BIGINT UNSIGNED
        MODIFIES SQL DATA
        DETERMINISTIC
        comment '해당하는 시퀀스의 current_value 를 1 증가시키고, 증가된 값을 반환한다.'
      BEGIN
        DECLARE ret BIGINT UNSIGNED;
        UPDATE custom_sequences SET current_value = current_value + 1 WHERE name = input_name;
        SELECT current_value INTO ret FROM custom_sequences WHERE name = input_name LIMIT 1;
        RETURN ret;
      END $$
      DELIMITER ;

위와 같이 current_value 값을 하나 증가시키는 함수를 만들었다. 회원가입 시 이 함수를 호출해 member id를 얻고, 이를 이용해 새로운 회원을 등록하는 쿼리를 실행한다. InnoDB에서 UPDATE를 실행할 때 레코드 락이 걸리기 때문에 동시성 문제가 발생하지 않을 것이라 생각했다.

그러나 실제로는 이 레코드 락이 문제였다. 동시에 많은 회원이 가입하려고 할 때, 여러 트랜잭션이 동일한 자원에 접근해 경합이 발생했고, 이로 인해 Lock Wait Timeout이 발생했다. 공교롭게도 custom_sequences 테이블에는 항상 단 하나의 레코드만 존재하고, 이 레코드의 current_value를 증가시키는 UPDATE 문이 실행되기 때문에 모든 새로운 회원이 이 하나의 레코드를 바라보게 된다. 갑작스럽게 많은 회원이 가입하려는 상황에서 락을 얻기 위해 대기 시간이 길어지면서 문제가 발생한 것이다.

해결방법

다시 고려한 해결책은 하나의 레코드만을 업데이트 하는 것이 아니라 레코드를 쌓으면서 수를 증가시키는 것이다.
이에 따라 Auto Increment Lock과 비교하는 작업이 발생하였다.

Auto Increment Lock은 MySQL 서버의 Innodb 스토리지 엔진에서 제공하는 Lock이다. 해당 락은 테이블 전체를 잠그기 때문에 성능이 나쁘다고 생각할 수 있지만 아주 짧은 시간 동안 걸렸다가 해제된다. 그리고 MySQL 5.1이상부터는 해당 작동 방식을 변경할 수 있는 설정이 추가되었다. 바로 innodb_autoinc_lock_mode를 통해서이다.
해당 설정이 의미하는 바를 정리하면 아래와 같다.

• innodb_autoinc_lock_mode=0 : 무조건 자동 증가 락을 사용
• innodb_autoinc_lock_mode=1
  • 예측할 수 없는 대량 insert 즉 insert ..select인 경우에는 자동증가 락을 사용한다. 이렇게 대량인 경우에는 한번에 할당 받고 나서 남은 경우 폐기된다. 따라서 연속적으로 증가하는 체번을 기대할 수 없다.
  • 하지만 예측 가능한 한번의 실행에서는 자동증가 락이 아닌 이보다 가벼운 뮤텍스가 사용된다.
• innodb_autoinc_lock_mode=2 : 모든 경우에 가벼운 뮤텍스 실행, 하지만 연속된 자동 증가를 보장하지 않는다. insert ..select 중에도 다른 커넥션에서 insert가 가능해서 동시 처리 성능이 높아진다.

실제로 운영 환경에서 해당 설정 값을 확인해보니 2가 나왔고 회원 가입이 대량으로 발생되는 경우에 테이블 전체를 잠그는 자동증가 락이 아닌 가벼운 뮤텍스가 실행될 것이다!

Lock Wait Timeout 문제 해결할 때 보면 좋은 진단 도구들

MySQL 8.0 버전부터 performance_schema의 data_locks와 data_waits 테이블로 확인 가능하다.

mysql 공식 문서(https://dev.mysql.com/doc/refman/8.4/en/innodb-information-schema-examples.html)에 따르면 아래와 같은 쿼리를 이용해서 확인이 가능하다.

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread,
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM       performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
  ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
  ON r.trx_id = w.requesting_engine_transaction_id;

아래 예시는
3개의 트랜잭션에서 커밋을 하지 않은 상태에서 id(프라이머리 키)가 7인 레코드의 이름을 변경하는 쿼리를 실행했을 때 발생하는 잠금을 보여준다.

---

QA 작업

QA는 데이터 분리 전후의 응답을 비교하며 dev 환경에서 진행했고, 이를 위해 기존 dev와 분리된 dev-v2 환경을 만들어 데이터베이스를 분리하여 테스트했다.

또한, 생산성을 높여주는 플러그인을 사용해 endpoints 목록을 손쉽게 확인할 수 있었다.

이외에도 Postman을 활용한 다양한 유용한 팁들을 배웠는데, 그중 가장 인상 깊었던 것은 script 기능이었다. 이 기능을 통해 응답 값을 바로 Environments의 변수로 설정할 수 있었다.

또한, QA 과정에서 200개가 넘는 API의 응답을 일일이 비교하는 대신 이를 자동화하는 방법을 도입했다면, 작업이 좀 더 수월하고 효율적으로 마무리될 수 있었을 것이라는 생각도 들었다.