MySQL 8.4 LTS--InnoDB 新的默认设置

MySQL 8.4 版本最终删除了大量过期设置，并修改了多个 InnoDB 变量的默认值，以适应当前的工作负载和硬件规格。其中修改了 20 个 InnoDB 变量的默认值！

 

·innodb_buffer_pool_in_core_file

之前的值是 on；现在的值on，但是如果支持 madv_dontdump ，默认值就是 off

 

madv_dontdump 是 Linux 3.4 及更高版本支持的一个宏，Windows 机器或大多数 MacOS 系统不支持该宏。

 

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

 

·innodb_buffer_pool_instances

之前的值是8，如果 buffer pool 小于 1GB，则是 1；

现在的规则是，如果 buffer pool 小于等于 1GB，则是 1；如果 buffer pool 大于 1 GB，最小值在 1--64范围之内，且在(innodb_buffer_pool_size / innodb_buffer_pool_chunk_size) / 2 和 1/4的逻辑处理器二者之间。

原先设置成8，可能对有些系统来说太大了。

 

·innodb_change_buffering

之前的值是 all；现在的值是 none

change buffering是一种通过延迟对二级索引的写入操作而有利于顺序 I/O 的技术。在最新的硬件上，随机 I/O 不再是问题。

 

·innodb_dedicated_server

以前的设置是 off；现在的设置也是 off

 

自 MySQL 8.0 起，建议启用该变量，当 MySQL 运行在专用服务器上，数据库有所有资源可用时，不要手动修改由该变量负责的 InnoDB 设置。

 

该变量的默认值相同，但启用 innodb_dedicated_server 后控制的变量不同。自 MySQL 8.4 起，innodb_dedicated_server 会配置以下变量：

--innodb_buffer_pool_size

服务器内存小于 1 GB 时为 128MB。

检测到的服务器内存 * 0.5（服务器内存在 1GB 至 4GB 之间）。

如果服务器内存超过 4GB，则检测到的服务器内存 * 0.75。

--innodb_redo_log_capacity：（可用逻辑处理器数量/2）GB，最大为 16GB。

 

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

 

·innodb_adaptive_hash_index

以前的设置是 on；现在的设置是 off

长期以来，AHI（InnoDB 自适应哈希索引）一直是造成一些性能问题的原因。每个有经验的 DBA 都会建议禁用它，就像禁用以前的查询缓存一样。

当数据未发生变化并完全缓存在缓冲池中时，AHI 可能会给读查询（SELECT）带来一些好处。一旦有写操作，或系统负载较高，或无法缓存读取所需的所有数据，自适应哈希索引就会成为一个巨大的瓶颈。

为了获得更可预测的响应时间，建议禁用自适应哈希索引。

 

·innodb_doublewrite_files

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

 

文档指出，该值定义了每个缓冲池的 double write 文件数。但在我的印象中，它是独立于缓冲池实例数量的全局值。

 

以下是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

可以看到，有两个buffer pool 实例，但是也只有两个 double write buffer 文件。根据文档应该有四个的。第三个文件#ib_16384_0.bdblwr被创建，是因为innodb_doublewrite 被设置成了"DETECT_ONLY"

 

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

 

·innodb_doublewrite_pages

之前的默认是值根据 innodb_write_io_threads来设置，默认是4；现在默认值是128

根据测试结果和出于性能的原因，设置大的值比较好。

 

·innodb_flush_method

之前的默认值是 fsync；现在的默认值是 O_DIRECT或 fsync

在受支持的情况下，O_DIRECT 一直是首选值，我们建议使用它来绕过文件系统缓存，将 InnoDB 的更改刷新到磁盘（数据文件和日志文件）。

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

 

·innodb_io_capacity

之前默认值是 200；现在默认值是10000

 

·innodb_io_capacity_max

以前的默认设置是 2 * innodb_io_capacity，最小值是 2000；新的默认值更简单，只是 innodb_io_capacity 的两倍。

如果 InnoDB 需要更急剧地刷新，这个变量会定义 InnoDB 执行后台操作时可以使用的最大 IOPS 数量。

 

·innodb_log_buffer_size

之前的默认设置16MB；现在默认设置是64MB

大日志缓冲区能让大型事务在运行时不需要在事务提交前将日志写入磁盘。

 

·innodb_numa_interleave

以前的默认设置是 off；现在的默认设置是 on

 

·innodb_page_cleaners

以前的默认设置是 4；现在的默认设置是 innodb_buffer_pool_instances 的值

 

·innodb_parallel_read_threads

以前的默认设置是 4；现在的默认设置是 logical processors / 8 (min 4)

 

·innodb_purge_threads

以前的默认设置是 4；现在的默认设置是如果逻辑处理的数量小于等于16，则为1，否则就是4

 

·innodb_read_io_threads

以前的默认设置是 4；现在的默认设置是 logical processors / 8 (min 4)

 

·innodb_use_fdatasync

以前的默认设置是 off；现在的默认设置是 on

除非需要，否则 fdatasync() 调用不会刷新对文件元数据的更改。这样可以提高性能。

 

·temptable_max_ram

以前的默认设置是 1GB；现在的默认设置是内存的3%(1--4GB之间)

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

 

·temptable_max_mmap

以前的默认设置是 1GB；现在的默认设置是0

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

 

·temptable_use_mmap

以前的默认设置是 on；现在的默认设置是 off

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