MySQL 社区经理：MySQL 8.4 InnoDB 参数默认值为什么要这么改？

最新推荐文章于 2024-09-25 13:25:59 发布

爱可生开源社区

最新推荐文章于 2024-09-25 13:25:59 发布

阅读量1k

点赞数 30

文章标签： mysql oceanbase 数据库

本文链接：https://blog.csdn.net/ActionTech/article/details/138496472

版权

MySQL8.4的第一个长期支持(LTS)版本发布，文章详细介绍了20个InnoDB变量的默认值变更，旨在提升生产环境的稳定性与性能。这些改动包括优化缓存策略、I/O处理和内存管理，以适应现代硬件和工作负载需求。

摘要由CSDN通过智能技术生成

MySQL 8.4 LTS 版本，我们一共修改了 20 个 InnoDB 变量的默认值。

作者：Frederic Descamps，EMEA 和亚太地区的 MySQL 社区经理。于 2016 年 5 月加入 MySQL 社区团队。担任开源和 MySQL 顾问已超过 15 年。最喜欢的主题是高可用和高性能。

本文和封面来源：https://lefred.be，爱可生开源社区翻译。

本文约 2400 字，预计阅读需要 8 分钟。

2024 年 4 月 30 日，MySQL 8.4（第一个 LTS 版）正式发布，也验证了 Oracle 官方在之前宣布的 MySQL 版本发布节奏。

什么是 LTS 版？目前还有哪些版本？

目前，MySQL 的发布模型分为两个主要路径：LTS 版（长期支持）和创新版。所有 LTS 和创新版本都包含错误和安全修复，并被视为生产级质量。更多MySQL 版本介绍

MySQL 发布模型（图片来源：oracle.com）

什么情况适合 LTS 版？

需要稳定的功能和更长的支持期。
除了第一个 LTS 版本删除了一些功能，其他版本仅包含必要的修复，不在删除功能。
LTS 版本遵循 Oracle 终身支持政策（5 年主要支持和 3 年延长支持）。

什么情况适合创新版？

想了解最新功能、改进。适合快节奏开发环境中的开发和 DBA，具有更高水平的自动化测试和现代持续集成技术，可实现更快的升级周期。
除新功能外，随着代码重构、删除不推荐功能以及修改 MySQL 使其更符合 SQL 标准（在 LTS 版本中不会发生）。
支持至下一个创新版。

MySQL 各版本的生命周期

MySQL 各版本生命周期（图片来源：oracle.com）

请参考 Oracle 官方提供的 MySQL 各版本生命周期计划（20240430），以更好地安排您生产环境的 MySQL 版本选择。

以下内容为 MySQL 社区经理 Frederic Descamps 对该版本中 InnoDB 参数默认值修改的详细介绍。

发布于当地时间 2024 年 5 月 1 日

MySQL 项目经理 Frederic Descamps（图片来源：oracle.com）

昨天（4/30），MySQL 的第一个 LTS 版本 MySQL 8.4 发布了。

许多弃用的内容最终被删除，并且几个 InnoDB 变量默认值已被修改以匹配当前的工作负载和硬件规格。

有 20 个 InnoDB 变量的默认值已被修改！

让我们看一下这些变量并解释这样修改的原因：

被修改默认值的 InnoDB 变量

innodb_buffer_pool_in_core_file

版本	默认值
8.4 之前	ON
8.4 LTS	如果支持 MADV_DONTDUMP 为 OFF，否则 ON

MADV_DONTDUMP 是 Linux 3.4 及更高版本中支持的宏（存在“ sys/mman.h”头文件并包含符号 MADV_DONTDUMP，一个 madvise() 的 non-POSIX 扩展），Windows 系统或大多数 MacOS 系统不支持此宏。

总之，这意味着默认情况下，在 Linux 系统上，缓冲池的内容不会转储到核心文件中。

innodb_buffer_pool_instances

版本	默认值
8.4 之前	8（如果 BP < 1 GB，则为 1）
8.4 LTS	如果 BP <= 1 GB：1 如果 BP > 1 GB：则为 1-64 范围内的最小值： a. (innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) / 2 b. 1/4 可用逻辑处理器

旧值 8 在某些系统上可能太大。手册中包含 BP 大小计算的好示例，请参阅配置 InnoDB 缓冲池大小。

innodb_change_buffering

版本	默认值
8.4 之前	all
8.4 LTS	none

Change Buffer 是一种通过延迟对二级索引的写入操作来支持顺序 I/O 的技术。在最新的硬件上，随机 I/O 不再是问题。

innodb_dedicated_server

版本	默认值
8.4 之前	OFF
8.4 LTS	OFF

从 MySQL 8.0 开始，当 MySQL 运行在可供数据库使用的所有资源的专用服务器上时，我们建议启用此变量，并且不要手动修改 InnoDB 设置。

该变量的默认值是相同的，但是通过启用 innodb_dedicated_server 控制的变量是不同的。

innodb_buffer_pool_size
- 128MB 是服务器内存小于 1GB。
- 如果服务器内存在 1GB 到 4GB 之间，则检测到的服务器内存 * 0.5。
- 如果服务器内存超过 4GB，则检测到的服务器内存 * 0.75。
innodb_redo_log_capacity:（可用逻辑处理器数量/2）GB，最大 16GB。

当 innodb_dedicated_server 启用时，innodb_flush_method 不会自动配置。

innodb_adaptive_hash_index

版本	默认值
8.4 之前	ON
8.4 LTS	OFF

AHI（InnoDB 自适应哈希索引）长期以来一直是一些性能问题的原因。每个经验丰富的 DBA 总是建议禁用它，就像旧的查询缓存一样。我很惊讶没有像 Domas Mituzas 的查询缓存调优器那样的 AHI 调优器 😉

当没有数据发生更改并且完全缓存在缓冲池中时，AHI 可能会对读查询 (SELECT) 提供一些好处。一旦有写入操作，或者系统负载较高，或者读取所需的所有数据都无法缓存，自适应哈希索引就会成为巨大的瓶颈。

为了获得更可预测的响应时间，建议禁用它。

innodb_doublewrite_files

版本	默认值
8.4 之前	innodb_buffer_pool_instances * 2
8.4 LTS	2

之前默认值是根据缓冲池的数量计算的，为了简化，现在默认为 2。

相关文档指出该值定义每个缓冲池的双写文件数。但我的印象是，它是全局的，与缓冲池实例的数量无关。

从 MySQL 错误日志来看：

2024-05-01T05:43:03.226604Z 1 [Note] [MY-012955] [InnoDB] Initializing buffer pool, total size = 2.000000G, instances = 2, chunk size =128.000000M 
[...]
2024-05-01T05:43:03.288068Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-01T05:43:03.295917Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite
2024-05-01T05:43:03.317319Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.bdblwr' for doublewrite
2024-05-01T05:43:03.317398Z 1 [Note] [MY-013566] [InnoDB] Double write buffer files: 2
2024-05-01T05:43:03.317410Z 1 [Note] [MY-013565] [InnoDB] Double write buffer pages per instance: 128
2024-05-01T05:43:03.317423Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_0.dblwr' for doublewrite
2024-05-01T05:43:03.317436Z 1 [Note] [MY-013532] [InnoDB] Using './#ib_16384_1.dblwr' for doublewrite

我们看到我们有 2 个缓冲池实例，但仍然只有 2 个双写缓冲文件。根据文档，我期望 4 。第三个文件 #ib_16384_0.bdblwr 是在 innodb_doublewrite 设置为 DETECT_ONLY 时创建的。

使用 DETECT_ONLY 时，只有元数据会写入双写缓冲区。数据库页内容不会写入双写缓冲区，并且恢复不会使用双写缓冲区来修复不完整的页写入。此轻量级设置仅用于检测不完整的页面写入。

innodb_doublewrite_pages

版本	默认值
8.4 之前	innodb_write_io_threads（默认为 4）
8.4 LTS	128

从测试结果和出于对性能的角度考虑，我们意识到默认值越大越好，经常建议增加它。

innodb_flush_method

版本	默认值
8.4 之前	fsync
8.4 LTS	O_DIRECT (或 fsync)

当支持时，O_DIRECT 始终是首选值，我们建议使用它绕过文件系统缓存，将 InnoDB 更改刷新到磁盘（对于数据文件和日志文件）。

如果不支持 O_DIRECT*，我们使用旧的 *fsync 方法。这是针对 Unix 的，在 Windows 上，默认值是 unbuffered。

innodb_io_capacity

版本	默认值
8.4 之前	200
8.4 LTS	10000

对于最近的系统（RAID、SSD 等），默认 I/O 容量太低。由于该变量定义了 InnoDB 后台操作可用的 IOPS 数量，因此值太低会限制性能。

innodb_io_capacity_max

版本	默认值
8.4 之前	2 * innodb_io_capacity（最小 2000）
8.4 LTS	2 * innodb_io_capacity

如果 InnoDB 需要更积极地刷新，则此变量定义 InnoDB 可用于执行后台操作的最大 IOPS 数。新的默认值更简单，因为它只是 innodb_io_capacity 的两倍。

innodb_log_buffer_size

版本	默认值
8.4 之前	16MB
8.4 LTS	64MB

我们增加了默认值，因为大的日志缓冲区允许大型事务运行，而无需在事务提交之前将日志写入磁盘。

innodb_numa_interleave

版本	默认值
8.4 之前	OFF
8.4 LTS	ON

当系统支持 NUMA 时，新的默认值在分配 InnoDB 缓冲池期间将 mysqld 的 NUMA 内存策略设置为 MPOL_INTERLEAVE 。此操作随机平衡所有 NUMA 节点的内存分配，从而在这些节点之间实现更好的分布。

当然，只有当您的系统具有多个 NUMA 节点时，您才能从中受益。

这是验证节点数量的方法：

$ numactl --hardware
    available: 2 nodes (0-1)
    node 0 size: 16160 MB
    node 0 free: 103 MB
    node 1 size: 16130 MB
    node 1 free: 83 MB
    node distances:
    node   0   1 
      0:  10  20 
      1:  20  10

在上面的例子中，我们可以看到 CPU 有两个节点。

您还可以使用 lstopo 显示架构并显示 NUMA 核心。这是另一个例子：

innodb_page_cleaners

版本	默认值
8.4 之前	4
8.4 LTS	innodb_buffer_pool_instances

新的默认设置是使用与缓冲池实例一样多的线程从缓冲池实例中刷新脏页。

innodb_parallel_read_threads

版本	默认值
8.4 之前	4
8.4 LTS	逻辑处理器 / 8（最少 4 个）

出于性能原因，在具有大量逻辑 CPU 的系统上，用于并行聚集索引读取的线程数会自动增加。

innodb_purge_threads

版本	默认值
8.4 之前	4
8.4 LTS	如果逻辑处理器 <= 16，则为 1，否则为 4

对于具有大量 (>=16) vCPU 的系统，此变量也会以某种方式自动配置。但我们也意识到，在某些较小的系统上拥有 4 个清除线程可能会出现问题。对于这样的系统，我们将默认值减少到 1。

innodb_read_io_threads

版本	默认值
8.4 之前	4
8.4 LTS	逻辑处理器 / 2（最少 4 个）

如果系统有超过 8 个 vCPU，该变量也会自动增加。

innodb_use_fdatasync

版本	默认值
8.4 之前	OFF
8.4 LTS	ON

在支持它的系统上，除非需要，否则 fdatasync() 的调用不会刷新对文件元数据的更改。这提供了性能优势。

temptable_max_ram

版本	默认值
8.4 之前	1GB
8.4 LTS	总内存的 3%（1-4GB 范围内）

如果系统受益于大量内存，默认值现在会自动增加。但默认上限为 4GB。因此，对于内存超过 132GB 的系统，默认情况下 temptable_max_ram 的值将设置为 4GB。

temptable_max_mmap

版本	默认值
8.4 之前	1GB
8.4 LTS	0（禁用）

新的默认设置禁止从内存映射临时文件分配内存（不在 tmpdir 中创建文件）。

temptable_use_mmap

版本	默认值
8.4 之前	ON
8.4 LTS	OFF

当 temptable_use_mmap 被禁用（新默认设置）时，TempTable 存储引擎会使用 InnoDB 磁盘上的内部临时表，而不是在 temptable_max_ram 变量定义的限制被超过时，在 tmpdir 中为内部内存临时表分配空间作为内存映射的临时文件。

总结

通过这个全新版本的 MySQL（第一个 LTS），我们有机会更改某些 InnoDB 变量的默认值，使它们更符合生产服务器的实际情况。

有些现在可以自动调整以更好地匹配 MySQL 运行的系统。

享受 MySQL 并享受新的默认设置！

参考

[1] MySQL 版本介绍: https://dev.mysql.com/doc/refman/8.4/en/mysql-releases.html

[2] MySQL 8.4: https://dev.mysql.com/doc/relnotes/mysql/8.4/en/

[3] 配置 InnoDB 缓冲池大小: https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool-resize.html

[4] sysvar_innodb_doublewrite_files: https://dev.mysql.com/doc/refman/8.4/en/innodb-parameters.html#sysvar_innodb_doublewrite_files

本文原文：https://lefred.be/content/mysql-8-4-lts-new-production-ready-defaults-for-innodb/

更多技术文章，请访问：https://opensource.actionsky.com/

关于 SQLE

SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。

SQLE 获取

类型	地址
版本库	https://github.com/actiontech/sqle
文档	https://actiontech.github.io/sqle-docs/
发布信息	https://github.com/actiontech/sqle/releases
数据审核插件开发文档	https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse