Aurora MySQL 中 MySQL 功能的建议 - Amazon Aurora
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅 中国的 Amazon Web Services 服务入门 (PDF)

Aurora MySQL 中 MySQL 功能的建议

以下功能在 Aurora MySQL 中可用于实现 MySQL 兼容性。但是,它们在 Aurora 环境中存在性能、可扩展性、稳定性或兼容性问题。因此,我们建议您在使用这些功能时遵循某些准则。例如,我们不建议使用某些功能进行生产 Aurora 部署。

在 Aurora MySQL 中使用多线程复制

使用多线程二进制日志复制时,SQL 线程会从中继日志中读取事件并将其排队,以便 SQL 工作线程应用。SQL 工作线程由协调器线程管理。尽可能并行应用二进制日志事件。

Aurora MySQL 版本 3 和 Aurora MySQL 2.12.1 及更高版本支持多线程复制。

对于低于 3.04 的 Aurora MySQL 版本,当 Aurora MySQL 数据库集群用作二进制日志复制的只读副本时,Aurora 默认情况下使用单线程复制。

早期的 Aurora MySQL 版本 2 继承了 MySQL Community Edition 中多线程复制方面存在的一些问题。对于这些版本,建议您不要在生产环境中使用多线程复制。

如果您确实要使用多线程复制,建议您对其进行全面测试。

有关在 Amazon Aurora 中使用复制的更多信息,请参阅使用 Amazon Aurora 进行复制。有关 Aurora MySQL 中多线程复制的更多信息,请参阅多线程二进制日志复制

使用本机 MySQL 函数调用 Amazon Lambda 函数

我们建议使用原生 MySQL 函数 lambda_synclambda_async 以调用 Lambda 函数。

如果使用已弃用的 mysql.lambda_async 过程,我们建议您将对 mysql.lambda_async 过程的调用封装在一个存储过程中。您可以从不同的来源调用该存储过程,例如,触发器或客户端代码。这种方法可以帮助您避免出现阻抗不一致问题,并使数据库编程人员更轻松地调用 Lambda 函数。

有关从 Amazon Aurora 中调用 Lambda 函数的更多信息,请参阅 从 Amazon Aurora MySQL 数据库集群中调用 Lambda 函数

避免将 XA 事务与 Amazon Aurora MySQL 结合使用

我们建议您不要在 Aurora MySQL 中使用扩展架构 (XA) 事务,因为在 XA 处于 PREPARED 状态时,它们可能会造成较长的恢复时间。如果必须在 Aurora MySQL 中使用 XA 事务,请遵循以下最佳实践:

  • 不要在 PREPARED 状态下打开 XA 事务。

  • 使 XA 事务尽可能小。

有关将 XA 事务与 MySQL 结合使用的更多信息,请参阅 MySQL 文档中的 XA 事务

在 DML 语句执行期间保持打开外键

foreign_key_checks 变量设置为 0 (off) 时,我们强烈建议您不要运行任何数据定义语言 (DDL) 语句。

如果您需要插入或更新暂时违反外键的行,请执行以下步骤:

  1. foreign_key_checks 设置为 0

  2. 执行数据操纵语言 (DML) 更改。

  3. 确保您完成的更改不会违反任何外键约束。

  4. foreign_key_checks 设置为 1 (on)。

此外,请遵循外键约束的这些其他最佳实践:

  • 确保客户端应用程序未将 foreign_key_checks 变量作为 0 变量的一部分设置为 init_connect

  • 如果从诸如 mysqldump 这样的逻辑备份还原失败或还原不完整,请务必先将 foreign_key_checks 设置为 1,然后再在同一会话中开始任何其他操作。当逻辑备份开始时,它会将 foreign_key_checks 设置为 0

配置刷新日志缓冲区的频率

在 MySQL 社区版中,为了使事务持久,必须将 InnoDB 日志缓冲区刷新到持久存储。您可以使用 innodb_flush_log_at_trx_commit 参数来配置将日志缓冲区刷新到磁盘的频率。

在将 innodb_flush_log_at_trx_commit 参数设置为默认值 1 时,每次事务提交时都会刷新日志缓冲区。此设置有助于保持数据库符合 ACID 要求。我们建议您保留原定设置 1。

innodb_flush_log_at_trx_commit 更改为非默认值有助于减少数据操作语言(DML)延迟,但会牺牲日志记录的持久性。这种缺乏持久性使数据库不符合 ACID 要求。我们建议您的数据库符合 ACID 要求,以避免在服务器重新启动时丢失数据的风险。有关此参数的更多信息,请参阅 MySQL 文档中的 innodb_flush_log_at_trx_commit

在 Aurora MySQL 中,重做日志处理被卸载到存储层,因此数据库实例上不会发生刷新到日志文件的过程。发出写入操作时,重做日志将从写入器数据库实例直接发送到 Aurora 集群卷。跨网络执行的仅有的写入操作是写入重做日志记录。始终不会从数据库层写入任何页面。

默认情况下,每个提交事务的线程都要等待来自 Aurora 集群卷的确认。此确认表明该记录和所有先前的重做日志记录均已写入并已达到法定数目。无论是通过自动提交还是显式提交,保留日志记录并达到法定数目都会使事务变得持久。有关 Aurora 存储架构的更多信息,请参阅 Amazon Aurora 存储揭秘

Aurora MySQL 不会像 MySQL 社区版那样将日志刷新到数据文件。但是,在将重做日志记录写入 Aurora 集群卷时,您可以使用 innodb_flush_log_at_trx_commit 参数放松持久性约束。

对于 Aurora MySQL 版本 2:

  • innodb_flush_log_at_trx_commit = 0 或 2 – 数据库不会等待有关重做日志记录已写入 Aurora 集群卷的确认。

  • innodb_flush_log_at_trx_commit = 1 – 数据库会等待有关重做日志记录已写入 Aurora 集群卷的确认。

对于 Aurora MySQL 版本 3:

  • innodb_flush_log_at_trx_commit = 0 – 数据库不会等待有关重做日志记录已写入 Aurora 集群卷的确认。

  • innodb_flush_log_at_trx_commit = 1 或 2 – 数据库会等待有关重做日志记录已写入 Aurora 集群卷的确认。

因此,要在 Aurora MySQL 版本 3 中获得与 Aurora MySQL 版本 2 中将该值设置为 0 或 2 相同的非默认行为,请将此参数设置为 0。

虽然这些设置可以降低客户端的 DML 延迟,但也可能导致在故障转移或重新启动时丢失数据。因此,我们建议您将 innodb_flush_log_at_trx_commit 参数保持设置为默认值 1。

虽然在 MySQL 社区版和 Aurora MySQL 中都可能发生数据丢失,但由于每个数据库的架构不同,对于它们的行为也有所不同。这些架构差异可能导致不同程度的数据丢失。要确保您的数据库符合 ACID 要求,请始终将 innodb_flush_log_at_trx_commit 设置为 1。

注意

在 Aurora MySQL 版本 3 中,在将 innodb_flush_log_at_trx_commit 更改为 1 以外的值之前,必须先将 innodb_trx_commit_allow_data_loss 的值更改为 1。这样做即表示您承认数据丢失的风险。

最大限度地减少 Aurora MySQL 死锁以及排查相关问题

运行工作负载的用户在同时修改同一数据页上的记录时,如果经常遇到对唯一的二级索引或外键违反约束的情形,则可能会遇到更多的死锁和锁等待超时。这些死锁和超时是由于 MySQL 社区版错误修复造成的。

此修复包含在 MySQL 社区版版本 5.7.26 及更高版本中,并已向后移植到 Aurora MySQL 版本 2.10.3 及更高版本中。该修复对于实施可序列性是必要的,其具体方法为:对于这些类型的数据操作语言(DML)操作,针对 InnoDB 表中的记录更改实施额外的锁定。此问题是在调查先前的 MySQL 社区版错误修复所引入的死锁问题时发现的。

该修复更改了对于 InnoDB 存储引擎中元组(行)更新的部分回滚的内部处理。在外键或唯一二级索引上生成违反约束的操作会导致部分回滚。这包括但不限于并发 INSERT...ON DUPLICATE KEY UPDATEREPLACE INTO,INSERT IGNORE 语句(upsert)。

在这种情况下,部分回滚不是指应用程序级事务的回滚,而是当遇到违反约束的情形时,InnoDB 对聚集索引更改进行内部回滚。例如,在 upsert 操作期间发现了重复的键值。

在正常的插入操作中,InnoDB 会自动为每个索引创建聚集和二级索引条目。如果 InnoDB 在 upsert 操作期间在唯一二级索引上检测到重复值,则必须还原聚集索引中插入的条目(部分回滚),然后必须将更新应用于现有的重复行。在此内部部分回滚步骤中,InnoDB 必须锁定被视为操作的一部分的每条记录。该修复通过在部分回滚后引入额外的锁定来确保事务的可序列性。

最大限度地减少 InnoDB 死锁

您可以采用以下方法来减少数据库实例中出现死锁的频率。可以在 MySQL 文档中找到更多示例。

  1. 为了减少出现死锁的几率,请在进行一组相关更改后立即提交事务。可以通过将大型事务(两次提交之间的多行更新)分解为较小的事务来做到这一点。如果您要批量插入行,则请尝试减少批量插入大小,尤其是在使用前面提到的 upsert 操作时。

    要减少可能的部分回滚次数,可以尝试以下一些方法:

    1. 将批量插入操作替换为一次插入一行。这可以减少可能存在冲突的事务持有锁的时间量。

    2. 不使用 REPLACE INTO,而是将 SQL 语句重写为多语句事务,如以下所示:

      BEGIN; DELETE conflicting rows; INSERT new rows; COMMIT;
    3. 不使用 INSERT...ON DUPLICATE KEY UPDATE,而是将 SQL 语句重写为多语句事务,如以下所示:

      BEGIN; SELECT rows that conflict on secondary indexes; UPDATE conflicting rows; INSERT new rows; COMMIT;
  2. 避免长时间运行的事务,无论是活跃的还是空闲的,因为它们可能会被锁定。这包括交互式 MySQL 客户端会话,这些会话可能会在未提交的事务中打开很长一段时间。在优化事务大小或批次大小时,影响可能会有所不同,具体取决于许多因素,例如并发性、重复项数和表结构。任何更改都应根据您的工作负载进行实施和测试。

  3. 在某些情况下,当两个事务尝试以不同的顺序访问一个或多个表中的相同数据集时,可能会出现死锁。为防止这种情况,您可以修改事务以按相同顺序访问数据,从而使访问序列化。例如,创建要完成的事务队列。当多个事务同时发生时,这种方法可以帮助避免死锁。

  4. 向表中添加精心选择的索引可以提高选择性并减少访问行的需求,从而减少锁定。

  5. 如果您遇到间隙锁定,可以将会话或事务的事务隔离级别修改为 READ COMMITTED,以防止出现这种情况。有关 InnoDB 隔离级别及其行为的更多信息,请参阅 MySQL 文档中的事务隔离级别

注意

虽然您可以采取预防措施来降低死锁发生的可能性,但死锁是数据库的预期行为,仍然可能发生。应用程序应具有必要的逻辑,以便在遇到死锁时进行处理。例如,在应用程序中实现重试和退避逻辑。最好解决问题的根本原因,但如果确实出现死锁,应用程序可以选择等待并重试。

监控 InnoDB 死锁

当应用程序事务尝试以导致循环等待的方式获取表级和行级锁定时,MySQL 中可能会发生死锁。偶尔的 InnoDB 死锁不一定是问题,因为 InnoDB 存储引擎会立即检测到这一情况并自动回滚其中一个事务。如果您经常遇到死锁,我们建议您检查和修改您的应用程序,以缓解性能问题并避免死锁。开启死锁检测(原定设置)时,InnoDB 会自动检测事务死锁并回滚一个或多个事务以打破死锁。InnoDB 尝试挑选要回滚的小事务,其中事务的大小由插入、更新或删除的行数决定。

  • SHOW ENGINE 语句 – SHOW ENGINE INNODB STATUS \G 语句包含自上次重新启动以来在数据库上遇到的最新死锁的详细信息

  • MySQL 错误日志 – 如果您经常遇到死锁,其中 SHOW ENGINE 语句的输出不充足,则可以开启 innodb_print_all_deadlocks 数据库集群参数。

    开启此参数后,有关 InnoDB 用户事务中所有死锁的信息将记录在 Aurora MySQL 错误日志中。

  • Amazon CloudWatch 指标 – 我们还建议您使用 CloudWatch 指标 Deadlocks 主动监控死锁。有关更多信息,请参阅 Amazon Aurora 的实例级指标

  • Amazon CloudWatch Logs – 利用 CloudWatch Logs,您可以查看指标、分析日志数据并创建实时警报。有关更多信息,请参阅使用 Amazon CloudWatch 监控 Amazon Aurora MySQL 和 Amazon RDS for MySQL 中的错误,并使用 Amazon SNS 发送通知

    使用已开启 innodb_print_all_deadlocks 的 CloudWatch Logs,您可以配置警报,以便在死锁数量超过给定阈值时通知您。要定义阈值,我们建议您观察趋势并使用基于正常工作负载的值。

  • Performance Insights – 当您使用 Performance Insights 时,您可以监控 innodb_deadlocksinnodb_lock_wait_timeout 指标。有关这些指标的更多信息,请参阅 Aurora MySQL 的非本机计数器