MySQL DBA DDL 실무 장애 경험 - (NULL -> NOT NULL)

https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html

MySQL :: MySQL 5.7 Reference Manual :: 14.13.1 Online DDL Operations

 

실무에서 DBA는 개발팀의 요구사항을 처리할 일이 참 많았습니다.

다양한 요청이 들어오지만, 그 중 대부분의 유형은 테이블의 스키마를 변경하는 작업입니다.

percona 사의 pt-online-schema-change 툴을 사용할 수도 있지만, 거의 모든 작업을 MySQL 에서 지원하는 Online DDL로 해결했습니다.

 

이 글에서는 온라인 서비스에서, 컬럼 제약사항을 NULL -> NOT NULL 으로 변경하는 DDL 작업중 일어난 장애를 공식문서 관점에서 살펴보겠습니다.

---

예를 들어 아래와 같은 sample1 테이블이 있다고 가정합니다.

CREATE TABLE `sample1` (
  `id` bigint NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

sample1 테이블의 name 컬럼은 DEFAULT 가 NULL 입니다.

 

그런데, 비즈니스 요구사항이 변경되어서 name 컬럼에 값을 필수로 입력해야 한다면 어떻게 할까요?

 

다음과 같이 테이블 스키마를 변경해서, NULL 이 INSERT 되지 않게 막아야합니다.

mysql> ALTER TABLE `sample1` MODIFY COLUMN name varchar(255) NOT NULL;
Query OK, 0 rows affected (0.66 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table sample1\G
*************************** 1. row ***************************
       Table: sample1
Create Table: CREATE TABLE `sample1` (
  `id` bigint NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

 

 

 

(ALTER 명령시 의도적인 지연 발생을 위해 데이터 백만건을 삽입했습니다)

-- 데이터 삽입에 사용한 프로시저 (프리티어 RDS에서 34초 소요)
DELIMITER //
CREATE PROCEDURE insert_data_proc()
BEGIN
  DECLARE i INT DEFAULT 1;
  SET autocommit=0;
  WHILE i <= 1000000 DO
    INSERT INTO sample1 (id, name, description)
    VALUES (i, REPEAT('A', 100), REPEAT('B', 100));
    SET i = i + 1;
  END WHILE;
  SET autocommit=1;
END //
DELIMITER ;

---

ALTER 문은 언제나 서비스 영향도를 생각해야 합니다. 그래서 공식문서를 보고 서비스 영향도를 파악해봅니다.

 

MySQL InnoDB 에서 DDL은 Online DDL 이라는 이름으로 불립니다.

아래와 같은 연산을 할 수 있습니다.

• Index Operations
• Primary Key Operations
• Column Operations
• Generated Column Operations
• Foreign Key Operations
• Table Operations
• Tablespace Operations
• Partitioning Operations

 

방금처럼 테이블의 스키마를 변경 - 컬럼의 형태를 바꾼 연산은 Column Operation에 속합니다.

MySQL Online DDL 공식문서를 보면, 다음과 같이 정리되어 있습니다. (기준이 되는 버전은 5.7입니다.)

Column Operation 의 Online DDL 연산 종류

우리가 봐야할 것은 `Making a Column NOT NULL` 항목입니다.

하나하나 살펴보자면,

In Place 항목이 Yes입니다.
Metadata만 변경하는 가벼운 작업이 아니고,
테이블을 Rebuild하는 과정도 포함된다고 나와있습니다.
가장 결정적으로, 동시 DML을 허용한다고 나와있습니다.

 

 

동시 DML이 허용되면 일이 쉬워집니다.

개발팀에게 DB-WAS 리퀘스트 관련 양해를 자세히 구할 필요가 줄어들고,

새벽작업을 안해도 되고,,, 새벽작업을 안해도 되고,,, 업무시간에 작업을 해도 됩니다,,,, ㅋㅋ

 

별 문제 없는 작업인 것 같으니 이제 준비는 끝났고, 작업을 시작하면 됩니다.

(테스트 환경에서 미리 테스트도 완료했습니다)

---

 

글의 흐름상 이제 장애 이야기가 나와야겠지요,,,

공식 문서만 철썩같이 믿고 작업을 시작하자마자 문제가 시작되었습니다ㅠ

 

아래는 당시 상황을 재현한 상태의 processlist입니다.

18번 세션이 ALTER 문을 실행한 후 25번 세션이 UPDATE 문을 실행하려고 했는데, wating for table metadata lock이라는 상태로 멈춰있습니다. 

mysql> show processlist;
+----+-----------------+-----------------------+-----------------+---------+------+---------------------------------+--------------------------------------------------------------+
| Id | User            | Host                  | db              | Command | Time | State                           | Info                                                         |
+----+-----------------+-----------------------+-----------------+---------+------+---------------------------------+--------------------------------------------------------------+
|  5 | event_scheduler | localhost             | NULL            | Daemon  |  265 | Waiting on empty queue          | NULL                                                         |
|  9 | rdsadmin        | localhost             | NULL            | Sleep   |    1 |                                 | NULL                                                         |
| 11 | rdsadmin        | localhost             | NULL            | Sleep   |    1 |                                 | NULL                                                         |
| 18 | admin           | 116.123.115.238:51273 | schema_for_test | Query   |   25 | copy to tmp table               | ALTER TABLE sample1 MODIFY COLUMN NAME VARCHAR(255) NOT NULL |
| 20 | rdsadmin        | localhost             | NULL            | Sleep   |    4 |                                 | NULL                                                         |
| 25 | admin           | 116.123.115.238:51278 | schema_for_test | Query   |   20 | Waiting for table metadata lock | update sample1 set description='BBB' where id = 123          |
| 31 | rdsadmin        | localhost             | NULL            | Sleep   |    2 |                                 | NULL                                                         |
| 34 | rdsadmin        | localhost             | NULL            | Sleep   |   82 |                                 | NULL                                                         |
| 42 | admin           | 116.123.115.238:51358 | NULL            | Query   |    0 | init                            | show processlist                                             |
+----+-----------------+-----------------------+-----------------+---------+------+---------------------------------+--------------------------------------------------------------+
9 rows in set (0.01 sec)

다시 말해, DBMS에서 동시 DML이 허용되지 않고 있다는 말입니다.

 

유저 트래픽이 있을 때 작업을 시작했으니

결국, 개발팀에게 소통해서 작업을 취소하고 추후 진행하기로 합니다.

 

---

무엇이 문제였을까요??!!

 

언제나 공식문서는 자세히 봐야합니다...

아래 이미지는 NOT NULL로 변경하는 연산의 상세 설명란입니다.

Making a column NOT NULL 상세 설명

한 문장에 집중했습니다.

작업이 성공하려면 STRICT_ALL_TABLES 또는 STRICT_TRANS_TABLES SQL_MODE가 필요합니다.(STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed.)

 

음? 테스트 할 때는 ALTER가 성공적으로 잘 되었는데,, 뭐가 문제지?

DMBS의 SQL_MODE 변수를 확인해봅니다.

mysql> show variables like '%sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.19 sec)

흠.. 비어있습니다. AWS 콘솔 상에서 확인해봅니다.

aurora 클러스터 파라미터 sql_mode 기본값.

0 이군요. 패착이 여기서 드러납니다. ㅠ_ㅠ

AWS MySQL Aurora의 sql_mode 변수의 기본값은 0입니다.

(참고로 일반 mysql 인스턴스는 기본값이 'NO_ENGINE_SUBSTITUTION' 입니다.)

 

아무 것도 모르고 기본값으로 진행하게 된다면 무조건 동시 DML이 허용되지 않습니다.

이것이 장애의 원인이었습니다.

 

---

sql_mode를 공식문서에서 설명하는 것 처럼 STRICT_ALL_TABLE 혹은 STRICT_TRANS_SQL_MODE 로 변경하고 다시 수행합니다.

SET sql_mode="STRICT_ALL_TABLES"

 

이제 정상적으로 ALTER를 실행하는 도중에도 동시 DML이 허용됩니다. 

이렇게 실무에서 장애를 겪고 해결한 썰을 풀어봤습니다..

글을 어떻게 마무리지어야할 지 모르겠네요 ㅎㅎ

 

 

 

 

sql_mode에 대한 설명은 너무 길어지게 되니 공식문서 링크로 대체합니다..ㅋㅋ

https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html

MySQL :: MySQL 8.0 Reference Manual :: 5.1.11 Server SQL Modes