复制
多年来帮助 MySQL 如此流行的功能之一是支持复制,它允许您拥有一个 MySQL 实例,该实例会自动接收来自其源的更新并应用它们。使用快速事务和低延迟网络,复制可以接近实时,但请注意,由于除了 NDB 群集之外,MySQL 中没有同步复制,因此仍有可能较大的延迟。数据库管理员的一项经常性任务是提高复制的性能。多年来,MySQL 复制有许多改进,包括一些可帮助您提高复制性能的改进。
本章将首先提供复制的高级别概述,以便介绍术语和测试设置,用于复制监视部分。本章的另一半讨论如何提高连接和应用程序线程的性能,以及如何使用复制将工作卸载到副本。
复制概述
在深入了解提高复制性能之前,讨论复制的工作原理非常重要。这将有助于就术语达成一致,并在本章其余部分为讨论提供参考点。
的工作原理是记录对复制源所做的更改,然后将其发送到连接线程存储数据并应用数据的一个或多个应用线程的副本。图显示了复制的简化概述,省略了与存储引擎和实现详细信息相关的所有内容。
当事务提交其更改时,这些更改将写入 InnoDB 特定文件(重做日志和数据文件)和二。二进制日志由一系列文件以及索引文件以及列出二进制日志文件的索引文件组成。将事件写入二进制日志文件后,它们将发送到副本。可能有多个副本,在这种情况下,事件将发送到所有副本。
在副本上,连接接收事件并将它们写入中。中继日志的工作方式与二进制日志相同,只是它用作临时存储,直到应用线程可以应用事件。可能有一个或多个应用程序线程。也可能副本从多个源(称为多源复制)复制,在这种情况下,每个复制通道有一组一个连接线程和一个或多个。(也就是说,最常见的是每个副本的单个源。或者,副本将更改写入其自己的二进制日志,使其成为复制副本进一步下游复制链的源。在这种情况下,通常调用它为中。图显示了一个使用副本接收来自两个源的更新的设置示例,其中一个是中继实例。
此处复制到中继,而中继实例又复制到。也会复制到实例。每个都有一个名称,可以区分它们,在多源复制中,每个通道必须具有唯一的名称。默认通道名称为空字符串。讨论监视时,它将使用像图中的复制设置。
监测
当您遇到复制性能问题时,第一步是确定在上一节中描述的步骤链中引入延迟的地点。如果您在早期版本的 MySQL 中一直在使用复制,可以跳转到以检查复制的运行状况;如果一直在使用复制,可以跳转到”显示从状态”命令,以检查复制的运行状况。但是,在 MySQL 8 中,这是要检查的最后一个监视信息源。
在 MySQL 8 中,监视复制信息的主要来源是其中包含多个表,描述复制副本上每个复制步骤的配置和状态。性能架构表的一些优点如下:
- 状态表包括有关复制延迟的更多详细信息,其形式为复制过程中的每个步骤提供微秒分辨率,并且具有来自原始和直接源的时间戳。
- 您可以使用 SELECT 语句表。这允许您查询您最感兴趣的信息,并可以操作数据。当您有多个复制通道时,这一优势尤其明显,的输出在控制台中检查时会很快变得难以使用。
- 数据被拆分为逻辑组,每个组有一个表。配置和应用程序进程有单独的表,配置和状态有单独的表。
首次开始使用性能架构复制表时,可能很难理解这些表之间的关系及其与复制流的关系。图显示了单个复制通道的复制流,并添加了与它们包含的信息对应的复制表。图中的表也可用于组复制,通道用于事务通道。
事件从直接源到达图形的顶部,由具有两个表的连接线程处理,该连接和。连接线程将事件写入中继日志,并且应用器在应用复制筛选器时从中继日志中读取事件。复制筛选器可以在”复制”和中找到。 可以在”应用程序”和”应用程序”表中应用状态。
在并行也称为多线程,协调器然后处理事务,并使其可供工作人员使用。可以通过该表监视如果副本使用单线程复制,则跳过协调器步骤。
最后一步是应用程序工作人员。在并行复制的情况下,每个线程,并且每个线程都有一个行,其状态中。
本节的其余部分将介绍连接和应用程序的性能架构复制表以及日志状态和组复制表。
连接表
复制事件到达副本时的第一步是将它们写入中继日志。处理此操作的是连接线程。
有两表提供与连接相关的信息:
- 复制通道的配置。
- 的状态。这包括显示最初提交最后一个和当前队列事务的时间戳、在直接源实例上提交的时间以及写入中继日志的时间戳。每个通道有一行。
复制连接表包括与连接到直接上游源相关的信息,以及在原始源上提交最新接收事件时的时间戳。在简单的复制设置中,即时源和原始源是相同的,但在链式复制中,两者是不同的。清单显示了上一节中讨论的中两个连接表的内容示例。为了提高本书的可读性,对输出重新格式化。原始格式化的输出(包括复制通道的行)包含在文件。
Listing 26-1. The replication connection tables
mysql> SELECT *
FROM performance_schema.replication_connection_configuration
WHERE CHANNEL_NAME = 'relay'\G
*************************** 1. row ***************************
CHANNEL_NAME: relay
HOST: 127.0.0.1
PORT: 3308
USER: root
NETWORK_INTERFACE:
AUTO_POSITION: 1
SSL_ALLOWED: YES
SSL_CA_FILE:
SSL_CA_PATH:
SSL_CERTIFICATE:
SSL_CIPHER:
SSL_KEY:
SSL_VERIFY_SERVER_CERTIFICATE: NO
SSL_CRL_FILE:
SSL_CRL_PATH:
CONNECTION_RETRY_INTERVAL: 60
CONNECTION_RETRY_COUNT: 86400
HEARTBEAT_INTERVAL: 30
TLS_VERSION:
PUBLIC_KEY_PATH:
GET_PUBLIC_KEY: NO
NETWORK_NAMESPACE:
COMPRESSION_ALGORITHM: uncompressed
ZSTD_COMPRESSION_LEVEL: 3
1 row in set (0.0006 sec)
mysql> SELECT *
FROM performance_schema.replication_connection_status
WHERE CHANNEL_NAME = 'relay'\G
*************************** 1. row ***************************
CHANNEL_NAME: relay
GROUP_NAME:
SOURCE_UUID: cfa645e7-b691-11e9-a051-
ace2d35785be
THREAD_ID: 44
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 26
LAST_HEARTBEAT_TIMESTAMP: 2019-08-11
10:26:16.076997
RECEIVED_TRANSACTION_SET: 4d22b3e5-a54f-11e9-8bdbace2d35785be:23-44
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
LAST_QUEUED_TRANSACTION: 4d22b3e5-a54f-11e9-8bdbace2d35785be:44
LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-08-11 10:27:09.483703
LAST_QUEUED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-08-11 10:27:10.158297
LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP: 2019-08-11 10:27:10.296164
LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP: 2019-08-11 10:27:10.299833
QUEUEING_TRANSACTION:
QUEUEING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
QUEUEING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
QUEUEING_TRANSACTION_START_QUEUE_TIMESTAMP: 0000-00-00 00:00:00
1 row in set (0.0006 sec)
配置表在很大程度上对应于使用 CHANGE Master TO 语句设置复制时可以提供,并且除非显式更改配置,否则数据是静态的。状态表主要包含随着事件处理而快速变化的可变数据。
状态表中的时间戳特别令人感兴趣。有两个组,第一组显示最后一个排队事件的时间戳,第二个组显示当前正在排队的事件的时间戳。正在排队的事件意味着它正在写入中继日志。例如,请考虑事件的时间戳:
- 事件在原始源上提交的时间(来源
- 事件在直接源上提交的时间(中)。
- 此实例开始排队的事件的时间 , 即接收事件和连接线程开始将事件写入中继日志的时间。
- 连接线程完成将事件写入中继日志的时间。
微秒分辨率显示,因此它允许您详细了解事件从原始源到中继日志的进行多长时间。零时间戳 () 表示没有要返回的数据;例如,当连接线程完全保持最新时,当前排队时间戳可能会发生这种情况。applier 表提供了有关事件通过副本的旅途中的更多详细信息。
应用表
应用线程更为复杂,因为它们既处理事件筛选和应用事件,又支持并行应用器。
在编写时,存在以下表,包含有关 applier 线程的信息:
- 此表显示每个复制通道的 applier 线程的配置。目前唯一的设置是配置的复制延迟。每个通道有一行。
- 每个复制通道的复制筛选器。该信息包括筛选器的配置和激活时间。
- 应用于所有复制通道的复制筛选器。该信息包括筛选器的配置和激活时间。
- 应用器的总体状态,包括服务状态、剩余延迟(当配置了所需的延迟时)以及事务已出现的重报数。每个通道有一行。
- 使用并行复制时,协调器线程看到的应用器状态。上次处理的事务和当前处理的事务有时间戳。每个通道有一行。对于单线程复制,此表为空。
- 每个”应用器”状态。上次应用的事务和当前应用的事务有时间戳。配置并行复制时,每个工作线程有一行(每个通道配置的数)。对于单线程复制,每个通道有一行。
在高级别上,applier 表遵循与连接表相同的模式,增加了筛选器配置表并支持并行应用器。清单显示了中继表的示例。已对输出进行重新格式化,以提高可读性。在本书的 GitHub 存储库中中的文件中也有输出。
Listing 26-2. The replication_applier_status_by_worker table
mysql> SELECT *
FROM performance_schema.replication_applier_status_by_worker
WHERE CHANNEL_NAME = 'relay'\G
*************************** 1. row ***************************
CHANNEL_NAME: relay
WORKER_ID: 1
THREAD_ID: 54
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION:
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION:
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
*************************** 2. row ***************************
CHANNEL_NAME: relay
WORKER_ID: 2
THREAD_ID: 55
SERVICE_STATE: ON
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
LAST_APPLIED_TRANSACTION: 4d22b3e5-a54f11e9-8bdbace2d35785be:213
LAST_APPLIED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-08-11 11:29:36.1076
LAST_APPLIED_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-08-11 11:29:44.822024
LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP: 2019-08-11 11:29:51.910259
LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP: 2019-08-11 11:29:52.403051
APPLYING_TRANSACTION: 4d22b3e5-a54f-11e9-8bdbace2d35785be:214
APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP: 2019-08-11 11:29:43.092063
APPLYING_TRANSACTION_IMMEDIATE_COMMIT_TIMESTAMP: 2019-08-11 11:29:52.685928
APPLYING_TRANSACTION_START_APPLY_TIMESTAMP: 2019-08-11 11:29:53.141687
LAST_APPLIED_TRANSACTION_RETRIES_COUNT: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
LAST_APPLIED_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
APPLYING_TRANSACTION_RETRIES_COUNT: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_NUMBER: 0
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_MESSAGE:
APPLYING_TRANSACTION_LAST_TRANSIENT_ERROR_TIMESTAMP: 0000-00-00 00:00:00
时间与之前看到的相同的模式,该模式包含上次处理的事务和当前事务的信息。请注意,对于第一行,所有时间戳均为零,这表明应用者无法利用并行复制。
对于具有全局事务标识符 4d22b3e5-a54f-11e9-8bdb-ace2d35785be:213 的最后一个应用事务,可以看到事务是在 11:29:36.1076 在原始源上提交的, 在 11:29:44.822024 上提交,开始在 11:29:51.910259 执行此实例,并在 11:29:52.403051 完成执行。这表明每个实例都会增加大约 8 秒的延迟,但事务本身只需半秒即可执行。您可以得出结论,复制延迟不是由应用单个大型事务引起的,而是中继和副本实例无法像原始源那样快速处理事务、延迟是由较早运行的长期事件引入的,并且复制尚未赶上,或者延迟是在复制链的其他部分引入的。
日志状态
与复制相关的表是 log_status表它提供有关二进制日志、中继日志和 InnoDB 重做日志的信息,使用日志锁返回对应于同一时间点的数据。引入该表时时时时要牢记查询表需要具有权限。清单显示了使用函数的示例输出,以便更轻松地读取作为 JSON 文档返回的信息。
Listing 26-3. The log_status table
mysql> SELECT SERVER_UUID,
JSON_PRETTY(LOCAL) AS LOCAL,
JSON_PRETTY(REPLICATION) AS REPLICATION,
JSON_PRETTY(STORAGE_ENGINES) AS STORAGE_ENGINES
FROM performance_schema.log_status\G
*************************** 1. row ***************************
SERVER_UUID: 4d46199b-bbc9-11e9-8780-ace2d35785be
LOCAL: {
"gtid_executed": "4d22b3e5-a54f-11e9-8bdb-ace2d35785be:1-380,\ncbffdc28-
bbc8-11e9-9aac-ace2d35785be:1-190",
"binary_log_file": "binlog.000003",
"binary_log_position": 199154947
}
REPLICATION: {
"channels": [
{
"channel_name": "relay",
"relay_log_file": "relay-bin-relay.000006",
"relay_log_position": 66383736
},
{
"channel_name": "source2",
"relay_log_file": "relay-bin-source2.000009",
"relay_log_position": 447
}
]
}
STORAGE_ENGINES: {
"InnoDB": {
"LSN": 15688833970,
"LSN_checkpoint": 15688833970
}
}
1 row in set (0.0005 sec)
LOCAL包括有关已执行的全局事务标识符以及二进制日志文件和在此实例上的位置的信息。复制显示与复制过程相关的日志数据,每个通道有一个对象。列包含有关 InnoDB 日志序列号的信息。
组复制表
如果使用””,则有两个附加表可用于监视复制。一个表包含有关组成员的高级别信息,另一个表包含成员的各种统计信息。
两个表是
- 成员概述。每个成员有一行,数据包括当前状态以及它是主成员还是辅助成员。
- 较低,如队列中的事务数、对所有成员提交哪些事务、有多少事务在本地或远程发起,等等。
replication_group_members对于验证成员的状态最有用。replication_group_member_stats可用于查看每个节点如何查看已完成的工作,以及冲突和回滚率是否高。这两个表都包括来自群集中所有节点的信息。
现在您已经知道如何监视了,您可以开始优化连接和应用程序线程。
连接
连接线程处理到复制的直接源的出站连接、复制事件的接收以及将事件保存到中继日志。这意味着优化连接过程围绕复制事件、网络、维护有关已收到哪些事件的信息以及写入中继日志。
复制事件
使用(默认值和建议)时,事件包括有关已更改的行和新值(映像之前和之后)的信息。默认情况下,包含更新和删除事件的映像前的完整。这使得副本能够应用事件,即使源和副本的列顺序不同或具有不同的主要键定义。但是,它确实使二进制日志 (因此也中继日志) 更大,这意味着更多的网络流量、内存使用情况和磁盘 I/O。
如果不需要显示完整的图像之前,可以将”binlog_row_image最小意味着只有标识行所需的列包含在前图像中,后图像仅包括事件更改的列。使用时,除外的所有列都包含在上图中,图像中包含 Blob 和文本列(如果其值已更改)。 使用值是性能的最佳方法,但在对生产系统进行更改之前,请确保进行彻底测试。
也可以设置”,因此可以根据需要更改该选项。
网络
MySQL 中用于复制的网络的主要调优选项是使用的接口以及是否启用压缩。如果网络过载,它很快就会使复制落后。避免这种情况的选项是使用专用网络接口和路由进行复制流量。另一种选择是启用压缩,可以减少以更高的 CPU 负载代价传输的数据量。这两个解决方案都使用实现。
定义如何连接到复制源时,可以使用选项指定要用于连接的接口。例如,如果要使用副本上具有 IP 地址 192.0.2.102 的接口从源复制,则可以使用:
CHANGE MASTER TO MASTER_BIND='192.0.2.102',
MASTER_HOST='192.0.2.101',
MASTER_PORT=3306,
MASTER_AUTO_POSITION=1,
MASTER_SSL=1;
根据需要替换地址和其他信息。
压缩在 MySQL 8.0.18使用采用一组允许的算法的”压缩”选项。支持的算法是
- 压缩。这是默认值。
- 使用 zlib 压缩算法。
- 使用 ztd 版本 1.3 压缩算法。
如果包括算法,则可以使用选项指定压缩级别。支持级别为 1~22(两者包括),默认值为 3。将复制连接配置为使用压缩级别为 5 的或算法的示例是
CHANGE MASTER TO MASTER_COMPRESSION_ALGORITHMS='zlib,zstd',
MASTER_ZSTD_COMPRESSION_LEVEL=5;
在 MySQL 8.0.18 之前,您可以指定是否将一起使用。如果源和副本支持算法,则将选项设置为 会使复制连接使用 zlib 压缩。
维护源信息
副本需要跟踪从源接收的信息。这是通过表完成的。也可以将信息存储在文件中,但自 8.0.18 起,该信息已被弃用,不鼓励这样做。使用文件还可以降低副本从崩溃中恢复的弹性。
关于维护此信息的性能,因此重要的选择这指定信息更新的频率,默认值为每 10000 个事件。您可能认为,与源端类似的数据,在每个事件之后同步数据非常重要;然而,情况并非如此。
没有必要非常频繁地更新信息的原因是,通过丢弃中继日志和从应用程序到达点开始获取所有内容,可以从信息丢失中恢复。因此,默认值 10000 是好的,很少有任何理由更改它。
写入中继日志
日志是接收复制事件的连接和应用程序已处理它们之间的复制事件的中间存储。影响中继日志写入速度的因素主要有两个:磁盘性能和中继日志同步到磁盘的速度。
您需要确保写入中继日志的磁盘有足够的 I/O 容量来维持写入和读取活动。一个选项是将中继日志存储在单独的存储中,以便其他活动不会干扰中继日志的写入和读取。
使用”sync_relay_log”与磁盘默认值是每 10000 个事件同步一次。除非将基于位置的复制(禁用与并行应用线程一恢复中继日志。对于基于位置的并行复制除非在操作系统崩溃时重新生成副本是可以接受的。
这意味着,从性能角度来看,建议在执行 CHANGE Master TO并设置”=1。否则,请将与主信息相关的其他设置保留为默认值,并将中继日志保留为默认值。
The Applier
应用程序是复制延迟的最。主要问题是,对源所做的更改通常是高度并行工作负载的结果。相反,默认情况下,应用器是单线程的,因此单个线程必须跟上源上潜在的数十或数百个并发查询。这意味着,用于解决应用程序延迟的主要工具是启用并行复制。此外,还将讨论主键的重要性、放宽数据安全设置的可能性以及复制筛选器的使用。
并行应用器
将应用程序配置为使用多个线程并行应用事件是提高复制性能的最有力方法。但是,它不像将 1 的值那样简单。还有其他选项(在源和副本上)需要考虑。
表总结了影响并行复制的配置选项,包括是否应在源或副本上设置该选项。
选项名称和配置地点 | 描述 |
---|---|
binlog_transaction_dependency_tracking设置在源上 | 要在二进制日志中包含哪些有关事务之间依赖项的信息。 |
binlog_transaction_dependency_history_size设置在源上 | 上次更新行时保存信息的时间。 |
transaction_write_set_extraction设置在源上 | 如何提取写入集信息。 |
binlog_group_commit_sync_delay设置在源上 | 等待更多事务在组提交功能中分组在一起的延迟。 |
slave_parallel_workers设置在副本上 | 要为每个通道创建多少个应用线程 |
slave_parallel_type设置在副本上 | 是并行于数据库还是逻辑时钟。 |
slave_pending_jobs_size_max设置在副本上 | 可用于保存尚未应用的事件的内存量。 |
slave_preserve_commit_order设置在副本 | 是否确保副本以与源顺序相同的顺序将事务写入其二进制日志。启用此功能需要将设置为。 |
slave_checkpoint_group设置在副本上 | 要在检查点操作之间处理的最大事务数。 |
slave_checkpoint_period设置在副本上 | 检查点操作之间的最大时间(毫秒)。 |
选项最常用的是和副本上。
源上的二进制日志事务依赖项跟踪和写入集提取选项是相关的。选项指定如何提取写入集信息(有关哪些行受事务影响的信息)。写入集也是组复制用于冲突检测的用途。将它设置为这也是组复制所需的值。
指定二进制日志中可用的事务依赖项信息。对于并行复制来说,这一点对于能够知道哪些事务可以安全地并行应用非常重要。默认值是使用提交顺序并依赖于提交时间戳。为了改进根据逻辑时钟并行化时并行复制性能,请将
指定不断提供上次修改给定行的事务的信息的行哈希数。默认值 25000 通常足够大;但是,如果对不同行的修改率非常高,则值得增加依赖项历史记录大小。
在副本上,使用”slave_parallel_workers复制。 这是将每个复制通道创建的应用辅助线程数。设置得足够高,使复制保持,但不是太高,以最终有空闲工作人员或您看到来自过于平行的工作负荷的争用。
更新副本通常需要的另一个选项是”slave_parallel_type这指定了如何在应用工作者之间拆分事件。默认值为名称建议根据它们所属的架构拆分更新。另一种选择使用组提交信息或二进制日志中的写入集信息来确定哪些事务可以安全地一起应用。除非在二进制日志中包含多个层的副本通常是最佳选择。
如果在未启用使用并行化以在组提交功能中将更多事务组合在一起,但代价是更长的提交延迟。这将使并行复制有更多的事务在工作人员之间分布,从而提高有效性。
复制延迟的另一个主要因素是缺少主密钥。
主键
使用基于行的复制时,处理事件的应用人员必须找到必须更改的行。如果有主键则非常简单高效 , 只是一个主键查找。但是,如果没有主键,则必须检查所有行,直到找到所有列的值与复制事件前映像中的值相同的行。
如果表很大,这样的搜索是昂贵的。如果事务修改了相对较大的表中的许多行,则在最坏的情况下,复制似乎已经停止。MySQL 8 使用优化,它使用哈希匹配一组行与表;但是,有效性取决于在一个事件中修改的行数,并且它永远不会像主键查找那样有效。
强烈建议您向所有表添加显式主键(或非唯一键)。如果您自己不添加主密钥,则 InnoDB 会添加隐藏的主密钥(不能用于复制),因此没有磁盘空间或内存可以节省任何空间或内存。隐藏的主键是一个 6 字节整数,并使用全局计数器,因此,如果有许多具有隐藏主键的表,则计数器可能会成为瓶颈。此外,如果要使用组复制,则严格要求所有表都具有显式主键或非唯一索引。
如果无法向某些键,则哈希搜索算法效果更好,每个复制事件中包含的行数更多。通过增加复制的源实例上的 binlog_row_event_max_size 的大小,行数。
放松数据安全
提交事务时,必须在磁盘上保留事务。在 InnoDB 中,通过重做日志和通过二进制日志复制来保证持久性。在某些情况下,在副本上放宽更改已保留的保证是可以接受的。此优化要花费在操作系统崩溃时重建副本的费用。
InnoDBinnodb_flush_log_at_trx_commit,以确定每次事务提交时是否刷新重做日志。默认(和最安全的设置)是在每次提交后刷新)。冲洗是一项昂贵的操作,甚至某些 SSD 驱动器也遇到无法跟上繁忙系统所需的冲洗问题。如果能够承受丢失最多一秒钟的已提交事务的费用,您可以将innodb_flush_log_at_trx_commit 0 或 2。 如果您愿意进一步推迟刷新,这将设置刷新重做日志之间的最大时间量(以秒为单位)。默认值和最小值为 1 秒。这意味着,如果发生灾难性故障,您可能需要重建副本,但好处是,应用程序线程可以提交比源更便宜的更改,从而更容易跟上。
二进制日志同样使用选项,该选项也默认为 1,这意味着每次提交后刷新二进制日志。如果您不需要副本上的二进制日志(请注意,对于组复制,必须在所有注释上启用二进制日志),可以考虑完全禁用它或减少日志的同步频率。通常情况下,最好将 sync_binlog 设置为100 或 1000 而不是 0,因为 0 通常会导致整个二进制日志在旋转时一次刷新。刷新千兆字节可能需要几秒钟;同时,还有一个静音,可以防止提交事务。
复制筛选器
如果不需要副本上的所有数据,可以使用复制筛选器来减少应用线程所需的工作,并减少磁盘和内存要求。这还可以帮助副本保持最新的源。有六个选项来设置。选项可以分为三组,包括 do,如表。
选项名称 | 描述 |
---|---|
复制 - 做 db复制 - 忽略 - db | 是否包括作为值给出的架构(数据库)的更改。 |
复制 - 做表复制-忽略表 | 是否包括作为值给出的表的更改。 |
复制野生做表复制野生忽略表 | 与和一样,但对 _ 通配符的支持与编写子句时相同。 |
指定其中一个选项时,可以选择使用规则应应用于和冒号的通道名称为架构/表添加前缀。例如,忽略源 的世界架构更新
[mysqld]
replicate-do-db = source2:world
这些选项只能在 MySQL 配置文件,并且需要重新启动 MySQL 才能生效。您可以多次指定每个选项以添加多个规则。如果需要动态更改配置,可以使用”更改复制筛选器”筛选器,例如:
mysql> CHANGE REPLICATION FILTER
REPLICATE_IGNORE_DB = (world)
FOR CHANNEL 'source2';
Query OK, 0 rows affected (0.0003 sec)
需要使用世界各地的,因为如果需要包含多个数据库,可以指定列表。如果多次指定同一规则,则应用后者,而忽略前者。
复制筛选器最适合基于行的复制,因为可以清楚地了解哪个表受事件影响。当您有一个语句时,该语句可能会影响多个表,因此对于基于语句的复制,并不总是清楚筛选器是否应允许该语句。应特别注意复制复制就像使用基于语句的复制一样,它们使用默认架构来决定是否允许语句。更糟糕的是,使用具有行和语句事件混合的复制筛选器,因为筛选器的效果可能取决于更改复制的格式。
结束关于如何提高复制性能。还剩下一个主题,这与目前讨论的内容正好相反 — 如何使用副本提高源的性能。
将工作卸载到副本
如果实例因读取查询而重载时出现问题,则提高性能的常见策略是将某些工作卸载到一个或多个副本。一些常见方案是使用副本进行读取横向扩展,并使用副本进行报告或备份。本节将介绍此内容。
读取横向扩展
复制的最常见用途之一是允许查询使用副本,从而减少复制源上的负载。这是可能的,因为副本具有与其源相同的数据。需要注意的主要事情是,即使在最好的时候,在源上提交事务的小延迟,直到副本发生更改。
如果应用程序对读取陈旧数据很敏感,则选择”组复制”或”InnoDB 群集”,该群集在版本 8.0.14 及更晚版本中支持一致性级别,因此您可以确保应用程序使用所需的一致性级别。
使用副本还可以帮助您使应用程序和 MySQL 更接近最终用户,从而减少往返延迟,从而获得更好的体验。
任务分离
副本的另一个常见用途是对副本执行影响任务,以减少复制源上的负载。两个典型的任务是报告和备份。
使用副本报告查询时,配置副本的方式可能与源不同,以针对它用于的特定工作负荷对其进行优化。。也可以使用复制筛选器来避免包括来自源的所有数据和更新。数据越少意味着副本必须应用更少的事务并写入更少的数据,并且您可以将较大比例的数据读取到缓冲池中。
使用副本进行备份也很常见。如果副本专用于备份,则无需担心由于磁盘 I/O 或缓冲池污染而导致的锁和性能下降,只要副本在下次备份之前可以赶上。您甚至可以考虑在备份期间关闭副本并执行冷备份。
总结
本章介绍复制的工作原理、如何监视和改进复制过程的性能,以及如何使用复制在多个实例之间分发工作。
本章的开始与复制有关,包括介绍术语,并展示了在什么地方可以找到复制的监视信息。在 MySQL 8 中,监视复制的最佳方式是使用一系列性能架构表,这些表根据线程类型以及信息是配置还是状态来拆分信息。还有专用于日志状态和组复制的表。
可以通过减少复制事件的大小来优化连接线程,只需包含有关复制事件中更新行之前值的最小信息。不过,这并不能适用于所有应用程序。您还可以更改网络和写入中继日志。建议使用启用自动定位的基于 GTID 的复制,从而放松中继日志的同步。
对于 applier 的性能来说,最重要的两点是启用并行复制并确保所有表都有主键。并行复制可以通过更新影响架构或逻辑时钟进行。后者通常是性能最好的,但也有例外,因此您需要验证您的工作负载。
最后,讨论了如何使用副本来卸载在复制源上必须执行的工作。您可以使用复制进行读取横向扩展,因为您可以使用副本读取数据,并专用于需要写入数据的任务的源。您还可以将副本用于高度密集的工作,如报告和备份。
最后一章将介绍通过使用缓存完成的量。