배경
pt-osc를 사용하지 않은 대용량 테이블 DDL 작업 시 실패할 수 있는 시나리오를 정리하고 각 케이스 별 준비사항과 모니터링 방법을 확인한다
실패하는 케이스는 아니어도, 무심코 놓치면 위험한 케이스도 정리하였다
요약
300GB가 넘는 테이블 작업은 피할 수 있다면 피하자
- 컬럼 추가 같이 rebuild 가 필요한 작업은 child 테이블 생성 후 join 해서 쓰는 모델링 관점으로 풀도록 개발팀에 안내한다
pt-osc를 사용하는 경우 DB부하는 훨씬 줄어들지만 시간 예측이 더 어려울 수 있으니 꼼꼼히 확인해보자
- 테이블 사이즈 뿐만 아니라 PK의 big int / UUID 여부 , auto increment가 어디까지 할당되어있는지, 테이블의 건 수 등도 큰 영향을 끼친다
설득이 실패한 경우 공통 준비사항
- DB 설정
set session lock_wait_timeout = 120;
=> 단, 이 설정으로 인해 서비스쿼리들이 메타락 대기로 쭉 밀릴 수 있기 때문에 주의할 것. 롱쿼리,롱트래잭션이 없는 서비스라면 기존 5초 설정도 충분함
innodb_online_alter_log_max_size = 10737418240 (클러스터 파라미터그룹)
=> 온라인 dml 버퍼 1GB → 10GB 증가시켜 놓고 작업할것
innodb_adaptive_hash_index = OFF
=> DDL 수행 시 hash index 메모리에 있던 대상 테이블의 데이터들이 한번 쭉 비워짐
이때 hash index가 클수록 그 부하가 커지고 디비 전체가 행 걸리는 것 같은 이슈가 발생하면서 세션이 순간적으로 쭉 밀리는 현상이 발생할 수 있음
- 인덱스 추가 시
- 작업시간 산정 : 테이블 사이즈 (data) 1GB 당 30초 (파티션 테이블의 경우 파티션 개수마다 차이가 있지만 보통 1.5배로 계산)
- 테이블 사이즈 만큼의 Free Local Storage(tmpdir) 를 확보한다 부족하면 스펙업할 것
- 스펙 별 local storage
instance class local storage(GB)
db.r6i.32xlarge 2560
db.r6i.24xlarge 1920
db.r6i.16xlarge 1,280
db.r6i.12xlarge 960
db.r6i.8xlarge 640
db.r6i.4xlarge 320
db.r6i.2xlarge 160
db.r6i.xlarge 80
db.r6i.large 32
db.t4g.xlarge 32
db.t4g.medium 32
db.t3.large 32
db.t3.medium 32
db.t3.small 32
db.t2.medium 32
db.t2.small 32
- 컬럼 추가 등 기타 inplace
- 작업시간 산정 : 테이블 사이즈 (data + index) 1GB 당 1분 (파티션 테이블의 경우 파티션 개수마다 차이가 있지만 보통 1.5배로 계산)
- varchar 컬럼 길이 변경 시엔 반드시 인덱스에 포함된 컬럼인지 확인할것(aurora 2 한정)
- mysql 5.7.12 버전의 버그로 metadata만 변경하고 바로 완료되는 구간이어도 inplace로 동작하게됨
모니터링
DB 리소스 측면에서는 아래 두 메트릭을 확인한다
이 중 Freeable Memory는 index 추가, 컬럼 추가 등 모든 DDL 공통으로 사용하고
Free Local Storage는 index 작업시에만 사용한다.
(onpremise mysql에서 /tmpdir 을 사용하는 것과 동일하다)
두 메트릭 중 하나라도 소진하는 경우 작업이 실패하게 되고
특히 Freeable Memory는 작업이 끝나도 작업시 사용한 메모리가 모두 반환되는 게 아니기 때문에
이 메모리가 부족한 경우엔 DB재기동 등을 통해 회수해주는 것이 좋다
쿼리 측면에서는 DDL 진행도를 파악하여 쿼리 예상 종료시간을 예측해볼 수 있다
### 사전 준비
mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE 'events_stages%';
### DDL 진행도 파악
mysql> SELECT
esc.THREAD_ID ,
esc.SQL_TEXT ,
estc.EVENT_NAME ,
estc.WORK_COMPLETED ,
estc.WORK_ESTIMATED ,
(
100 * estc.WORK_COMPLETED / estc.WORK_ESTIMATED
) AS 'Progress_%'
FROM
performance_schema.events_statements_current esc,
performance_schema.events_stages_current estc
WHERE
estc.THREAD_ID = esc.THREAD_ID AND estc.NESTING_EVENT_ID = esc.EVENT_ID;
+-----------+------------------------------------------------------------------+---------------------------------------+----------------+----------------+------------+
| THREAD_ID | SQL_TEXT | EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED | Progress_% |
+-----------+------------------------------------------------------------------+---------------------------------------+----------------+----------------+------------+
| 79261 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (merge sort) | 379860 | 554662 | 68.4850 |
+-----------+------------------------------------------------------------------+---------------------------------------+----------------+----------------+------------+
1 row in set (0.01 sec)
==> 379860 , 554662 숫자가 의미하는 건 테이블의 row 수가 아니라 작업량을 page 수로 표현한 의미라한다.
### 컬럼 추가
mysql> SELECT pseshl.EVENT_ID
-> , pseshl.NESTING_EVENT_ID
-> , esh.SQL_TEXT
-> , pseshl.EVENT_NAME
-> , sys.format_time(pseshl.TIMER_WAIT)
-> FROM performance_schema.events_stages_history_long pseshl
-> , performance_schema.events_statements_history esh
-> WHERE pseshl.NESTING_EVENT_ID = esh.EVENT_ID
-> ORDER BY pseshl.NESTING_EVENT_ID, pseshl.TIMER_START
-> limit 5;
+----------+------------------+----------------------------------------------------------------------+------------------------------------------------------+------------------------------------+
| EVENT_ID | NESTING_EVENT_ID | SQL_TEXT | EVENT_NAME | sys.format_time(pseshl.TIMER_WAIT) |
+----------+------------------+----------------------------------------------------------------------+------------------------------------------------------+------------------------------------+
| 147 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (merge sort) | 4.60 s |
| 148 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (insert) | 4.24 s |
| 149 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (merge sort) | 5.43 s |
| 150 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (insert) | 5.64 s |
| 151 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (merge sort) | 5.84 s |
| 152 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (insert) | 5.71 s |
| 153 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (merge sort) | 6.30 s |
| 154 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (insert) | 5.38 s |
| 155 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (flush) | 5.64 us |
| 156 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (log apply table) | 113.96 us |
| 157 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (end) | 17.33 us |
| 158 | 146 | alter table t1 add column t07202 int,algorithm=inplace,lock=none | stage/innodb/alter table (log apply table) | 521.72 ms |
### 인덱스 추가
| 160 | 159 | alter table t1 add index idx0720(t07202),algorithm=inplace,lock=none | stage/innodb/alter table (read PK and internal sort) | 5.07 s |
| 161 | 159 | alter table t1 add index idx0720(t07202),algorithm=inplace,lock=none | stage/innodb/alter table (merge sort) | 4.47 s | <---- 가장 오래걸리는 구간
| 162 | 159 | alter table t1 add index idx0720(t07202),algorithm=inplace,lock=none | stage/innodb/alter table (insert) | 4.77 s |
| 163 | 159 | alter table t1 add index idx0720(t07202),algorithm=inplace,lock=none | stage/innodb/alter table (flush) | 2.00 us |
| 164 | 159 | alter table t1 add index idx0720(t07202),algorithm=inplace,lock=none | stage/innodb/alter table (log apply index) | 507.75 us |
| 165 | 159 | alter table t1 add index idx0720(t07202),algorithm=inplace,lock=none | stage/innodb/alter table (flush) | 86.97 us |
| 166 | 159 | alter table t1 add index idx0720(t07202),algorithm=inplace,lock=none | stage/innodb/alter table (end) | 50.85 ms |
### 사용 쿼리
SELECT esc.THREAD_ID
, esc.SQL_TEXT
, estc.EVENT_NAME
, estc.WORK_COMPLETED
, estc.WORK_ESTIMATED
, (100 * estc.WORK_COMPLETED / estc.WORK_ESTIMATED) AS 'Progress_%'
FROM performance_schema.events_statements_current esc
,performance_schema.events_stages_current estc
WHERE estc.THREAD_ID = esc.THREAD_ID
AND estc.NESTING_EVENT_ID = esc.EVENT_ID
SELECT pseshl.EVENT_ID
, pseshl.NESTING_EVENT_ID
, esh.SQL_TEXT
, pseshl.EVENT_NAME
, sys.format_time(pseshl.TIMER_WAIT)
FROM performance_schema.events_stages_history_long pseshl
, performance_schema.events_statements_history esh
WHERE pseshl.NESTING_EVENT_ID = esh.EVENT_ID
ORDER BY pseshl.NESTING_EVENT_ID, pseshl.TIMER_START
limit 5;
참고 : https://dev.mysql.com/doc/refman/8.0/en/monitor-alter-table-performance-schema.html
케이스 정리
실패 case 1. Free Local Storage 부족
index 추가 시엔 Local Storage를 사용하는데 이 때 작업 테이블 사이즈만큼의 공간을 필요로 하고, 부족한 경우엔 작업이 실패함
add column 같은 rebuild DDL은 local storage가 아니라 datadir (클러스터볼륨)을 사용하기 때문에 걱정이없다
340GB 테이블에 인덱스를 추가할 때
2xlarge의 경우 Free Local Storage 공간이 부족하여 작업이 실패하였고
4xlarge의 경우 거의 300GB만큼의 공간을 사용한 뒤 성공하였음
ERROR 1878 (HY000) Temporary file write failure 발생
실패 case 2. online dml buffer 부족
online DDL 수행 중 테이블의 변경사항은 innodb_online_alter_log_max_size 설정값(default 1GB) 만큼
innodb_online_alter_log buffer에 보관할 수 있게 되는데 이것이 부족한 경우 작업이 실패한다
작업 초반부에 버퍼가 초과해도 바로 에러가 발생하는 게 아니라 작업 막바지에 buffer 데이터 반영하는 시점에 실패하게 되기 때문에 제일 가슴이 아픈 케이스다
실패 case 3. metadata lock timeout
+-------+-----------------+--------------------+------+-------------+--------+---------------------------------------------------------------+-----------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-----------------+--------------------+------+-------------+--------+---------------------------------------------------------------+-----------------------------------------------------------------+
| 79231 | master | 172.27.2.39:52353 | test | Query | 112 | Waiting for table metadata lock | alter table t1 add column t0720 int,algorithm=inplace,lock=none |
| 79412 | master | 172.27.2.39:52920 | test | Sleep | 108 | NULL | NULL |
| 79418 | master | 172.27.2.39:52925 | test | Query | 25 | Waiting for table metadata lock | select * from t1 limit 1 |
| 79425 | master | 172.27.2.39:52935 | test | Query | 3 | Waiting for table metadata lock | select * from t1 limit 1 |
| 79428 | master | 172.27.2.39:52937 | NULL | Query | 0 | starting | show processlist |
+-------+-----------------+--------------------+------+-------------+--------+---------------------------------------------------------------+-----------------------------------------------------------------+
mysql> alter table t1 add column t0720 int,algorithm=inplace,lock=none;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
79412 세션이 t1 테이블에 대한 트랜잭션(롱 쿼리)을 유지하고 있어서 DDL이 메타락을 획득하지 못하고 대기하다가 timeout이 발생하는 케이스
문제는 DDL 컬럼이 메타락을 획득하려고 시도하는 동안,
그 뒤로 들어오는 해당 테이블을 사용하는 모든 쿼리들도 메타락 대기를 하게 되어 서비스 장애가 발생할 수 있기 때문에
제일 무서운 케이스라고 볼 수 있다
실패 case 4. fulltext index 테이블에 online DDL 시도
CREATE TABLE `t1` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`TABLE_NAME` varchar(64) DEFAULT NULL,
`COLUMN_NAME` varchar(64) DEFAULT NULL,
`ORDINAL_POSITION` int(11) DEFAULT NULL,
`ttt` varchar(30) NOT NULL,
`ttt2` varchar(20) DEFAULT NULL,
`ttt3` varchar(10) DEFAULT NULL,
`tttt2` int(11) DEFAULT NULL,
`tttt444` int(11) DEFAULT NULL,
`ttt55` varchar(20) NOT NULL,
`ttt66` int(11) DEFAULT NULL,
`t07202` int(11) DEFAULT NULL,
PRIMARY KEY (`tid`,`ttt`),
KEY `idx` (`ttt`),
KEY `idx_0710` (`ttt55`),
KEY `idx_0711` (`ttt55`),
KEY `idx_0712` (`ttt55`),
KEY `idx0720` (`t07202`),
KEY `idx07202` (`t07202`),
FULLTEXT KEY `idx1` (`ttt`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000042 DEFAULT CHARSET=utf8mb4
mysql> alter table t1 add column t07203 int,algorithm=inplace,lock=none;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: InnoDB presently supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY.
mysql> alter table t1 add index idx00 (t07202),algorithm=inplace,lock=none;
Query OK, 0 rows affected, 1 warning (15.09 sec)
컬럼 추가 같은 rebuild 작업이 inplace로 안되는 모습
inplace로 동작할줄알고 algorithm=inplace,lock=none; 구문 제외하는 순간 COPY방식으로 동작하여 장애가 날 수 있다
반면 인덱스 추가는 inplace로 되는 모습
실패 case 5. index걸린 컬럼 varchar size 변경 시도 (aurora2 , mysql 5.7.12 한정)
CREATE TABLE `t1` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`TABLE_NAME` varchar(64) DEFAULT NULL,
`COLUMN_NAME` varchar(64) DEFAULT NULL,
`ORDINAL_POSITION` int(11) DEFAULT NULL,
`ttt` varchar(30) NOT NULL,
`ttt2` varchar(10) DEFAULT NULL,
`ttt3` varchar(10) DEFAULT NULL,
`tttt2` int(11) DEFAULT NULL,
`tttt444` int(11) DEFAULT NULL,
`ttt55` varchar(10) NOT NULL,
`ttt66` int(11) DEFAULT NULL,
`t07202` int(11) DEFAULT NULL,
PRIMARY KEY (`tid`,`ttt`),
KEY `idx` (`ttt`),
KEY `idx_0710` (`ttt55`),
KEY `idx_0711` (`ttt55`),
KEY `idx_0712` (`ttt55`),
KEY `idx0720` (`t07202`),
KEY `idx07202` (`t07202`)
) ENGINE=InnoDB AUTO_INCREMENT=1000000042 DEFAULT CHARSET=utf8mb4 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> alter table t1 modify ttt2 varchar(20),algorithm=inplace,lock=none;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify ttt55 varchar(20) not null,algorithm=inplace,lock=none;
Query OK, 0 rows affected (46.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
인덱스가 걸려있지 않은 컬럼은 metadata만 변경해서 바로 되는 반면
인덱스 걸려있는 컬럼은 inplace로 동작한다
실패 case 6. 컬럼 추가한 뒤 바로 인덱스 걸어주는 경우
대용량 테이블에 컬럼을 추가와 해당 컬럼에 대해서만 인덱스 추가가 바로 진행되는 경우
해당 인덱스의 카디날리티는 당연히 1 이다 (NULL값, 혹은 default값)
인덱스가 생성됐다고 바로 쿼리가 배포되는 순간
통계가 갱신되기 전 까지는 카디날리티가 1이기 때문에 인덱스를 못타고 풀스캔을 하게 된다
그러나 통계가 갱신되려면 전체 테이블 Row의 10%만큼 해당되는 변화가 있어야하는데 자주 갱신되기는 쉽지않다.
이런 DDL 요청이 오는 경우 개발팀에 아래 사례를 공유하며 DDL과 쿼리 배포의 텀을 길게 하여 그 사이에 통계 갱신이 되도록 하거나
신규 컬럼 단일 인덱스를 생성하기 보다는 선별도 높은 다른 컬럼과 함께 쿼리와 인덱스에 추가하도록 한다