数据库异常难定位？GaussDB(DWS)运维神器TopSQL来解决
本文分享自华为云社区《【云驻共创】GaussDB（DWS）运维利刃：TopSQL工具解析》，作者：金鱼哥。
在生产环境中，难免会面临查询语句出现异常中断、阻塞时间长等突发问题，如果没能及时记录信息，事后就需要投入更多的人力及时间成本进行问题的定位和解决，有时还无法定位到错误出现的地方。为了解决这种场景，GaussDB（DWS）提供了强大的运维工具TopSQL，对运行中和运行完成的语句全方面记录，帮助用户定位性能问题。而TopSQL被誉为性能定位、劣化分析等的重要基石，为用户提供覆盖内存、耗时、IO、网络、空间等多方面的信息记录。接下来将介绍TopSQL的基本原理、能力及典型应用场景，并讲解如何使用好TopSQL这把运维利刃。
一、TopSQL简介
1. 什么是TopSQL？
TopSQL是GaussDB（DWS）数据库中内置的一款功能十分强大的性能分析工具。在实际生产环境中，难免会出现一些突发情况，导致查询语句出现异常中断、阻塞时间长等情况，如果当时没能记录下来，那么事后就要投入更多的人力以及时间成本去对错误进行定位和解决，有时还往往定位不到错误出现的地方。
为了解决这样的窘迫的情况，GaussDB(DWS)开发了TopSQL功能，对运行中的语句记录（实时TopSQL），对运行完成的语句进行记录（历史TopSQL）。
TopSQL作为GaussDB(DWS)的性能诊断工具，记录GaussDB(DWS)中各个作业、算子级别的资源使用数据、耗时数据，包括下盘信息、内存、网络、耗时、自诊断告警、基础信息等作业执行的数据。
2. 怎么实现TopSQL功能？
在GaussDB中，通过设定GUC参数，来开启TopSQL功能，由于小功能不同，那么相应的小功能的GUC参数也会有所区别。
大部分都是开关类型的，想要自定义场景就只需要关注传参类型的参数，以及参数之间的依赖关系即可。下一章节将介绍常用系统表和参数的设置介绍。
开启TopSQL功能必须要确保如下参数设置（下列参数为开启TopSQL功能的必要条件）：
- user_workload_manager = on（默认为on）
- enable_resource_track = on（默认为on）
- resource_track_level = query / perf / operator（默认为query）
- 若是8.2.1.200版本，新增一个枚举参数为operator_realtime
二、TopSQL系统表和参数介绍
1. 公共GUC参数
以GaussDB(DWS) 8.1.3版本为例，推荐TopSQL相关的一些参数，如下表所示：
| 参数配置 | 解释及用法 |
|---|---|
| enable_resource_track：on | 资源监控总开关，开启后TopSQL才能发挥作用。 |
| enable_resource_record：on | 控制实时TopSQL是否做历史TopSQL转储，813版本推荐打开，方便对历史问题做定位分析。 |
| resource_track_cost：0 | 执行代价超过预值，才会被记录，建议保持默认值0。 |
| resource_track_duration：1 | 实时TopSQL是否转储到历史TopSQL，默认值为60秒，建议设置为1。 |
| resource_track_level：query/perf | 语句级TopSQL信息记录到历史TopSQL的系统表中。 |
| TopSQL_retention_time：30 | TopSQL老化时间为30秒，建议保持默认值。 |
| enable_track_record_subsql：按需开启 | 控制子查询是否记录到TopSQL系统表中。 |
TopSQL系统表关键字段：
| 字段 | 字段说明 |
|---|---|
| username | 用户名 |
| block_time | 排队时间，辅助定位CCN场景 |
| Start_time | 语句执行的开始时间 |
| duration | 语句执行时长，重点关注 |
| Estimate_memory | 估算内存，复制定位ccn排队或内存问题 |
| Max_peak_memory | 实际最大使用内存，定位内存不足场景 |
| Max_spill_size | 下盘大小 |
| Unique_sql_id | 归一化ID，标志同一语句/同一语句不同入参 |
| Cpu_skew_percent | CPU倾斜情况 |
| warning | 自诊断信息 |
2. 实时TopSQL
系统提供了query级别和算子级别的资源监控实时视图来用于查询实时TopSQL。资源监控实时视图记录了查询作业运行时的资源使用情况（包括内存、下盘、CPU时间、IO等）以及性能告警信息。
实时视图具体的对外接口如下表所示：
| 视图级别 | 节点范围 | 查询视图 |
|---|---|---|
| Query级别 | 当前CN | gs_wlm_session_statistics |
| 所有CN | pgxc_wlm_session_statistics | |
| 算子级别 | 当前CN | gs_wlm_operator_statistics |
| 所有CN | pgxc_wlm_operator_statistics |
开启条件
必须要确保如下GUC参数设置：
- use_workload_manager为on（默认为on）
- enable_resource_track为on（默认为on）
- resource_track_level为query或operator（默认为query）
监控作业类型为：
- 优化器估算的执行代价大于或等于resource_track_cost的取值的作业。
- 增删改查，包括使用explain analyze和explain performance来执行的场景。query级别视图还额外支持create table as语法。
- 重分布过程中的作业不统计。
Cgroups功能正常加载，可通过gs_cgroup -P查看控制组信息。
use_workload_manager为系统级参数，用于是否开启资源管理功能。
enable_resource_track为系统级参数，用于设置是否开启资源监控功能。
resource_track_level为session级参数，可以对某个session的资源监控级别进行灵活设置。
这两个参数的设置方法如下表：
| enable_resource_track | resource_track_level | query级别信息 | 算子级别信息 |
|---|---|---|---|
| on(default) | none | 不统计 | 不统计 |
| on(default) | query(default) | 统计 | 不统计 |
| on(default) | operator | 统计 | 统计 |
| off | none/query/operator | 不统计 | 不统计 |
3. 历史TopSQL
系统提供了query级别和算子级别的资源监控历史视图用例查询历史TopSQL。资源监控历史视图记录了查询作业运行结束时的资源使用情况（包括内存、下盘、CPU时间、IO等）和运行状态信息（包括报错、终止、异常等）以及性能告警信息。但对于由于FATAL、PANIC错误导致查询异常结束时，状态信息列只显示aborted，无法记录详细异常信息。对于查询解析，优化阶段的状态信息则无法监控。
历史视图具体的对外接口如下表所示：
| 视图级别 | 节点范围 | 查询视图 | |
|---|---|---|---|
| Query级别 | 当前CN | 历史（Database Manager接口） | gs_wlm_session_history |
| 历史（内部转储接口） | gs_wlm_session_info | ||
| 所有CN | 历史（Database Manager接口） | pgxc_wlm_session_history | |
| 历史（内部转储接口） | pgxc_wlm_session_info | ||
| 算子级别 | 当前CN | 历史（Database Manager接口） | gs_wlm_operator_history |
| 历史（内部转储接口） | gs_wlm_operator_info | ||
| 所有CN | 历史（Database Manager接口） | pgxc_wlm_operator_history | |
| 历史（内部转储接口） | pgxc_wlm_operator_info |
开启条件
必须要确保如下GUC参数设置：
- use_workload_manager为on （默认为on）
- enable_resource_track为on （默认为on）
- resource_track_level为query或operator（默认为query）
- enable_resource_record为on（默认为off）
- resource_track_duration小于作业执行时间（默认为60s）
监控作业类型为：
- 资源监控实时视图中记录的作业结束时的执行时间大于或等于resource_track_duration的作业。
- 优化器估算的执行代价大于或等于resource_track_cost的取值的作业
Cgroups功能正常加载，可通过gs_cgroup -P查看控制组信息。
该功能会引起存储空间膨胀及轻微性能影响，不建议生成系统默认使用。
三、TopSQL典型应用场景
1. 实时TopSQL
问题场景：某集群业务反馈某业务SQL偶发执行慢，该集群resource_track_duration设置较大，历史TopSQL中没有记录计划详情，需要定位原因。
处理过程：
① 系统管理员根据queryid查看等待视图，等待视图部分结果如下：
② 管理员执行explain verbose，得到的执行计划如下
③ 第8层非Stream算子，说明该计划不是正在执行语句的计划，使用实时TopSQL查看实际计划：
2. 历史TopSQL
问题场景：某客户由于内存规格较小，经常出现内存不可用的报错，云上运维人员通过autopilot定期巡检时发现该集群内存周期性冲高，且存在单实例内存使用倾斜的问题，需定位原因。
处理过程：
① 通过历史TopSQL找到内存占用高的语句
Select \* from pgxc_wlm_session_info where start_time > '2023-10-30 10:05' and start_time < '2023-10-30 10:10' order by max_peak_memory desc limit 100;
② 根据unique_sql_id确认作业的历史执行情况
3. 存储过程子语句
问题场景：某客户在业务中封装了大量的存储过程和匿名块脚本，用于业务系统的调度，随着业务数据越来越多，存储过程和匿名块脚本执行越来越慢，需要对其中的脚本进行优化。示例脚本如下：
处理过程：
① 查看历史TopSQL，存储过程和匿名块的query_plan字段显示NoPlan。
② 设置enable_track_record_subsql: on，该参数打开后可以记录存储过程和匿名块中的自语句和执行计划。
③ 重新执行慢的存储过程，根据query_id查看历史TopSQL中各个自语句的执行计划。
四、如何通过TopSQL进行信息统计
1. 常用TopSQL进行业务信息统计
① 识别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;
2. TopSQL使用注意事项
① 查询时使用start_time做条件，避免全表查询。
② 查询时使用limit对结果集大小限制，防止结果集过大导致客户端OOM。
总结
- GaussDB 具备将瞬息万变的负载情况记录下来，提供回看数据库系统内部资源负载情况的能力，方便管理员对已经发生的问题做定位定界。
- TopSQL收集的数据来源于数据库内部，为用户提供了实时监控数据库的能力。
- TopSQL功能被用户广泛使用，是性能定位、劣化分析、审计回溯等重要的基石，为用户提供覆盖内存、耗时、IO、网络、空间等多方面的监控能力。在此基础上，内核也拓展出了异常规则等一些高阶用法，在日常使用中，用户可对TopSQL提出了更高的要求，比如记录子语句、记录语句类型、提升算子级别语句监控准确性等诸多建议。