查询及分析TopSQL最佳实践

TopSQL简介

在DWS分布式数据库的复杂运维场景中，计划跳变、异常中断、长事务阻塞等突发状况频发。传统运维手段常面临"三无困境"：异常现场难复现、执行过程无追踪、运行指标无留存，导致故障定位如同"大海捞针"，既耗费大量人力成本，又易陷入问题溯源的死循环。

为解决此问题，DWS推出了TopSQL监控功能，支持记录运行中和运行完成的SQL语句（实时TopSQL和历史TopSQL），主要实现以下功能：

• 快速定位影响数据库性能且占用资源最密集的查询语句。
• 监控和跟踪这些查询语句随时间推移的性能变化。
• 分析查询执行计划以确定潜在的优化手段。

作为数据库运维的核心支撑工具，TopSQL已深度集成于生产环境各场景，为系统性能诊断、SQL劣化追踪及安全审计溯源等关键环节提供全链路支持，其多维监控矩阵全面涵盖内存占用、执行耗时、IO吞吐、网络时延及存储空间等多项核心指标。

TopSQL原理介绍

作业下发执行后，数据库内核通过打桩记录作业的各项资源信息，如内存、CPU、下盘、IO和网络信息等，作业执行完成后该资源信息先存在无锁队列中，资源管理后台辅助线程将该数据信息定期转储到dbms_om.gs_wlm_session_info系统表中，后续通过topsql_retention_time定期老化数据。

TopSQL相关GUC参数

集群创建成功后，系统默认开启TopSQL功能（由GUC参数use_workload_manager，enable_resource_track，enable_resource_record控制，默认都为on），GUC详细参数如表1所示。

1

SELECT * FROM pgxc_total_memory_detail WHERE nodename LIKE 'cn_%' AND memorytype LIKE '%topsql%' ORDER BY 1,2;

设置GUC参数方法：

通过DWS控制台设置：

1. 在DWS控制台，左侧导航栏，选择“专属集群 > 集群列表”。
2. 在集群列表中找到所需要的集群，单击集群名称，进入“集群详情”页面。
3. 单击“参数修改”页签，并在“参数列表”模块修改相应的参数值，然后单击“保存”。

查询TopSQL相关GUC参数设置的常用SQL:

SELECT name,setting FROM pg_settings WHERE name LIKE '%resource%';

TopSQL相关视图及分析思路

TopSQL监控等级包括query、perf、operator_realtime、operator4个级别，由GUC参数resource_track_level控制，默认为query级别。

• query级别：此级别会把SQL语句的计划信息（类似explain输出信息）记录到TopSQL中。
• perf级别：此级别会把包含实际执行时间和执行行数的计划信息（类似explain analyze输出信息）记录到TopSQL中。
• operator_realtime级别：此级别会记录实时运行的作业算子信息到TopSQL中，但不会持久化到历史TopSQL中。
• operator级别：此级别不仅会把包含实际执行时间和执行行数的信息记录到TopSQL中，还会把算子级别执行信息刷新到TopSQL中。

TopSQL功能主要通过视图承载，相关视图如表2所示，根据视图中的字段信息可以进一步分析TopSQL信息，视图中常用的分析字段参见表3。

总结：

1. 因数据量变化，导致作业执行时间增加，可以分析A2/B1/D1/G1，进而确认作业查询的数据表是否有明显的数据量增加。
2. 由于目前历史TopSQL视图字段信息较多，建议使用PGXC_QUERY_INFO视图查看查询经典字段信息，无需手动过滤无关字段。
3. 因其它并发作业抢占，导致作业排队，进而导致作业执行时间增加，可以分析A1/B1/D1，查看作业执行的同时期是否有大量并发作业在执行。
4. 因其它作业而产生的CPU抢占，导致作业执行时间增加，可以分析A2/D1/E1，进而查看作业执行的同时期是否有大量并发作业在执行。
5. 因其它作业而产生的IO抢占，导致作业执行时间增加，可以分析A2/F1，进而查看作业执行的同时期是否有大量并发作业在执行。
6. 禁止某一类语句执行，可参考H1。

值得注意的是，发生资源争抢时，可能会出现并发现象，即CPU、IO抢占，作业排队现象都会发生，针对并发问题，可以逐步分析解决，比如：

1. 调整作业执行顺序，减少并发作业数量，减少阻塞时间。
2. 定位出同时段执行的典型计算密集型、存储密集型作业，先移动到其它时间段执行，减少对本作业的影响。
3. 在无其他作业明显干预的情况下，做进一步分析。

TopSQL约束与限制

• TopSQL不记录白名单和内部语句，但可正常记录超户下发的语句信息，可记录定时任务。
• query和perf级别的TopSQL监控主要差异点在于query_plan字段，相比于query级别的算子信息，perf级别的query_plan字段增加算子的实际信息，如算子实际内存峰值，内存自动扩展信息，CU，Buffers等统计信息。
• query和perf级别实时TopSQL和历史TopSQL的start_time字段信息含义不一致，实时TopSQL中的start_time表示的是作业下发的时间，历史TopSQL中的start_time表示的是作业真正开始运行的时间。
• 查询历史TopSQL Query，perf以及算子级别数据时，仅能连接postgres数据库进行查询。
• 实时TopSQL中能够记录的SQL语句的规格是：
  1. 不记录特殊数据定义语句，如：SET、RESET、SHOW、ALTER SESSION SET、SET CONSTRAINTS语句。
  2. 记录数据定义语句，例如：执行CREATE、ALTER、DROP、GRANT、REVOKE和VACUUM语句。
  3. 记录数据操作语句，例如：
     1. 执行SELECT、INSERT、UPDATE和DELETE语句。
     2. 执行explain analyze和explain performance场景。
     3. 使用query级别/perf级别视图。
  4. 记录函数与存储过程的调用入口语句，当GUC参数enable_track_record_subsql开启的情况下，可记录存储过程的部分内部语句(declare定义语句除外)，仅会记录其中下发到DN执行的内部语句，未下发到DN执行的内部语句会被过滤掉。
  5. 记录匿名块语句，当GUC参数enable_track_record_subsql开启的情况下，可记录匿名块中的部分内部语句，仅会记录其中下发到DN执行的内部语句，未下发到DN执行的内部语句会被过滤掉。
  6. 记录游标语句，当游标并未从缓存中读取数据，而确实触发语句下发到DN上执行的条件下，该游标语句会被记录，并且会进行语句、执行计划增强，但当游标从缓存中读取数据时，不进行记录；当游标语句在匿名块或者函数中使用时，当游标从DN上读取较多数据但不完全使用时，因当前架构限制，无法记录该游标在DN上的监控信息。对于With Hold游标，由于该语法执行逻辑较特殊，会在事务提交阶段执行实际查询动作，当语句在该阶段执行报错时，作业的aborted状态无法反馈到TopSQL历史表中。
  7. 重分布过程中的作业不统计。
  8. JDBC执行的带占位符语句，通常会补齐参数内容，但如果参数和原语句合起来长度超过64KB，则不记录参数，或者如果是轻量化语句，直接下发到DN上执行，不记录参数。如果普通语句超过64KB，在TopSQL的query字段记录中将被截断。
  9. 8.1.3及以上集群版本，query、perf级别TopSQL运行时监控功能已完全不影响查询性能，对当前会话的语句进行资源监控的GUC参数resource_track_cost默认值已修改为0，查询TopSQL实时监控视图时，默认会显示所有正在执行的语句。
  10. 8.1.3及以上集群版本，对于存储过程中的子语句监控功能，如果在查询TopSQL实时监控视图的会话中，开启控制子语句记录归档功能的GUC参数enable_track_record_subsql，不论业务语句中是否开启子语句监控开关，查询TopSQL实时监控视图的结果都能看到执行语句的子语句运行信息。
  11. 关于存储过程中子语句的监控功能即enable_track_record_subsql，8.1.3集群版本中建议不要全面开启，由于没有按时间过滤子语句的功能，全面开启可能会记录过多子语句，导致归档的监控表占用大量磁盘空间；8.1.3集群版本建议仅用于查询实时监控信息，或对个别存储过程业务做定位分析时，仅开启对应会话中的参数。8.2.1版本新增GUC参数resource_track_subsql_duration（默认值为180秒），可以通过执行时间过滤需要归档的子语句，用户可以按需调整该值大小。
  12. 由于规格限制，对于未下盘的主语句，TopSQL历史表中的记录会有延时，等待下次作业下发时才会显示在TopSQL历史表中。
  13. 从8.2.1.200集群版本开始，新增operator_realtime级别的TopSQL运行时监控能力，提供算子级实时监控的能力，开启此级别的监控可以查询语句的执行计划以及具体执行信息，查询TopSQL算子级实时监控视图时，默认会显示所有正在执行的语句。但是对于存储过程和游标场景，暂时不支持显示算子级实时监控信息。
  14. operator_realtime级别TopSQL运行时监控对于CN轻量化和存储过程的情况，暂时不支持。另由于算子执行速度较快的原因，对于算子信息的显示会有一定滞后性。
  15. query级别的作业监控和operator的算子监控中的spill_size字段，由于统计维度不同，会有一定差异，query级别监控的语句实际下盘文件大小，算子监控的是具体算子在逻辑层IO读写的数据量。
  16. 当GUC参数enable_stream_operator设置为off状态时，算子执行信息存在显示不准的情况。
• 在8.1.3版本中，除初始用户外，enable_gtm_free开启且关联队列不管控情况下，用户作业不进入资源管控。此时实时与历史TopSQL均不记录该用户下发的作业。

通过TopSQL识别密集占用资源的SQL

• 识别stream数量多的语句：

  SELECT *,(length(query_plan) - length(replace(query_plan, 'Streaming', ''))) / length('Streaming') AS stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC limit 100; 

• 识别内存占用高的语句：

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_peak_memory desc limit 100; 

• 识别需要优化的语句：

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning is not null ORDER BY duration desc limit 100; 

• 识别执行时间长的语句：

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY duration desc; 

• 识别不下推的语句：

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning like ‘%can not be shipped%’ ORDER BY max_peak_memory desc; 

• 识别高CPU语句：

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_cpu_time desc; 

• 识别下盘量大的语句：

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' ORDER BY max_cpu_time desc; 

• 识别未做analyze的语句：

  SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning ilike '%statistics%';

监控PERT作业级别TopSQL

现网中有概率出现某一个SQL语句起初执行较快随后执行时间变长的情况，此时可以利用TopSQL中的query_plan以及其他资源信息进行分析定位，query_plan中的信息越详细，越接近于explain performance，定位过程就更容易。query级别与perf级别的TopSQL差异主要在query_plan，perf级别的TopSQL的query_plan字段可显示算子的时间actual_time，memory，rows和buffer等信息。

监控OPERATOR算子级的TopSQL

在SQL执行过程中，有些用户更希望能对算子执行进度进行监控，对于长时间运行的SQL，能够看出哪个算子执行时间长，通过算子执行时间和已处理行数等信息，确定是否需要查杀SQL，此时需要将resource_track_level参数设置为OPERATOR。

算子监控可以将SQL执行过程的算子监控数据以可视化的方式呈现出来，以便用户更加直观地了解算子的运行情况和性能表现。算子监控主要有以下价值：

• 提升用户体验：用户可以更加直观地了解算子的运行情况和性能。
• 性能优化：可以发现算子运行中的瓶颈和问题，进行优化和调整，提高算子的运行效率。
• 故障排查：可以及时发现算子运行中的问题和异常，进行修复和维护，提高SQL的可维护性。
• 提高算子的可扩展性：可以发现算子运行中的瓶颈和问题，及时进行优化和调整，提高算子的可扩展性，为后续的业务发展提供支持。

OPERATOR算子监控和QUERY/PERF语句监控功能类似，均包含实时和历史二种形态，包含静态和动态二类信息：

• 语句静态信息是语句在真正执行前就已经由优化器生成的信息，如执行计划plan_node_name，queryid，预估行数estimated rows等信息。可用来分析生成的执行计划是否合适。
• 语句动态信息是语句在执行器中执行过程中所占用的资源信息，如算子执行进度progress、内存peak_memory、算子下盘spill_size、网络net_size、磁盘IO(read_bytes、write_bytes)，CPU（cpu_time）等不同DN的实时的信息记录。可用来分析语句执行过程中的进度和资源消耗情况，通过该字段可以分析出语句在运行时消耗较久的在什么地方，便于后续优化。

功能展示：下发某个查询，在另一个会话中查询实时算子视图pgxc_wlm_operator_statistics，结果如下：

TopSQL与其他视图交互

不同视图从不同维度统计业务在数据库中的运行状态，因为或多或少都包含部分同类字段，可用于后续关联分析，此处给出历史TopSQL视图常交互的其他视图：

图1 TopSQL与其他视图交互

使用示例：如果某个语句在历史pgxc_wlm_session_info视图中显示该作业很快运行完成，但在pgxc_stat_activity视图中发现该语句运行很长时间没有结束，则此时可能有异常，可利用以下步骤进行定位：

常用定位过程如下所示：

1. 查询pgxc_stat_activity活跃视图，找出长时间运行不结束的异常作业。

   SELECT coorname, usename, client_addr, now()-query_start as dur, state, enqueue, waiting, pid, query_id, substr(query,1,150) FROM pgxc_stat_activity WHERE usename not in ('omm','Ruby') AND state = 'active' ORDER BY dur DESC limit 100; 

2. 根据1得到的query信息，查询历史TopSQL视图，比较以往和现在的运行情况，如果以前运行时间很快，但当前语句运行时间很长未结束，则可考虑该语句已发生异常（历史topsql视图按天分区，查询时尽量带start_time条件）。

   SELECT * FROM pgxc_wlm_session_info WHERE start_time > '2024-08-22 00:00' AND start_time < '2024-08-24 00:00' AND query ilike '%XXXXXXXXX%' ORDER BY start_time; 

3. 根据1得到的query_id查询等待视图pgxc_thread_wait_status，得到作业线程号lwtid，并打出作业堆栈进行分析。

   SELECT * FROM pgxc_thread_wait_status WHERE query_id = xxx; 
   SELECT * FROM gs_stack('nodename',tid);  # 本节点可直接 \! gstack lwtid

分析和处理TopSQL实践案例

通常情况下，TopSQL记录的信息较多， 查询时可使用start_time做条件，避免全表查询，且使用limit对结果集大小限制，防止结果集过大导致客户端异常。

• 案例1：某客户集群出现系统级性能问题，CPU持续飚高，业务受阻。

  通过TopSQL历史视图查询到有10+业务SQL存在stream数超过100，判断为CPU高的原因：

  SELECT *,(length(query_plan) - length(replace(query_plan, 'Streaming', ''))) / length('Streaming') as stream_count FROM pgxc_wlm_session_info ORDER BY stream_count DESC limit 100;

  

  针对此业务SQL进行下线并进行优化后，问题解决。

• 案例2：业务语句性能下降，业务起初较快后来变慢。

  因为TopSQL记录了一些语句的执行情况和资源消耗情况，在定位性能问题的时候非常有帮助，比如周期执行的SQL，突然有一天变慢了，可以通过分析语句的执行时间和阻塞时间判断是语句被阻塞了（排队等）还是执行慢了，通过记录的执行计划分析语句为什么慢了，是不是当时的统计信息统计不准，还是没有对表做ANALYZE，也可以根据下盘量大小分析是不是下盘量大造成的性能变慢。

  1. 通过pgxc_wlm_session_info视图确认历史的SQL信息（注：历史topsql视图按天分区，查询时尽量带start_time条件）。

     SELECT * FROM pgxc_wlm_session_info WHERE start_time > '2023-08-22 00:00' AND start_time < '2023-08-24 00:00' AND query ilike '%XXXXXXXXX%' ORDER BY start_time; 

  2. 根据作业的sql_hash信息确定作业的历史执行情况，借助历史TopSQL的资源信息和query_plan字段分析作业性能劣化原因。

     SELECT start_time, block_time, duration, sql_hash, warning, max_peak_memory, max_spill_size, query_plan FROM pgxc_wlm_session_info were start_time > 'xxxx-xx-xx xx:xx' and sql_hash = 'xxx' ORDER BY start_time desc limit 10; 

     找到对应的快慢语句后，对比其执行计划query_plan，发现执行计划跳变严重。

     

  3. 对相应的表做ANALYZE后，恢复合理计划，语句性能恢复。

     统计信息不准，导致计划跳变，是十分常见语句变慢原因，ANALYZE不影响读写，遇见语句变慢可预先做ANALYZE。

     ANALYZE dwrdim_dw1.dwr_dim_region_rc_d;

• 案例3：作业长时间运行不结束。

  在作业无排队无死锁正常运行期间，发现作业长时间不结束，此时可查看算子级别的实时TopSQL监控，能够看出哪个算子执行时间长，通过算子执行时间和已处理行数等信息，确定是否需要终止SQL。

  1. 开启实时算子监控：

     SET resource_track_level = 'operator_realtime';

     

  2. 根据需要查杀作业

     SELECT * FROM pg_terminate_backend(xxx);   # 入参为pid

• 案例4：作业整体使用内存较高，需分析算子内存使用情况。

  当发现作业内存整体使用较高，query级别的TopSQL无法记录算子的实际资源信息，此时可以设置TopSQL的监控级别为perf，query级别与perf级别的TopSQL差异主要在query_plan，perf级记录更详细执行信息，较query级性能损耗5%以内，perf级别的TopSQL的query_plan字段可显示算子的时间actual_time，memory，rows和buffer等信息。

  1. 开启perf级别TopSQL监控。

     SET resource_track_level = 'perf';

  2. 作业执行结果后，查询历史TopSQL视图pgxc_wlm_session_info查看各个算子的内存使用情况。