Documentation Home
MySQL 8.0 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 43.3Mb
PDF (A4) - 43.4Mb
Man Pages (TGZ) - 296.6Kb
Man Pages (Zip) - 401.9Kb
Info (Gzip) - 4.3Mb
Info (Zip) - 4.3Mb
Excerpts from this Manual

MySQL 8.0 Reference Manual  /  ...  /  Configuring the Merge Threshold for Index Pages

17.8.11 Configuring the Merge Threshold for Index Pages

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 copy
CREATE 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 copy
CREATE 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 using CREATE TABLE:

    Press CTRL+C to copy
    CREATE TABLE t1 ( id INT, KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40' );
  • Setting MERGE_THRESHOLD for an individual index using ALTER TABLE:

    Press CTRL+C to copy
    CREATE 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 using CREATE INDEX:

    Press CTRL+C to copy
    CREATE TABLE t1 (id INT); CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
Note

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 copy
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: 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 copy
mysql> 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
Note

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 copy
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

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 copy
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 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”.