Movatterモバイル変換

You can configure theMERGE_THRESHOLD value for index pages. If the“page-full” percentage for an index page falls below theMERGE_THRESHOLD value when a row is deleted or when a row is shortened by anUPDATE operation,InnoDB attempts to merge the index page with a neighboring index page. The defaultMERGE_THRESHOLD value is 50, which is the previously hardcoded value. The minimumMERGE_THRESHOLD value is 1 and the maximum value is 50.

When the“page-full” percentage for an index page falls below 50%, which is the defaultMERGE_THRESHOLD setting,InnoDB attempts to merge the index page with a neighboring page. If both pages are close to 50% full, a page split can occur soon after the pages are merged. If this merge-split behavior occurs frequently, it can have an adverse affect on performance. To avoid frequent merge-splits, you can lower theMERGE_THRESHOLD value so thatInnoDB attempts page merges at a lower“page-full” percentage. Merging pages at a lower page-full percentage leaves more room in index pages and helps reduce merge-split behavior.

TheMERGE_THRESHOLD for index pages can be defined for a table or for individual indexes. AMERGE_THRESHOLD value defined for an individual index takes priority over aMERGE_THRESHOLD value defined for the table. If undefined, theMERGE_THRESHOLD value defaults to 50.

Setting MERGE_THRESHOLD for a Table

You can set theMERGE_THRESHOLD value for a table using thetable_optionCOMMENT clause of theCREATE TABLE statement. For example:

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

You can also set theMERGE_THRESHOLD value for an existing table using thetable_optionCOMMENT clause withALTER TABLE:

CREATE TABLE t1 (   id INT,  KEY id_index (id));ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

Setting MERGE_THRESHOLD for Individual Indexes

To set theMERGE_THRESHOLD value for an individual index, you can use theindex_optionCOMMENT clause withCREATE TABLE,ALTER TABLE, orCREATE INDEX, as shown in the following examples:

Querying the MERGE_THRESHOLD Value for an Index

The currentMERGE_THRESHOLD value for an index can be obtained by querying theINNODB_INDEXES table. For example:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='id_index' \G*************************** 1. row ***************************       INDEX_ID: 91           NAME: id_index       TABLE_ID: 68           TYPE: 0       N_FIELDS: 1        PAGE_NO: 4          SPACE: 57MERGE_THRESHOLD: 40

You can useSHOW CREATE TABLE to view theMERGE_THRESHOLD value for a table, if explicitly defined using thetable_optionCOMMENT clause:

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

Likewise, you can useSHOW INDEX to view theMERGE_THRESHOLD value for an index, if explicitly defined using theindex_optionCOMMENT clause:

mysql> SHOW INDEX FROM t2 \G*************************** 1. row ***************************        Table: t2   Non_unique: 1     Key_name: id_index Seq_in_index: 1  Column_name: id    Collation: A  Cardinality: 0     Sub_part: NULL       Packed: NULL         Null: YES   Index_type: BTREE      Comment:Index_comment: MERGE_THRESHOLD=40

Measuring the Effect of MERGE_THRESHOLD Settings

TheINNODB_METRICS table provides two counters that can be used to measure the effect of aMERGE_THRESHOLD setting on index page merges.

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS       WHERE NAME like '%index_page_merge%';+-----------------------------+----------------------------------------+| NAME                        | COMMENT                                |+-----------------------------+----------------------------------------+| index_page_merge_attempts   | Number of index page merge attempts    || index_page_merge_successful | Number of successful index page merges |+-----------------------------+----------------------------------------+

When lowering theMERGE_THRESHOLD value, the objectives are:

AMERGE_THRESHOLD setting that is too small could result in large data files due to an excessive amount of empty page space.

For information about usingINNODB_METRICS counters, seeSection 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.