Online DDL - INSTANT 알고리즘

테이블에 적재된 데이터가 많다면 테이블을 변경하기 위해 실행한 DDL문이 바로 끝나지 않고 오래 실행될 수 있습니다. 그 중 어떤 작업은 테이블을 잠그기도 하는데 그 후에 어떤 일이 벌어지는지는 상상에 맡기겠습니다.

물론 모든 DDL문이 위와 같은 문제를 일으키는 것은 아닙니다.

MySQL, 정확히는 InnoDB 스토리지 엔진은 Online DDL을 지원합니다. 즉, 어떤 DDL은 서비스 중에 실행해도 괜찮고 또 어떤 DDL은 그렇지 않다는 것입니다.

개발자로서 이 둘을 구분할 수 있어야 하는데, Online DDL에 대한 전반적인 설명은 아래 레퍼런스 문서를 참조할 수 있습니다.
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

INSTANT 알고리즘

8.0.12 버전에 처음 선 보인 INSTANT 알고리즘은 Online DDL의 꽃🌷이라고 할 수 있습니다.
테이블에 적재된 데이터는 건드리지 않고 단지 테이블의 메타데이터만 수정하여 즉시 적용할 수 있는 ALTER 문, 바로 INSTANT 알고리즘으로 실행하는 ALTER 문입니다.

INSTANT 알고리즘을 사용한 ALTER문으로 할 수 있는 작업

• 컬럼 추가 / 삭제
• 컬럼 이름 변경
• 컬럼의 디폴트 값 설정 / 삭제
• ENUM과 SET 컬럼의 정의 변경
• virtual 타입의 generated 컬럼 추가/삭제
• 테이블 이름 변경
• 인덱스 유형 변경

📌 이 포스트는 컬럼을 추가하고 삭제하는 작업을 중심으로 INSTANT 알고리즘이 작동하는 방식을 설명합니다.

MySQL 버전에 따른 INSTANT 기능 차이

INSTANT 알고리즘은 8.0.12 이후 버전에서 사용할 수 있지만, 8.0.29를 전후하여 변화가 있었습니다.

8.0.12

• 테이블의 끝에 추가하는 컬럼에 대해서만 INSTANT 알고리즘을 사용할 수 있습니다.
• 컬럼을 삭제할 때 INSTANT 알고리즘을 사용할 수 없습니다.

8.0.29

• 테이블의 아무 위치에나 컬럼을 추가할 수 있습니다.
• 컬럼을 삭제할 때도 INSTANT 알고리즘을 사용할 수 있습니다.

INSTANT 알고리즘의 작동 원리

배경 지식

InnoDB에서 행(ROW)을 저장하는 방식

• 모든 행에는 ROW METADATA 영역이 있고 다음과 같은 정보를 담고 있습니다.
  • 다음 행의 위치를 알려주는 포인터
  • 행에 대한 그 밖의 메타데이터

ROW METADATA

• 행 메타데이터(Row Metadata)는 Additional Metadata와 Row Header로 구성되어 있습니다.
• 행 헤더(Row Header) 안에는 4bit 크기의 info_bits 영역이 있습니다.
  • info_bits의 두 번째 비트는 8.0.29 이전 버전까지 용도가 없는 비트였습니다.
  • info_bits의 두 번째 비트의 기본값은 UNSET, 즉 0입니다.

ROW VERSION

행 버전(Row Version)은 ALTER TABLE ... ADD COLUMN / DROP COLUMN ... , ALGORITHM = INSTANT; 문이 실행될 때마다 1씩 증가하는 값입니다.
이 값은 두 곳에 저장 됩니다.

• 각각의 행에 있는 행 메타데이터
• InnoDB 테이블 메타데이터
  단, 테이블의 메타데이터에 저장되는 행 버전은 가장 높은 버전. 즉, 가장 최신의 행 버전만을 저장하고 있습니다.

행 버전이 관리되는 방식과 순서는?

1. 모든 InnoDB 테이블은 최신 행 버전을 갖고 있고 최초 값은 0입니다.
   • information_schema.innodb_tables.total_row_versions 컬럼에 저장되어 있습니다.

     -- 최신 행 버전 조회
     SELECT name, total_row_versions
     FROM information_schema.innodb_tables
     WHERE name = '데이터베이스 이름/테이블 이름';

2. INSTANT 알고리즘으로 ALTER TABLE … ADD/DROP COLUMN 문을 실행하면, 테이블의 최신 행 버전 (total_row_versions)이 1 증가합니다.
3. ALTER TABLE … ADD/DROP COLUMN 문이 실행된 이후 INSERT 되는 모든 행에는, 테이블의 최신 행 버전의 값이, 새로 삽입된 행의 행 메타데이터(Row Metadata)에 기록됩니다.
   • Instant bit(info_bits의 두 번째 비트)를 1로 변경하고, 행 메타데이터(Row Metadata)에 행 버전을 기록합니다.
   • 최신 행 버전이 0일 때 삽입된 행
     • 인스턴트 비트(Instant bit): 0
     • 명시적인 행 버전 없음
4. 만약 한 번의 ALTER 테이블 문으로 여러 컬럼을 추가하거나 삭제하더라도 테이블의 최신 행 버전은 1만 증가합니다.

INSTANT METADATA

여러 ALTER TABLE ... ADD/DROP COLUMN을 거친 테이블에는 행 버전이 다른 여러 행이 있을 수 있습니다. 그리고 이 테이블에서 행을 가져오면 각 행의 행 버전과 인스턴트 메타데이터(Instant Metadata)를 사용하여 최신 행 버전으로 변환할 수 있습니다.

인스턴트 메타데이터에는 무엇을 저장하나?

• 컬럼이 추가되었을 때의 최신 행 버전과 추가한 컬럼의 DEFAULT 값
• 컬럼이 삭제되었을 때의 최신 행 버전

인스턴트 메타데이터는 어디에 저장되어 있나?

데이터 딕셔너리(Data Dictionary)에 저장합니다. 직접 조회할 수 없도록 보호되어 있어서 디버그 버전으로 빌드한 MySQL에서만 접근이 가능합니다.

예제를 통해 이해하기

-- 데이터베이스를 생성합니다.
CREATE DATABASE test;

USE test;

-- 테이블을 생성합니다.
CREATE TABLE t1 (
    col1 int,
    col2 int,
    col3 int
);

-- 테이블의 최신 행 버전을 조회합니다.
SELECT name, total_row_versions
FROM information_schema.innodb_tables
WHERE name = 'test/t1';

-- 첫 번째 행을 삽입합니다.
INSERT t1 (col1, col2, col3)
VALUES (1, 2, 3);

• t1 테이블의 최신 행 버전은 0입니다.
  
• 첫 번째 행의 인스턴트 비트(info_bits의 두 번째 비트)는 0이고, 따라서 행 버전은 기록되어 있지 않습니다.
  
• 데이터 딕셔너리에 저장된 인스턴트 메타데이터의 내용은 다음과 같습니다.
  

-- INSTANT 알고리즘으로 컬럼 `col4` 추가 (default: 4)
ALTER TABLE t1
ADD COLUMN col4 int NOT NULL DEFAULT 4, ALGORITHM = INSTANT;

-- 테이블의 최신 행 버전: 1
SELECT name, total_row_versions
FROM information_schema.innodb_tables
WHERE name = 'test/t1';

-- 두 번째 행을 삽입합니다.
INSERT t1 (col1, col2, col3, col4)
VALUES (11, 22, 33, 44);

-- 첫 번째 행의 비어 있는 `col4` 값을, 행 버전 1의 default 값인 4로 변환하여 리턴합니다.
SELECT *
FROM t1;

• t1 테이블의 최신 행 버전이 1로 바뀌었습니다.
  
• 방금 삽입한 두 번째 행의 행 메타데이터는 아래와 같습니다.
  
  • 인스턴트 비트 = 1
  • 행 버전(ROW VERSION) = 1
• 인스턴트 메타데이터
  
• 물리적으로 저장되어 있는 값
  
  • INSTANT 알고리즘은 테이블의 메타데이터만 수정하고 실제 데이터에 대한 I/O가 발생하지 않습니다. 따라서 첫 번째 행의 col4 컬럼에는 아무 값도 저장되어 있지 않다는 점에 유의하십시오!
• SELECT 결과
  
  • 첫 번째 행의 col4 컬럼의 값은 디스크에 물리적으로 기록되어 있지 않지만 4를 반환합니다.
  • 어떻게?
    • 첫 번째 행의 행 버전은 0 이지만, 최신 행 버전이 1 입니다. 최신 행 버전보다 낮은 행 버전을 가졌으므로 인스턴트 메타데이터를 참조하여 변환하는 작업을 수행합니다.
    • 인스턴트 메타데이터를 조회하면 col4 컬럼이 추가되었을 때의 default 값이 4 입니다. 따라서, 첫 번째 행의 col4 컬럼의 값으로 4 를 반환할 수 있습니다.

-- INSTANT 알고리즘으로 `col2` 컬럼을 삭제합니다.
ALTER TABLE t1
DROP COLUMN col2, ALGORITHM = INSTANT;

-- 테이블의 최신 행 버전: 2
SELECT name, total_row_versions
FROM information_schema.innodb_tables
WHERE name = 'test/t1';

-- 세 번째 행을 삽입합니다.
INSERT t1 (col1, col3, col4)
VALUES (111, 333, 444);

-- 첫 번째와 두 번째 행의 `col2` 컬럼에 물리적인 값이 존재하지만 리턴하지 않습니다.
SELECT *
FROM t1;

• t1 테이블의 최신 행 버전이 2로 바뀌었습니다.
  
• 인스턴트 메타데이터
  
• 물리적으로 저장되어 있는 값
  
  • 테이블의 메타데이터에서만 컬럼 col2를 삭제했기 때문에 디스크에는 첫 번째 행과 두 번째 행에 예전 col2 컬럼의 값이 여전히 저장되어 있습니다.
• SELECT 결과
  
  • 첫 번째 행과 두 번째 행의 col2 컬럼 값이 존재하지만, 최신 행 버전인 2 에서 col2 컬럼이 삭제되었기 때문에 값을 반환하지 않습니다.

더 생각해 보기

col4 컬럼의 default를 변경하면, 첫 번째 행의 col4 컬럼 값도 바뀔까?

ADD COLUMN과 DROP COLUMN에 의해서만 테이블의 최신 행 번호가 올라가므로, 첫 번째 행의 col4 컬럼은 예전 default인 4를 반환합니다.

-- 추가한 컬럼 col4의 default 값을 4에서 4444로 변경하면 어떻게 될까?
ALTER TABLE t1
CHANGE COLUMN col4 col4 int NOT NULL DEFAULT 4444, ALGORITHM = INSTANT;

-- 행 버전이 올라가지 않습니다.
SELECT name, total_row_versions
FROM information_schema.innodb_tables
WHERE name = 'test/t1';

-- 첫 번째 행의 비어 있는 col4 값은 새로운 default 값이 아니라 행 버전 1의 default 값인 4로 변환하여 리턴합니다.
SELECT *
FROM t1;

NOT NULL 속성의 컬럼을 추가하면서 default를 설정하지 않는다면 어떻게 될까?

컬럼의 데이터 유형에 따라 다릅니다.

• 숫자: 묵시적으로 0을 반환합니다.
• 문자열: 묵시적으로 빈 문자열을 반환합니다.
• 날짜: ALTER 문 실행이 실패합니다.

-- NOT NULL이면서 default가 없는 컬럼 col5를 추가합니다.
ALTER TABLE t1 
ADD COLUMN col5 int NOT NULL, ALGORITHM = INSTANT;

-- 테이블의 최신 행 버전 3
SELECT name, total_row_versions
FROM information_schema.innodb_tables
WHERE name = 'test/t1';

INSERT t1 (col1, col3, col4, col5)
VALUES (1111, 3333, 4444, 5555);

SELECT *
FROM t1; -- DEFAULT가 없는 col5 컬럼의 값은 0으로 묵시적 변환합니다.

행 버전, 주의할 점은?

행 버전의 제한

• 행 버전은 최대 64까지 가능하고, 64회를 초과하는 ALTER TABLE ... ADD/DROP COLUMN 문에 대해서는 INSTANT 알고리즘을 사용할 수 없습니다.
• 64회를 초과한 ALGORITHM=INSTANT을 사용했을 때 오류 메시지
  • ERROR 4080 (HY000): Maximum row versions reached for table test/t1. No more columns can be added or dropped instantly. Please use COPY/INPLACE.
• 📌64회를 초과하는 ALTER TABLE ... ADD/DROP COLUMN 문을 명시적으로 INSTANT 알고리즘으로 설정하지 않고 실행한다면, 시스템은 묵시적으로 INPLACE 또는 COPY 알고리즘을 사용합니다. 의도하지 않은 시스템 장애가 발생할 수 있으므로 주의해야 합니다!

남은 변경 횟수 관리

ALTER문을 사용하기 전에 INSTANT 알고리즘을 사용해 테이블을 변경할 수 있는 횟수를 확인하고, 정기적으로 관리해야 합니다.

-- 현재 데이터베이스의 모든 InnoDB 테이블에 대해 남아 있는 INSTANT ALTER 횟수 조회 (10회 이하 only)
SELECT SUBSTRING_INDEX(name, '/', -1) AS table_name
    , 64 - total_row_versions AS remaining_instant_alter
FROM information_schema.innodb_tables
WHERE name LIKE CONCAT(DATABASE(), '/%')
    AND total_row_versions > 54 -- 남은 횟수가 10회 이하인 테이블
ORDER BY 2;

최신 행 버전을 0으로 초기화하는 방법

• 점검 시간을 이용하여 아래 구문으로 초기화할 수 있습니다.
  • OPTIMIZE TABLE ...
  • ALTER TABLE ... ENGINE=InnoDB
• 다음 구문이 실행되는 경우에도 최신 행 버전이 초기화 됩니다.
  • TRUNCATE TABLE
  • INPLACE 또는 COPY 알고리즘으로 테이블을 ALTER 할 때

INSTANT 알고리즘을 사용하여 ALTER할 수 없는 테이블

• Fulltext Index가 있는 테이블
• row_format이 compressed인 테이블
• 임시 테이블

기타 권고 사항

8.0.29에서 추가된 기능이지만, 8.0.32 이상에서 사용하는 것을 권고합니다.

---

참고 자료
https://blogs.oracle.com/mysql/post/mysql-80-instant-add-drop-columns
https://blogs.oracle.com/mysql/post/mysql-80-instant-add-and-drop-columns-2

---