허니스크린 포인트 시스템 마이그레이션을 위한 MySQL 성능 최적화

이전 블로그 포스트에서 새롭게 변경한 DynamoDB 기반의 허니스크린의 포인트 시스템을 소개하였습니다. 가장 큰 변화는 따로 분리되어 있던 포인트 적립 히스토리 테이블과 유저의 총 포인트 테이블을 하나로 합쳐서 일관성에 문제가 없는 스키마 구조를 만든 것에 있습니다. 또 하나의 변화는 기존에 포인트 타입별로 따로 관리하였던 테이블을 하나로 합친 것이었습니다. 처음 허니스크린 포인트 테이블 스키마를 설계할 때 포인트를 타입별로 여러개의 테이블을 만들어 관리할 것이냐 하나의 테이블을 만들고 포인트 타입 컬럼을 추가하여 타입별 구분을 가능하게 할 것이냐의 고민을 하였습니다. 각각의 장단점은 아래와 같습니다.

여러개의 테이블

각 포인트 타입에 필요한 컬럼 및 인덱스 설정을 따로 할 수 있음
인덱스 추가 없이 타입에 종속된 쿼리를 빠르게 실행할 수 있음
특정 유저에 대해 시간별 포인트 적립 히스토리를 보기 위해서는 모든 테이블에 쿼리를 날려야해 성능저하 발생
포인트 타입이 많아질 수록 관리가 어려워짐

하나의 테이블

타입에 상관없이 하나의 스키마를 사용해야 하므로 특정한 포인트 타입에 대해서 추가적인 컬럼이 필요할 경우 전체 테이블을 변경 해야함
포인트 타입마다 foreign key로 사용하는 컬럼이 있는데(광고 포인트는 광고 아이디, 유저 구매 포인트 차감은 주문 아이디) 이를 하나의 컬럼에서 관리할지 여러개의 컬럼을 둘지 고민해야함
포인트 타입이 추가되어도 테이블을 추가로 생성할 필요가 없음
포인트 총합 테이블이 따로 필요 없는 테이블 구조 설계 가능

처음 설계당시 저의 판단은 여러개의 테이블을 만들어서 사용하는 것이었습니다. 하지만 시간이 지남에 따라 하나의 테이블로 관리하는것이 더 좋았을 것 같다는 생각을 하게되었습니다. 가장 결정적인 원인은 포인트 테이블만 거의 10개에 달해 한 유저의 적립 히스토리를 가져오는데 성능적으로 문제가 생기는 것은 물론 관리에도 어려움이 있었다는 것이었습니다. 그리고 그 동안의 경험을 바탕으로 각 포인트 타입마다 필요한 요구사항들을 이미 알고 있기 때문에 제너럴한 포인트 테이블 설계가 가능한 상태였습니다. 그래서 포인트 히스토리를 하나의 테이블로 합치는 작업을 하기로 결정하였고 내친김에 DynamoDB로 이전하는 작업까지 병행하였습니다.

하나의 테이블로 관리되는 포인트 시스템에서는 마치 통장 내역을 기록하는 것 처럼 각 포인트를 쌓을 때 마다 쌓는 시점의 총 적립한 포인트를 같이 기록하기 때문에 특정 시점의 유저 포인트가 얼마였는지를 확인하는 것이 가능했습니다. 기존 시스템에서는 최종 시점의 총 포인트만을 별도의 테이블에 가지고 있기 때문에 특정 시점의 유저 포인트 상태를 확인하기 위해서는 그 시점까지 쌓은 포인트를 일일이 합산해야하는 문제점이 있었는데 이 문제가 자연스럽게 해결이 되었습니다. 포인트 시스템을 전환하면서 새로 쌓는 포인트에 대해서는 각 히스토리에 대한 총 포인트 데이터가 있었지만 이미 기존 시스템을 통해서 쌓은 포인트는 해당 정보가 없기에 이를 생성하기 위해서 몇가지 작업이 필요했습니다. 하지만 이미 그 동안 쌓아온 포인트가 수십억건에 달했기때문에 기존 히스토리에 대해 총 포인트를 업데이트 하는 작업이 쉽지는 않았습니다. 크게 아래와 같은 순서로 기존 포인트 히스토리에 대한 마이그레이션 작업을 진행했습니다.

사용중인 AWS RDS 디비의 스냅샷을 이용해 마이그레이션용 인스턴스 생성
통합 포인트 테이블 생성
기존 타입별 포인트 테이블의 데이터를 통합 포인트 테이블로 복사
각 유저의 첫 히스토리부터 시작하여 시간 순서대로 포인트 총합 컬럼 업데이트
S3으로 통합 포인트 테이블 백업

통합 포인트 테이블에서 각 유저의 히스토리별 총 포인트를 업데이트 하기 위해서는 유저별로 시간 순으로 정렬이 가능해야 했습니다. 이를 위해서 통합 포인트 테이블에는 유저 아이디와 적립 시간을 키로 가지는 복합키 인덱스가 필요했습니다. 하지만 인덱스를 먼저 생성하고 기존 테이블에서 데이터를 옮기면 insert성능이 저하되기 때문에 인덱스가 없는 상태로 데이터를 먼저 옮기고 그 후에 인덱스를 생성하는 방식으로 최적화를 하였습니다.

이어서 각 유저의 포인트 총 합을 업데이트하는 작업을 진행하였습니다. 하지만 이를 MySQL쿼리를 이용해서 구현하기가 쉽지 않았습니다. 다행히 링크에서 설명한 방법대로 order by를 이용한 update 쿼리와 variable의 조합을 이용하여 문제를 해결할 수 있었습니다. update 쿼리를 유저 아이디, 적립 시간을 기준으로 정렬을 하고 point_sum variable에 포인트 누계를 저장해 놓은 뒤 다음 row를 업데이트 할 때 point_sum variable을 참조해서 업데이트 하는 방식입니다. 다만 해당 링크에서는 전체 테이블을 하나의 히스토리로 보고 업데이트를 했지만 허니스크린에서는 각 유저별 히스토리를 구분하여 업데이트해야 했기에 조금 더 복잡한 쿼리를 사용해야 했습니다. last_user_id variable을 하나 더 선언하여 현재 업데이트할 row의 user_id와 비교하여 다른 경우 point_sum을 0으로 초기화해주는 작업이 필요합니다. 사용한 쿼리는 대략 아래와 같습니다.

SET @last_point_sum := 0;
SET @last_user_id := -1;
UPDATE
  points
SET
  point_sum = IF(@last_user_id = user_id, @last_point_sum := @last_point_sum + point_amount, @last_point_sum := 0),
  @last_user_id := user_id
WHERE
  user_id BETWEEN 0 AND 999
ORDER BY
  user_id, created_at;

작은 데이터 셋을 가지고 검증해보니 기대한대로 동작을 했지만 수십억건에 달하는 포인트 히스토리를 가지고 업데이트를 수행해보니 속도가 너무 느렸습니다. 이대로 가다가는 쿼리를 수행하는데 몇 주 이상이 걸릴 것으로 보여 포기하고 최적화할 방법을 찾아보기 시작했습니다. 가장 먼저 한 작업은 MySQL관련 파라미터를 수정하는 것이었습니다. 수정한 파라미터는 다음과 같습니다.

innodb_flush_log_at_trx_commit: 0 innodb_support_xa: 0 sync_binlog: 0 tx_isolation: READ-UNCOMMITTED

innodb_flush_log_at_trx_commit는 장애시 commit이 완료된 트랜잭션이 유실되는 것을 방지하기 위해 필요한 파라미터인데 성능을 높이기 위해서는 0으로 설정하여 매 트랜잭션 commit마다 InnoDB log를 디스크로 sync하지 않도록 해줍니다. sync_binlog를 0으로 세팅하면 binary log flush를 OS가 알아서 하게 하므로 성능을 높일 수 있습니다. Binary log는 리플리케이션과 특정한 데이터 복구 작업에서만 필요하기 때문에 아예 끄는 것이 더 좋은 것 같습니다. RDS 환경에서 binary log를 끄기 위해서는 backup-retention-period를 0으로 세팅하면 됩니다. innodb_support_xa는 InnoDB log와 binary log의 일관성을 보장하기 위해서 필요하다고 하는데 여기서는 필요 없기때문에 0으로 설정하였습니다. 하지만 이미 binary log를 끈 상태이기 때문에 큰 의미가 없을 것 같습니다. skip-innodb_doublewrite 옵션을 이용하면 약간의 성능을 더 얻을 수 있지만 프로덕션 환경에서는 권장하지 않고 RDS 환경에서도 변경이 불가능하여 사용하지 못 했습니다. 이번 경우처럼 단순히 마이그레이션 용도인 경우에 가능하다면 skip-innodb_doublewrite 옵션도 사용하면 좋을 것 같습니다.

MySQL의 Transaction Isolation Level 기본 값은 REPEATABLE READ 입니다. 하지만 이를 보장하기 위해서 Gap Lock을 사용하는데 이를 피하려면 Transaction Isolation Level을 READ COMMITTED나 READ-UNCOMMITTED로 변경하면 됩니다. READ-UNCOMMITTED가 가장 오버헤드가 적다고 판단했기에 선택하였습니다. 다음으로 update쿼리의 문제점을 파악하기 위해 explain 쿼리를 이용해 수행 플랜을 확인해봤습니다. 의도한대로 유저 아이디/적립 시간 복합키를 이용하기는 했지만 Extra 컬럼에 “Using where; Using filesort” 와 같이 소팅을 별도로 하고 있는 것을 알 수 있었습니다. 스택오버플로우를 찾아보니 update구문은 where절에서만 인덱스를 사용한다는 답변이 있어 만들어놓은 복합 인덱스를 활용하지 못 하게 되었습니다. 그리고 또 다른 문제는 쿼리가 수행되는 동안 lock으로 인한 오버헤드가 있다는 것입니다. transaction의 lock 상태를 확인하기 위해서 아래의 커맨드를 이용해봤습니다.

SELECT * FROM information_schema.INNODB_TRX

update 쿼리가 실행중인 동안 위의 쿼리를 실행해보면 trx_rows_locked 지속적으로 증가하는 것을 확인 할 수 있습니다. lock를 사용하고 있는지 확인하는 다른 방법으로 아래의 쿼리도 사용해봤습니다.

SHOW ENGINE INNODB STATUS;

쿼리 상태가 계속 변하기 때문에 각 state에서의 상태를 보기위해 쿼리가 진행되는 동안 두 번 상태를 확인해봤고 필요한 부분만 뽑아보면 다음과 같이 row lock을 사용하고 있는 것을 알 수 있습니다.

---TRANSACTION 29583745568, ACTIVE 5 sec fetching rows mysql tables in use 1, locked 1 45467 lock struct(s), heap size 4863528, 5273956 row lock(s) MySQL thread id 424965, OS thread handle 0x2b64bcb9d700, query id 1579693 xxx.xxx.xxx.xxx user System lock
---TRANSACTION 29583745568, ACTIVE 18 sec starting index read mysql tables in use 1, locked 1 110753 lock struct(s), heap size 11744808, 8905501 row lock(s), undo log entries 447763 MySQL thread id 424965, OS thread handle 0x2b64bcb9d700, query id 1579693 xxx.xxx.xxx.xxx user updating

lock으로 인한 오버헤드를 줄이기 위해 기존 테이블에 업데이트 하는 대신 포인트 통합 테이블과 같은 스키마를 가지는 별도의 테이블을 만들고 기존테이블에서 읽어온 총 포인트를 업데이트한 결과를 새 테이블에 insert하는 방식으로 구현을 하였습니다. 이렇게한 이유는 MySQL 레퍼런스 매뉴얼에서 볼 수 있듯이 단순한 select 구문은 lock을 사용하지 않는 반면 update구문은 스캔한 모든 레코드에 대해서 exclusive next-key lock을 걸어야 하기 때문에 비효율적이라고 생각했기 때문입니다. 그리고 MySQL에서 auto-increment primary key를 가지는 테이블에 대한 insert가 매우 효율적으로 이루어진다고 알고 있습니다. 변경한 쿼리는 아래와 같습니다.

SET autocommit=0;
SET unique_checks=0;
SET foreign_key_checks=0;
SET @new_point_sum := 0;
SET @last_user_id := -1;
INSERT INTO
  points_final
  (
    SELECT
      user_id, point_amount, date_at, point_type, coalesce(@new_point_sum := IF(@last_user_id = user_id, @new_point_sum + point_amount, 0)), 
      coalesce(@last_user_id := user_id)
    FROM
      points
    WHERE
      user_id BETWEEN 0 AND 999
    ORDER BY
      user_id, date_at
  );
COMMIT;
SET autocommit=1;
SET unique_checks=1;
SET foreign_key_checks=1;

여기서 coalesce를 사용한 이유는 MySQL에서 variable을 evaluation하는 순서가 보장되지 않는 문제를 회피하기 위해서입니다. MySQL 레퍼런스 매뉴얼을 살펴보면 “However, the order of evaluation for expressions involving user variables is undefined.“와 같은 설명이 있습니다. 위의 최종 쿼리에서 살펴보면 @new_point_sum를 업데이트하는 구문이 실행되기 전에 @last_user_id를 업데이트하는 구문이 먼저 실행 될 가능성이 있다는 것입니다. 따라서 evaluation 순서를 보장할 수 있는 방법을 다음의 링크를 통해서 찾을 수 있었습니다. coalesce를 활용하는 방법인데 찜찜하지만 어쨌든 동작하는 것 같습니다.

그리고 autocommit을 0으로 세팅하여 쿼리가 트랜잭션 안에서 동작하게 하였는데 이렇게 하면 매 insert마다 commit을 하지 않고 트랙잭션이 종료되는 시점에 commit이 한번에 되기 때문에 더 효율 적입니다. 왠지 트랜잭션을 사용하면 무조건 느릴 것만 같은데 대량 insert 상황에서는 오히려 트랜잭션을 사용하는 것이 더 유리합니다. 그리고 이번 케이스에서는 별 의미가 없을 수 있지만 unique_checks, foreign_key_checks 를 0으로 설정하여 필요없는 constraint체크는 하지 않도록 했습니다. “SELECT * FROM information_schema.INNODB_TRX” 쿼리를 이용해 확인해보니 trx_rows_locked 값이 0으로 고정되어 있어 lock을 사용하지 않는 것을 확인 할 수 있었습니다. 마찬가지로 “SHOW ENGINE INNODB STATUS"를 이용해 확인해봐도 row lock이 걸리지 않는 것을 알 수 있었습니다.

---TRANSACTION 29583746127, ACTIVE 2 sec fetching rows mysql tables in use 2, locked 1 2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 203998 MySQL thread id 11, OS thread handle 0x2aea60329700, query id 390 xxx.xxx.xxx.xxx user Sending data
---TRANSACTION 29583746127, ACTIVE 15 sec inserting mysql tables in use 2, locked 1 2 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1294454 MySQL thread id 11, OS thread handle 0x2aea60329700, query id 390 xxx.xxx.xxx.xxx user Sending data

insert로 쿼리를 변경하고 나니 update하는 것 보다 2~3배 이상의 성능 개선 효과가 있었습니다. 하지만 아직도 성능은 충분하지 않았습니다. 마이그레이션 쿼리가 돌아가는 동안 인스턴스의 상태를 확인해보니 CPU 점유율은 별로 높지 않은데 Disk IOPS와 throughput또한 최대로 활용하고 있지 못하고 있었습니다. 도대체 어디서 병목이 발생하는 것인지 알 수가 없는 상황이었습니다. 위의 쿼리를 동작을 말로 풀어보면 아래와 같습니다.

유저 x의 전체 히스토리를 시간순으로 정렬된 상태로 가져옴.
이때 user_id/date_at으로 걸려있는 인덱스를 통해 필요한 데이터 리스트를 조회하고 실제 데이터는 데이터 블록에서 다시 조회
조회된 데이터를 순차적으로 돌며 @new_point_sum 업데이트
업데이트한 포인트 히스토리를 새로운 포인트 테이블에 insert
위의 작업을 모든 유저 x에 대해 수행

여기서 가장 문제가 될 만한 부분은 user_id/date_at 인덱스를 이용해 유저의 히스토리를 가져오는 부분이었습니다. 물론 인덱스를 이용하기 때문에 필요한 데이터 스캐닝은 빠르게 할 수 있지만 인덱스에 실려있는 user_id/date_at을 제외한 모든 다른 데이터는 데이터블록에서 한번 더 스캔해야 하는 문제점이 있습니다. 이 데이터 블록들은 디스크의 여기저기에 흩어져 있기 때문에 결과적으로는 최대 히스토리 갯수만큼 디스크 엑세스가 일어나야 한다는 것을 의미합니다. 따라서 속도가 매우 느릴 수 밖에 없습니다. 만약에 디스크가 HDD라면 디스크헤드가 여기저기 데이터를 찾느라 많은 시간을 소비할 수 밖에 없을 것이고 랜덤엑세스에 강한 SSD를 사용한다고 하더라도 문제는 여전히 있습니다. SSD는 블록 단위(512K)로 리드를 수행하기 때문에 한 row를 가져오더라도 한 블록 전체를 리드하게 되어 대역폭의 낭비가 심하게 됩니다. 전체 데이터가 인스턴스의 메모리크기보다 작다면 블록 단위로 읽어오더라도 메모리에 캐싱되어 있다가 나중에 재사용이 가능하지만 데이터 크기가 인스턴스의 메모리크기보다 크다면 계속해서 캐시 eviction이 발생하여 비효율적인 동작을 하게됩니다. 이를 해결하기 위해서 블로그의 내용처럼 커버링 인덱스를 사용하기로 했습니다. 기존 user_id/date_at 인덱스에 테이블의 모든 컬럼을 추가하는 것입니다. 이렇게 하면 유저별 히스토리를 가져올 때 데이터 블록을 참조하지 않아도 되므로 성능이 비약적으로 향상되었습니다. 실제 몇 주 이상 걸릴 쿼리가 단 하루안에 완료가 되었습니다.

이제 남은 단 하나의 작업은 마이그레이션이 완료된 테이블을 S3 으로 옮기는 것입니다. AWS에서 제공하는 서비스인 Data Pipeline을 이용해 시도해보았으나 실패하였습니다. 링크에 나와있는대로 4G이상이 되는 테이블에 대해서는 이전 작업이 불가능하다는 설명이 있습니다. 게다가 컬럼을 구분하는 구분자로 콤마 밖에 지원하지 않아 데이터에 콤마가 포함되는 경우 이용이 불가능합니다. MySQL 데이터를 S3으로 이전하는 일은 일반적인 일이라 판단해서 Data Pipeline에서 잘 지원할 줄 알았지만 이렇게 제약이 있다는 것은 조금 아쉬운 부분이었습니다. 그래서 차선책으로 찾아본 툴은 mysqldump였습니다. 하지만 이 툴은 insert 쿼리 형태로만 백업이 가능하고 CSV형식으로 백업을 하려면 실제 데이터베이스의 디스크에 접근할 권한이 필요했는데 RDS에서는 디스크 접근 권한을 주지 않기에 이 또한 불가능하였습니다. xtrabackup또한 검토해보았으나 마찬가지로 디스크 접근 권한이 필요하여 사용이 불가능했습니다. 그래서 mysqldump대신 mysql 커맨드라인 유틸리티를 직접 이용하는 방식을 참고하였습니다. 이제 백업한 데이터를 s3에 올리기만 하면 됩니다. 하지만 대용량의 백업 데이터를 디스크에 저장했다가 s3에 다시 올리는 것은 왠지 비효율적이라는 생각을 해서 백업한 데이터를 바로 s3에 올릴 수 있도록 쉘 스크립트를 작성하였습니다. pipe를 열어놓고 aws s3 커맨드의 인풋으로 설정하여 백그라운드 잡으로 실행한 뒤에 이 pipe에 mysql에서 읽어온 데이터를 그대로 밀어넣는 방식입니다. 쉘 스크립트에서 멀티프로세스 프로그래밍이 가능하다니 재미있는 것 같습니다.

trap "rm -f pipe" EXIT

rm -f pipe
mkfifo pipe

gzip <pipe | aws s3 cp - s3://path-to-file &

# Open pipe for writing.
exec 3>pipe

id=0
chunk=10000
while [ $id -le $last_pk ]
do
  to=$(( $id + $chunk - 1 ))
  mysql -N -B -uusername db_name -ppassword  -h mysql_address -e "SELECT * FROM points_final WHERE id between $id AND $to" >&3

  id=$(( $id + $chunk ))
done

# Close pipe.
exec 3>&-

echo "All done!!"
exit 0

결론

MySQL에 대한 이해가 아직은 많이 부족하지만 마이그레이션 작업을 위해 여러가지 시행착오를 거치면서 많은 것들을 배운 것 같습니다. 정리해보면 다음과 같습니다.

대량의 update가 필요한 경우 때에 따라서 새로운 테이블을 만들어서 select/insert를 하는 것이 더 효율적일 수 있다.
커버링 인덱스를 적극적으로 활용하자. 특히 마이그레이션을 위한 중간 테이블은 한 번 쓰고 버릴 것이므로 더더욱 커버링 인덱스를 활용하는 것이 유리하다.
쿼리 수행 플랜을 확인하기 위해서 explain 쿼리를 활용하자.
explain쿼리로 문제를 찾을 수 없으면 show engine innodb status 쿼리를 활용하자.
쉘 스크립트 만세!

감사합니다.