배경
얼마 전에 사업팀으로부터 기획전에 링크를 입력할 때 길이가 너무 짧아서 넣을 수 있는 링크가 한정적이라 길이를 늘려줬으면 좋겠다는 요구사항이 들어왔습니다.
동료 서버 엔지니어분께서 기획전 테이블의 link_url 컬럼의 길이가 기존에 31자까지만 저장할 수 있어서 255자까지 늘리는 DDL 작업을 요청주셨습니다.
ALTER TABLE `product_exhibition`
MODIFY link_url VARCHAR(255),
ALGORITHM = INPLACE,
LOCK = NONE;해당 쿼리를 보고 든 생각은 '이 쿼리 실행이 안 되겠는데?'였습니다. 저도 예전에 동일한 작업을 하다가 이분과 동일한 실수를 했어서 바로 알아차릴 수 있었습니다.
왜 실행이 안 되나요?
위 쿼리에서 문제가 되는 부분은 바로 ALGORITHM = INPLACE 입니다.
mysql 공식문서를 살펴보면 다음과 같은 구문이 명시되어 있습니다.
InnoDB에서 ALGORITHM = INPLACE를 설정하고 VARCHAR 컬럼의 사이즈를 바꿀 때는 문자를 나타내는데 필요한 바이트 수가 동일한 경우에만 가능합니다.
그리고 VARCHAR 컬럼은 사이즈가 0~255바이트 사이라면 문자를 나타내는데 1바이트가 필요하고, 256 이상이라면 2바이트가 필요합니다.
근데 VARCHAR 31, 255 다 0~255 사이 아닌가요?
테이블을 정의할 때 사용하는 31, 255 같은 숫자는 저장할 수 있는 최대 문자의 수를 의미합니다. 따라서, 컬럼의 최대 바이트 수가 얼마가 되는지는 따로 계산을 해줘야 합니다.
이때 고려해야 할 것이 테이블에 설정된 character set입니다. 보통 대한민국에서 서비스를 하는 경우 한글 및 이모티콘을 저장하기 위해서 CHARSET=utf8mb4 을 지정하는 경우가 많습니다.
이 경우 한 문자당 최대 4바이트가 필요하기 때문에 VARCHAR(64)를 하는 경우 최대 256바이트가 필요하게 됩니다.
따라서 VARCHAR(63) 이하에서 VARCHAR(64) 이상으로 올리거나 VARCHAR(64) 이상에서 VARCHAR(63) 이하로 내리는 작업에는 ALGORITHM = INPLACE를 설정할 수 없는 것입니다.
그럼 ALGORITHM = INPLACE 안 쓰고 돌리면 되겠네요?
이때는 ALGORITHM = COPY 방식으로 변경이 되는데 DDL이 돌아가는 동안 테이블에 락이 잡혀 DML이 실패하게 됩니다. 따라서 락이 오래 잡히고 있다면 서비스 전반적으로 영향을 주게 됩니다.
따라서, 크게 다음과 같은 해결책 중 하나를 선택해서 문제를 해결하는 경우가 많습니다.
1. 테이블 락이 잡혀도 큰 문제가 일어나지 않을 시간대(예: 트래픽이 적은 새벽) 혹은 시스템 점검시간을 이용해 DDL을 실행한다.
2. online-ddl 대신 pt-osc 같은 툴을 이용해 DDL을 실행한다.
3. ALGORITHM = INPLACE를 사용할 수 있도록 VARCHAR의 문자 수를 줄인다.
인사이트
- mysql/mariadb InnoDB에서 VARCHAR 글자수를 변경할 때
ALGORITHM = INPLACE를 사용할 수 없는 케이스가 있습니다. - 테이블을 정의할 때 VARCHAR 컬럼에 적는 숫자는 최대로 저장할 수 있는 문자 수를 의미한다. 따라서 최대 바이트 수를 구하기 위해서는 별도의 계산이 필요합니다.
ALGORITHM = COPY로 online-ddl을 실행하면 테이블에 락이 잡히니 실행할 때 주의해야 합니다.
참고한 글
