通过

排查 Azure Database for PostgreSQL 灵活服务器的 IOPS 利用率过高问题

  • 项目

适用于： Azure Database for PostgreSQL 灵活服务器

本文展示了如何快速确定高 IOPS（每秒输入/输出操作）利用率的根本原因，并提供了在使用 Azure Database for PostgreSQL 灵活服务器时控制 IOPS 利用率的补救操作。

在本文中，学习如何：

  • 关于识别并获取缓解根本原因的建议的故障排除指南。
  • 使用 Azure 指标、查询存储和 pg_stat_statements 等工具来识别过高的输入/输出 (I/O) 利用率。
  • 确定根本原因，例如长时间运行的查询、检查点计时、中断性 autovacuum 守护程序进程和高存储利用率。
  • 使用 Explain Analyze 解决 I/O 利用率过高的问题，优化与检查点相关的服务器参数，并优化 autovacuum 守护程序。

疑难解答指南

使用 Azure Database for PostgreSQL 灵活服务器门户上提供的功能故障排除指南，可以找到可能的根本原因和缓解高 IOPS 利用率方案的建议。 如何设置故障排除指南以使用它们，请遵循《设置故障排除指南》。

用于识别过高 I/O 利用率的工具

考虑使用以下工具来识别过高的 I/O 利用率。

Azure 指标

Azure 指标是用于检查定义日期和时间内的 I/O 利用率的良好起点。 指标提供有关 I/O 利用率过高的时间段的信息。 比较写入 IOPs、读取 IOPs、读取吞吐量和写入吞吐量的图表，找出工作负载导致过高 I/O 利用率的时间。 对于主动监视，可以针对指标配置警报。 有关分步指南，请参阅 Azure 指标。

查询存储

查询存储功能自动捕获查询和运行时统计信息的历史记录，并将其保留以供查看。 它按时间切分数据，以便查看时态使用模式。 所有用户、数据库和查询的数据都存储在 Azure Database for PostgreSQL 灵活服务器实例中名为 azure_sys 的数据库中。 有关分步指导，请参阅使用查询存储监视性能。

使用以下语句查看使用 I/O 的前五个 SQL 语句：

select * from query_store.qs_view qv where is_system_query is FALSE
order by blk_read_time + blk_write_time  desc limit 5;

pg_stat_statements 扩展

pg_stat_statements 扩展可帮助识别消耗服务器上的 I/O 的查询。

使用以下语句查看使用 I/O 的前五个 SQL 语句：

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time desc
LIMIT 5;

注意

对要填充的 blk_read_time 和 blk_write_time 列使用查询存储或 pg_stat_statements 时，需要启用服务器参数 track_io_timing。 有关 track_io_timing 的详细信息，请查看服务器参数。

确定根本原因

如果 I/O 消耗级别通常较高，根本原因可能是：

长时间运行的事务

长时间运行的事务所消耗的 I/O 可能导致 I/O 利用率过高。

以下查询有助于识别运行时间最长的连接：

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

检查点计时

在检查点发生频率过高的情况下，也可以看到高 I/O。 识别这一点的一种方法是检查 Azure Database for PostgreSQL 灵活服务器日志文件中的以下日志文本：“LOG: 检查点发生太过频繁。”

还可以使用一种方法进行调查，即保存带有时间戳的 pg_stat_bgwriter 的定期快照。 使用保存的快照，可以计算平均检查点间隔、请求的检查点数和计时的检查点数。

中断性 autovacuum 守护程序进程

运行以下查询来监视 autovacuum：

SELECT schemaname, relname, n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_all_tables WHERE n_live_tup > 0;

查询用于检查数据库中表的清空频率。

  • last_autovacuum：上次 autovacuum 在表上运行的日期和时间。
  • autovacuum_count：清空表的次数。
  • autoanalyze_count：分析表的次数。

解决 I/O 利用率过高的问题

若要解决 I/O 利用率过高的问题，可以使用以下三种方法之一。

EXPLAIN ANALYZE 命令

在确定了消耗高 I/O 的查询之后，使用 EXPLAIN ANALYZE 来进一步调查该查询并对其进行调优。 有关 EXPLAIN ANALYZE 命令的详细信息，请查看 EXPLAIN 计划。

终止长时间运行的事务

可以考虑终止长时间运行的事务。

若要终止会话的进程 ID (PID)，需要使用以下查询检测 PID：

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

还可以按其他属性进行筛选，例如 usename（用户名）或 datname（数据库名称）。

获取会话的 PID 后，可以使用以下查询终止它：

SELECT pg_terminate_backend(pid);

优化服务器参数

如果发现检查点发生得太频繁，请增加 max_wal_size 服务器参数，直到大多数检查点都为时间驱动，而非请求的。 最终，90% 甚至更多的检查点应该基于时间，并且两个检查点之间的间隔应该接近服务器上设置的 checkpoint_timeout 值。

  • max_wal_size：业务高峰期是得出合理 max_wal_size 值的好时机。 若要得出合理值，请执行以下操作：

    1. 运行以下查询以获取当前的 WAL LSN，然后记下结果：

      select pg_current_wal_lsn();

    2. 等待 checkpoint_timeout 设置的秒数。 运行以下查询以获取当前的 WAL LSN，然后记下结果：

      select pg_current_wal_lsn();

    3. 运行以下查询，它使用两个结果来检查差异（以 GB 为单位）：

      select round (pg_wal_lsn_diff ('LSN value when run second time', 'LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • checkpoint_completion_target：合理的做法是将值设置为 0.9。 例如，为 5 分钟的 checkpoint_timeout 使用值 0.9 表示完成检查点的目标是 270 秒（0.9*300 秒）。 值 0.9 可提供相当一致的 I/O 负载。 使用激进的 checkpoint_completion_target 值可能导致服务器上的 I/O 负载增加。

  • checkpoint_timeout：可以增大 checkpoint_timeout 值，而不是使用服务器上设置的默认值。 增大该值时，请考虑到这也会增大故障恢复时间。

优化 autovacuum 以减少干扰

有关在 autovacuum 干扰性太大的情况下进行监视和优化的详细信息，请查看 Autovacuum 优化。

增大存储

为服务器添加更多 IOPS 时，增加存储会有所帮助。 有关存储和关联的 IOPS 的详细信息，请查看计算和存储选项。

相关内容