目前TopSQL功能被用户广泛使用，是性能定位、劣化分析、审计回溯等重要的基石，为用户提供覆盖内存、耗时、IO、网络、空间等多方面的监控能力。 TopSQL可以帮助用户实现下列功能：①确定影响数据库性能的资源最密集的SQL查询；②监控和跟踪SQL查询随时间推移的性能变化；③分析查询执行计划以确定潜在的优化。

TopSQL功能主要通过视图承载，相关视图如下：

TopSQL功能默认打开（use_workload_manager，enable_resource_track，enable_resource_record参数均默认为on），GUC参数详细介绍如下：

资源管理总开关，TopSQL功能开启该参数需要为on。

是否开启监控功能，实时TopSQL的总开关，关闭之后实时TopSQL将不再进行记录，更不会在历史TopSQL中出现。

设置是否开启资源监控记录归档功能。开启时，对于执行结束的记录，会分别被归档到相应的INFO视图，CN和DN都需要设置上。

控制是否记录存储过程、匿名块内部语句（默认为on）(建议820及以上版本开启，TopSQL记录该子语句的前提是：子语句下推到DN执行；子语句执行时间超过resource_track_subsql_duration，目前TopSQL只能记录第一层循环的子语句，多层嵌套循环的子语句不会记录)。

设置实时TopSQL中记录的语句执行结束后进行历史信息转存的最小执行时间，该时间记录值的判断是包含了排队时间和运行时间（如果在解析优化阶段发生了锁等待，产生的时间不会记录到该参数中），当排队时间+运行时间 > RESOURCE_TRACK_DURATION时，TopSQL历史视图会记录作业信息。CPU和存储资源充足的场景建议设置为0，可记录更全的业务；在QPS高于100场景，可酌情调大，如1-10s。

说明：该参数可支持单位是毫秒、秒、分钟、小时、天，默认单位为秒，参数设置示例如下：

SET resource_track_duration='0.1s';
SET resource_track_duration=0.1;
SET resource_track_duration='0.01min'; # 由于该参数的默认单位是秒，采用四舍五入的方式，因此该处设置的值为1s,而不是0.6s。

存储过程内部中子语句转储的最小执行时间。

说明：该参数可支持单位是毫秒、秒、分钟、小时、天，默认单位为秒，参数设置示例如下：

SET resource_track_subsql_duration='0.1s';
SET resource_track_subsql_duration=0.1;
SET resource_track_subsql_duration='0.01min'; # 由于该参数的默认单位是秒，采用四舍五入的方式，因此该处设置的值为1s,而不是0.6s。

设置对当前会话的语句进行资源监控的最小执行代价。

设置当前会话的资源监控的等级，默认为query级别。

设置TopSQL记录指定客户端下发的语句，目前支持设置的客户端为：gs_rewind、cm_agent、pgxc_clean、gs_clean、gs_running_xacts、OM、wlm。设置多个客户端时，相邻的值需用英文逗号隔开

历史TopSQL中GS_WLM_SESSION_INFO和GS_WLM_OPERATOR_INFO表中数据的保存时间，单位为天(历史TopSQL视图的数据实际是存储在postgres数据库中的dbms_om.gs_wlm_session_info系统表上，该表通过start_time进行分区，每天一个分区，通过参数topsql_retention_time配置默认保留30个分区即30天的记录，定期对pgxc_wlm_session_info的分区进行清理、创建，hash分布且分布键为queryid。如判断该表占用空间较多，可调低该参数，最多30min后表空间可下降)。

设置历史查询视图的内存大小，如果语句运行过程中出现"TopSQL lfq is full, failed to save queryid"报错信息，可通过如下SQL查询TopSQL无锁队列总共的内存和已使用的内存。

select * from pgxc_total_memory_detail where nodename like 'cn_%' and memorytype like '%topsql%' order by 1,2;

如果发现某个CN节点topsql_used_memory大于或者即将大于max_topsql_memory，除调大该GUC参数外，可在该CN执行如下SQL，进行手动转储：

CALL pg_catalog.create_wlm_session_info(1);

通过GUC命令设置参数：

gs_guc reload -Z coordinator -Z -N all -I all -c "enable_resource_track=on"
gs_guc reload -Z coordinator -Z -N all -I all -c "resource_track_level=query"
gs_guc reload -Z coordinator -Z -N all -I all -c "enable_resource_record=on"
gs_guc reload -Z coordinator -Z -N all -I all -c "resource_track_duration=0"
gs_guc reload -Z coordinator -Z -N all -I all -c "resource_track_cost=0"

通过管控面设置TopSQL相关参数：

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

select name,setting from pg_settings where name like '%resource%';

视图常用分析字段：

总结：

值得注意的是，发生资源争抢时，可能会出现并发症，即CPU、IO抢占，作业排队现象都会发生，针对并发症问题，可以逐步分析解决，比如：第一步，调整作业执行顺序，减少并发作业数量，减少阻塞时间；第二步，定位出同时段执行的典型计算密集型、存储密集型作业，先移动到其它时间段执行，减少对本作业的影响；第三步，在无其他作业明显干预的情况下，做进一步分析。

注意事项：

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;

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;

select * from pgxc_wlm_session_info where start_time > 'xxxx-xx-xx' and start_time < 'xxxx-xx-xx' and warning ilike '%statistics%';

现网中有概率出现某一个SQL原先执行比较快，后来发现该语句执行时间变长，此时就需要利用TopSQL中的query_plan以及其他资源信息进行分析定位，毋庸置疑，query_plan中的信息越详细，越接近于explain performance，定位过程就更容易。query级别与perf级别的TopSQL差异主要在query_plan，perf级记录更详细执行信息，较query级性能损耗5%以内，perf级别的TopSQL的query_plan字段可显示算子的时间actual_time，memory，rows和buffer等信息。

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

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

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

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

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

使用示例：如果某个语句在历史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

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

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

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

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

postgres=# 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_hsah信息确定作业的历史执行情况，借助历史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查看各个算子的内存使用情况。