1、TopSQL简介

    在实际的生产环境中，难免会出现一些突发情况，如计划跳变、异常中断、作业长时间执行不结束等，如果已经没有现场，而且也没有工具将当时的作业运行情况记录下来的话，那么事后就要投入更多的人力以及时间成本对错误进行定位和解决，有时还往往定位不到错误出现的地方。为了解决这种情况，GaussDB(DWS)开发了TopSQL功能，对运行中的语句记录（实时TopSQL），对运行完成的语句进行记录（历史TopSQL）。 

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

2、TopSQL相关视图与GUC参数

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

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

• USE_WORKLOAD_MANAGER(ON)

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

• ENABLE_RESOURCE_TRACK (ON）

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

• ENABLE_RESOURCE_RECORD(ON)

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

• ENABLE_TRACK_RECORD_SUBSQL(ON)

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

• RESOURCE_TRACK_DURATION(60s)

       设置实时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。

• RESOURCE_TRACK_SUBSQL_DURATION(180s)

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

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

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

• RESOURCE_TRACK_COST(0)

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

• RESOURCE_TRACK_LEVEL(QUERY)

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

• RESOURCE_TRACK_APPLICATION

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

• TOPSQL_RETENTION_TIME(30)

       历史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后表空间可下降)。

• SESSION_HISTORY_MEMORY(100MB)

        设置历史查询视图的内存大小，如果语句运行过程中出现"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%';

视图常用分析字段：

    总结：

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

    注意事项：

• TopSQL不记录白名单和内部语句，但可正常记录超户下发的语句信息，可记录定时任务。
• query和perf级别的topsql监控主要差异点在于query_plan字段，相比于query级别的算子信息，perf级别的query_plan字段增加算子的实际信息，如算子实际内存峰值，内存自动扩展信息，CU，Buffers等统计信息。
  
• query和perf级别实时TopSQL和历史TopSQL的start_time字段信息含义不一致，实时TopSQL中的start_time表示的是作业下发的时间，历史TopSQL中的start_time表示的是作业真正开始运行的时间。

3、通过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%';

4、 PERT作业级级别TopSQL监控

     现网中有概率出现某一个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等信息。

5、OPERATOR算子级TopSQL监控

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

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

1. 提升用户体验：通过可视化的方式呈现算子执行信息，用户可以更加直观地了解算子的运行情况和性能。
2. 性能优化：通过对算子监控数据的可视化分析，可以发现算子运行中的瓶颈和问题，从而及时进行优化和调整，提高算子的运行效率。
3. 故障排查：通过对算子监控数据的可视化分析，可以及时发现算子运行中的问题和异常，从而及时进行修复和维护，提高SQL的可维护性。
4. 提高算子的可扩展性：通过对算子监控数据的可视化分析，可以发现算子运行中的瓶颈和问题，从而及时进行优化和调整，提高算子的可扩展性，为后续的业务发展提供支持。

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

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

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

6、TopSQL与其他视图交互

    不同视图从不同维度统计业务在数据库中的运行状态，因为或多或少都包含部分同类字段，可用于后续关联分析，此处给出历史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

7、TopSQL原理介绍

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

8、TopSQL实践

    通常情况下，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查看各个算子的内存使用情况。

9、参考文献

1. GaussDB for DWS 负载管理核心技术解密二： 白话历史资源监视-云社区-华为云 (huaweicloud.com)
2. GaussDB(DWS)监控工具指南（一）作业级监控TopSQL-云社区-华为云 (huaweicloud.com)
3. GaussDB（DWS）监控工具指南（四）算子级监控【绽放吧！GaussDB(DWS)云原生数仓】-云社区-华为云 (huaweicloud.com)