Get Hands-on with Amazon EKS - Workshop Event Series

Whether you're taking your first steps with Kubernetes or you're an experienced practitioner looking to sharpen your skills, our Amazon EKS workshop series delivers practical, real-world experience that moves you forward. Learn directly from AWS solutions architects and EKS specialists through hands-on sessions designed to build your confidence with Kubernetes. Register now and start building with Amazon EKS!

如何对 Aurora MySQL 兼容版数据库集群中运行缓慢的 SELECT 语句进行故障排除？

2 分钟阅读

我想对我的 Amazon Aurora MySQL 兼容版数据库集群中运行缓慢的 SELECT 语句进行故障排除。

简短描述

出于以下原因，您的 SELECT 语句可能会在 Aurora MySQL 兼容版数据库集群上运行缓慢：

您过度使用数据库系统资源。
数据库处于锁定状态。
SELECT 语句对大型表进行全表扫描。或者，该查询没有必要的索引。
长时间运行的事务会增加您的 InnoDB 历史记录列表长度 (HLL)。

解决方法

**注意：**如果您在运行 AWS 命令行界面 (AWS CLI) 命令时收到错误，请参阅 AWS CLI 错误故障排除。此外，请确保您使用的是最新版本的 AWS CLI。

使用 CloudWatch 数据库洞察来检查 SELECT 语句运行缓慢的原因

开启性能详情并使用数据库洞察来检测导致高数据库负载的查询。监控数据库负载图表中的 SQL、用户和等待等维度。另外监控“数据库遥测”选项卡的“慢速 SQL 查询”部分。此外，您可以根据性能详情的 SQL 统计数据分析查询执行情况。例如，如果您每次调用检查的行数高于正常值，则执行计划效率低下。

使用指标监控您的数据库实例系统资源

CPU 过高、内存不足或超出数据库实例类能力的大型工作负载可能会导致 SELECT 语句运行缓慢。要监控您的数据库实例资源，请使用以下工具：

使用 Amazon Aurora 的 Amazon CloudWatch 指标来监控您的 CPU 利用率。
使用增强监控查看操作系统 (OS) 指标的详细信息。
使用“数据库遥测”选项卡中的操作系统处理数据来检查您的数据库负载是否超过最大 vCPU。

磁盘搜索可能会导致 SELECT 语句运行缓慢。为了最大限度地减少磁盘 I/O，数据库引擎缓存从磁盘读取的数据块。当数据库需要相同的数据时，它会从内存而不是磁盘中获取数据。要确定您是从磁盘还是内存中提供特定查询，请使用以下指标：

检查 ReadIOPS 指标以查看磁盘 I/O 操作的数量。最佳做法是将该值保持在尽可能低的水平。
检查 BufferCacheHitRatio 指标以查看缓冲区缓存所服务的请求百分比。最佳做法是将该值保持在尽可能高的水平。
检查 FreeableMemory 指标以查看数据库实例的可用内存。最佳做法是保持该值稳定。可用内存不足会导致 BufferCacheHitRatio 过低和 ReadIOPS 过高。
**注意：**如果 BufferCacheHitRatio 指标下降且 SELECT 语句运行缓慢，则引擎会处理来自基础卷的查询。

磁盘搜索本地存储也会导致 SELECT 语句运行缓慢。Aurora MySQL 兼容版使用本地存储空间来存储手动和内部临时表。有关详细信息，请参阅 Aurora MySQL 版本 3 中的新临时表行为。要监控和解决本地存储空间的磁盘搜索问题，请检查数据库洞察的 Rdstemp 操作系统指标。最佳做法是将这些值保持在尽可能低的水平。

网络饱和可能会导致 SELECT 语句运行缓慢。Aurora 通过网络对集群卷执行 I/O 操作，并通过网络将查询结果发送给客户端。要监控和解决网络饱和问题，请检查 NetworkThroughput 和 StorageNetworkThroughput 指标。网络总吞吐量必须小于数据库实例的网络带宽。

如果由于您的工作负载导致任何资源使用量超过了数据库实例类型的容量，请升级数据库实例类。

识别死锁和阻塞锁

当多项事务由于相互阻塞而无法继续时，就会出现死锁。要识别数据库中的死锁，请在参数组中打开 innodb_print_all_deadlocks 参数。有关详细信息，请参阅 MySQL 网站上的 innodb_print_all_deadlocks。然后通过 Amazon RDS 控制台、AWS 命令行界面或 API 监控 mysql-error.log。

（可选）要识别死锁，请登录 MySQL 管理员账户，然后运行以下命令：

SHOW ENGINE INNODB STATUS\G;

**注意：**在 MySQL Workbench 的预期输出中，检查 Latest Detected Deadlock（最新检测到的死锁）部分。

即使没有死锁，一个保持锁定的长时间事务也可能是阻塞锁。要确定正在进行的阻塞锁，请参阅为什么在没有其他活动会话的情况下，对 Amazon RDS for MySQL 数据库实例的查询会被阻止？

检查您的查询是否使用索引

当查询没有索引或进行全表扫描时，查询运行缓慢。索引能让 SELECT 语句更快地运行。要检查您的查询是否使用索引，请使用 EXPLAIN 语句。有关详细信息，请参阅 MySQL 网站上的 EXPLAIN statement（EXPLAIN 语句）。

在 EXPLAIN 输出中，检查引擎在查询期间扫描的表名称、密钥和行数。如果输出未显示正在使用的任何密钥，请在 WHERE 子句中的列上创建索引。如果该表具有所需的索引，请检查表统计数据是否为最新。使用 ANALYZE 子句更新统计数据。有关详细信息，请参阅 MySQL 网站上的 The INFORMATION_SCHEMA STATISTICS Table（INFORMATION_SCHEMA STATISTICS 表）。

要识别运行缓慢的 SELECT 语句，请使用 slow_query_log。要记录慢速查询，请为数据库集群开启慢速查询日志记录。

检查 HLL

InnoDB 使用多版本并发控制 (MVCC)。MVCC 维护同一记录的多个副本以保持读取一致性。HLL 是包含历史记录列表中修改的撤消日志的总数。当有一个写入或读取数据的长时间运行的事务时，HLL 会增加，直到事务完成或回滚为止。其他事务会修改长时间运行的事务使用的表。如果您的工作负载需要多个打开或长时间运行的事务，则数据库的 HLL 可能很高。此外，如果清除线程无法跟上数据库的变化，则您的 HLL 可能会很高。HLL 过高会导致更高的资源使用率、缓慢且不一致的 SELECT 语句性能以及存储空间的增加。在极端情况下，高 HLL 可能会导致数据库中断。

要监控您的 HLL，请使用写入器实例的 RollbackSegmentHistoryListLength 指标。或者运行以下命令：

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 兼容版，HLL 处于集群级别。要在集群级别检查 HLL，请连接到您的写入器实例并运行以下语句：

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) 和数据库实例最早的读取视图事务 ID (Trx ID)。确保其中一个实例包含旧的读取视图。

要连接到包含旧读取视图的实例，请运行以下语句：

SELECT a.trx_id, a.trx_state, a.trx_started,  TIMESTAMPDIFF(SECOND,a.trx_started, now()) as "Seconds Transaction Has Been Open",    
a.trx_rows_modified, b.USER, b.host, b.db, b.command, b.time, b.state    
from information_schema.innodb_trx a, information_schema.processlist b    
where a.trx_mysql_thread_id=b.id    
order by trx_started;

**注意：**使用前面的语句来识别具有最早 trx_id 的会话或事务。要解除对清除操作的阻止，请确定是否可以结束会话。

要解决高 HLL 问题，请执行以下操作：

如果 DML 写入导致 HLL 增加，请回滚事务以取消查询。此过程很漫长，因为必须回滚大量更新。
如果 READ 导致 HLL 增加，请使用 mysql.rds_kill_query 取消查询。
**注意：**请联系您的数据库管理员，以检查您是否可以取消查询。

为防止 HLL 过高，最佳做法是小批量提交数据。另外，不要重启数据库集群或实例。当 HLL 可以访问缓冲池中的内存数据时，将其清除。如果重启数据库，则可存活的页面缓存可能会丢失。如果丢失了可存活的页面缓存，则必须读取集群卷中的数据页才能清除 HLL。此过程比从内存中清除要慢，并且会导致额外的 I/O 计费成本。