MySQL 8.0 Reference Manual(读书笔记77节--Merge Threshold for Index Pages & Automatic Configuration for a Dedicated MySQL Server)

这篇具有很好参考价值的文章主要介绍了MySQL 8.0 Reference Manual(读书笔记77节--Merge Threshold for Index Pages & Automatic Configuration for a Dedicated MySQL Server)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

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文章来源地址https://www.toymoban.com/news/detail-843830.html

到了这里,关于MySQL 8.0 Reference Manual(读书笔记77节--Merge Threshold for Index Pages & Automatic Configuration for a Dedicated MySQL Server)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

原文地址:https://www.cnblogs.com/xuliuzai/p/18099340

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL 8.0 Reference Manual(读书笔记34节-- 字符编码(1))

    MySQL includes character【ˈkærəktər 字母,符号;】 set support that enables you to store data using a variety【vəˈraɪəti (同一事物的)不同种类,多种式样; 变化; (植物、语言等的)变种,变体; 多样化; 综艺节目; 品种; 多变性; 异体; 】 of character sets and perform comparisons【kəmˈpɛrəsənz 比较; 对比

    2024年04月13日
    浏览(5)
  • MySQL 8.0 Reference Manual(读书笔记67节--Phantom Rows)

    The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times. For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a “phantom” row. Suppose that there is an index on the id column of the child table and that you wa

    2024年03月23日
    浏览(12)
  • MySQL 8.0 Reference Manual(读书笔记69节--InnoDB Startup Configuration)

    有些InnoDB的配置,是在实例初始化时,就决定了,所以,建议写在configuration file 文件中。 Because MySQL uses data file, log file, and page size settings to initialize InnoDB, it is recommended that you define these settings in an option file that MySQL reads at startup, prior to initializing InnoDB. Normally, InnoDB is initialize

    2024年03月24日
    浏览(15)
  • MySQL 8.0 Reference Manual(读书笔记80节-- InnoDB Row Formats)

    The row format of a table determines how its rows are physically stored, which in turn can affect the performance of queries and DML operations. As more rows fit into a single disk page, queries and index lookups can work faster, less cache memory is required in the buffer pool, and less I/O is required to write out updated values. The data in each table is

    2024年04月08日
    浏览(13)
  • MySQL 8.0 Reference Manual(读书笔记66节--locking read 与lock)

    --This is the MySQL Reference Manual. It documents MySQL 8.0 through 8.0.34, as well as NDB Cluster releases based on version 8.0 of NDB through 8.0.34-ndb-8.0.34, respectively. It may include documentation of features of MySQL versions that have not yet been released. (看的这个版本的官方文档) 提出背景 (或者说为了解决啥问题)-

    2024年03月23日
    浏览(12)
  • MySQL 8.0 Reference Manual(读书笔记70节--InnoDB Buffer Pool Configuration)

    When increasing or decreasing innodb_buffer_pool_size , the operation is performed in chunks. Chunk size is defined by the innodb_buffer_pool_chunk_size configuration option, which has a default of 128M.-----innodb_buffer_pool_size的扩容和缩容,都是以innodb_buffer_pool_chunk_size为单位进行的,其默认为128M 。 Buffer pool size must always

    2024年03月24日
    浏览(18)
  • MySQL 8.0 Reference Manual(读书笔记20节-- NULL+模式匹配+外键+自增属性)

    The NULL value can be surprising until you get used to it. Conceptually【kənˈsɛptʃuəli 概念; 观念上; 概念上; 在概念上; 概念地; 】, NULL means “a missing unknown value” and it is treated somewhat differently from other values. To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here:  You cannot use arithmetic comp

    2024年04月11日
    浏览(2)
  • MySQL 8.0 Reference Manual(读书笔记84节-- InnoDB and Online DDL (4))

    Disk space requirements for online DDL operations are outlined【ˈaʊtlaɪnd 概述;略述;显示…的轮廓;勾勒…的外形;】 below. The requirements do not apply to operations that are performed instantly. • Temporary log files: A temporary log file records concurrent DML when an online DDL operation creates an index or alters a table. The tempora

    2024年04月08日
    浏览(10)
  • MySQL 8.0 Reference Manual(读书笔记75节--Optimizer Statistics for InnoDB (1))

    This section describes how to configure persistent and non-persistent optimizer statistics for InnoDB tables. Persistent optimizer statistics are persisted across server restarts【意思是重启操作,对这些数据没有影响】, allowing for greater plan stability and more consistent query performance. Persistent optimizer statistics also provide con

    2024年03月27日
    浏览(9)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包