我有一个 Amazon Aurora MySQL 兼容版数据库集群,我想使用 SELECT 查询选择数据库中的数据。当我在数据库集群上运行 SELECT 查询时,查询运行缓慢。如何识别 SELECT 查询缓慢的原因并修复?
简短描述
SELECT 查询在 Aurora MySQL 兼容数据库集群上运行缓慢的原因有很多:
- 您的 Amazon Relational Database Service (Amazon RDS) 系统资源使用过度。发生这种情况的原因可能是 CPU 高、内存不足或工作负载超出数据库实例类型能够处理的范围。
- 数据库正在锁定,由此产生的等待事件导致 SELECT 查询执行不佳。
- SELECT 查询正在对大型表执行完整表扫描,或者查询缺乏必要的索引。
- 由于长时间运行的事务,您的 InnoDB 历史列表长度(HLL)已大幅增长。
解决方法
使用指标监控您的 Amazon RDS 系统资源
最佳实践是始终监控 Amazon Aurora 集群上的 CPU 利用率和可用内存。尽管偶尔出现 CPU 峰值是正常的,但长时间持续高 CPU 可能会使 SELECT 查询运行缓慢。使用以下工具来确定 CPU 的使用方式和位置:
1. Amazon CloudWatch 指标是监控 CPU 使用率的最简单方法。有关 Aurora 可用指标的更多信息,请参阅 Aurora 的 CloudWatch 指标。
2. 增强监控可以更低的粒度详细了解操作系统级别的指标。详细明细显示了进程如何使用 CPU。
3. 性能洞察准确地决定数据库负载。为数据库实例开启性能洞察,然后检查负载是否超出最大 vCPU。您还可以通过等待来监控负载查询和 SQL,并识别导致最大等待次数的用户。
由于磁盘搜索,SELECT 查询也会运行缓慢。为了最大限度地减少磁盘 I/O,数据库引擎会尝试缓存从磁盘读取的数据块。这意味着,下次数据库需要相同的数据块时,该数据块是从内存中获取的,而不是从磁盘中获取的。
使用以下指标检查您是从磁盘还是内存提供特定查询:
- **VolumeReadsIOPS:**此指标是计费卷级别 [磁盘] 读取操作的数量。最佳做法是确保该值尽可能低。
- **BufferCacheHitRatio:**此指标是缓冲区缓存处理的请求百分比。最佳做法是确保该值尽可能高。如果 BufferCacheHitRatio 丢弃,并且您的 SELECT 语句速度缓慢,则您正在处理来自底层卷的查询。
识别慢 SELECT 语句的另一个重要资源是慢查询日志。为数据库集群激活慢速查询日志记录,以记录这些查询并稍后采取措施。对于 MySQL 5.6 兼容版本,请使用 MySQL 性能架构持续监控查询性能。
识别死锁和等待事件
Amazon RDS 会锁定数据库中的数据,以便在任何给定时间只有一个用户会话可以写入或更新一行。需要此行的任何其他交易都将被保留。在共享锁中,读取事务读取数据时,写入/更新事务处于保持状态。如果查询正在等待访问被另一个查询锁定的行,则可能会导致死锁。
要识别数据库上的死锁,请在参数组中启用 innodb_print_all_deadlock 参数。然后通过 RDS 控制台/CLI/API 监控 mysql-error.log。
或者,使用管理员账户登录 MySQL,然后运行此命令以从最新检测到的死锁部分的命令输出中识别死锁:
mysql> SHOW ENGINE INNODB STATUS\G;
检查查询是否正在使用索引
如果查询没有索引或进行全表扫描,则查询的运行速度会更慢。索引有助于加快 SELECT 查询的速度。
要检查查询是否使用索引,请使用 EXPLAIN 查询。这是对缓慢查询进行故障排除的有用工具。在 EXPLAIN 输出中,检查表名称、使用的键以及查询期间扫描的行数。如果输出未显示任何正在使用的键,则在 WHERE 子句中使用的列上创建索引。
如果表需要索引,请检查表统计信息是否为最新状态。确保统计数据准确意味着查询优化程序使用具有正确基数的最具选择性的索引。这提高了查询性能。
检查历史记录列表长度 (HLL)
InnoDB 使用一种名为多版本并发控制 (MVCC) 的概念。MVCC 维护同一记录的多个副本以保持读取一致性。这意味着,当您提交事务时,InnoDB 会清除较旧的副本。但是,当事务长时间未提交时,由于撤消段的增长,历史记录列表长度(HLL)就会增加。InnoDB 历史记录列表长度表示未刷新更改的数量。
如果您的工作负载需要多个打开或长时间运行的事务,则您可能会在数据库中看到很高的 HLL。
**注意:**长时间运行的事务并不是 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';
如果为您的 Aurora MySQL 实例激活了性能详情,那么您可以查看 RollbackSegmentHistoryListLength。导航到写入器性能详情并执行以下操作:
1. 选择管理指标,然后选择数据库指标。
2. 选择 trx_rseg_history_len 指标,然后选择更新图表。
使用以下方法解决 HLL 增长的问题:
- 如果 DML(写入)导致 HLL 增长:取消或终止此语句涉及回滚中断的事务。这需要相当长的时间,因为直到此时间点之前进行的所有更新都将被回滚。
- 如果读取导致 HLL 增长 :使用 mysql.rds_kill_query 终止查询。
- 根据查询运行的时长,请使用 DBA 检查是否可以使用存储过程终止查询。
最佳做法是通过使用这些方法监控 HLL 来避免增长,并避免数据库上出现打开或长时间运行的事务。此外,最好以较小的批次提交数据。
**重要提示:**请勿重启数据库集群或实例。如果 HLL 可以访问缓冲池内存中的数据,则清除 HLL 会更有效。如果重新启动数据库,则生存性页面缓存可能会丢失。发生这种情况时,必须从集群卷读取数据页才能清除 HLL。这比在内存中执行清除操作要慢,并且会导致额外的 I/O 计费成本。
相关信息
使用 Amazon CloudWatch 监控 Amazon Aurora MySQL、Amazon RDS for MySQL 和 MariaDB 日志