sugar DB: [MySQL] 5.7 Online DDL 그리고 varchar 타입의 특성

MySQL 5.7 버전부터 새롭게 지원되는 Online DDL 기능이 있다.

1. Index rename
2. varchar 컬럼 확장

해당 기능이 MariaDB 5.5 / MySQL 5.6 / MySQL 5.7.8 버전에서 각각 어떻게 실행되는지 살펴본다.

먼저 index rename 을 각 버전에서 실행한 결과이다.

-- ===================================================

-- MariaDB 5.5

-- ===================================================

: MariaDB 5.5 는 index rename 명령을 지원하지 않는다. 실행 시 에러가 발생하며,

인덱스명을 변경하려면 drop index, create index 명령으로 수행해야 한다.

mysql> alter table test_tbl rename index idx_test_tbl to idx_test_tbl_normal ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'index idx_test_tbl to idx_test_tbl_normal' at line 1

-- ===================================================

-- MySQL 5.6

-- ===================================================

: MySQL 5.6 은 index rename 명령을 지원하지 않는다. 실행 시 에러가 발생하며,

인덱스명을 변경하려면 drop index, create index 명령으로 수행해야 한다.

mysql> alter table test rename index idx_test to idx_test_rename ;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'index idx_test to idx_test_rename' at line

그러나 MySQL 5.6 에서는 인덱스 추가가 Online 으로 수행이 가능하기 때문에,

-- 1. 변경하고자 하는 인덱스 명으로 새로운 인덱스를 추가한다.

-- 2. old 인덱스를 drop 한다.

이와 같은 순서로도 Online 작업을 유도할 수 있지만, 동일 컬럼으로 인덱스를 중복하여 생성하게 되므로 ( old 인덱스를 drop 하기 전 까지 )

인덱스의 크기가 클 경우, 저장공간의 여유가 있어야 한다.

동일 컬럼으로 인덱스를 생성할 경우 다음과 같은 warning 이 발생한다.

| Note | 1831 | Duplicate index 'idx_xxx' defined on the table 'test.test_tbl'. This is deprecated and will be disallowed in a future release. |

-- ===================================================

-- MySQL 5.7.8-rc

-- ===================================================

MySQL 5.7.8 버전에서는 rename index 명령이 지원되고, INPLACE 방식으로 동작함 ( table copy 하지 않음 )

mysql> select @@version ;
+--------------+
| @@version |
+--------------+
| 5.7.8-rc-log |
+--------------+

mysql> show create table test_tbl \G
*************************** 1. row ***************************
Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_test_tbl` (`id`,`val`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

-- idx_test_tbl 인덱스를 idx_test_tbl_normal 로 변경한다.

mysql> alter table test_tbl rename index idx_test_tbl to idx_test_tbl_normal ;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- 0.01 초 만에 완료된 것을 확인할 수 있다.

mysql> show create table test_tbl \G
*************************** 1. row ***************************
Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(60) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_test_tbl_normal` (`id`,`val`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

두 번째로, MySQL 5.7 버전에서 varchar 컬럼 길이 확장이 online 으로 가능한데 이 부부은 한가지 제약사항이 있다.

* INPLACE ( table copy 하지 않고 ) 로 varchar 타입 컬럼 길이를 확장 할 수 있는 경우

-- 1. VARCHAR 0 ~ 255 bytes 내에서 확장하는 경우
-- 2. VARCHAR 256 ~ bytes 내에서 확장하는 경우

만일 컬럼 크기가 255 byte 이내인 컬럼을 256 bytes 이상으로 변경하려고 하면 Table copy 로 동작한다. 즉 INPLACE 로 동작하지 않는다.

이 제약사항에 대한 내용은 아래에서 설명한다.

* 참고 : 다음 설명부터 컬럼의 "글자 수" 와 "bytes 수" 라는 표현을 주의한다.

MySQL 의 VARCHAR 타입은 괄호 안에 있는 값 - VARCHAR( N ) , N 이 글자 수 이다. byte 수 가 아니다.

예를 들어, character set 이 utf8 인 경우 한 글자의 max byte 가 3byte 로, VARCHAR(255) 는 255 * 3 = 764 byte 까지 저장 가능하다.

위 설명한 제약사항을 바탕으로 하여,

varchar 컬럼 크기를 255 byte 이내의 값으로 변경할 때 online 으로 수행되는지 테스트를 진행해본다.

테스트 MySQL 의 character set 은 utf8mb4 로 max length 가 4byte 이다. 255 byte / 4 = 63 자 이며,

varchar(20) 인 컬럼을 varchar(63) 으로 변경 해본다.

varchar(20) 은 20 * 4byte (utf8mb4) = 80 bytes

varchar(63) 은 63 * 4byte (utf8mb4) = 254 bytes

80 bytes 에서 254 bytes 로 변경하는 것 이므로, 0 ~ 255 byte 이내의 값으로 변경하는 것이다.

이 경우에는 Online 으로 동작한다.

Create Table: CREATE TABLE `test_tbl` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`val` varchar(20) NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_second` (`id`,`val`)

) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4

mysql> alter table test_tbl change column val val varchar(60) not null ;

Query OK, 0 rows affected (4.42 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table test_tbl \G

CREATE TABLE `test_tbl` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`val` varchar(60) NOT NULL,

PRIMARY KEY (`id`),

KEY `idx_test_tbl_normal` (`id`,`val`)

) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4

다른 세션에서 DDL 명령을 실행중인 세션 상태를 보면, altering table 로 표시되는 것을 볼 수 있다. ( online - Inplace 로 실행되는 것이다. )

mysql> show processlist ;

+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------------+

+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------------+

+----+------+-----------+------+---------+------+----------------+------------------------------------------------------------------+

실제로 online 으로 적용이 되는지, 아닌지 명령을 실행하기 전에 확인하고자 한다면

ALTER 구문에 ALGORITHM 옵션을 포함하여 실행하면 미리 확인할 수 있다.

참고로 MySQL 5.7 에서도 컬럼 축소를 할 경우엔 online 으로 수행이 불가능하다.

-- 실행 가능할 경우

alter table test_tbl change column val val varchar(60) not null , ALGORITHM=INPLACE ;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

-- 실행 불가능할 경우

alter table test_tbl change column val val varchar(20) not null , ALGORITHM=INPLACE ;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

위 테스트에서는 varchar 컬럼 크기 변경을 0 ~ 255 byte 범위 내에서 변경한 것이다.

다음은 0 ~ 255 byte 범위에서 변경이 아닌,

varchar(20) -> varchar(190) 으로 컬럼 사이즈를 변경해본다.

character set 은 utf8mb4 를 기준으로 한다.

varchar(20) : 20 * 4 = 80 byte

varchar(190) : 190 * 4 = 760 byte 로

실제로는 80 byte -> 760 byte 로 컬럼 크기를 변경하는 것이다.

-- 다음과 같이 ALGORITHM 옵션을 함께 실행하면 INPLACE ( Online ) 으로 실행 불가능 한 것을 확인할 수 있다.

mysql> alter table test_tbl change column val val varchar(190) NOT NULL , ALGORITHM=INPLACE ;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

-- 실제로 명령을 실행하면, thread status 가 copy to tmp table 임을 확인할 수 있다.

-- 이 DDL 구문이 실행중일 때 다른 스레드에서는 DML 명령 실행시, DDL 구문이 완료할 때 까지 대기하게 된다.

mysql> alter table test_tbl change column val val varchar(190) NOT NULL ;

Query OK, 2135808 rows affected (29.58 sec) <-- copy to tmp table 로 수행될 경우, rows affected 가 전체 테이블 row 로 나타난다.

Records: 2135808 Duplicates: 0 Warnings: 0

-- 다른 세션에서 DDL 구문을 실행중인 세션의 상태를 확인하면 copy to tmp table 로 나타난다.

mysql> show full processlist ;

+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+------------------------------------------------------------------+

그러면 256 bytes 이상의 범위에서 컬럼 사이즈를 변경하면 어떻게 될까 ?

현재 varchar(190) 인 컬럼의 bytes 수는 760 이다. ( 190 * 4bytes = 760 bytes )

이 컬럼을 varchar(255) 로 변경해보자.

character set 은 utf8mb4 를 기준으로 한다.

varchar(190) : 190 * 4 = 760 bytes

varchar(255) : 255 * 4 = 1020 bytes

760 bytes 를 1020 bytes 로 변경한다.

-- ALGORITM 옵션으로 Online 동작 여부를 확인한다.
mysql> alter table test_tbl change column val val varchar(255) NOT NULL , ALGORITHM=INPLACE ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- Online으로 동작하는 것을 확인하고 실제로 명령을 실행해본다.
mysql> alter table test_tbl change column val val varchar(255) NOT NULL ;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

-- 0.00 초 내로 완료된 것을 확인할 수 있다.
mysql> show create table test_tbl \G
*************************** 1. row ***************************
Table: test_tbl
Create Table: CREATE TABLE `test_tbl` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`val` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2228434 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

-- ===================================================

-- varchar 타입의 특성

-- ===================================================

varchar 컬럼 확장이 0 ~ 255 byte 이내 / 256 byte 이상 범위에서만 각각 in-place 로 동작하는 varchar 타입의 특성 때문이다.

varchar 는 가변길이 문자열을 저장하는 타입으로,

문자열 저장 시, 맨 앞의 1 또는 2 byte 를 컬럼에 저장된 문자열의 byte 길이를 나타낸다.

여기서 1 또는 2 bytes 에 문자열 byte 길이를 저장할 때는 2 진수로 저장한다.

1 byte = 8 bit 로 이진수 여덟자리를 저장할 수 있다.

위에서 설명한 바와 같이, VARCHAR 의 괄호 안의 값은 저장 가능한 문자열 갯수이다.

만일 varchar(255) 의 경우 255 자 문자열을 저장할 수 있다는 것을 의미한다. 저장 가능한 byte 수는 character set 마다 값이 다르다.

( ex. utf8 은 max length 가 3byte 이므로, 255 개 문자열 * 3byte = 756 bytes )

이 때, 255 자가 들어있다는 것을, varchar 저장 시 맨 앞의 1 byte 에 2진수로 표현하면

10진수 255 = 2진수 11111111 이다.

그런데, 256 자가 저장되어있다는 것을 표현하려면 2진수 여덟자리로는 표현이 불가능하다.

10진수 255 = 2진수 1 0000 0000

결과적으로 2진수로 256 개의 문자열이 저장되어있다는 것을 저장하려면 2byte 가 필요하다.

2진수 16자리로 표현해야 한다는 것이다. ( 0000 0001 0000 0000 )

그래서 varchar(255) 를 varchar(256) 으로 변경을 하려고 하면, 맨 앞의 문자열 길이를 저장하는 바이트 수를 1byte 에서 2byte 로 변경해야한다.

이러한 문제로 0 ~ 255 자 / 256 자 이상 각각의 범위 내에서는 online 으로 컬럼 사이즈 변경이 가능하지만

그렇지 않을 경우 맨 앞자리 바이트 수를 늘려줘야 하기 때문에 테이블을 재생성하여, 데이터가 저장된 블록을 재 할당시키는 방식으로 동작한다.

MySQL 5.7 의 online DDL new feature 는 varchar 타입에 한정된 것으로, 다른 타입을 변경하면 table copy 로 동작한다.

MySQL 버전이 올라가면서 Online 기능들도 추가되고 성능적인 부분도 개선되고 있지만,

서비스중에 DDL ( ALTER ) 구문을 실행할 때에는 항상 주의깊게 모니터링을 해야 한다는 점을 명심한다.

-- unsigned 로 변경해도 INPLACE 로 동작 안됨

-- session1

mysql> alter table test_tbl change column id id int(10) unsigned not null auto_increment ;

-- session2

show full processlist ;

+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------------+

-- bigint 로 변경해도 INPLACE 로 동작 안됨

alter table test_tbl change column id id bigint(20) not null auto_increment ;

show full processlist ;

+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------+

+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------------------------+

2 rows in set (0.00 sec)

sugar DB

2015년 10월 22일 목요일

[MySQL] 5.7 Online DDL 그리고 varchar 타입의 특성

댓글 없음:

댓글 쓰기

블로그 보관함