AnalyticDB PostgreSQL版提供了慢查询诊断功能_云原生数据仓库AnalyticDB(AnalyticDB)-阿里云帮助中心

AnalyticDB PostgreSQL版提供了慢查询诊断功能，旨在帮助您对慢查询进行诊断、分析和采取优化措施。在使用该功能前，您需要开启慢查询功能。开启后，系统将自动记录超过阈值为1秒的SQL语句，您可以连接到postgres库进行查询和分析。

前提条件

慢查询诊断功能仅支持存储弹性模式7.0版本，且内核版本必须为V7.0.5.0及以上。如何查看和升级内核版本以及内核小版本，请参见查看内核小版本和版本升级。

注意事项

慢查询日志默认保留一个星期的数据，但不会记录失败的查询。它记录所有执行时间超过GUCslow_query_min_duration参数设置（默认值是1秒）的SQL语句，即记录执行时间超过1秒的所有查询（包括DDL），为了保障系统的稳定性，建议不要修改该参数。
慢查询日志将不会记录文本长度超过1024字节的慢SQL语句，超过该数值的SQL文本将被截断。

开启或关闭慢查询

--on代表开启, off代表关闭
SHOW adbpg_feature_enable_query_monitor;
--业务库开启慢查询诊断功能
ALTER database {业务库} SET adbpg_feature_enable_query_monitor to ON;

使用示例

系统将默认采集执行时间大于1秒的查询以及所有的DDL，慢查询日志存放在postgres系统库中。如需查询当前实例的慢查询情况，请切换到postgres库，通过查询qmonitor.instance_slow_queries（实例级别）和qmonitor.host_slow_queries（节点级别）这两个视图来进行慢查询的诊断。以下将为您介绍进行慢查询诊断的典型场景，主要包括：

说明

详细字段信息请参考：附录

查询某时间范围内所有的慢Query语句。

查询最近某个时间段（如30分钟）所有慢Query语句。您也可以根据业务需求修改具体时间，查询目标时间段消耗较高的慢Query语句。

SELECT 
       query_start AS "开始时间",
       query_end AS "结束时间",
       query_duration_ms AS "耗时(ms)",
       query_id AS "查询ID",
       query AS "查询SQL"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= now() - interval '30 min';

查询2024年02月26日这天（慢查询日志默认保留最近7天的数据）所有慢Query语句。

SELECT 
       query_start AS "开始时间",
       query_end AS "结束时间",
       query_duration_ms AS "耗时(ms)",
       query_id AS "查询ID",
       query AS "查询SQL"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= '2024-02-26 00:00:00'
 AND query_end <= '2024-02-27 00:00:00';

查询某时间范围内实例级别某资源（CPU使用、内存使用或者落地文件大小）TopN的慢Query语句。

查询实例某个时间段（如30分钟）CPU消耗排名前20的慢Query语句。您也可以根据业务需求修改具体时间，查询目标时间段消耗较高的慢Query语句。

SELECT 
       (cpu_time_ms/1000)::text || ' s' AS "CPU时间",
       query_start AS "开始时间",
       query_end AS "结束时间",
       query_duration_ms AS "耗时(ms)",
       query_id AS "查询ID",
       query AS "查询SQL"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '30 min'
ORDER BY cpu_time_ms DESC
LIMIT 20;

查询实例某个时间段（如30分钟）内存消耗排名前20的慢Query语句。您也可以根据业务需求修改具体时间，查询目标时间段消耗较高的慢Query语句。
```
SELECT 
       pg_size_pretty(memory_bytes) AS "内存使用",
       query_start AS "开始时间",
       query_end AS "结束时间",
       query_duration_ms AS "耗时(ms)",
       query_id AS "查询ID",
       query AS "查询SQL"
 FROM qmonitor.instance_slow_queries
 WHERE query_start >= now() - interval '30 min'
 ORDER BY memory_bytes DESC
 LIMIT 20;
```
说明
在进行统计查询时，需要考虑内存的消耗情况。在查询过程中，内存的消耗呈现出一定的波动。因此，仅采集查询过程中的内存使用峰值作为内存的消耗数值，这并非精确值，而是对Query内存使用量的粗略体现。

查询2024年02月26日0点至12点CPU消耗排名前10的慢Query语句：

SELECT 
       (cpu_time_ms/1000)::text || ' s' AS "CPU时间",
       query_start AS "开始时间",
       query_end AS "结束时间",
       query_duration_ms AS "耗时(ms)",
       query_id AS "查询ID",
       query AS "查询SQL"
FROM qmonitor.instance_slow_queries
WHERE query_start >= '2024-02-26 00:00:00'
AND query_end <= '2024-02-26 12:00:00'
ORDER BY cpu_time_ms DESC
LIMIT 10;

查询某时间范围内节点级别某资源（CPU使用、内存使用或者落地文件大小）TopN的慢Query语句。

查询某节点某个时间段（如30分钟）CPU消耗排名前20的慢Query语句。您也可以根据业务需求修改具体时间，查询目标时间段消耗较高的慢Query语句：

SELECT 
        (host_cpu_time_ms/1000)::text || ' s' as "CPU时间",
       query_start as "开始时间",
       query_end as "结束时间",
       query_duration_ms as "耗时(ms)",
       query_id as "查询ID",
       query as "查询SQL"
FROM qmonitor.host_slow_queries
WHERE hostname = '节点hostname' 
AND query_start >= now() - interval '30 min'
ORDER BY host_cpu_time_ms DESC
LIMIT 20;

查询某节点某个时间段（如30分钟）内存消耗排名前20的慢Query语句。您也可以根据业务需求修改具体时间，查询目标时间段消耗比较高的慢Query语句：

SELECT 
        pg_size_pretty(host_mem_bytes) as "内存使用",
       query_start as "开始时间",
       query_end as "结束时间",
       query_duration_ms as "耗时(ms)",
       query_id as "查询ID",
       query as "查询SQL"
 FROM qmonitor.host_slow_queries
 WHERE hostname = '节点hostname'
 AND query_start >= now() - interval '30 min'
 ORDER BY host_mem_bytes DESC
 LIMIT 20;

查询某个慢查询日志的具体信息。

SELECT * FROM qmonitor.instance_slow_queries WHERE query_id = '<某查询的QueryId>';

查询某时间范围内每个用户的慢Query情况。
- 查询某时间范围内（如10分钟）每个用户的慢Query情况，您也可以根据业务需求修改具体时间，查询目标时间段内每个用户的慢Query情况：
```
SELECT user_name AS "用户",
COUNT(1) AS "Query个数"
FROM qmonitor.instance_slow_queries
WHERE query_start >= now() - interval '10 min'
GROUP BY user_name
ORDER BY COUNT(1) DESC;
```

导出慢查询日志

AnalyticDB PostgreSQL版支持使用SELECT语句将慢查询日志（qmonitor.instance_slow_queries）或（qmonitor.host_slow_queries）中的数据导出到用户自定义的内部表或者MaxCompute、OSS等外部表中，详细请参考数据湖分析。为了正确和高效地导出慢查询日志中的数据，您需要注意：

在AnalyticDB PostgreSQL版中，慢查询日志会根据查询开始时间query_start创建索引并进行分区表字段的设置。当按时间范围导出时，在查询条件中包含query_start列，从而实现更佳的性能和更少的资源消耗。例如想导出2024年2月26日下午1点到4点的慢日志, 则可添加条件：query_start >= '2024-02-26 13:00:00' and query_start <= '2024-02-26 16:00:00' 。

配置项

通过配置项可以修改慢Query语句的默认阈值，方便您查询出不同条件下的慢查询日志。

说明

只支持超级用户（Superuser）修改该配置项。

slow_query_min_duration
该配置项记录的慢Query语句的阈值默认是1秒，也可以通过修改该配置项记录小于1秒的慢Query语句。某个语句的执行时间大于或等于该设置值时，慢查询日志会记录该语句、执行时间以及其它相关信息。将该参数设置为-1则表示不记录任何慢查询。
示例如下;
1. 设置当前数据库记录大于5秒的慢Query语句，需要高权限用户进行设置。
```
ALTER DATABASE '<数据库名称>' SET slow_query_min_duration = '5s';
```
2. 设置当前Session记录大于5秒的慢Query语句，普通用户可以执行。
```
SET slow_query_min_duration = '5s';
```

slow_query_plan_min_duration
该配置项记录的执行计划信息，系统将默认展示大于等于10秒的慢执行计划。当某个语句的执行时间大于等于该数值时，慢查询日志会记录该语句的执行计划。通常情况下，可以通过explain命令连接SELECT语句即时查看执行计划，无需记录。该参数取值设置为-1则表示关闭对执行计划的记录。
示例如下：
1. 设置记录大于10秒的执行计划，需要高权限用户进行设置。
```
ALTER DATABASE '<数据库名称>' SET slow_query_plan_min_duration = '10s';
```
2. 设置记录大于10秒的执行计划，普通用户可以执行。
```
SET slow_query_plan_min_duration = '10s';
```

附录

qmonitor.instance_slow_queries（实例级别）视图的各个字段如下：

字段	类型	说明
query_id	text	查询ID，代表Query的唯一性。
session_id	integer	查询会话的ID。
db_name	character varying(128)	查询的数据库名。
user_name	character varying(128)	查询的用户名。
application_name	character varying(128)	查询应用类型。
client_hostname	character varying(128)	Query的来源客户端Hostname地址。
client_addr	character varying(128)	Query的来源客户端IP地址。
client_port	character varying(32)	Query的来源客户端端口。
rsg_name	character varying(128)	如果开启了资源管理resource group，则查询的是所在resource group的组名。
query_start	timestamptz	查询开始时间。
query_end	timestamptz	查询结束时间。
query_duration_ms	bigint	查询的耗时（ms)。 query_duration代表SQL的总耗时时间，其中包含： optimizer_duration_ms lock_wait_time_ms queue_wait_time_ms executor_duration_ms
optimizer_duration_ms	bigint	生成执行计划的耗时（ms），耗时较高通常是因为SQL较复杂。
lock_wait_time_ms	bigint	查询等锁耗时（ms）。
queue_wait_time_ms	bigint	查询等待资源队列耗时（ms）。
executor_duration_ms	bigint	该查询在执行引擎上运行耗时（ms）。
query	text	查询文本内容。
is_plpgsql	boolean	查询是否为一个PL/PGSQL存储过程。
query_optimizer	character varying(16)	查询所使用的优化器（ORCA or Planner）。
access_tables	text	查询所访问的表名。
result_rows	bigint	返回查询的行数。如果是INSERT命令，则返回插入的行数。
num_segments	integer	查询所在计算节点的个数。
num_slices	integer	查询计划生成slice的个数。
cpu_time_ms	numeric	总的CPU使用时间（ms）。包含如下时间：协调节点Master上消耗的CPU时间。所有计算节点上任务所耗费CPU时间的累加值。
mem_bytes	numeric	各节点上使用内存峰值的累加值为非精确值，可以粗略地体现Query的内存使用量。
spill_bytes	numeric	由于在计算过程中，内存不足可能导致数据落盘。落盘峰值的累加值并非精确值，反映了查询在所有计算节点上落盘文件峰值的累加情况，即查询操作所使用的落盘空间。

qmonitor.host_slow_queries（节点级别）视图的各个字段如下：

字段	类型	说明
hostname	character varying(128)	节点所在的hostname。
hostrole	text	节点的角色包含以下两种： master（协调节点） segment（计算节点）
query_id	text	查询ID，代表Query的唯一性。
db_name	character varying(128)	查询数据库名。
user_name	character varying(128)	查询用户名。
query_start	timestamptz	查询开始时间。
query_end	timestamptz	查询结束时间。
query	text	查询文本内容。
query_duration_ms	bigint	查询耗时（ms）。
optimizer_duration_ms	bigint	生产执行计划的耗时（ms），通常耗时较高是因为SQL较为复杂。
host_cpu_time_ms	numeric	该节点上查询使用CPU时间（ms）。
host_mem_bytes	numeric	该节点上查询使用的内存峰值。
host_spill_bytes	numeric	该节点上查询过程中落盘的峰值。