1、TopSQL简介

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

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

2、TopSQL相关视图与GUC参数

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

    GUC参数：

• 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历史视图会记录作业信息。

• RESOURCE_TRACK_SUBSQL_DURATION(180s)

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

• RESOURCE_TRACK_COST(0)

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

• RESOURCE_TRACK_LEVEL(QUERY)

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

• 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"

视图常用分析字段：

    总结：

1. 因数据量变化，导致作业执行时间增加，可以分析A2/B1/D1/G1，进而确认作业查询的数据表是否有明显的数据量增加；

2. 因其它并发作业抢占，导致作业排队，从而导致作业执行时间增加，可以分析A1/B1/D1，进而查看作业执行的同时期是否有大量并发作业在执行；

3. 因其它作业而产生的CPU抢占，导致作业执行时间增加，可以分析A2/D1/E1，进而查看作业执行的同时期是否有大量并发作业在执行；

4. 因其它作业而产生的IO抢占，导致作业执行时间增加，可以分析A2/F1，进而查看作业执行的同时期是否有大量并发作业在执行；

5. 禁止某一类语句执行，可参考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原理介绍

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

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

5、算子级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;
gstack xxx

7、TopSQL实践

        某客户集群出现系统级性能问题，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进行下线并进行差分优化后，问题解决。

8、参考文献

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