mysql 有等待事件,排查性能详情中活动会话正在 SYNCH 等待事件状态下等待的问题...

为什么我的 MySQL 数据库实例在性能详情中显示大量活动会话正在 SYNCH 等待事件状态下等待？

上次更新时间：2020 年 12 月 14 日

我有 RDS MySQL、RDS MariaDB 或 Amazon Aurora MySQL 实例，并启用了性能详情。我的数据库实例显示大量平均活动会话 (AAS) 正在同步 (SYNCH) 等待事件状态下等待。为什么 SYNCH 事件会减慢我的查询速度？如何提高数据库实例的性能？

简短描述

如果您在性能详情中看到 MySQL SYNCH 等待事件，这意味着数据库中的大量会话正在尝试访问相同的受保护对象或内存结构。MySQL 中的受保护对象包括以下内容：

二进制日志源实例中的活动二进制日志文件 - 包含在任何时候都只允许一个会话执行读取或写入的互斥锁。

数据字典 - 用于写入，通常由数据控制语言 (DCL) 或数据定义语言 (DDL) 语句引起。

自适应哈希索引 - 包含在任何时候都只允许一个会话执行读取或写入的互斥锁。

打开的表缓存 - 只有一个会话可以在该缓存中添加或删除表。

InnoDB 缓冲池内的每个单数据库块 - 每次只有一个会话可以修改内存中块的内容。

注意：您可以通过多个步骤来提高 SQL 查询的并行性。在有些情况下，您需要仔细研究应用程序的架构，以及应用程序如何使用数据库来解决这些问题。

解决方法

确保数据库实例有足够的 CPU 资源来处理工作负载

如果您有大量会话在 SYNCH 事件下等待，则会导致 CPU 使用率过高。如果使用率达到 100%，则会增加正在等待的会话数量。进行故障排查时，请升高数据库实例的大小，以确保有足够的 CPU 来处理额外的工作负载。

由于这些事件通常很短暂，因此 Amazon CloudWatch CPU 利用率指标可能无法正确显示峰值使用率。检查此问题的最佳方法是使用 RDS 增强监控 中的一秒钟 CPU 计数器。这些计数器更加精细和详细。

增加 MySQL 的互斥锁/锁定等待阵列

MySQL 使用内部数据结构来协调线程。默认情况下，此阵列的大小为 1。这适用于单 CPU 计算机，但在拥有多个 CPU 的计算机上可能会产生问题。如果您的工作负载有大量等待线程，请增加阵列大小。将 MYSQL 参数 innodb_sync_array_size 设置为 CPU 的数量(或更高，最高 1024)。

注意：innodb_sync_array_size 参数仅在数据库启动时应用。

减少并发

一般来说，并行性有助于提高吞吐量。但是，当大量会话尝试执行相同或类似的活动时，会话需要访问相同的受保护对象。会话数量越多，您所用的 CPU 中正在等待的就越多。

分散这些活动的时间，或者将其安排为连续进行。您还可以将多个操作捆绑到单个语句中，例如多行插入。

检查具体的等待事件

使用下面的示例解决方案来排查您的特定等待事件：

synch/rwlock/innodb/dict sys RW lock 或 synch/rwlock/innodb/dict_operation_lock - 这表示同时触发了 DDL 的大量并发 DCL。减少应用程序在常规应用活动期间对使用 DDL 的依赖。

synch/cond/sql/MDL_context::COND_wait_status - 这表示尝试访问 DCL 或 DDL 正在修改的表的 SQL(包括选择)的数量很多。避免在常规应用程序活动期间将 DDL 语句运行到高流量表。

synch/cond/sql/MYSQL_BIN_LOG::COND_done / synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit 或 synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log - 您已启用二进制日志记录，可能存在高提交吞吐量、大量事务提交、副本阅读二进制日志或它们的组合。考虑将数据库升级到与 5.7 或更高版本兼容的主要版本。此外，使用多行语句，或将多个语句捆绑到单个事务中。在 Aurora 中，使用全局数据库而非二进制日志复制，或者使用 aurora_binlog_* 参数。

synch/mutex/sql/LOCK_open 或 synch/mutex/sql/LOCK_table_cache - 这表示您的会话打开的表数量超过表定义缓存或表打开缓存的大小。升高这些缓存的大小。

synch/mutex/sql/LOG - 您的数据库可能正在执行大量语句，并且当前的日志记录方法无法跟上。如果您使用的是 TABLE 输出方法，请尝试改用 FILE。如果您使用的是常规日志，请改用 Aurora 的高级审计。如果对 long_query_time 参数使用零或小于一的值，请尝试升高。

synch/mutex/innodb/buf_pool_mutex 或 synch/mutex/innodb/aurora_lock_thread_slot_futex 或 synch/rwlock/innodb/index_tree_rw_lock - 有大量类似的 DML 同时访问同一个数据库对象。尝试使用多行语句。此外，将工作负载分散到不同的数据库对象。可行的一种方法是使用分区。