Skip to content

Latest commit

 

History

History
623 lines (448 loc) · 43.2 KB

Chapter23.md

File metadata and controls

623 lines (448 loc) · 43.2 KB
Raw

配置

在本书的第四部分中，有几个影响 MySQL 行为的配置选项示例。这些选项包括字符集和排序规则的选择、如何创建索引统计信息、优化器应如何工作等。还有其他选项直接或间接影响查询的性能。本章将审议其他地方未涵盖的最常用选项，以及配置 MySQL 时的一些一般注意事项。

本章首先介绍一些有关更改配置的"最佳实践"。然后，以下部分用于 InnoDB、查询缓冲区和内部临时表。

最佳实践

当您开始进行配置更改时，值得记住一些原则，这些原则可以使您更成功地进行配置更改。将讨论的最佳做法包括：

  • 注意最佳实践。
  • 使用监视来验证效果。
  • 一次更改一个选项。
  • 进行相对较小的增量更改。
  • 少往往更好。
  • 请确保您了解该选项的用向。
  • 考虑副作用。

最佳做法清单的第一项是警惕最佳做法，这听就是一种紧缩。意思是当你看到一些建议时，你不应该直接向前跳，并应用它。

没有两个系统是相同的，所以虽然建议一般可能是好的，你仍然需要考虑它是否也适用于你的系统。另一个建议是查看适用于旧版本的 MySQL 或 8 Gib 内存量大的时候的建议。如果你谷歌一些设置，这是有可能的，你可以看到许多年前写的建议。同样，由于应用程序工作负载的更改，以前一段时间对系统运行良好的建议可能不再有效。最后，即使建议将提高系统的性能，也可能存在副作用，例如丢失承诺的更改的风险，这是您无法接受的。

提示

对最佳做法持谨慎态度的建议也适用于本书中的建议。始终考虑它们如何应用于您的系统。

那么，您应该如何处理配置更改？应用第。图概括了这些步骤。

图23-1

性能

您首先定义问题是什么，然后通过监视系统或计时查询或类似来收集基线。基线也可以是可观测值的组合。然后，您可以定义优化的目标。重要的是，你定义什么足够好，否则你永远不会完成。接下来的步骤是确定原因，并用它来找到解决方案。最后，实现解决方案，通过与基线进行比较来验证效果。如果问题未解决或已确定多个问题，可以重新开始。

在此过程中非常重要，因为它既用于定义问题、收集基线，也用于验证效果。如果跳过这些步骤，则几乎不知道您的解决方案是否有效，以及它是否也影响了其他查询。

当您决定解决方案时，请进行尽可能小的更改。这既适用于打开旋钮的配置选项数，也适用于转动旋钮的远远。如果一次更改多个选项，则无法测量每个更改的效果。例如，两个更改可能会相互抵消，因此您认为当其中一个更改真正效果很大，另一个更改使情况变得更糟时，解决方案不起作用。

配置也经常有一个甜蜜点。如果设置太小，则选项表示的功能不能用于产生重大影响。如果设置太大，则功能开销将变得比优势更严重。在两者之间，您可以获得最佳功能优势的最佳组合，而开销有限。图。

图23-2

选项值和性能之间的典型关系

通过进行小增量更改，您可以最大限度地找到这个甜蜜点。

这与下一点有关：小往往更好。例如，仅仅因为您有足够的内存来增加每个查询或每个联接缓冲区，并不意味着它使查询更快地增加缓冲区大小。当然，这取决于这一原则适用的程度。对于 InnoDB 缓冲池的大小，最好有一个相对较大的缓冲区，因为它有助于减少磁盘 I/O 并提供来自内存的数据。关于缓冲池，要记住的一个关键点也是内存分配仅在 MySQL 启动时以及动态增加缓冲池的大小时发生。但是，对于可能为单个查询多次分配的联接缓冲区等缓冲区，分配缓冲区的绝对开销可能成为问题。这在"查询缓冲区"部分中进一步讨论。在所有情况下，对于与资源相关的选项，您需要记住，分配给一个要素的资源不适用于其他功能。

"少往往更好"的概念既适用于选项的最佳值，也适用于您调整的选项数。在配置文件中设置的选项越多，配置文件就越混乱，就越难以对已更改的内容和原因进行概述。（它还有助于按功能对设置进行分组，例如，将所有 InnoDB 设置放在一起。如果您习惯于将选项设置为其默认值，最好不要将它们包括选项，因为这些选项意味着您将错过对作为优化默认配置以反映对 MySQL 内部组件的更改或标准硬件更改所做的更改的更改。

注意

在 MySQL 5.6 及更晚的中，在改进 MySQL 配置选项的默认值方面已投入大量精力。这些更改主要发生在基于开发团队测试的主要版本和 MySQL 支持团队、客户和社区成员的反馈之间。

建议开始尽可能少地设置备选方案。您很可能想要设置重做日志以及可能设置表缓存的大小。您可能还需要设置某些路径和端口，并且您可能要求启用某些功能（或组复制）。除此之外，仅根据观测结果进行更改。

提示

首先使用最小配置，该配置只设置 InnoDB 缓冲池的大小，并重做日志、路径和端口，并启用所需的功能。否则，仅根据观测值进行配置更改。

列表中的最后两点是相关的：确保您了解选项的作用并考虑副作用。了解选项的作用有助于确定该选项是否对您的案例有用，以及该选项可能具有哪些其他影响。例如，。这说明了对二进制日志的更新应同步到磁盘的频繁时间。在 MySQL 8 中，默认值是与每个提交同步，对于同步性能较差的磁盘，这些提交会显著影响查询性能。因此，将"sync_binlog为0 会非常诱人，从而禁用强制同步;然而， 副作用可以接受吗？如果不同步更改，则它们只生活在内存中，直到其他更改（如其他用途所需的内存）强制同步发生。这意味着，如果 MySQL 崩溃，则更改将丢失，如果您有副本，则必须重新生成它。这是可以接受的吗？

即使你可以接受可能丢失二进制日志事件，使用"sync_binlog 仅仅因为同步不发生在事务提交中并不意味着它永远不会发生。二进制日志的最大大小是 1 选项）加上最后一个事务的大小，旋转二进制日志意味着旧的二进制日志被刷新到磁盘。现在，这通常意味着 MySQL 最终会编写 1 GiB，然后一次刷新所有。即使在快速磁盘上，编写一千兆字节的数据也确实需要大量时间。同时，MySQL 无法执行任何提交，因此发出提交（无论是隐式还是显式）的任何连接都将在同步完成之前停止。这会让人感到意外，而摊位最终可能会足够长，让最终用户——他们可能是客户——感到不安。本书的作者已经看到由二进制日志旋转引起的提交停滞，其范围是几秒钟到半分钟。简而言之提供了总体最高的吞吐量和平均提交提供了最佳的数据安全性和最可预测的提交延迟。

本章的其余部分提供了与查询调优相关的选项的一些建议，这些选项通常需要更改。

InnoDB 概述

鉴于涉及表的所有查询都与 InnoDB 存储引擎交互，因此需要花一些时间来查看 InnoDB 参数的配置非常重要。其中包括 InnoDB 缓冲池的大小和重做日志大小 - 两个配置需要为大多数生产系统进行调整。

在讨论配置选项之前，值得查看数据在表空间和缓冲池之间如何流动，并通过重做日志系统返回表空间。图显示了此流的简单概述。

Figure 23-3

InnoDB

当查询请求数据时，始终从缓冲池读取数据。如果数据尚未在缓冲池中，则从表空间获取数据。InnoDB 将缓冲池分为两部分：旧块子列表和新块子列表。数据始终在整页中读取到旧块子列表的头部（顶部）。如果再次需要来自同一页的数据，则数据将移动到新块子列表。两个子列表（LRU） 原则来确定需要为新页面创建空间时要驱逐的页面。页面从旧块子列表中从缓冲池中逐出。由于新页面在升级至新块子列表之前会花时间在旧块子列表中，这意味着如果页面使用了一次，但随后未使用，则它很快就会再次从缓冲池中排出。这样可以防止大型罕见扫描（如备份）污染缓冲池。

当查询更新更改时，更改将写入内存中的日志缓冲区，并从那里写入，然后刷新到重做日志，该日志由至少两个文件组成。重做日志文件以循环方式使用，因此写入从一个文件的开头开始，然后填满该文件，当文件已满时，InnoDB 将继续使用下一个文件。文件大小固定，文件数量固定。当日志到达最后一个文件的末尾时，InnoDB 将移回第一个文件的开头。

更改也会写回缓冲池，并标记为脏，直到它们可以刷新到表空间文件。InnoDB 使用双写缓冲区来确保在发生崩溃时可以检测写入是否成功。是必要的，因为大多数文件系统不能保证原子写入，因为 InnoDB 页大于文件系统块大小。在编写时，唯一可以安全地禁用双写缓冲区的文件系统是 ZFS。

谨慎

即使文件系统应该处理 InnoDB 页的原子写入，它在实践中可能不起作用。例如，启用日记的 EXT4 文件系统，理论上在没有双写缓冲区的情况下应该是安全的，但实际上可能会导致数据损坏。

将在下一节中讨论的配置选项围绕数据的这一生命周期展开。

InnoDB 缓冲池

是 InnoDB 缓存数据和索引的地方。由于所有数据请求都经过缓冲池，因此从性能角度来看，它自然成为 MySQL 非常重要的一部分。缓冲区池有几个重要参数将在这里讨论。

表总结了缓冲区池相关的配置选项，您可能需要更改这些配置选项来优化查询性能。

Table 23-1

缓冲的重要配置选项

选项名称 默认值 评论
innodb_buffer_pool_size 128 MiB InnoDB 缓冲池的总大小。
innodb_buffer_pool_instances 自动尺寸 缓冲池拆分成多个部分。如果总大小小于 1 GiB，则默认值为 1，否则为 8。对于 32 位 Windows，默认值为 1 低于 1.3 GiB;对于 32 位 Windows，默认值为 1 个，低于 1.3 GiB;否则，每个实例的 128 MiB。实例的最大数为 64。
innodb_buffer_pool_dump_pct 25 转储池内容（备份它）时包含的缓冲池中最近使用的页面的百分比。
innodb_old_blocks_time 1000 在重新，页面必须驻留在旧块子列表中的毫秒（以毫秒为单位）将页面提升为新块子列表。
innodb_old_blocks_pct 37 旧块子列表的大小应占整个缓冲池的百分比。
innodb_io_capacity 200 在非医疗条件下允许使用多少个 I/O 操作/秒 InnoDB。
innodb_io_capacity_max 2000 在紧急条件下允许使用多少个 I/O 操作/秒 InnoDB。
innodb_flush_method 未缓冲或fsync InnoDB 用于将更改写入磁盘的方法。默认值在缓冲，在上为 fsync 提供。

本节的其余部分将更详细地讨论这些选项，从与缓冲池大小相关的选项开始。

注意

缓存 InnoDB 索引没有任何关系。该选项在 MySQL 的早期得到了它的名字，当时 MyISAM 存储引擎是主存储引擎，因此不需要用 mysiam 来前。除非使用 MyISAM 表，否则没有理由

缓冲池大小

这些选项中最重要的是池的大小。128 MiB 的默认大小适合在笔记本电脑上设置测试实例，而不会耗尽内存（以及默认值为何如此之小），但对于生产系统来说，您很可能想要分配更多的内存。在工作数据集适合缓冲池大小可以带来好处。工作数据集是执行查询所需的数据。通常，这是总数据集的子集，例如，某些数据处于非活动状态，因为它与过去的事件有关。

提示

如果您有一个大型缓冲池，并且启用了核心转储，选项以避免在发生核心转储时转储整个缓冲池。该选项在 MySQL 8.0.14 及更晚版本中可用。

您可以使用以下公式获取缓冲池命中率（即，页面请求可以直接从缓冲池完成的频率，而无需从磁盘读取）。 两个变量状态变量。 清单显示了如何计算缓冲池命中率的示例。

Listing 23-1. Calculating the buffer pool hit rate
mysql> SELECT Variable_name, Variable_value
 FROM sys.metrics
 WHERE Variable_name IN
 ('Innodb_pages_read',
 'Innodb_buffer_pool_read_requests')\G
*************************** 1. row ***************************
 Variable_name: innodb_buffer_pool_read_requests
Variable_value: 141319
*************************** 2. row ***************************
 Variable_name: innodb_pages_read
Variable_value: 1028
2 rows in set (0.0089 sec)
mysql> SELECT 100 - (100 * 1028/141319) AS HitRate;
+---------+
| HitRate |
+---------+
| 99.2726 |
+---------+
1 row in set (0.0003 sec)

计算缓冲池命中率

在该示例中，99.3%请求从缓冲池完成。此数字跨所有缓冲池实例。如果要确定给定期间的命中率，则需要在周期的开始和结束时收集状态变量的值，并在计算中使用它们之间的差值。您还可以从信息架构或的视图获取速率。在这两种情况下，速率都根据千次请求返回。清单显示了这方面的例子。您需要确保已执行一些查询以生成一些缓冲池活动以获得有意义的结果。

Listing 23-2. Getting the buffer pool hit rate directly from InnoDB
mysql> SELECT POOL_ID, NUMBER_PAGES_READ,
 NUMBER_PAGES_GET, HIT_RATE FROM information_schema.INNODB_
BUFFER_POOL_STATS\G
*************************** 1. row ***************************
 POOL_ID: 0
NUMBER_PAGES_READ: 1028
 NUMBER_PAGES_GET: 141319
 HIT_RATE: 1000
1 row in set (0.0004 sec)
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
 Type: InnoDB
 Name:
Status:
=================================================
2019-07-20 19:33:12 0x7550 INNODB MONITOR OUTPUT
=================================================
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 536469
Buffer pool size 8192
Free buffers 6984
Database pages 1190
Old database pages 428
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 38, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1028, created 237, written 1065
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 1190, unzip_LRU len: 0
I/O sum[6]:cur[0], unzip sum[0]:cur[0]
...

直接从 Innodb 获取缓冲池命中率

重要的是要认识到，InnoDB 直接返回命中率是上次检索缓冲池统计信息以来的时间段，并且它们是每个缓冲池实例。如果要完全控制命中率的时间段，则需要使用状态变量或计算

您应该的目标是使缓冲池的命中率尽可能接近 100% 或 1000/1000。也就是说，在某些情况下，它根本不可能，因为数据量不可能放入内存中。在这种情况下，缓冲池命中率仍然很有用，因为它允许您监视缓冲池在一段时间内的有效性，并与常规查询统计信息进行比较。如果缓冲池命中率开始下降，查询性能下降，则应考虑进行规定，以便可以增加缓冲池的大小。

缓冲池实例

自版本 5.5以来，MySQL 支持多个缓冲池实例。引入它的原因是，典型的数据库工作负载具有越来越多的查询并行运行，每个主机的 CPU 也越来越普遍。这导致在访问缓冲池中的数据时进行互斥争用。

减少争用的解决方案之一是允许将缓冲池拆分为多个实例，每个实例具有不同的互斥。使用"自动"选项控制数。使用"特定"实例之间均匀分配。除了在 32 位 Windows 上，默认值是对于小于 1 GB 的缓冲池大小有一个实例。对于较大的缓冲池，默认值为八个实例。实例的最大数为 64。

对于单线程工作负载，最佳方式是将所有内存都位于单个缓冲池中。工作负荷越并行，其他实例有助于减少争用。增加缓冲池数量的确切效果取决于并行查询请求存储在不同页面中的数据的程度。如果所有请求都针对不同的页面，则可以从增加实例数以减少并发查询数中获益。如果所有查询都在同一页中请求数据，则更多实例没有任何好处。通常，请注意不要使每个缓冲池实例太小。如果没有显示其他情况的监视数据，则允许每个实例为 1 GB 或更大，用于至少 8 GB 的大缓冲池。

转储缓冲池

重新启动数据库的常见问题之一是缓存在缓存预热之前在一段时间中不能正常工作。这可能导致查询性能很差和最终用户满意度差。为此的解决方案是在关闭时存储缓冲区池中最常用的页面的列表，并在重新启动后立即将这些页面读取到缓冲池中，即使尚未请求查询。

默认情况下启用此功能，要考虑的主要事项是要在转储中包含多少缓冲池。这通过"innodb_buffer_pool_dump_pct控制该选项需要包含页面的百分比。默认值为 25%。这些页面从新块子列表的头部读取，因此它是包含的最近使用的页面。

转储仅包括对应读取的页面的引用，因此转储的大小约为每页 8 字节。如果您有 128 个 GiB 缓冲池，并且使用的是 16 个 KiB 页，则缓冲池中有 8，388，608 页。如果对缓冲池转储使用默认值 25%，则该转储的转储约为 16 MiB。转储存储在。

提示

通过复制表空间文件（物理备份或原始备份）创建备份时备份该文件。您可以使用选项创建最近使用的页面的新副本。例如，这是由 MySQL 企业备份自动完成的。但是，对于逻辑备份（其中数据导出为 SQL 或 CSV 文件文件没有用处。

如果在重新启动后遇到慢速查询，以在转储中包含较大部分的缓冲池。增加选项的主要缺点是，关闭需要更长的时间，因为导出更多的页面引用文件变大，并在重新启动后加载页面的时间较长。将页面加载回缓冲池在后台进行，但通过包含更多页面，可能需要更长时间才能在缓冲池中还原所有最重要的页面。

旧块子列表

如果数据集大于缓冲池，则潜在的问题是，大型扫描可能会拉取仅用于该扫描的数据，然后长时间不再使用。发生这种情况时，您可能会将更常用的数据从缓冲池中排出，并且需要该数据的查询将受到影响，直到扫描完成并恢复余额。逻辑备份（如 进行的逻辑备份是可以触发问题的作业的很好示例。备份过程需要扫描所有数据，但在下次备份之前不需要再次访问数据。

为了避免此问题，缓冲池被拆分为两：新旧块子列表。从表空间读取页面时，它们首先在旧块子列表中"隔离并且只有当页面在缓冲池中超过 innodb_old_blocks_time 毫秒并再次使用时，它们才会移动到新块子列表中。这有助于使缓冲池扫描具有抵抗力，因为单个表扫描只会快速连续从页面读取行，然后不再使用该页。这样，InnoDB 在扫描完成后可以自由地驱逐页面。

其默认值为 1000 毫秒，对于大多数工作负载来说，这足以避免扫描污染缓冲池。如果作业在短时间后再次返回到同一行（但时间超过一秒），，则可以考虑增加 innodb_old_blocks_time 。

旧块子列表的大小由 innodb_old_blocks_pct 选项，该选项指定应用于旧块子列表的缓冲池的百分比。默认值为使用 37%。如果您有一个大型缓冲池，您可能希望新加载的页面占用太多的缓冲池。旧块子列表的最佳大小还取决于将瞬态页面加载到缓冲池中的速率。

您可以监视新旧块子这些子列表与发现命中率的方式类似。清单显示了使用 INNODB_BUFFER_POOL_STATSInnoDB 监视器的示例输出。

Listing 23-3. Obtaining information about the new and old blocks sublists
mysql> SELECT PAGES_MADE_YOUNG,
 PAGES_NOT_MADE_YOUNG,
 PAGES_MADE_YOUNG_RATE,
 PAGES_MADE_NOT_YOUNG_RATE,
 YOUNG_MAKE_PER_THOUSAND_GETS,
 NOT_YOUNG_MAKE_PER_THOUSAND_GETS
 FROM information_schema.INNODB_BUFFER_POOL_STATS\G
*************************** 1. row ***************************
 PAGES_MADE_YOUNG: 98
 PAGES_NOT_MADE_YOUNG: 354
 PAGES_MADE_YOUNG_RATE: 0.00000000383894451752074
 PAGES_MADE_NOT_YOUNG_RATE: 0
 YOUNG_MAKE_PER_THOUSAND_GETS: 2
NOT_YOUNG_MAKE_PER_THOUSAND_GETS: 10
1 row in set (0.0005 sec)
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
 Type: InnoDB
 Name:
Status:
===============================================
2019-07-21 12:06:49 0x964 INNODB MONITOR OUTPUT
===============================================
...
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137363456
Dictionary memory allocated 463009
Buffer pool size 8192
Free buffers 6974
Database pages 1210
Old database pages 426
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 98, not young 354
0.00 youngs/s, 0.00 non-youngs/s
Pages read 996, created 223, written 430
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 10 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead
0.00/s
LRU len: 1210, unzip_LRU len: 0
I/O sum[217]:cur[0], unzip sum[0]:cur[0]
...

获取有关新旧块子列表的信息

意味着位于旧块子列表中将移动到新块子列表。页面不年轻意味着它停留在旧块子列表中。自上次获取数据以来，两个速率列是每秒。每千页获取的页面是年轻或保留在旧块子列表中的页数，每千页请求;这也是自上次报告以来。

可能需要配置旧块子列表的一个可能迹象是，在扫描正在进行时，缓冲池命中率会降低。如果使页面年轻且同时扫描大量，则应考虑增加后续读取使页面年轻。或者，请考虑减少在旧块子列表中较短的时间后从扫描中逐出页面。

反之亦然，如果您很少扫描，并且页面保留在旧块子列表中（非年轻制作统计数据减少 innodb_old_blocks_time 以更快地提升页面或增加允许页面在旧块子列表中停留更长时间，然后再被逐出。

刷新页面

InnoDB 需要平衡在将更改合并到表空间文件中时的工作方式。如果它太懒，重做日志最终是满的，并且需要强制刷新，但如果它工作太辛苦，它可能会影响系统其他部分的性能。 不用说，把等式弄对了很复杂。除非在崩溃恢复期间或恢复物理备份（如使用 MySQL 企业备份创建备份）后，否则合并是通过将脏页从缓冲池刷新到表空间文件完成的。

在最近的 MySQL 版本中，您通常不需要做太多工作，因为 InnoDB 使用的自适应刷新算法在有足够多的重做日志使用后就善于实现良好的平衡。需要考虑三个选项：两个用于设置系统的 I/O 容量，另一个用于设置刷新方法。

I/O 容量的两在刷新更改期间使用"自动"选项，应设置为允许 InnoDB 以秒使用的 I/O 操作数。在实践中，要知道使用什么价值并不容易。默认值为 200，大致对应于低端 SSD。通常，高端存储可以从将容量设置为几千中获益。最好从相对较低的值开始，如果监控显示刷新滞后且存在备用 I/O 容量，则最好增加该值。

注意

用于确定 InnoDB 将脏页刷新到表空间文件的速度。还包括其他 I/O 活动，如合并来自更改缓冲区的数据。

表示如果冲洗滞后，允许 InnoDB 推动的硬度。默认值为 2000 年的最小值，是 2000innodb_io_capacity。在大多数情况下，默认值工作得很好，但如果您有低端磁盘，则应考虑将设置减小到 1000 以下。如果您遇到异步刷新（这将与重做日志讨论），并且您的监视显示 InnoDB 没有使用足够的 I/O 容量，请增加的值。

谨慎

将 I/O 容量设置过高会严重影响系统的性能。

可以通过多种方式对脏页进行刷新，例如，使用操作系统 I/O 缓存或避免它。这是使用"innodb_flush_method"选项。在 Microsoft Windows 上，您可以在未缓冲推荐值）和普通 值。在支持以下值的 Linux 和 Unix 上，选择更加困难：

  • ：这是默认值。InnoDB 使用调用。数据也将缓存在操作系统 I/O 缓存中。
  • 重做日志文件（同步写入）时使用选项，并使用 fsync 处理数据文件。使用被证明是太不安全的，使用。
  • 这类似于但操作系统 I/O 缓存被绕过。它仅适用于表空间文件。
  • 这与系统调用。由于 EXT4 和 XFS 文件系统中的 Bug，在 MySQL 8.0.14 实施这些 Bug 的解决方法之前，使用此方法不安全。如果重做日志文件位于与表空间文件不同的文件系统上，则而不是在大多数生产系统上，这是最佳选择。

此外，还有一些实验刷新方法，只能用于性能测试。这些实验方法不在此介绍。

刷新方法能提供性能是非常复杂的。由于 InnoDB 缓存其数据本身，并且比操作系统可能缓存数据更好（正如 InnoDB 知道如何使用数据选项之一将工作得最好。通常情况也是如此;然而，生活是更复杂的，在某些情况下更快。因此，您需要在系统上进行测试，以确定哪种刷新方法效果最佳。另一件事是，在重新启动 MySQL 而不重新启动操作系统时，如果您使用，则 InnoDB 可以在首次读取数据时从 I/O 缓存中受益。

在数据流的另一端有重做日志。

重做日志

重做日志用于保留已提交的更改，同时提供顺序 I/O，以使性能尽可能好。为了提高性能，更改首先写入内存中的日志缓冲区，然后再写入日志文件。

然后，后台进程通过双写缓冲区将来自缓冲池的更改合并到表空间中。尚未合并到表空间文件的页面不能从缓冲池中逐出，因为它们被视为脏。页面脏，这意味着它的内容与表空间中的同一页不同，因此在合并更改之前，不允许 InnoDB 从表空间读取该页。

表总结了您可能需要做日志相关配置选项，以优化查询性能。

表23-2

重做日志的重要配置选项

选项名称 默认值 评论
innodb_log_buffer_size 16 米布 在写入磁盘重做日志文件之前，重做日志事件存储在内存中的日志缓冲区的大小。
innodb_log_file_size 48 米布 重做日志中每个文件的大小。
innodb_log_files_in_group 2 重做日志中的文件数。必须至少有两个文件。

本节的其余部分将介绍这些选项。

日志缓冲区

日志InnoDB 用于在将日志事件写入磁盘之前缓冲重做日志事件的内存中缓冲区。这允许事务将更改保留在内存中，直到缓冲区已满或提交更改。日志缓冲区的默认大小为 16 MiB。

如果您有大量事务或大量较小的并发事务，建议增加日志缓冲区的大小。使用"日志"选项设置日志大小。在 MySQL 8 中（与旧版本不同），您可以动态更改大小。最佳地，缓冲区应足够大，InnoDB 只需要在提交更改时写出这些更改;但是，这当然应该与内存的用量进行权衡。如果单个事务在缓冲区中具有大量更改，它还可以减慢提交速度，因为此时必须将所有数据写入重做日志，因此对于非常大的日志缓冲区大小，这是需要考虑的另一件事。

日志缓冲区已满或事务提交后，重做日志事件将写入重做日志文件。

日志文件

重做日志的大小是固定的，由多个文件（至少两个）组成，每个文件的大小。配置重做日志时，主要考虑的是确保它们足够大，不会变为"满"。实际上，完全意味着 75% 的容量，因为当时触发异步刷新。异步刷新会阻止触发刷新的线程，而原则上其他线程可以继续执行其工作。实际上，异步刷新非常凶猛，通常会导致系统停止磨削。还有一个同步刷新，以 90% 的容量触发并阻塞所有线程。

您可以使用两个选项控制大小 重做日志的总大小是两个值的倍数。建议将文件大小设置为 1+2 GiB，并调整文件数，以至少使用两个文件获取所需的总大小。不让每个重做日志文件变得非常大的原因是它们在操作系统 I/O 缓存中缓冲越大，重做日志在 I/O 缓存中使用大量内存的可能性越大。重做日志的总大小不允许超过 512 GiB，最多可以有 100 个文件。

注意

重做日志越大，可以存储的尚未从缓冲池刷新到表空间的更改数。这会增加崩溃时的恢复时间以及执行正常关机的时间。

确定重做日志的较大性的最佳方法是通过监视解决方案监视它随着时间的推移的满数。图显示了显示重做日志文件的 I/O 速率以及按检查点延迟测量的重做日志的使用情况的图形示例。如果要创建类似的东西，则需要执行密集的写入工作日志;如果想要创建类似的东西，则需要执行密集的写入工作日志。数据库可能很有用。具体要求取决于硬件、配置、其他进程使用的资源等。

重做日志的时间序列图

确保未检查点的重做日志部分75% 标记保持不一。在此示例中，重做日志的 96 MiB（14：37）中的最高峰值约为 73 MiB，这意味着几乎 76% 的重做日志用于脏页。这意味着在这段时间左右有异步刷新，这将影响当时运行的查询。您可以使用重做日志文件的 I/O 速率来了解文件系统对重做日志的 I/O 压力。

手动检查当前重做日志使用情况的最佳方法是启用和 InnoDB 指标，该指标允许您在进行最后一个检查点时查询当前日志序列号和日志序列号。然后，将总重做日志的百分比的检查点滞后率计算为 。

可以从"数据"或从获取当前值。或者，日志序列号也可以从 InnoDBLOG，无论指标是否已启用。清单显示了使用这些资源确定检查点延迟的示例。

Listing 23-4. Querying the redo log usage
mysql> SET GLOBAL innodb_monitor_enable = 'log_lsn_current',
 GLOBAL innodb_monitor_enable = 'log_lsn_last_checkpoint';
Query OK, 0 rows affected (0.0004 sec)
mysql> SELECT *
 FROM sys.metrics
 WHERE Variable_name IN ('log_lsn_current',
 'log_lsn_last_checkpoint')\G
*************************** 1. row ***************************
 Variable_name: log_lsn_current
Variable_value: 1678918975
 Type: InnoDB Metrics - log
 Enabled: YES
*************************** 2. row ***************************
 Variable_name: log_lsn_last_checkpoint
Variable_value: 1641343518
 Type: InnoDB Metrics - log
 Enabled: YES
2 rows in set (0.0078 sec)
mysql> SELECT ROUND(
 100 * (
 (SELECT COUNT
 FROM information_schema.INNODB_METRICS
 WHERE NAME = 'log_lsn_current')
 - (SELECT COUNT
 FROM information_schema.INNODB_METRICS
 WHERE NAME = 'log_lsn_last_checkpoint')
 ) / (@@global.innodb_log_file_size
 * @@global.innodb_log_files_in_group
 ), 2) AS LogUsagePct;
+-------------+
| LogUsagePct |
+-------------+
| 39.25       |
+-------------+
1 row in set (0.0202 sec)
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
 Type: InnoDB
 Name:
Status:
===============================================
2019-07-21 17:04:09 0x964 INNODB MONITOR OUTPUT
===============================================
...
---
LOG
---
Log sequence number 1704842995
Log buffer assigned up to 1704842995
Log buffer completed up to 1704842235
Log written up to 1704842235
Log flushed up to 1696214896
Added dirty pages up to 1704827409
Pages flushed up to 1668546370
Last checkpoint at 1665659636
5360916 log i/o's done, 23651.73 log i/o's/second
...

查询重做日志使用情况

首先InnoDB 指标。启用这些功能的开销非常小，因此可以启用它们。然后从视图查询指标的值，然后使用表直接计算滞后。最后，日志序列号也存在于 InnoDB 监视器输出中。日志序列号变化非常快，因此即使您连续查询它们，如果有任何工作进行，它们也会发生变化。这些值反映 InnoDB 中完成的字节量，因此它们在任何两个系统上都不同。

并行查询执行

自 MySQL 8.0.14 以来，InnoDB 对并行执行的支持有限。这通过使用多个读取线程执行群集索引或分区的扫描而发生。在 8.0.17 中，实现得到了极大的改进，这是这里所考虑的。

并行扫描根据要扫描的索引子树数自动进行。您可以通过设置"其他"选项来配置 InnoDB 可创建的最大线程数，以便跨所有执行。这些线程创建为后台线程，仅仅根据需要存在。如果所有并行线程都在使用中，InnoDB 将恢复为任何其他查询的单线程执行，直到线程再次可用。

从 MySQL 8.0.18 起，并行扫描用于 （允许多个表）， 没有任何筛选条件，以及 CHECK TABLE 执行的两个扫描。

通过查找名称为 performance_schema/innodb/parallel_read_thread 的线程，可以查看 例如，如果要尝试此功能，可以使用 MySQL Shell 中的 Python 模式继续计算员工数：

Py> for i in range(100): session.run_sql('SELECT COUNT(*) FROM employees.
salaries')

具有 innodb_parallel_read_threads 线程的输出示例是

mysql> SELECT THREAD_ID, TYPE, THREAD_OS_ID
 FROM performance_schema.threads
 WHERE NAME = 'thread/innodb/parallel_read_thread';
+-----------+------------+--------------+
| THREAD_ID | TYPE | THREAD_OS_ID |
+-----------+------------+--------------+
| 91 | BACKGROUND | 12488 |
| 92 | BACKGROUND | 5232 |
| 93 | BACKGROUND | 13836 |
| 94 | BACKGROUND | 24376 |
+-----------+------------+--------------+
4 rows in set (0.0005 sec)

您可以尝试使用较小的表（如世界数据库中表），并查看后台线程数的差异。

如果您看到所有配置的读取线程大部分时间都在使用 中，并且您有备用 CPU，可以考虑增加。支持的最大值为 256。请记住为单线程查询保留足够的 CPU 资源。

如果您看到信号量等待，并且 CPU 的监视表明存在 CPU 资源存在争用，而存在许多并行读取线程以减少查询的并行性。

查询缓冲区

MySQL在查询执行期间使用多个。其中包括存储联接中使用的列值、用于排序的缓冲区等。人们很容易认为，更多对这些缓冲区更好，但一般来说并非如此。相反，往往少是更好。本节讨论为什么会这样。

当 MySQL 需要对查询或部分查询使用缓冲区时，有几个因素决定了对查询的影响。这些因素包括：

  • 缓冲区是否足够大，需要作业？
  • 内存够吗？
  • 分配缓冲区的成本是多少？

如果缓冲区不够大，则算法无法以最佳方式执行，因为需要更多的迭代，或者需要溢出到磁盘。但是，在某些情况下，缓冲区的配置值用作最小大小，而不是最大大小。例如，这是联接缓冲区的情况，其大小由始终分配最小大小，如果最小大小不够大，在将最小大小用于联接时，无法容纳单个行所需的列，则将根据需要展开该大小。

关于内存的问题也非常相关。MySQL 崩溃的最常见原因可能是操作系统内存不足，操作系统终止了 MySQL。对于单个查询，各种缓冲区所需的内存量似乎加起来不多，但如果然后乘以所有并发执行的查询并添加空闲连接和全局分配所需的内存，则可能会突然变得比您喜欢的更接近内存不足。这也可能导致交换，这是一个主要的性能杀手。

最后一点比大多数人更令人惊讶。分配内存是有代价的，通常需要的内存越多，每个字节的成本也越高。例如，在 Linux 上，分配方法发生更改的各种阈值。这些阈值取决于 Linux 发行版，但例如256 KiB 和 2 MiB。如果超过其中一个阈值，分配方法将变得更加昂贵。这也是选项的默认值为 256 KiB 256 KiB 的一部分。 这意味着，有时最好使用有点太小的缓冲区，因为最佳大小的缓冲区的好处不能提高性能，不足以补偿分配更多内存的开销。

提示

缓冲区的分配是进行改进的领域之一，因此在某些情况下，升级可以允许您使用较大的缓冲区，而不会有传统的缺点。例如，在 MySQL 8.0.12 及更晚的示例中，使用了排序缓冲区的新算法。这意味着在 Linux/Unix 和 Windows 上的非正流排序上，内存是增量分配的，这使得对于具有大值的内存更sort_buffer_size 。不过，您仍需要考虑单个查询允许使用多少内存。

结论是，最好对在查询期间分配的缓冲区保持保守。保持全局设置较小 - 默认值是一个很好的起点 - 并且仅增加查询，其中您可以证明在增加设置时有显著改进。

内部临时表

当查询需要存储子查询的结果时，合并 UNION 语句以及类似的查询，它使用内部临时表。MySQL 8 具有引擎，它非常，因为它支持可变宽度列（版本 8.0.13 支持 Blob 和文本列）。此外支持使用 mmap 溢出到磁盘，因此如果表不适合内存，可以避免存储引擎转换。

对于 MySQL 8 中的内部临时表，需要考虑两个设置：以及如果需要溢出到磁盘，应会发生什么。

您可以使用"外部"选项配置内部临时表内存。这是一个全局设置，默认为 1 GiB。此内存在需要内部临时表的所有查询之间共享，因此很容易限制总内存使用量。可以设置"设置"选项。

如果内存不足，则有必要开始将临时表存储在磁盘上。如何完成，由版本 8.0.16 中引入的temptable_use_mmap选项控制。 默认值为这意味着引擎将磁盘上数据的空间分配为内存映射的临时文件。这也是 8.0.16 之前使用的方法。如果该值设置为，则使用 InnoDB 磁盘上的内部临时表。除非遇到内存映射文件的问题，否则建议使用默认设置。

您可以使用内存//内存和内存使用情况。物理 RAM 事件显示引擎内存部分的内存使用情况，而物理磁盘事件显示内存映射部分。清单显示了查询两个内存事件的内存使用情况的三个示例。

Listing 23-5. Querying the TempTable memory usage
mysql> SELECT *
 FROM sys.memory_global_by_current_bytes
 WHERE event_name
 IN ('memory/temptable/physical_ram',
 'memory/temptable/physical_disk')\G
*************************** 1. row ***************************
 event_name: memory/temptable/physical_ram
 current_count: 14
 current_alloc: 71.00 MiB
current_avg_alloc: 5.07 MiB
 high_count: 15
 high_alloc: 135.00 MiB
 high_avg_alloc: 9.00 MiB
*************************** 2. row ***************************
 event_name: memory/temptable/physical_disk
 current_count: 1
 current_alloc: 64.00 MiB
current_avg_alloc: 64.00 MiB
 high_count: 1
 high_alloc: 64.00 MiB
 high_avg_alloc: 64.00 MiB
2 rows in set (0.0012 sec)
mysql> SELECT *
 FROM performance_schema.memory_summary_global_by_event_name
 WHERE EVENT_NAME
 IN ('memory/temptable/physical_ram',
 'memory/temptable/physical_disk')\G
*************************** 1. row ***************************
 EVENT_NAME: memory/temptable/physical_disk
 COUNT_ALLOC: 2
 COUNT_FREE: 1
 SUM_NUMBER_OF_BYTES_ALLOC: 134217728
 SUM_NUMBER_OF_BYTES_FREE: 67108864
 LOW_COUNT_USED: 0
 CURRENT_COUNT_USED: 1
 HIGH_COUNT_USED: 1
 LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 67108864
 HIGH_NUMBER_OF_BYTES_USED: 67108864
*************************** 2. row ***************************
 EVENT_NAME: memory/temptable/physical_ram
 COUNT_ALLOC: 27
 COUNT_FREE: 13
 SUM_NUMBER_OF_BYTES_ALLOC: 273678336
 SUM_NUMBER_OF_BYTES_FREE: 199229440
 LOW_COUNT_USED: 0
 CURRENT_COUNT_USED: 14
 HIGH_COUNT_USED: 15
 LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 74448896
 HIGH_NUMBER_OF_BYTES_USED: 141557760
2 rows in set (0.0004 sec)
mysql> SELECT *
 FROM performance_schema.memory_summary_by_thread_by_event_name
 WHERE EVENT_NAME
 IN ('memory/temptable/physical_ram',
 'memory/temptable/physical_disk')
 AND COUNT_ALLOC > 0\G
*************************** 1. row ***************************
 THREAD_ID: 29
 EVENT_NAME: memory/temptable/physical_disk
 COUNT_ALLOC: 2
 COUNT_FREE: 1
 SUM_NUMBER_OF_BYTES_ALLOC: 134217728
 SUM_NUMBER_OF_BYTES_FREE: 67108864
 LOW_COUNT_USED: 0
 CURRENT_COUNT_USED: 1
 HIGH_COUNT_USED: 1
 LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 67108864
 HIGH_NUMBER_OF_BYTES_USED: 67108864
1 row in set (0.0098 sec)

查询内存

前两个查询请求全局用法而第三个查询请求每个线程的用法。第一个视图，该视图返回当时具有current_alloc 0 的事件。 这表明引擎正在使用中，并且部分数据已使用内存映射文件溢出到磁盘。第二个查询使用性能架构，并且将始终返回这两个事件的数据，即使当前没有分配给它内存。第三个查询显示哪些线程已分配内存。由于实现方式，无法查看哪些线程使用性能架构在磁盘上具有文件。

总结

本章介绍配置 MySQL 实例的一般注意事项以及最需要调整的选项。当您考虑对配置进行更改时，最重要的是考虑为什么要进行更改，它应该解决什么，以及为什么它会解决它，以及您确认它是否有效。最好通过一次对单个选项进行小增量更改来确认这一点。

最有可能从非默认值中受益的缓冲池大小的选项。讨论的其他 InnoDB 选项控制缓冲池实例的数量、转储时包含多少缓冲池、旧块子列表、如何刷新页面以及重做日志缓冲区的大小。

在 MySQL8.0.14 及更晚的中，支持并行执行某些查询。您可以使用从选项限制并行性，该选项指定 InnoDB 将跨所有连接创建并行线程的总最大值。并行执行线程被视为后台线程，仅在并行执行查询时才存在。

您的查询也可能从每个查询缓冲区的较大值中受益，但必须小心，因为较大的值不一定比较小的值工作得更好。建议使用这些缓冲区的默认值，并且仅对测试证明有显著益处的查询增加它们。

最后，讨论了内部临时表。在 MySQL 8 中，引擎，该引擎支持在达到全局最大内存使用量时溢出到磁盘。在磁盘上存储内部临时表时，还可以将其转换为 InnoDB。

下一章将介绍如何更改查询以执行更好的操作。