云数据仓库ADB实例查询变慢应该怎么定位和分析 ？

当云数据仓库AnalyticDB（ADB）实例查询变慢时，可以通过以下步骤进行定位和分析。这些方法结合了SQL审计、慢查询诊断、性能调优以及系统监控工具，帮助您快速找到问题的根本原因。

1. 使用SQL审计功能定位慢查询

SQL审计功能可以帮助您记录和分析执行时间较长的SQL语句，从而定位可能的性能瓶颈。

操作步骤：

1. 登录AnalyticDB PostgreSQL版控制台。
2. 选择目标实例所在地域，并通过实例ID进入实例详情页。
3. 导航至“实例审计”页面。
4. 使用筛选条件（如“数据库名”、“执行时长”等）查询相关SQL信息。
   • 注意：SQL审计记录默认保存14天，高SQL查询量下可能丢失部分记录。
5. 复制或导出相关SQL语句，进一步分析其执行计划和耗时。

2. 开启并分析慢查询日志

慢查询日志记录了所有执行时间超过slow_query_min_duration参数设置（默认值为1秒）的SQL语句，是定位慢查询的重要工具。

开启慢查询功能：

ALTER DATABASE {业务库} SET adbpg_feature_enable_query_monitor TO ON;

• 注意：仅超级用户（Superuser）可以修改该配置项。

查询慢查询日志：

切换到postgres系统库，使用以下视图查询慢查询信息： - 实例级别慢查询：qmonitor.instance_slow_queries - 节点级别慢查询：qmonitor.host_slow_queries

示例查询：

1. 查询最近30分钟内的慢查询：

   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';
   

2. 查询某时间段内CPU消耗排名前20的慢查询：

   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;
   

3. 查询内存消耗排名前20的慢查询：

   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;
   

3. 使用pg_stat_activity监控当前查询

pg_stat_activity系统视图可用于实时监控当前正在执行的查询，帮助分析和排查SQL任务及异常。

关键字段：

• query：当前或最近执行的查询文本。
• state：后端状态（如active、idle等）。
• waiting：是否处于锁等待状态。
• query_start：查询开始时间。

示例查询：

查询某用户近30分钟内未完成的查询：

SELECT * 
FROM pg_stat_activity 
WHERE xact_start < now() - interval '30 min' 
  AND waiting='f' 
  AND usename='odps_in';

4. 分析查询执行计划

通过查询执行计划，您可以深入了解SQL语句的执行过程，识别潜在的性能问题。

操作步骤：

1. 登录AnalyticDB PostgreSQL版控制台。
2. 在左侧导航栏中，选择诊断与优化 > 查询分析。
3. 在查询诊断页面，选择目标数据库、筛选条件以及时间范围。
4. 查看查询执行泳道图或SQL列表，获取查询执行状态信息。
   • 单击详情可进入查询属性页面，查看详细的执行计划和统计信息。

重点关注：

• 执行耗时：包括总耗时、优化器耗时、锁等待耗时、队列等待耗时和执行引擎耗时。
• 资源消耗：如CPU时间、内存使用、扫描量等。

5. 检查系统资源使用情况

查询变慢可能与系统资源（如CPU、内存、磁盘）的使用情况有关。可以通过以下方式检查资源使用情况：

常用接口：

1. 获取连接数： 调用DescribeConnectionCountRecords接口，查看当前集群的连接数。
2. 查看运行查询： 调用DescribeProcessList接口，查看正在运行的查询及其资源消耗。
3. 终止任务： 如果发现某些任务占用过多资源，可以调用KillProcess接口终止任务。

6. 性能调优建议

根据常见问题场景，以下是一些性能调优的建议：

1. 索引优化：

   • 确保查询涉及的字段已建立合适的索引。
   • 避免对大表进行全表扫描。

2. 资源管理：

   • 检查是否开启了资源组（Resource Group），并合理分配资源。

3. 统计信息更新：

   • 定期更新统计信息，确保查询优化器生成高效的执行计划。

4. 分布式开销：

   • 对于小规模数据查询，考虑使用单机版集群以减少分布式开销。

7. 其他注意事项

• 慢查询日志限制：慢查询日志不会记录失败的查询，且文本长度超过1024字节的SQL会被截断。
• 执行计划记录：可通过slow_query_plan_min_duration参数设置记录执行计划的阈值，默认为10秒。
• Pattern分析：通过SQL Pattern分析，比较平均和最大耗时、内存使用、扫描量等指标，判断是否存在异常波动。

通过以上步骤，您可以全面定位和分析云数据仓库ADB实例查询变慢的原因，并采取相应的优化措施。