17.8.11 Configuring the Merge Threshold for Index Pages

17.8.11 Configuring the Merge Threshold for Index Pages

index page에 대한 MERGE_THRESHOLD 값을 설정할 수 있습니다. index page에 대해 “page-full” 비율이 MERGE_THRESHOLD 값보다 낮아지면, 행이 삭제되거나 UPDATE 연산에 의해 행이 짧아질 때, InnoDB는 해당 index page를 인접한 index page와 병합하려고 시도합니다. 기본 MERGE_THRESHOLD 값은 50이며, 이는 이전에 하드코딩되어 있던 값입니다. MERGE_THRESHOLD의 최소값은 1이고 최대값은 50입니다.

index page에 대한 “page-full” 비율이 기본 MERGE_THRESHOLD 설정값인 50% 아래로 떨어지면, InnoDB는 index page를 인접한 page와 병합하려고 시도합니다. 두 page가 모두 50%에 가까운 비율로 채워져 있을 경우, 병합 후 곧바로 page split이 발생할 수 있습니다. 이러한 merge-split 동작이 자주 발생하면 성능에 악영향을 줄 수 있습니다.

잦은 merge-split을 방지하기 위해, 더 낮은 “page-full” 비율에서 InnoDB가 page 병합을 시도하도록 MERGE_THRESHOLD 값을 낮출 수 있습니다. 더 낮은 page-full 비율에서 page를 병합하면 index page에 더 많은 여유 공간이 남게 되고, merge-split 동작을 줄이는 데 도움이 됩니다.

index page에 대한 MERGE_THRESHOLD는 테이블 단위 또는 개별 index 단위로 정의할 수 있습니다. 개별 index에 대해 정의된 MERGE_THRESHOLD 값은 테이블에 대해 정의된 MERGE_THRESHOLD 값보다 우선합니다. 정의되지 않은 경우 MERGE_THRESHOLD 값은 기본적으로 50입니다.

Table에 대한 MERGE_THRESHOLD 설정

CREATE TABLE statement의 table_optionCOMMENT 절을 사용하여 테이블에 대한 MERGE_THRESHOLD 값을 설정할 수 있습니다. 예를 들면 다음과 같습니다:

1CREATE TABLE t1 (
2   id INT,
3  KEY id_index (id)
4) COMMENT='MERGE_THRESHOLD=45';

또한, 기존 테이블에 대해서도 ALTER TABLE의 table_optionCOMMENT 절을 사용하여 MERGE_THRESHOLD 값을 설정할 수 있습니다:

1CREATE TABLE t1 (
2   id INT,
3  KEY id_index (id)
4);
5
6ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

개별 Index에 대한 MERGE_THRESHOLD 설정

개별 index에 대한 MERGE_THRESHOLD 값을 설정하려면, CREATE TABLE, ALTER TABLE, 또는 CREATE INDEX에서 index_optionCOMMENT 절을 사용할 수 있습니다. 다음 예를 참고하십시오:

• CREATE TABLE을 사용하여 개별 index에 대한 MERGE_THRESHOLD 설정:

1CREATE TABLE t1 (
2     id INT,
3    KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
4);

• ALTER TABLE을 사용하여 개별 index에 대한 MERGE_THRESHOLD 설정:

1CREATE TABLE t1 (
2     id INT,
3    KEY id_index (id)
4);
5
6ALTER TABLE t1 DROP KEY id_index;
7ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';

• CREATE INDEX를 사용하여 개별 index에 대한 MERGE_THRESHOLD 설정:

1CREATE TABLE t1 (id INT);
2CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

참고

GEN_CLUST_INDEX 수준에서는 MERGE_THRESHOLD 값을 수정할 수 없습니다. GEN_CLUST_INDEX는 primary key 또는 unique key index 없이 InnoDB 테이블이 생성될 때 InnoDB가 생성하는 클러스터드 인덱스입니다. GEN_CLUST_INDEX에 대한 MERGE_THRESHOLD 값은 테이블에 대한 MERGE_THRESHOLD를 설정하는 방식으로만 수정할 수 있습니다.

Index에 대한 MERGE_THRESHOLD 값 조회

index에 대한 현재 MERGE_THRESHOLD 값은 INNODB_INDEXES 테이블을 조회하여 얻을 수 있습니다. 예를 들면 다음과 같습니다:

1mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='id_index' \G
2*************************** 1. row ***************************
3       INDEX_ID: 91
4           NAME: id_index
5       TABLE_ID: 68
6           TYPE: 0
7       N_FIELDS: 1
8        PAGE_NO: 4
9          SPACE: 57
10MERGE_THRESHOLD: 40

table_optionCOMMENT 절을 사용하여 명시적으로 정의된 경우, SHOW CREATE TABLE을 사용하여 테이블에 대한 MERGE_THRESHOLD 값을 확인할 수 있습니다:

1mysql> SHOW CREATE TABLE t2 \G
2*************************** 1. row ***************************
3       Table: t2
4Create Table: CREATE TABLE `t2` (
5  `id` int(11) DEFAULT NULL,
6  KEY `id_index` (`id`) COMMENT 'MERGE_THRESHOLD=40'
7) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

참고

index 수준에서 정의된 MERGE_THRESHOLD 값이 테이블에 대해 정의된 MERGE_THRESHOLD 값보다 우선합니다. 정의되지 않은 경우, MERGE_THRESHOLD는 기본값 50%(MERGE_THRESHOLD=50, 이전에 하드코딩된 값)으로 설정됩니다.

마찬가지로, index_optionCOMMENT 절을 사용하여 명시적으로 정의된 경우, SHOW INDEX를 사용하여 index에 대한 MERGE_THRESHOLD 값을 확인할 수 있습니다:

1mysql> SHOW INDEX FROM t2 \G
2*************************** 1. row ***************************
3        Table: t2
4   Non_unique: 1
5     Key_name: id_index
6 Seq_in_index: 1
7  Column_name: id
8    Collation: A
9  Cardinality: 0
10     Sub_part: NULL
11       Packed: NULL
12         Null: YES
13   Index_type: BTREE
14      Comment:
15Index_comment: MERGE_THRESHOLD=40

MERGE_THRESHOLD 설정 효과 측정

INNODB_METRICS 테이블은 index page 병합에 대한 MERGE_THRESHOLD 설정 효과를 측정하는 데 사용할 수 있는 두 개의 카운터를 제공합니다.

1mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS
2       WHERE NAME like '%index_page_merge%';
3+-----------------------------+----------------------------------------+
4| NAME                        | COMMENT                                |
5+-----------------------------+----------------------------------------+
6| index_page_merge_attempts   | Number of index page merge attempts    |
7| index_page_merge_successful | Number of successful index page merges |
8+-----------------------------+----------------------------------------+

MERGE_THRESHOLD 값을 낮출 때의 목표는 다음과 같습니다:

• 더 적은 수의 page 병합 시도 및 성공한 page 병합 횟수

• 유사한 수의 page 병합 시도 및 성공한 page 병합 횟수

너무 작은 MERGE_THRESHOLD 설정값은 과도한 빈 page 공간으로 인해 데이터 파일이 매우 커지는 결과를 초래할 수 있습니다.

INNODB_METRICS 카운터 사용에 대한 정보는 Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”을 참조하십시오.