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

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

本文介绍如何确定 CPU 利用率高的根本原因。 它还提供可能的修正操作，以控制使用 Azure Database for PostgreSQL 灵活服务器时的 CPU 利用率。

本文介绍以下内容：

• 关于故障排除指南，通过识别根本原因并获得建议来缓解问题。
• 介绍用于识别高 CPU 使用率的工具，例如 Azure 指标、查询存储和 pg_stat_statements。
• 如何确定根本原因，例如长时间运行的查询和总连接。
• 如何使用 EXPLAIN ANALYZE、连接池和清空表解决 CPU 使用率过高的问题。

通过故障排除指南，可以识别可能导致 CPU 使用率高的根本原因，还可阅读建议来缓解发现的问题。

若要了解如何设置和使用故障排除指南，请参阅设置故障排除指南。

考虑使用以下工具列表来识别 CPU 利用率高的情况。

要检查具体时段的 CPU 利用率，Azure 指标是一个很好的起点。 指标提供了 CPU 利用率高的期间使用的资源的相关信息。 将写入 IOP、读取 IOP、读取吞吐量字节数/秒和写入吞吐量字节数/秒的图与CPU 百分比图进行比较，找出工作负载导致 CPU 利用率高的时间点。

对于主动监视，可以针对指标配置警报。 有关分步指南，请参阅 Azure 指标。

查询存储自动捕获查询和运行时统计信息的历史记录，并保留它们以供查看。 它按时间切分数据，以便可以查看时间使用模式。 所有用户、数据库和查询的数据都存储在 Azure Database for PostgreSQL 灵活服务器实例中名为 azure_sys 的数据库中。

查询存储可以将等待事件信息与查询运行时统计信息关联。 使用查询存储来标识某个期间 CPU 消耗量较高的查询。

有关详细信息，请参阅查询存储。

pg_stat_statements 扩展可帮助识别服务器上消耗时间的查询。 有关此扩展的详细信息，请参阅相应的文档。

SELECT userid::regrole, dbid, query, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 5;

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 5;

执行以下语句，按总执行时间查看前五个 SQL 语句。

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY total_exec_time
DESC LIMIT 5;

SELECT userid::regrole, dbid, query,
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

如果 CPU 消耗水平通常较高，可能的根本原因包括：

长时间运行的事务消耗的 CPU 资源可能导致 CPU 使用率过高。

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

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;

数据库的大量连接也可能会导致 CPU 和内存利用率增加。

以下查询提供按状态排序的连接数的相关信息：

SELECT state, count(*)
FROM  pg_stat_activity
WHERE pid <> pg_backend_pid()
GROUP BY state
ORDER BY state ASC;

使用 EXPLAIN ANALYZE 时，考虑使用内置的 PgBouncer 连接池器，并终止长时间运行的事务来解决 CPU 使用率过高的问题。

了解消耗更多 CPU 的查询后，使用 EXPLAIN ANALYZE 进一步调查和优化这些查询。

有关 EXPLAIN ANALYZE 命令的详细信息，请查看相应的文档。

如果存在许多短时连接，或者许多连接在大部分时间都保持空闲状态，请考虑使用 PgBouncer 等连接池工具。

有关 PgBouncer 的更多信息，请参阅 PgBouncer 的连接池处理 和 PostgreSQL 连接处理的最佳实践。

Azure Database for PostgreSQL 灵活服务器将 PgBouncer 作为内置连接池解决方案予以提供。 有关详细信息，请参阅 PgBouncer。

可以考虑终止将长时间运行的事务作为选项。

若要终止会话的 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);

使表统计信息保持最新状态有助于提高查询性能。 监视是否正在执行常规清空。

以下查询有助于识别需要清空的表：

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

last_autovacuum 和 last_autoanalyze 列提供上次自动清空或分析表的日期和时间。 如果未定期清空表，请执行以下步骤来优化自动清空。

有关自动清空故障排除和优化的详细信息，请参阅自动清空故障排除。

短期解决方案是对可看到缓慢查询的表执行手动清空分析：

VACUUM ANALYZE <table>;

• 排查 Azure Database for PostgreSQL 灵活服务器中内存利用率过高的问题。
• 排查 Azure Database for PostgreSQL 灵活服务器中 IOPS 利用率过高的问题。
• 排查和识别 Azure Database for PostgreSQL 灵活服务器中运行缓慢的查询的问题。
• Azure Database for PostgreSQL 灵活服务器中的服务器参数。
• Azure Database for PostgreSQL 灵活服务器中的自动清理优化。