You can configure the MERGE_THRESHOLD
value for
index pages. If the “page-full” percentage for an
index page falls below the MERGE_THRESHOLD
value when a row is deleted or when a row is shortened by an
UPDATE
operation,
InnoDB
attempts to merge the index page with a
neighboring index page. The default
MERGE_THRESHOLD
value is 50, which is the
previously hardcoded value. The minimum
MERGE_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 default
MERGE_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 the MERGE_THRESHOLD
value so that
InnoDB
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.
The MERGE_THRESHOLD
for index pages can be
defined for a table or for individual indexes. A
MERGE_THRESHOLD
value defined for an individual
index takes priority over a MERGE_THRESHOLD
value defined for the table. If undefined, the
MERGE_THRESHOLD
value defaults to 50.
Setting MERGE_THRESHOLD for a Table
You can set the MERGE_THRESHOLD
value for a
table using the table_option
COMMENT
clause of the
CREATE TABLE
statement. For
example:
Press CTRL+C to copyCREATE TABLE t1 ( id INT, KEY id_index (id) ) COMMENT='MERGE_THRESHOLD=45';
You can also set the MERGE_THRESHOLD
value for
an existing table using the
table_option
COMMENT
clause with ALTER TABLE
:
Press CTRL+C to copyCREATE TABLE t1 ( id INT, KEY id_index (id) ); ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';
Setting MERGE_THRESHOLD for Individual Indexes
To set the MERGE_THRESHOLD
value for an
individual index, you can use the
index_option
COMMENT
clause with CREATE TABLE
,
ALTER TABLE
, or
CREATE INDEX
, as shown in the
following examples:
Setting
MERGE_THRESHOLD
for an individual index usingCREATE TABLE
:Press CTRL+C to copyCREATE TABLE t1 ( id INT, KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40' );
Setting
MERGE_THRESHOLD
for an individual index usingALTER TABLE
:Press CTRL+C to copyCREATE TABLE t1 ( id INT, KEY id_index (id) ); ALTER TABLE t1 DROP KEY id_index; ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';
Setting
MERGE_THRESHOLD
for an individual index usingCREATE INDEX
:Press CTRL+C to copyCREATE TABLE t1 (id INT); CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
You cannot modify the MERGE_THRESHOLD
value
at the index level for GEN_CLUST_INDEX
, which
is the clustered index created by InnoDB
when
an InnoDB
table is created without a primary
key or unique key index. You can only modify the
MERGE_THRESHOLD
value for
GEN_CLUST_INDEX
by setting
MERGE_THRESHOLD
for the table.
Querying the MERGE_THRESHOLD Value for an Index
The current MERGE_THRESHOLD
value for an index
can be obtained by querying the
INNODB_INDEXES
table. For example:
Press CTRL+C to copymysql> 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: 57 MERGE_THRESHOLD: 40
You can use SHOW CREATE TABLE
to
view the MERGE_THRESHOLD
value for a table, if
explicitly defined using the
table_option
COMMENT
clause:
Press CTRL+C to copymysql> SHOW CREATE TABLE t2 \G *************************** 1. row *************************** Table: t2 Create 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
A MERGE_THRESHOLD
value defined at the index
level takes priority over a MERGE_THRESHOLD
value defined for the table. If undefined,
MERGE_THRESHOLD
defaults to 50%
(MERGE_THRESHOLD=50
, which is the previously
hardcoded value.
Likewise, you can use SHOW INDEX
to
view the MERGE_THRESHOLD
value for an index, if
explicitly defined using the
index_option
COMMENT
clause:
Press CTRL+C to copymysql> 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
The INNODB_METRICS
table provides two
counters that can be used to measure the effect of a
MERGE_THRESHOLD
setting on index page merges.
Press CTRL+C to copymysql> 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 the MERGE_THRESHOLD
value, the
objectives are:
A smaller number of page merge attempts and successful page merges
A similar number of page merge attempts and successful page merges
A MERGE_THRESHOLD
setting that is too small
could result in large data files due to an excessive amount of
empty page space.
For information about using
INNODB_METRICS
counters, see
Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.