Understanding MySQL InnoDB MERGE_THRESHOLD: Page Merge and Split Mechanics

MySQL InnoDB data pages or secondary index pages (referred to as data pages or index pages) are merged and split frequently, which significantly influences overall table performance; the more such operations occur, the greater the impact on write throughput.

MySQL provides a MERGE_THRESHOLD parameter that can be manually adjusted in certain scenarios to reduce page merges and splits.

Each InnoDB page defaults to 16KB with a default MERGE_THRESHOLD value of 50 (range 1‑50). When the occupancy of a page falls below 50%, MySQL merges it with an adjacent page to keep pages compact and avoid wasted space.

Trigger scenarios

Scenario 1: After deletions, a page’s occupancy drops below 50%, triggering a merge.

Scenario 2: Updating records to shorter values (e.g., shortening a long string) reduces page occupancy below 50%, also triggering a merge.

Page merge description

If page A’s occupancy falls below the threshold and an adjacent page C also has less than 50% occupancy, MySQL merges C into A, freeing page C for future inserts.

Page split description

When two pages D and E each have 49% occupancy, merging them leaves only 2% free space on the resulting page, causing subsequent inserts to require a split.

Similarly, pages F and H behave the same way.

If a new record’s key falls between pages D and F, but both have only 2% free space, MySQL creates a new page I, redistributes records, and fills the pages, which can be costly if splits happen frequently.

Setting MERGE_THRESHOLD

To set the threshold for an entire table, include it in the table comment, e.g.:

mysql> create table sample1(id int primary key, r1 int, r2 varchar(1000)) comment 'MERGE_THRESHOLD=40';

Or alter an existing table:

mysql> alter table t1 comment 'MERGE_THRESHOLD=40';

For a specific index, set the comment on the index column (which overrides the table setting):

mysql> create table t1(id int, key idx_id(id)) comment 'MERGE_THRESHOLD=40';

Or modify an existing index:

mysql> alter table t1 drop key idx_id, add key idx_id(id) comment 'MERGE_THRESHOLD=40';

Or create a standalone index with the comment:

mysql> create index idx_id on t1(id) comment 'MERGE_THRESHOLD=40';

Viewing MERGE_THRESHOLD

Check the table definition: mysql> show create table sample1\G Check index definition: mysql> show index from t1\G Or query the information_schema.innodb_indexes table:

mysql> select a.name as tablename, b.name as index_name, b.MERGE_THRESHOLD from innodb_tables as a, innodb_indexes as b where a.table_id = b.table_id and a.name like 'ytt%';

Evaluating MERGE_THRESHOLD impact

InnoDB metrics provide counters for page merge attempts and successes, which are disabled by default and must be enabled:

mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';

Enable the counters:

mysql> set global innodb_monitor_enable='index_page_merge_attempts';

Experiment: create two tables, one with default MERGE_THRESHOLD and one with a lower value (e.g., 20), insert 10,000 rows, delete half, and compare merge success counts.

Results showed that the table with a lower threshold had far fewer successful merges (e.g., 2 vs. 20), demonstrating that manually adjusting the threshold can effectively control merge frequency.

Setting the threshold to the minimum value of 1 essentially prevents merges.

Conclusion

The article introduced the concept of MySQL index page merge thresholds, how to configure them for tables and indexes, and how to assess their effect on page merges and splits using InnoDB metrics, providing practical guidance for performance tuning.