MySQL 8.0 Reference Manual（讀書筆記77節--Merge Threshold for Index Pages & Automatic Configuration for a Dedicated MySQL Server）

东山絮柳仔發表於2024-03-27
MySql筆記IndexServer

1. 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【預設值是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.--【可以針對表級進行設定】

1.1 Setting MERGE_THRESHOLD for a Table--針對表

針對表,進行設定,可以透過以下方式

建立表時

CREATE TABLE t1 (
 id INT,
 KEY id_index (id)
) COMMENT='MERGE_THRESHOLD=45';

也可以,透過修改表的方式

CREATE TABLE t1 (
 id INT,
 KEY id_index (id)
);
ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

1.2 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:

建立表時,就指定了index屬性

CREATE TABLE t1 (
 id INT,
 KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
);

修改表

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';

甚至可以是修改 索引

CREATE 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.

1.3 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.

SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='id_index' \G

You can use SHOW CREATE TABLE【也可以透過這個檢視】 to view the MERGE_THRESHOLD value for a table, if explicitly defined using the table_option COMMENT clause.

注意: 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.【表中index有定義；同時表也可以有定義】

Likewise, you can use SHOW INDEX to view the MERGE_THRESHOLD value for an index, if explicitly defined using the index_option COMMENT clause.--

【也可以檢視指定的Index的定義值】

1.4 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.--【透過這個系統表去檢視】

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

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.

2. Enabling Automatic Configuration for a Dedicated MySQL Server

When innodb_dedicated_server is enabled, InnoDB automatically configures the following variables:

• innodb_buffer_pool_size

• innodb_redo_log_capacity or, prior to MySQL 8.0.30, innodb_log_file_size and innodb_log_files_in_group.

• innodb_flush_method

Only consider enabling innodb_dedicated_server if the MySQL instance resides【rɪˈzaɪdz 居住在;定居於】 on a dedicated server where it can use all available system resources. For example, consider enabling innodb_dedicated_server if you run MySQL Server in a Docker container or dedicated VM that only runs MySQL. Enabling innodb_dedicated_server is not recommended if the MySQL instance shares system resources with other applications.

If an automatically configured option is configured explicitly in an option file or elsewhere, the explicitly specified setting is used, and a startup warning similar to this is printed to stderr:

[Warning] [000000] InnoDB: Option innodb_dedicated_server is ignored for
innodb_buffer_pool_size because innodb_buffer_pool_size=134217728 is specified
explicitly.

Explicit configuration of one option does not prevent the automatic configuration of other options.

If innodb_dedicated_server is enabled and innodb_buffer_pool_size is configured explicitly, variables configured based on buffer pool size use the buffer pool size value calculated according to the amount of memory detected on the server rather than the explicitly defined buffer pool size value.

Automatically configured settings are evaluated and reconfigured if necessary each time the MySQL server is started.

--https://dev.mysql.com/doc/refman/8.0/en/index-page-merge-threshold.html

--https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

相關文章