Rechercher dans le manuel MySQL
15.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:
You can also set the MERGE_THRESHOLD
value for
an existing table using the
table_option
COMMENT
clause with ALTER TABLE
:
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
:Setting
MERGE_THRESHOLD
for an individual index usingALTER TABLE
:Setting
MERGE_THRESHOLD
for an individual index usingCREATE INDEX
:
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:
You can use SHOW CREATE TABLE
to
view the MERGE_THRESHOLD
value for a table, if
explicitly defined using the
table_option
COMMENT
clause:
- *************************** 1. row ***************************
- Table: t2
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:
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.
- +-----------------------------+----------------------------------------+
- +-----------------------------+----------------------------------------+
- +-----------------------------+----------------------------------------+
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 15.14.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-index-page-merge-threshold.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.