발단
얼마전에 IDC에서 운영 중이던 MySQL을 AWS Aurora로 데이터 이관 작업을 진행했었다.
기존에 사용하던 옵션들을 확인해서 패킷 바디 사이즈, 최대 커넥션 개수를 Aurora Cluster Parameter Group으로 재정의 했었다.
이때 time_zone을 의식하지 못하여 설정해주지 않아.. 데이터가 UTC로 저장되어 사용자에게 노출되는 현재 시간 정보들은 9시간 이전의 정보로 노출되었다.
| GLOBAL.time_zone | SESSION.time_zone | system_time_zone | |
|---|---|---|---|
| 소스 DB | SYSTEM | SYSTEM | KST |
| 대상 DB | SYSTEM | SYSTEM | UTC |
GLOBAL, SESSION time_zone 정보는 SYSTEM이라고 되어 있으면 system_time_zone과 동일하다는 의미이다.
AWS DMS를 통한 이관은 SELECT (소스 엔드포인트) → INSERT (타겟 엔드포인트) 로 이루어지는데 KST 기준으로 저장된 정보를 UTC 기준으로 저장한 것이다.
소스 DB의 timezone이 UTC가 아닐 때에는 AWS DMS의 소스 엔드포인트에
ServerTimezone을 설정하여야 한다. 참고
즉, 04.30 09:00:00까지 이관되었다고 가정하면 이관된 데이터는 KST 기준임에도 불구하고 UTC로 저장된 것이고, 이관 완료 후 오픈 시점 04.30 09:00:01 이후 정보들은 UTC로 저장된 것이다.
이관된 후에 대상 DB의 timezone을 Asia/Seoul로 변경하면 기존에 UTC로 저장된 데이터들을 KST로 계산하여 보여주기 때문에 이관된 데이터는 9시간 증가할 것이라고 이야기 들었다.
예를 들어, 소스 DB에서 이관된 데이터는 04.30 01:00:00 KST 이지만 대상 DB에 저장될 때는 04.30 01:00:00 UTC로 저장된 것이기 때문에 이 시점부터 대상 DB의 timezone을 Asia/Seoul로 변경하면 기존 이관된 데이터들의 날짜:시간 정보들은 +9시간 되어버린다는 것이다.
두 필드의 차이점
"UTC로 저장된 데이터가 timezone을 KST로 변경하면 9시간 누적되어서 출력될까?" 궁금했다.
create table TIMEZONE_TEST
(
id int primary key,
datetime datetime default CURRENT_TIMESTAMP not null,
timestamp timestamp default CURRENT_TIMESTAMP not null
)
-- UTC 기준
+--------------------+---------------------+--------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone | @@system_time_zone |
+--------------------+---------------------+--------------------+
| SYSTEM | SYSTEM | UTC |
+--------------------+---------------------+--------------------+
mysql> select * from TIMEZONE_TEST;
+----+---------------------+---------------------+
| id | datetime | timestamp |
+----+---------------------+---------------------+
| 1 | 2024-05-01 10:07:30 | 2024-05-01 10:07:30 |
| 2 | 2024-05-01 10:07:33 | 2024-05-01 10:07:33 |
| 3 | 2024-05-01 10:07:37 | 2024-05-01 10:07:37 |
+----+---------------------+---------------------+
-- Asia/Seoul 기준 (UTC+9)
set time_zone = 'Asia/Seoul'
+--------------------+---------------------+--------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone | @@system_time_zone |
+--------------------+---------------------+--------------------+
| SYSTEM | Asia/Seoul | UTC |
+--------------------+---------------------+--------------------+
mysql> select * from TIMEZONE_TEST;
+----+---------------------+---------------------+
| id | datetime | timestamp |
+----+---------------------+---------------------+
| 1 | 2024-05-01 10:07:30 | 2024-05-01 19:07:30 |
| 2 | 2024-05-01 10:07:33 | 2024-05-01 19:07:33 |
| 3 | 2024-05-01 10:07:37 | 2024-05-01 19:07:37 |
+----+---------------------+---------------------+
-- America/Anchorage 기준 (UTC-8)
set time_zone = 'America/Anchorage';
+--------------------+---------------------+--------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone | @@system_time_zone |
+--------------------+---------------------+--------------------+
| SYSTEM | America/Anchorage | UTC |
+--------------------+---------------------+--------------------+
mysql> select * from TIMEZONE_TEST;
+----+---------------------+---------------------+
| id | datetime | timestamp |
+----+---------------------+---------------------+
| 1 | 2024-05-01 10:07:30 | 2024-05-01 02:07:30 |
| 2 | 2024-05-01 10:07:33 | 2024-05-01 02:07:33 |
| 3 | 2024-05-01 10:07:37 | 2024-05-01 02:07:37 |
+----+---------------------+---------------------+
DATETIME 필드는 바뀌지 않지만 TIMESTAMP 필드만 DB 서버의 timezone 영향을 받는 것을 확인할 수 있다.
MySQL은 TIMESTAMP 값을 저장할 때 현재 표준 시간대에서 UTC로 변환하고, 검색할 때 UTC에서 현재 표준 시간대로 다시 변환합니다. (DATETIME과 같은 다른 유형에서는 이 작업이 수행되지 않습니다.)
기본적으로 각 연결의 현재 표준 시간대는 서버의 시간입니다. 표준 시간대는 연결별로 설정할 수 있습니다. 표준 시간대 설정이 일정하게 유지되는 한 저장한 값과 동일한 값을 반환합니다.
타임스탬프 값을 저장한 다음 표준 시간대를 변경하여 값을 검색하면 검색된 값이 저장한 값과 다릅니다.
이는 양방향 변환 에 동일한 표준 시간대가 사용되지 않았기 때문에 발생합니다. 현재 표준 시간대는 time_zone 시스템 변수의 값으로 사용할 수 있습니다. 자세한 내용은 섹션 7.1.15, "MySQL 서버 표준 시간대 지원"을 참조하세요.
공식 문서 참고
결론
이관 대상인 테이블을 모두 확인하여 TIMESTAMP 타입이 존재하는지 확인하였지만 DATETIME 필드만 사용되고 있었기에 데이터 재이관은 필요하지 않았고, 데이터 수정만으로 이 문제를 해결할 수 있었다.
그래서 오픈 이후 적재된 데이터를 확인해보니 총 103만건(A 테이블 100만건, 그 외 합쳐서 3만건) 정도 확인되었다.
새벽에 AWS 파라미터 그룹을 수정하고 인스턴스들을 재시작한 후에 일단 사용자에게 노출되는 정보인 3만건 먼저 업데이트하였으며 시간은 30초도 안걸렸다.
그외 나머지 100만건은 파티셔닝된 테이블이기도 하고 데이터가 많아서 한 번에 기간으로 잡아서 업데이트하기에는 오래 걸릴 것으로 판단됐다.
그래서 100만건의 프라이머리 키를 파일로 추출하여 10개의 스레드로 10만건씩 (한 번의 업데이트 쿼리에 10개의 레코드) 업데이트를 진행하여 10분만에 수정하였다.
(동시에 유입된 쿼리는 최대 8개로 보인다.)