Last Updated on 9월 27, 2023 by Jade(정현호)
안녕하세요
이번 포스팅에서는 MySQL 8.0 버전에서 추가된 LOB 과 JSON에 대한 Partial Update(부분 업데이트) 관련된 내용을 확인해보려고 합니다.
Contents [숨기기]
JSON 값의 부분 업데이트
MySQL 8.0 은 JSON 값의 부분 업데이트(Partial update of JSON values) 를 도입했습니다. 이는 큰 JSON 문서의 작은 부분을 자주 업데이트하는 애플리케이션에 대한 성능 향상을 목적으로 하고 있습니다.
이전의 MySQL 5.7 버전에서는 저장된 JSON 문서를 변경할 때마다 업데이트가 문서에서 몇 바이트만 변경하더라도 새로운 전체 JSON 문서가 데이터베이스에 기록되게 되는 방식이었습니다. 즉 부분 업데이트가 아닌 전체 내용에 대한 갱신이 수행되었습니다.
MySQL 8.0의 새로운 점은 특정 UPDATE 문이 JSON 문서를 IN-PLACE 형태로 수정할 수 있는 기능이 추가된 것입니다.
그리고 이러한 명령문(statement)에 대해 수정 사항을 설명하는 패치를 스토리지 엔진에 제공합니다. 스토리지 엔진은 이러한 패치를 사용하여 최소한의 데이터를 쓸 수 있습니다.
이러한 패치는 적절한 경우 복제 환경에서 Replica(슬레이브)로 전달되는 바이너리 로그의 양을 줄이기 위해 Row Base(행 기반) 복제에서도 사용됩니다.
패치는 적절한 옵션(binlog_row_value_options)이 활성화된 경우 복제 슬레이브에 전달되는 바이너리 로그의 양을 줄이기 위해 행 기반(row-based) 복제에서도 사용됩니다. 그 결과 업데이트 집약적인 워크로드에 대해 디스크 I/O 및 네트워크 I/O가 크게 감소할 수 있습니다.
그 결과 업데이트 집약적 워크로드에 대해 디스크 I/O 및 네트워크 I/O가 크게 감소할 수 있습니다.
How to use it
애플리케이션에서 JSON 값의 부분 업데이트의 장점을 사용하려면 MySQL 8.0으로 업그레이드하면 됩니다. 부분 업데이트를 위한 새로운 구문은 따로 없습니다.
대신 MySQL 은 각 UPDATE 문을 분석하고 사용 가능할 때 기능이 활성화(사용됨)하므로 UPDATE 문이 이미 부분 업데이트(partial update)로 인식되는 형태일 경우 애플리케이션에서 자동으로 JSON의 개선의 이점을 얻을 수 있습니다.
아래의 간단한 예는 JSON 값에서 name 속성을 변경하는 다음 명령문입니다.
UPDATE t SET json_col = JSON_SET(json_col, '$.name', 'Knut') WHERE id = 123
이 업데이트는 JSON 열(json_col)을 가져와서 해당 구성원 중 하나를 수정한 다음 (json_col)에서 읽는 것과 동일한 열에 저장하기 때문에 부분 업데이트(partial update)로 수행할 수 있습니다.
부분 업데이트(partial update) 으로 수행되기 위해서는 업데이트되는 JSON 값에는 이미 name 이라는 멤버가 있어야 하고 JSON 값에 새 이름을 저장할 수 있는 기존 공간이 충분해야 합니다.
업데이트 중인 JSON 값이 {"name": "John", ...}과 같은 경우 스토리지 엔진은 "John" 문자열의 4바이트를 문자열 "Knut"의 4바이트로 덮어쓸 수 있다고 알려줍니다. ", 나머지 값은 그대로 둘 수 있습니다.
다른 유형으로 이름을 "Knut" 으로 변경하려는 과정에서는 업데이트 하려는 대상의 JSON 값이 {"name": "Peter", ...} 와 같이 더 긴 이름이 포함된 경우 업데이트를 부분 업데이트로 계속 수행할 수 있습니다.
이 경우 스토리지 엔진은 "Peter"의 처음 4바이트를 "Knut"으로 덮어쓰고 이름 속성의 길이 바이트를 값 4로 덮어써 새 길이와 일치시키라는 지시를 받습니다.
반면에 업데이트 하려는 JSON 값이 {"name": "Joe", ...}와 같이 업데이트 하려는 값보다 더 짧을 경우(짧은 이름일 경우) 새 이름을 위한 공간이 충분하지 않으며 이럴 경우 업데이트는 전체 업데이트를 수행하는 것으로 대체 수행하게 됩니다.
즉, 전체 새로운 JSON 값을 데이터베이스에 씁니다. 마찬가지로 원래 JSON 값에 이름 속성이 전혀 없는 경우 새 속성을 위한 공간을 만들기 위해 전체 업데이트가 수행됩니다.
부분 업데이트(partial update) 으로 수행되기 위해서는 업데이트되는 JSON 값에는 이미 name 이라는 멤버가 있어야 하고 JSON 값에 새 이름을 저장할 수 있는 기존 공간이 충분해야 합니다.
JSON 부분 업데이트는 JSON_SET 함수에 국한되지 않습니다. JSON_REPLACE 및 JSON_REMOVE 함수도 해당 기능을 함께 사용할 수도 있습니다. 아래 두 UPDATE 문은 JSON 열을 부분 업데이트(partially update) 하려고 시도합니다.
예시)
UPDATE t SET json_col = JSON_SET(json_col, '$.name', UPPER(json_col->>'$.name')),
json_col = JSON_REPLACE(json_col, '$.age', int_col),
json_col = JSON_REMOVE(json_col, '$.address'),
int_col = int_col + 1;
UPDATE t SET json_col = JSON_REPLACE(JSON_REMOVE(json_col, '$.address'),
'$.name', UPPER(json_col->>'$.name'),
'$.age', int_col),
int_col = int_col + 1;
JSON Performance Improve
일부 큰 JSON 값을 업데이트할 때 MySQL 8.0 은 이것이 성능에 어떤 영향을 미치는지 또는 개선이 되는지를 MySQL 5.7 버전과 8.0 버전 각각에서 확인을 해보도록 하겠습니다. 테스트를 위해서 큰 문서가 있는 테이블을 만들어 보겠습니다.
먼저 테이블과 데이터를 입력 하기전에 max_allowed_packet 시스템 변수의 값이 기본값이나 크지 않은 값을 사용한다면 시스템 변수를 증가시켜줍니다.
해당 시스템 변수가 작다면 데이터 입력 과정에서 아래와 같은 에러가 발생할 수도 있습니다.
ERROR 1301 (HY000): Result of repeat() was larger than max_allowed_packet (4194304) - truncated
시스템 변수가 기본값이나 작아서 에러가 발생한다면 시스템 변수를 증가시킵니다.
<mysql 접속> -- 시스템 변수 값 확인 mysql> show variables like 'max_allowed_packet'; -- 시스템 변수 값 변경 mysql> set global max_allowed_packet=1073741824; exit <그 다음 다시 접속>
테이블 과 데이터를 입력합니다.
drop table t;
CREATE TABLE t(id INT PRIMARY KEY AUTO_INCREMENT,
json_col JSON,
name VARCHAR(100) AS (json_col->>'$.name'),
age INT AS (json_col->'$.age'));
INSERT INTO t(json_col) VALUES
(JSON_OBJECT('name', 'Joe', 'age', 24,
'data', REPEAT('x', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Sue', 'age', 32,
'data', REPEAT('y', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Pete', 'age', 40,
'data', REPEAT('z', 10 * 1000 * 1000))),
(JSON_OBJECT('name', 'Jenny', 'age', 27,
'data', REPEAT('w', 10 * 1000 * 1000)));
INSERT INTO t(json_col) SELECT json_col FROM t;
INSERT INTO t(json_col) SELECT json_col FROM t;
데이터를 입력하였다면 MySQL 5.7 버전과 8.0 버전에서 동일하게 update 를 수행해보도록 하겠습니다.
• MySQL 5.7(5.7.33)
mysql> update t set json_col = json_set(json_col, '$.age', age + 1); Query OK, 16 rows affected (4.56 sec) Rows matched: 16 Changed: 16 Warnings: 0
• MySQL 8.0(8.0.23)
mysql> update t set json_col = json_set(json_col, '$.age', age + 1); Query OK, 16 rows affected (1.26 sec) Rows matched: 16 Changed: 16 Warnings: 0
테스트 시스템에서 업데이트를 하는데 5.7 버전에서는 4.56 초, 8.0 버전에서는 1.26초가 소요되었습니다.
(※ 테스트 환경과 시스템 사양에 따라서 테스트 결과 및 차이는 달라집니다)
16개 문서에서 몇 바이트를 업데이트하는 데 5.7 버전에 비해서 빠르긴 하지만 1.26초는 여전히 초고속이 아닙니다.
그 이유는 MySQL 8.0은 기본적으로 복제되지 않은 환경에서도 바이너리 로깅이 활성화되어 있기 때문입니다.
이는 큰 JSON 값의 이전 이미지와 이후 이미지가 모두 바이너리 로그에 기록됨을 의미합니다. 앞서 언급한 것처럼 부분 업데이트가 있을 때 바이너리 로그에 전체 JSON 문서 대신 패치를 포함하는 옵션 이 추가되었습니다. 해당 옵션을 활성화해 보겠습니다.
• 옵션 정보 : binlog_row_value_options
mysql> SET binlog_row_value_options = PARTIAL_JSON; mysql> update t set json_col = json_set(json_col, '$.age', age + 1); Query OK, 16 rows affected (0.64 sec)
참고한 원문에서는 binlog_row_image 까지 MINIMAL 으로 설정하고 진행하였으나 실제로는 FULL 을 주로 사용하기 때문에 binlog_row_image 는 변경하지 않았습니다. binlog_row_image 까지 MINIMAL 로 조정하면 조금 더 빠르게 수행되긴 합니다.
MySQL 5.7 버전에 비해 부분 업데이트에 빨라진 JSON 업데이트가 추가된 시스템 변수 binlog_row_value_options 설정에 의해서 소요시간이 더 많이 줄어 들게 되었습니다.
MySQL 8.0은 업데이트가 JSON_SET, JSON_REPLACE 및 JSON_REMOVE 함수를 사용하여 큰 JSON 문서의 작은 부분을 변경하는 경우 JSON 값의 일부 업데이트 속도를 높입니다.
바이너리 로깅이 활성화된 경우 업데이트 성능을 더욱 향상시키기 위해 바이너리 로그에 JSON 값의 변경된 부분만 포함하도록 하는 옵션을 사용하는 것이 좋을 것이라고 생각 합니다.
더 빠른 업데이트를 위한 LOB 인덱스 도입
JSON 문서에 대한 새로운 기능인 부분 업데이트(partial update) 를 지원하기 위해 InnoDB는 MySQL 8.0에서 대형 객체(LOB)를 저장하는 방식을 변경하게 되었습니다.
MySQL 의 InnoDB 에는 별도의 JSON을 위한 데이터 타입이 없고 JSON 문서는 대용량 객체(large objects (LOBs))로 저장을 하게 됩니다.
이러한 의미는 InnoDB 수준에서 모든 BLOB 또는 TEXT 컬럼에 대해서도 부분 업데이트가 지원됨을 의미합니다.
그러나 현재로서는(포스팅 하는 시점) BLOB 또는 TEXT 열에 대해 일반적으로 액세스할 수 있는 구문이(syntax) 없기 때문에 사용자는 아직 이를 활용할 수는 없습니다.
현재 이 기능(부분 업데이트)은 json_set() 및 json_replace() ,json_remove() 함수를 통해 JSON 문서에만 사용할 수 있습니다.
압축되지 않은 LOB의 이전 형식
이번 섹션에서는 이전 LOB 형식을 간략하게 살펴보겠습니다. 다음 그림을 참조하십시오.
[InnoDB Introduces LOB Index For Faster Updates]
위의 그림은 InnoDB에 있는 BLOB의 이전 저장 형식을 그림으로 보여줍니다.
클러스터형 인덱스 레코드에는 0, 1 또는 그 이상의 LOB 참조가 포함됩니다. 각 LOB 참조는 LOB의 첫 번째 페이지를 가리킵니다.
LOB의 첫 번째 페이지에는 LOB의 두 번째 페이지에 대한 참조가 포함되고 LOB의 두 번째 페이지에는 LOB의 세 번째 페이지에 대한 참조가 포함됩니다.
이것은 LOB 데이터 페이지의 단일 연결 목록(singly linked list)을 형성합니다.
이러한 LOB 데이터 페이지의 페이지 유형은 FIL_PAGE_TYPE_BLOB 입니다. 전체 LOB는 단일 유형의 데이터 페이지로 구성됩니다.
이 형식의 한계(limitation)는 LOB 내의 다양한 오프셋이 순차적으로만 액세스되어야 한다는 것이었습니다.
예를 들어, LOB의 세 번째 페이지에 속하는 LOB 부분에 액세스해야 하는 경우 이 부분은 먼저 LOB의 첫 번째 페이지에 액세스한 다음 LOB의 두 번째 페이지를 방문한 다음 LOB의 두 번째 페이지 를 방문해야만 세 번째 페이지를 액세스할 수 있습니다.
물론, 단일 LOB 데이터 페이지 내의 다양한 오프셋은 무작위로 액세스할 수 있지만 이것만으로는 충분하지 않습니다. LOB가 20페이지를 포함하는 더 큰 경우 LOB의 마지막 페이지에서 작은 부분에 액세스하는 오버헤드가 훨씬 더 높습니다. LOB의 크기가 커질수록 이러한 문제는 계속 악화됩니다.
크기에 관계없이 LOB의 다양한 부분에 무작위로 액세스할 수 있는 보다 효율적인 방법이 필요했습니다. 따라서 LOB의 저장 형식을 변경해야 했습니다.
우리(InnoDB Team)가 한 것은 LOB 인덱스를 도입하여 LOB의 다른 부분에 보다 효율적으로 액세스할 수 있도록 하는 것입니다.
압축되지 않은 LOB의 새로운 형식
이전에는 LOB 데이터 페이지가 데이터 페이지의 단일 연결 목록이었습니다. LOB 페이지를 순차적으로 읽어야만 LOB의 다양한 오프셋에 액세스할 수 있습니다.
새 형식에는 JSON 문서에 대한 임의 액세스를 제공하는 데 도움이 되는 LOB 인덱스가 도입되었습니다.
[InnoDB Introduces LOB Index For Faster Updates]
그림에서 볼 수 있듯이 클러스터형 인덱스 레코드에는 LOB 참조가 포함되어 있습니다.
그러나 LOB 참조는 LOB 첫 번째 페이지(LOB First Page) 라고 하는 새로운 유형의 페이지를 가리키며 거기에서 LOB 인덱스 페이지 목록이 있습니다.
이 두 가지 유형의 페이지는 LOB 인덱스를 구성하고 이 LOB 인덱스는 LOB 데이터 페이지에 대한 참조를 포함합니다.
LOB 인덱스
LOB 인덱스는 LOB에 대한 더 많은 임의 액세스를 제공하기 위해 새로 도입되었습니다.
LOB 인덱스는 파일 기반 목록 유틸리티 flst_base_node_t 및 flst_node_t 를 사용하여 구현된 이중 연결 목록(doubly linked list) 입니다.
LOB 인덱스의 각 항목은 하나의 LOB 데이터 페이지를 나타내는 파일 목록 노드입니다. LOB 인덱스 항목에는 다음 정보가 포함됩니다.
이전 인덱스 항목에 대한 포인터입니다.
다음 인덱스 항목에 대한 포인터입니다.
이 인덱스 항목에 대한 이전 버전 목록에 대한 포인터입니다.
작성자 트랜잭션 식별자입니다.
수정자 트랜잭션 식별자
작성자 트랜잭션의 실행 취소 번호입니다.
수정자 트랜잭션의 실행 취소 번호입니다.
LOB 데이터 페이지의 페이지 번호입니다.
바이트 단위로 포함된 LOB 데이터의 양입니다.
이 인덱스 항목이 속한 LOB 버전 번호입니다.
완전성을 위해 단일 LOB 인덱스 항목의 모든 내용을 나열했습니다.
해당 포스팅에서는 다음 인덱스 항목에 대한 포인터, LOB 데이터 페이지의 페이지 번호 및 포함된 LOB 데이터의 양만 살펴봅니다.
[InnoDB Introduces LOB Index For Faster Updates]
위의 이미지의 예시에서 총 6페이지가 사용됩니다. 총 LOB 인덱스 항목 수는 6입니다.
FIL_PAGE_TYPE_LOB_FIRST 유형의 1개 LOB 첫 번째 페이지와 FIL_PAGE_TYPE_LOB_DATA 유형의 5개 LOB 데이터 페이지가 있습니다.
이 예에서는 LOB가 충분히 크지 않기 때문에 FIL_PAGE_TYPE_LOB_INDEX의 페이지 유형이 사용되지 않습니다.
명시적인 오프셋 정보는 LOB 인덱스에 저장되지 않지만 각 LOB 데이터 페이지에 저장된 데이터 양을 기반으로 계산할 수 있습니다.
LOB 인덱스 항목의 순서는 중요합니다. 첫 번째 인덱스 항목이 나타내는 데이터는 전체 LOB에서 맨 처음에 오고 두 번째 인덱스 항목이 나타내는 데이터는 첫 번째 항목 바로 다음에 오는 식입니다.
새 페이지 유형
이제 LOB에 3가지 다른 페이지 유형이 있습니다. 상세한 설명은 아래에 기술되어 있습니다.
• FIL_PAGE_TYPE_LOB_FIRST : 압축되지 않은 LOB의 첫 번째 페이지입니다. 여기에는 LOB 인덱스 항목과 LOB 데이터가 모두 포함됩니다. 클러스터형 인덱스 레코드에 저장된 LOB 참조에 언급된 페이지 번호입니다. 첫 페이지의 다음 페이지(FIL_PAGE_NEXT)는 LOB 인덱스 페이지이다.
• FIL_PAGE_TYPE_LOB_INDEX : 이러한 LOB 페이지는 인덱스 항목만 보유합니다. 여기에는 가능한 한 많은 인덱스 항목이 포함됩니다. 이 모든 인덱스 페이지는 FIL_PAGE_NEXT 페이지 헤더 필드를 통해 서로 연결됩니다.
• FIL_PAGE_TYPE_LOB_DATA : LOB 데이터를 보유하는 페이지입니다. 각 LOB 데이터 페이지는 인덱스 항목으로 가리킵니다. LOB 데이터 페이지의 수는 LOB 인덱스의 항목 수와 같습니다.
Goal Was Performance
이러한 LOB 저장소 형식 변경을 수행하는 전체적인 목적은 json 문서 기능의 부분 업데이트를 지원하는 것이었습니다. 이는 모두 성능에 관한 것입니다.
이전에는 JSON 문서의 작은 부분이 JSON_SET() 또는 JSON_REPLACE()로 업데이트되었을 때 전체 JSON 문서가 수정되고 다시 실행되고 bin이 기록되었습니다. 그러나 이 기능을 사용하면 redo 로그 및 bin 로그 생성을 줄여 전체 작업을 훨씬 빠르게 수행할 수 있습니다.
이를 달성하기 위해 InnoDB는 LOB 데이터 페이지에 대한 메타 데이터를 포함하는 LOB 인덱스 페이지를 도입했습니다. 이 새로운 저장 형식은 지금 포스팅에서 설명하고 있습니다.
설명할 것이 사실은 훨씬 더 많습니다(특히 LOB의 MVCC) 이에 대해서는 추가로 업데이트 하도록 하겠습니다.
다음은 InnoDB Team의 QA 팀의 내부 시스템의 성능 테스트 보고서입니다.
그들은 별도의 커스텀 된(수정된 내용의) sysbench 를 통해 벤치마크를 하였습니다. JSON 문서 크기는 부분 업데이트 기능의 이점을 보여주기에 충분히 크도록 조정되었습니다.
[InnoDB Introduces LOB Index For Faster Updates]
검은색 선은 기준선이고 빨간색 선은 이 기능으로 인한 성능 향상입니다.
| 스레드 | 기준 코드가 있는 평균 TPS | BLOB 기능의 부분 업데이트가 있는 평균 TPS | 백분율 개선 |
|---|---|---|---|
| 8 | 9007.593 | 45368.317 | 403.67% |
| 16 | 11627.067 | 83082.127 | 614.56% |
| 64 | 10524.580 | 162884.660 | 1,447.66% |
| 128 | 10093.880 | 150841.183 | 1,394.38% |
| 512 | 9576.630 | 106293.767 | 1,009.93% |
개선된 수치는 403.67%에서 1,009.93% 로 확인되고 있습니다.
sysbench는 더 큰 JSON 문서를 사용하도록 수정(커스텀 변경)되었으며, UPDATE 문은 가능할 때마다 JSON_SET() 및 JSON_REPLACE()를 사용하도록 만들어졌습니다.
InnoDB 에서 큰 객체(LOB)의 새로운 저장 형식을 살펴보았습니다. 이 새로운 형식은 JSON 문서의 부분 업데이트(partial update) 기능을 지원하는 데 필요했습니다.
기본 아이디어는 LOB의 다양한 부분에 대한 임의 액세스를 하여 보다 효율적으로 수행하는 데 도움이 되는 각 대형 개체에 대한 LOB 인덱스를 도입하는 것이었습니다.
이번글은 MySQL8.0 버전에서 추가된 LOB 과 JSON 데이터타입에 대한 Partial Update(부분 업데이트) 관련된 확인해보았습니다.
MySQL에서 JSON 데이터 타입을 사용할 경우 조회 시 type conversion(타입 변환) 때문에 보통 TEXT 데이터 타입으로 많이 사용하고 있지만, 사용하는 업무 패턴이나 유형에서 읽기 보다 데이터 변경, 그리고 전제 JSON 문서 중에서 작은 일부분의 갱신을 하는 업무 유형이라면 JSON 데이터 타입 과 JSON Native Function을 사용해서 Partial Update(부분 업데이트) 성능적 장점을 사용해보는 것도 좋을 것이라고 생각됩니다.
긴 글 읽어 주셔서 감사합니다. 이번 글은 여기에서 마무리하도록 하겠습니다.
Reference
Reference URL
• dev.mysql.com/partial-update-of-json-values
• dev.mysql.com/innodb-introduces-lob-index
• dev.mysql.com/binlog_row_value_options
연관된 다른 글
Principal DBA(MySQL, AWS Aurora, Oracle)
핀테크 서비스인 핀다에서 데이터베이스를 운영하고 있어요(at finda.co.kr)
Previous - 당근마켓, 위메프, Oracle Korea ACS / Fedora Kor UserGroup 운영중
Database 외에도 NoSQL , Linux , Python, Cloud, Http/PHP CGI 등에도 관심이 있습니다
purityboy83@gmail.com / admin@hoing.io
잘 읽었습니다! 덕분에 JSON_SET() 이 있다는걸 처음 알았습니다
안녕하세요
도움이 되셨다니 다행이네요
코멘트 감사합니다.
좋은 하루 되세요