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”.
Traduction non disponible
Le manuel MySQL n'est pas encore traduit en français sur l'infobrol. Seule la version anglaise est disponible pour l'instant.
Document créé le 26/06/2006, dernière modification le 26/10/2018
Source du document imprimé : https://www.gaudry.be/mysql-rf-index-page-merge-threshold.html
L'infobrol est un site personnel dont le contenu n'engage que moi. Le texte est mis à disposition sous licence CreativeCommons(BY-NC-SA). Plus d'info sur les conditions d'utilisation et sur l'auteur.
Références
Ces références et liens indiquent des documents consultés lors de la rédaction de cette page, ou qui peuvent apporter un complément d'information, mais les auteurs de ces sources ne peuvent être tenus responsables du contenu de cette page.
L'auteur de ce site est seul responsable de la manière dont sont présentés ici les différents concepts, et des libertés qui sont prises avec les ouvrages de référence. N'oubliez pas que vous devez croiser les informations de sources multiples afin de diminuer les risques d'erreurs.