为什么我的 SELECT 查询在我的 Amazon Aurora MySQL 数据库集群上运行缓慢？
上次更新日期：2020 年 12 月 10 日
我有一个 Amazon Aurora for MySQL 数据库集群，我想使用 SELECT 查询从我的数据库中选择数据。当我在数据库集群上运行 SELECT 查询时，查询运行缓慢。如何识别 SELECT 查询缓慢的原因并修复它们？

简短描述
SELECT 查询可能会在 Amazon Aurora for MySQL 数据库集群上运行缓慢的原因有很多。
您的 Amazon Relational Database Service (Amazon RDS) 系统资源已过度利用。发生这种情况的原因可能是 CPU 高、内存不足或工作负载超出数据库实例类型能够处理的范围。数据库正在锁定，由此产生的等待事件导致 SELECT 查询执行不佳。 SELECT 查询正在对大型表执行完整表扫描，或者查询缺乏必要的索引。由于长时间运行的事务，您的 InnoDB 历史列表长度 (HLL) 已大幅增长。

解决方法

使用指标监控您的 Amazon RDS 系统资源
您应始终监控 Amazon Aurora 集群上的 CPU 利用率和可用内存。尽管偶尔出现 CPU 峰值是正常的，但长时间持续高 CPU 可能会使 SELECT 查询运行缓慢。使用以下工具来确定 CPU 的使用方式和位置：

Amazon CloudWatch 指标是监控 CPU 使用率的最简单方法。有关 Amazon Aurora 可用指标的更多信息，请参阅监控 Amazon Aurora 数据库集群指标。增强的监控功能可以更低的粒度详细了解操作系统级别的指标。它为您提供了进程如何使用 CPU 的详细明细。性能洞察准确地决定数据库负载。为数据库实例启用性能洞察，然后检查负载是否超出最大 vCPU。您还可以通过等待来监控负载查询和 SQL，并识别导致最长等待时间的用户。

由于磁盘搜索，SELECT 查询也可能运行缓慢。为了最大限度地减少磁盘 I/O，数据库引擎会尝试缓存从磁盘读取的数据块。这意味着，下次数据库需要相同的数据块时，可以从内存中获取，而不是去磁盘。

使用以下指标检查您是从磁盘还是内存提供特定查询：

VolumereadSIOps：此指标是计费卷级别读取操作的数量，应尽可能低。

BufferCacheHitRatia：此指标是缓冲区缓存处理的请求百分比，应尽可能高。如果 BufferCacheHitRatia 丢弃，并且您的 SELECT 语句速度缓慢，则您正在处理来自底层卷的查询。

识别慢 SELECT 语句的另一个重要资源是慢查询日志。为数据库集群启用慢查询日志记录，以记录这些查询并稍后采取措施。如果您使用的是 MySQL 5.6 兼容版本，请使用 MySQL 性能架构持续监控查询的性能。

识别死锁和等待事件

Amazon RDS 会锁定数据库中的数据，以便在任何给定时间只有一个用户会话可以写入或更新一行。需要此行的任何其他交易都将被保留。在共享锁中，读取事务读取数据时，写入/更新事务处于保持状态。如果查询正在等待访问被另一个查询锁定的行，可能会导致死锁。

要识别数据库上的死锁，请在参数组中启用 innodb_print_all_deadlock 参数。

然后，在“最新死锁”部分运行以下命令以识别死锁：

SHOW ENGINE INNODB STATUS\G;

注意：您无法在 Aurora Readers 上运行此命令，因为默认情况下，读取器使用 MySQL innodb_read_only 参数设置为 ON（开）进行配置。此参数对于数据库实例不可修改，因为 Aurora MySQL 根据集群类型管理数据库实例的只读和读/写状态。

您还可以查询 INFORMATION_SCHEMA INNODB_TRX 表。这为您提供了有关 INNODB 中每个正在运行的事务的准确详情，包括等待。

检查查询是否正在使用索引
如果查询没有索引或进行了完整的表扫描，则通常会比较慢。索引有助于加快 SELECT 查询的速度。

要检查查询是否使用索引，请使用 EXPLAIN 查询。这是对缓慢查询进行故障排除的有用工具。在 EXPLAIN 输出中，检查表名称、使用的键以及查询期间扫描的行数。如果输出未显示任何正在使用的键，则在 WHERE 子句中使用的列上创建索引。

如果表需要索引，请检查表统计信息是否为最新状态。确保统计数据准确意味着查询优化程序使用具有正确基数的最具选择性的索引。这提高了查询性能。

检查历史记录列表长度 (HLL)

InnoDB 使用一种名为多版本并发控制 (MVCC) 的概念。MVCC 维护同一记录的多个副本以保持读取一致性。这意味着，一旦您提交了事务，InnoDB 就会清除旧的副本。但是，当事务长时间未提交时，由于撤消段的增长，历史记录列表长度 (HLL) 就会增加。InnoDB 历史记录列表长度表示未刷新更改的数量。

如果您的工作负载需要大量长时间运行或打开的事务，则您可能会在数据库中看到很高的 HLL。

注意：长时间运行的事务并不是 HLL 峰值的唯一原因。即使清除线程无法跟上数据库上的变化，HLL 也可能保持高水平。

如果不监控 HLL 的大小，将导致性能随时间的推移而下降，以及资源消耗增加、SELECT 语句性能降低和不一致以及存储量增加。在极端情况下，这可能会导致数据库中断。

要检查历史记录列表的长度，请运行以下命令：

SHOW ENGINE INNODB STATUS;

输出:

------------ TRANSACTIONS ------------
Trx id counter 26368570695
Purge done for trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

对于 Aurora MySQL，由于共享存储卷的性质，历史记录列表长度为集群级别，而不是单个实例级别。连接您的读取器并运行以下查询：

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

此查询可帮助您了解读取器节点和写入器节点之间的副本延迟。它还详细介绍了数据库实例用于从存储中读取的最旧 LSN，以及数据库实例的最旧读取视图 TRX ID。使用此信息来检查其中一个读取器是否保持有旧的读取视图（与读取器上的引擎 InnoDB 状态相比）。

注意：从 Aurora MySQL 1.19 和 2.06 开始，您可以使用 CloudWatch 中的 RollbackSegmentHistoryListLength 指标监控 HLL。或者，在旧版本中，使用以下命令，使用 trx_rseg_history_len 检查 HLL：

select NAME AS RollbackSegmentHistoryListLength, 
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

使用以下方法解决 HLL 增长的问题：

如果 DML（写入）导致 HLL 增长：取消或终止此语句涉及回滚中断的事务。这需要相当长的时间，因为直到此时间点之前进行的所有更新都将被回滚。

如果读取导致 HLL 增长 ：使用 mysql.rds_kill_query 终止查询。

根据查询运行的时长，请使用 DBA 以查看是否可以使用存储过程终止查询。

避免增长的最佳方法是使用上述方法监控 HLL，并避免数据库上长时间运行或打开的事务。此外，最佳做法是以较小批量提交数据。

重要提示：请勿重启数据库集群或实例。如果 HLL 能够访问缓冲池中的数据，则清除 HLL 会更有效。如果重新启动数据库，则将从磁盘中读取所有数据以进行清理。

排查 Amazon Aurora MySQL 数据库集群上缓慢的 SELECT 查询

解决方法

发表回复