# 查询及分析TopSQL最佳实践 #### TopSQL简介 在DWS分布式数据库的复杂运维场景中，计划跳变、异常中断、长事务阻塞等突发状况时有发生。传统运维手段存在异常现场难复现、执行过程无追踪、运行指标无留存三大问题，导致故障定位困难，既耗费大量人力成本且难以高效溯源。 为解决上述问题，DWS提供TopSQL监控功能，支持**记录运行中和运行完成的SQL语句（实时TopSQL和历史TopSQL）**，主要实现以下功能： - 快速定位占用资源最密集且影响数据库性能的查询语句。 - 监控查询语句随时间推移的性能变化。 - 分析查询执行计划，确定潜在的优化手段。 作为数据库运维的核心支撑工具，TopSQL已深度集成于生产环境各场景，为系统性能诊断、SQL劣化追踪及安全审计溯源等关键环节提供全链路支持，监控指标涵盖内存占用、执行耗时、IO吞吐、网络时延及存储空间等核心维度。 #### TopSQL原理介绍 作业下发执行后，数据库内核通过打桩记录作业的各项资源信息，包括内存、CPU、下盘、IO和网络信息等，作业执行完成后，资源信息先存入无锁队列，由资源管理后台辅助线程定期转储至dbms_om.gs_wlm_session_info系统表中，后续通过topsql_retention_time参数定期老化清理。 图1TopSQL原理 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002478231774.png "点击放大") #### TopSQL相关GUC参数 **集群创建成功后，系统默认开启TopSQL功能** （由GUC参数**use_workload_manager** ，**enable_resource_track** 及**enable_resource_record** 控制，默认值全部为on），GUC详细参数如[表1]所示。 表1TopSQL相关GUC参数 | 参数 | 描述 | 建议值 | |:---|:---|:---| | **use_workload_manager** | 是否开启资源管理功能，开启TopSQL功能，该参数默认为on。 | on | | **enable_resource_track** | 是否开启资源实时监控功能，实时TopSQL的总开关，关闭之后实时TopSQL将不再记录，更不会在历史TopSQL中出现。 | on | | **enable_resource_record** | 是否开启资源监控记录归档功能。开启时，对于执行结束的记录，会分别被归档到相应的INFO视图，CN和DN都需要设置。 | on | | **enable_track_record_subsql** | 是否开启子语句记录归档功能，控制是否记录存储过程、匿名块内部语句。 - TopSQL记录该子语句的前提是：子语句下推到DN执行；子语句执行时间超过resource_track_subsql_duration。 - 目前TopSQL只能记录第一层循环的子语句，多层嵌套循环的子语句不会记录。 | on | | **resource_track_duration** | 设置实时TopSQL中记录的语句执行结束后进行历史信息转存的最小执行时间，该时间记录值的判断包含排队时间和运行时间，当排队时间+运行时间 \> resource_track_duration时，TopSQL历史视图会记录作业信息。 - CPU和存储资源充足的场景建议设置为0，可记录更全面的业务信息。 - 在QPS高于100场景，可酌情调大，如1\~10s。 | - 9.1.1.100以下集群版本中推荐设置为60s。 - 9.1.1.100及以上集群版本中推荐设置为100ms。 - 9.1.2及以上集群版本中推荐设置为0。 | | **resource_track_subsql_duration** | 存储过程内部子语句转储的最小执行时间。 该参数仅8.2.1及以上集群版本支持。 | - 9.1.2以下集群版本中推荐设置为180s - 新安装的9.1.2及以上集群版本中推荐设置为100ms。 | | **resource_track_cost** | 设置对当前会话的语句进行资源监控的最小执行代价。 | 0 | | **resource_track_level** | 设置当前会话的资源监控等级。 - none，不开启资源监控功能。 - query，开启query级别资源监控功能，开启此功能会把SQL语句的计划信息（类似explain输出信息）记录到TopSQL中。 - perf，开启perf级别资源监控功能，开启此功能会把包含实际执行时间和执行行数的计划信息（类似explain analyze输出信息）记录到TopSQL中。 - operator_realtime，开启operator级别资源监控功能，开启此功能后会记录实时运行的作业算子信息到TopSQL中，但不会持久化到历史TopSQL中。 - operator，开启operator级别资源监控功能，开启此功能不仅会把包含实际执行时间和执行行数的信息记录到TopSQL中，还会把算子级别执行信息刷新到TopSQL中。 | query | | **topsql_retention_time** | 历史TopSQL中GS_WLM_SESSION_INFO和GS_WLM_OPERATOR_INFO表中数据的保存时间，单位为天。 | 30 | | **session_history_memory** | 设置历史查询视图的内存大小。 如果语句运行过程中出现"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; ``` | - 9.1.2以下集群版本中推荐设置为100MB。 - 9.1.2及以上集群版本中推荐设置为500MB。 | **设置GUC参数方法**： 通过DWS控制台设置： 1. 登录DWS管理控制台。 2. 在左侧导航栏中，单击**"集群 \> 集群列表"**。 3. 在集群列表中找到所需要的集群，单击集群名称，进入**"集群详情"**页面。 4. 单击"参数修改"页签，并在"参数列表"模块修改相应的参数值，然后单击**"** **保存"** 。 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002689606881.png "点击放大") 5. 在**"修改预览** **"** 窗口，确认修改无误后，单击**"保存"**。 6. 可根据修改参数所在行的**"是否重启集群"**列，判断集群是否需要重启操作。 **查询TopSQL相关GUC参数设置的常用SQL：** ``` SELECT name,setting FROM pg_settings WHERE name LIKE '%resource%'; ``` #### TopSQL相关视图及分析思路 TopSQL监控等级包括query、perf、operator_realtime、operator4个级别，由GUC参数[resource_track_level](https://support.huaweicloud.com/devg-dws/dws_04_0922.html)控制。 - **query级别**：此级别会把SQL语句的计划信息（类似explain输出信息）记录到TopSQL中。 - **perf级别**：此级别会把包含实际执行时间和执行行数的计划信息（类似explain analyze输出信息）记录到TopSQL中。 - **operator_realtime级别**：此级别会记录实时运行的作业算子信息到TopSQL中，但不会持久化到历史TopSQL中。 - **operator级别**：此级别不仅会把包含实际执行时间和执行行数的信息记录到TopSQL中，还会把算子级别执行信息刷新到TopSQL中。 TopSQL功能主要通过视图承载，相关视图如[表2]所示，根据视图中的字段信息可以进一步分析TopSQL信息，视图中常用的分析字段参见[表3]。 表2TopSQL相关视图 | 级别 | 类型 | 查询数据范围 | 视图名称 | 描述 | |:---|:---|:---|:---|:---| | query/perf级别 | 实时 | 单CN | [GS_WLM_SESSION_STATISTICS](https://support.huaweicloud.com/devg-dws/dws_04_0706.html) | 用户在当前CN上正在执行的作业的负载管理记录。 | | query/perf级别 | 实时 | 所有CN | [PGXC_WLM_SESSION_STATISTIC](https://support.huaweicloud.com/devg-dws/dws_04_0841.html) | 用户在所有CN上正在执行的作业的负载管理信息。 | | query/perf级别 | 历史 | 单CN | [GS_WLM_SESSION_INFO](https://support.huaweicloud.com/devg-dws/dws_04_0704.html) | 用户在当前CN执行作业结束后的负载管理记录。 | | query/perf级别 | 历史 | 所有CN | [PGXC_WLM_SESSION_INFO](https://support.huaweicloud.com/devg-dws/dws_04_0839.html) | 用户在所有CN上执行作业结束后的负载管理记录。 | | query/perf级别 | 历史 | 所有CN（显示关键字段信息） | [PGXC_QUERY_INFO](https://support.huaweicloud.com/devg-911-dws/dws_04_1299.html) | 用户在所有CN上执行作业的TopSQL关键字段信息。**仅9.1.1.100及以上集群版本支持**。 | | operator_realtime级别 / operator级别 | 实时 | 单CN | [GS_WLM_OPERATOR_STATISTICS](https://support.huaweicloud.com/devg-dws/dws_04_0703.html) | 用户在当前CN上正在执行的作业的算子相关信息。 | | operator_realtime级别 / operator级别 | 实时 | 所有CN | [PGXC_WLM_OPERATOR_STATISTICS](https://support.huaweicloud.com/devg-dws/dws_04_0838.html) | 用户在所有CN上正在执行作业的算子信息。 | | operator_realtime级别 / operator级别 | 历史 | 单CN | [GS_WLM_OPERATOR_INFO](https://support.huaweicloud.com/devg-dws/dws_04_0701.html) | 用户在当前CN上执行作业结束后的算子相关的记录。 | | operator_realtime级别 / operator级别 | 历史 | 所有CN | [PGXC_WLM_OPERATOR_INFO](https://support.huaweicloud.com/devg-dws/dws_04_0837.html) | 用户在所有CN上执行作业结束时的算子信息。 | 表3视图常用分析字段 | 序号 | 字段名称 | 字段描述 | 分析出可能的现象 | |:---|:---|:---|:---| | 1 | block_time | 语句执行前的阻塞时间，包含：语句解析、语句优化时间，以及作业排队时间。 | **A1**：block_time较大而duration值并无明显变化，说明用户作业受其他作业影响，在实际开始执行前进行了较长时间的排队，下一步需要查看当前数据表，统计起始时间小于start_time、结束时间大于finish_time的作业数量。 **A2**：block_time较小而duration值较大，说明用户作业执行时间大幅增加由自身原因导致，需进一步分析数据量的变化情况和各DN的执行时间变化。 | | 2 | start_time | 语句开始执行时间。 | **A1**：block_time较大而duration值并无明显变化，说明用户作业受其他作业影响，在实际开始执行前进行了较长时间的排队，下一步需要查看当前数据表，统计起始时间小于start_time、结束时间大于finish_time的作业数量。 **A2**：block_time较小而duration值较大，说明用户作业执行时间大幅增加由自身原因导致，需进一步分析数据量的变化情况和各DN的执行时间变化。 | | 3 | finish_time | 语句执行结束时间。 | **A1**：block_time较大而duration值并无明显变化，说明用户作业受其他作业影响，在实际开始执行前进行了较长时间的排队，下一步需要查看当前数据表，统计起始时间小于start_time、结束时间大于finish_time的作业数量。 **A2**：block_time较小而duration值较大，说明用户作业执行时间大幅增加由自身原因导致，需进一步分析数据量的变化情况和各DN的执行时间变化。 | | 4 | duration | 语句执行时长。 | **A1**：block_time较大而duration值并无明显变化，说明用户作业受其他作业影响，在实际开始执行前进行了较长时间的排队，下一步需要查看当前数据表，统计起始时间小于start_time、结束时间大于finish_time的作业数量。 **A2**：block_time较小而duration值较大，说明用户作业执行时间大幅增加由自身原因导致，需进一步分析数据量的变化情况和各DN的执行时间变化。 | | 5 | status | 语句执行的结束状态，正常为finished，异常为aborted。 | 可以查看作业是否正常结束，如果异常，还会有异常原因。 | | 6 | abort_info | 语句执行结束状态为aborted时显示异常信息。 | 可以查看作业是否正常结束，如果异常，还会有异常原因。 | | 7 | min_peak_memory | 语句在所有DN上的最小内存峰值，单位MB。 | **B1**：对于同一个查询，可对比前后几次的内存消耗情况，内存消耗平均值能够反映出数据表的数据量是否有变化，memory_skew_percent值反映出相关数据表在各DN上的数据分布是否有倾斜。同时，query_plan（参见该表的序号31）能够直接显示作业的执行计划，对比执行计划是否有变化。 | | 8 | max_peak_memory | 语句在所有DN上的最大内存峰值，单位MB。 | **B1**：对于同一个查询，可对比前后几次的内存消耗情况，内存消耗平均值能够反映出数据表的数据量是否有变化，memory_skew_percent值反映出相关数据表在各DN上的数据分布是否有倾斜。同时，query_plan（参见该表的序号31）能够直接显示作业的执行计划，对比执行计划是否有变化。 | | 9 | average_peak_memory | 语句执行过程中的内存使用平均值，单位MB。 | **B1**：对于同一个查询，可对比前后几次的内存消耗情况，内存消耗平均值能够反映出数据表的数据量是否有变化，memory_skew_percent值反映出相关数据表在各DN上的数据分布是否有倾斜。同时，query_plan（参见该表的序号31）能够直接显示作业的执行计划，对比执行计划是否有变化。 | | 10 | memory_skew_percent | 语句在各DN间的内存使用倾斜率。 | **B1**：对于同一个查询，可对比前后几次的内存消耗情况，内存消耗平均值能够反映出数据表的数据量是否有变化，memory_skew_percent值反映出相关数据表在各DN上的数据分布是否有倾斜。同时，query_plan（参见该表的序号31）能够直接显示作业的执行计划，对比执行计划是否有变化。 | | 11 | min_spill_size | 若发生下盘，所有DN上下盘的最小数据量，单位MB。 | **C1**：对有大量下盘的查询有显著帮助，当下盘量剧增的时候，通常是表数据量有大幅增加，或者是执行计划有问题导致的，结合query_plan能进一步分析，spill_skew_percent可以查看作业是否有严重数据倾斜。 | | 12 | max_spill_size | 若发生下盘，所有DN上下盘的最大数据量，单位MB。 | **C1**：对有大量下盘的查询有显著帮助，当下盘量剧增的时候，通常是表数据量有大幅增加，或者是执行计划有问题导致的，结合query_plan能进一步分析，spill_skew_percent可以查看作业是否有严重数据倾斜。 | | 13 | average_spill_size | 若发生下盘，所有DN上下盘的平均数据量，单位MB。 | **C1**：对有大量下盘的查询有显著帮助，当下盘量剧增的时候，通常是表数据量有大幅增加，或者是执行计划有问题导致的，结合query_plan能进一步分析，spill_skew_percent可以查看作业是否有严重数据倾斜。 | | 14 | spill_skew_percent | 若发生下盘，DN间下盘倾斜率。 | **C1**：对有大量下盘的查询有显著帮助，当下盘量剧增的时候，通常是表数据量有大幅增加，或者是执行计划有问题导致的，结合query_plan能进一步分析，spill_skew_percent可以查看作业是否有严重数据倾斜。 | | 15 | min_dn_time | 语句在所有DN上的最小执行时间，单位ms。 | **D1**：DN上的执行时间，结合duration数据，如果一个查询在DN执行时间有严重倾斜，那就需要考虑数据表的分区、分布列是否设置合适；不合理的分区、分布列，可能会导致本应分散到多个DN的执行任务被集中到个别DN上执行，执行时间必然大大增加。 | | 16 | max_dn_time | 语句在所有DN上的最大执行时间，单位ms。 | **D1**：DN上的执行时间，结合duration数据，如果一个查询在DN执行时间有严重倾斜，那就需要考虑数据表的分区、分布列是否设置合适；不合理的分区、分布列，可能会导致本应分散到多个DN的执行任务被集中到个别DN上执行，执行时间必然大大增加。 | | 17 | average_dn_time | 语句在所有DN上的平均执行时间，单位ms。 | **D1**：DN上的执行时间，结合duration数据，如果一个查询在DN执行时间有严重倾斜，那就需要考虑数据表的分区、分布列是否设置合适；不合理的分区、分布列，可能会导致本应分散到多个DN的执行任务被集中到个别DN上执行，执行时间必然大大增加。 | | 18 | dntime_skew_percent | 语句在各DN间的执行时间倾斜率。 | **D1**：DN上的执行时间，结合duration数据，如果一个查询在DN执行时间有严重倾斜，那就需要考虑数据表的分区、分布列是否设置合适；不合理的分区、分布列，可能会导致本应分散到多个DN的执行任务被集中到个别DN上执行，执行时间必然大大增加。 | | 19 | min_cpu_time | 语句在所有DN上的最小CPU时间，单位ms。 | **E1**：CPU执行时间是分配给该作业的实际执行时间，当duration有明显增加，而平均CPU执行时间无明显变化时，很可能的一个原因是作业执行期间，有多个其他计算密集型作业同时段执行，因CPU抢占的原因，拉长了该作业的执行时长。 | | 20 | max_cpu_time | 语句在所有DN上的最大CPU时间，单位ms。 | **E1**：CPU执行时间是分配给该作业的实际执行时间，当duration有明显增加，而平均CPU执行时间无明显变化时，很可能的一个原因是作业执行期间，有多个其他计算密集型作业同时段执行，因CPU抢占的原因，拉长了该作业的执行时长。 | | 21 | total_cpu_time | 语句在所有DN上的CPU总时间，单位ms。 | **E1**：CPU执行时间是分配给该作业的实际执行时间，当duration有明显增加，而平均CPU执行时间无明显变化时，很可能的一个原因是作业执行期间，有多个其他计算密集型作业同时段执行，因CPU抢占的原因，拉长了该作业的执行时长。 | | 22 | cpu_skew_percent | 语句在DN间的CPU时间倾斜率。 | **E1**：CPU执行时间是分配给该作业的实际执行时间，当duration有明显增加，而平均CPU执行时间无明显变化时，很可能的一个原因是作业执行期间，有多个其他计算密集型作业同时段执行，因CPU抢占的原因，拉长了该作业的执行时长。 | | 23 | min_peak_iops | 语句在所有DN上的每秒最小IO峰值。 | **F1**：IO是变化最大的资源之一，作业在数据量、内存消耗、CPU执行时间、下盘量都无变化的情况下，偏偏duration增加了，那最可能的原因是IO变化导致。IO的特点是，IOPS变小表示作业执行时间变慢，即IO性能未达预期，拖长了作业执行时间；而其他属性通常相反，如：内存、CPU、下盘量，这些值变小通常意味着作业执行变快了。 | | 24 | max_peak_iops | 语句在所有DN上的每秒最大IO峰值。 | **F1**：IO是变化最大的资源之一，作业在数据量、内存消耗、CPU执行时间、下盘量都无变化的情况下，偏偏duration增加了，那最可能的原因是IO变化导致。IO的特点是，IOPS变小表示作业执行时间变慢，即IO性能未达预期，拖长了作业执行时间；而其他属性通常相反，如：内存、CPU、下盘量，这些值变小通常意味着作业执行变快了。 | | 25 | average_peak_iops | 语句在所有DN上的每秒平均IO峰值。 | **F1**：IO是变化最大的资源之一，作业在数据量、内存消耗、CPU执行时间、下盘量都无变化的情况下，偏偏duration增加了，那最可能的原因是IO变化导致。IO的特点是，IOPS变小表示作业执行时间变慢，即IO性能未达预期，拖长了作业执行时间；而其他属性通常相反，如：内存、CPU、下盘量，这些值变小通常意味着作业执行变快了。 | | 26 | iops_skew_percent | 语句在DN间的IO倾斜率。 | **F1**：IO是变化最大的资源之一，作业在数据量、内存消耗、CPU执行时间、下盘量都无变化的情况下，偏偏duration增加了，那最可能的原因是IO变化导致。IO的特点是，IOPS变小表示作业执行时间变慢，即IO性能未达预期，拖长了作业执行时间；而其他属性通常相反，如：内存、CPU、下盘量，这些值变小通常意味着作业执行变快了。 | | 27 | query_plan | 语句的执行计划。 | **G1**：作业执行计划是否有变化。 | | 28 | unique_sql_id | 标识一类语句。 | **H1** ：如果某类语句可能占用较多内存或者CPU资源，需要终止，可利用[gs_append_blocklist(unique_sql_id int8)](https://support.huaweicloud.com/sqlreference-dws/dws_06_0063.html) 或者[gs_append_blocklist(sql_hash text)](https://support.huaweicloud.com/sqlreference-dws/dws_06_0063.html) 函数将该类语句加入黑名单，后续该类语句下发将不再执行。 | | 29 | sql_hash | 标识一类语句。 | **H1** ：如果某类语句可能占用较多内存或者CPU资源，需要终止，可利用[gs_append_blocklist(unique_sql_id int8)](https://support.huaweicloud.com/sqlreference-dws/dws_06_0063.html) 或者[gs_append_blocklist(sql_hash text)](https://support.huaweicloud.com/sqlreference-dws/dws_06_0063.html) 函数将该类语句加入黑名单，后续该类语句下发将不再执行。 | | 30 | enqueue | 语句的排队状态。 | **I1**：作业是否异常排队/排队时间过久。可能出现的几种情况有： 1. Memory 2. Waiting in queue 3. Waiting in global queue 4. Waiting in respool queue 5. Waiting in ccn queue 6. No waiting queue 7. Null | | 31 | warning | 语句的告警信息及SQL自诊断调优相关告警，常见的warning信息参见[表4]。 | **J1**：可能出现的异常有下面几类告警： 1. Spill file size large than 256MB 2. Broadcast size large than 100MB 3. Early spill 4. Spill times is greater than 3 5. Spill on memory adaptive 6. Hash table conflict | **总结**： 1. 因数据量变化导致作业执行时间增加，可以分析A2/B1/D1/G1，确认作业查询的数据表是否存在明显的数据量增长。 2. 因其他并发作业抢占，导致作业排队，进而导致作业执行时间增加，可以分析A1/B1/D1，查看作业执行的同时段是否有大量并发作业在执行。 3. 因其他作业而产生的CPU抢占，导致作业执行时间增加，可以分析A2/D1/E1，查看作业执行的同时段是否有大量并发作业在执行。 4. 因其他作业而产生的IO抢占，导致作业执行时间增加，可以分析A2/F1，查看作业执行的同时段是否有大量并发作业在执行。 5. 禁止某一类语句执行，可参考H1。 6. 由于目前历史TopSQL视图字段信息较多，建议使用PGXC_QUERY_INFO视图查看查询关键字段信息，无需手动过滤无关字段。 资源争抢通常伴随并发问题，如CPU、IO抢占和作业排队现象都会发生，建议逐步分析解决： 1. 调整作业执行顺序，减少并发作业数量，减少阻塞时间。 2. 定位出同时段执行的典型计算密集型和存储密集型作业，调整至其他时间段执行，减少对本作业的影响。 3. 排除其他作业干扰后，再进一步分析。 表4TopSQL视图warning字段解析 | 场景分类 | 具体信息 | 解决方法 | |:---|:---|:---| | 下盘量过大或过早下盘 | The max spill size exceeds the alarm size xxxMB | 下盘可能是因为缓冲区设置过小，也可能是因为表的连接顺序或连接方式不合理等原因导致，要结合具体的SQL进行分析，可通过改写SQL语句或者通过[Plan Hint](https://support.huaweicloud.com/devg-dws/dws_04_0455.html)指定连接方式等手段解决。 | | 下盘量过大或过早下盘 | The max broadcast size exceeds the alarm size xxxMB | 下盘可能是因为缓冲区设置过小，也可能是因为表的连接顺序或连接方式不合理等原因导致，要结合具体的SQL进行分析，可通过改写SQL语句或者通过[Plan Hint](https://support.huaweicloud.com/devg-dws/dws_04_0455.html)指定连接方式等手段解决。 | | 下盘量过大或过早下盘 | Early spill | 下盘可能是因为缓冲区设置过小，也可能是因为表的连接顺序或连接方式不合理等原因导致，要结合具体的SQL进行分析，可通过改写SQL语句或者通过[Plan Hint](https://support.huaweicloud.com/devg-dws/dws_04_0455.html)指定连接方式等手段解决。 | | 下盘量过大或过早下盘 | Spill times is greater than 3 | 下盘可能是因为缓冲区设置过小，也可能是因为表的连接顺序或连接方式不合理等原因导致，要结合具体的SQL进行分析，可通过改写SQL语句或者通过[Plan Hint](https://support.huaweicloud.com/devg-dws/dws_04_0455.html)指定连接方式等手段解决。 | | 下盘量过大或过早下盘 | Spill on memory adaptive | 下盘可能是因为缓冲区设置过小，也可能是因为表的连接顺序或连接方式不合理等原因导致，要结合具体的SQL进行分析，可通过改写SQL语句或者通过[Plan Hint](https://support.huaweicloud.com/devg-dws/dws_04_0455.html)指定连接方式等手段解决。 | | 下盘量过大或过早下盘 | Hash table conflict | 下盘可能是因为缓冲区设置过小，也可能是因为表的连接顺序或连接方式不合理等原因导致，要结合具体的SQL进行分析，可通过改写SQL语句或者通过[Plan Hint](https://support.huaweicloud.com/devg-dws/dws_04_0455.html)指定连接方式等手段解决。 | | 统计信息 | Nestloop in hashjoin | 有可能统计信息不准确，及时对相关业务表进行ANALYZE操作。 | | 统计信息 | Table whose delta data exceeds 10% cannot be analyzed on VW | 弹性VW场景下，不支持ANALYZE，此为规格限制。 | | 统计信息 | Replication table cannot be analyzed on VW in temporary table sampling mode. | 弹性VW场景下，不支持ANALYZE，此为规格限制。 | | 统计信息 | Table cannot be analyzed on VW when enable_paralled_analyze is disabled in temporary table sampling mode. | 弹性VW场景下，不支持ANALYZE，此为规格限制。 | | 统计信息 | Replication table with more than 100,000 rows cannot be analyzed on VW. | 弹性VW场景下，不支持ANALYZE，此为规格限制。 | | 弹性VW | Concurrent scaling is not supported because the statement is in a transaction block. | 弹性VW负载均衡功能开启后，作业未能路由到弹性VW，此为规格限制。 | | 弹性VW | Concurrent scaling is not supported because the statement is a stored procedure. | 弹性VW负载均衡功能开启后，作业未能路由到弹性VW，此为规格限制。 | | 弹性VW | Concurrent scaling is not supported because the statement is not of the DML type. | 弹性VW负载均衡功能开启后，作业未能路由到弹性VW，此为规格限制。 | | 弹性VW | Concurrent scaling is not supported because the statement involves tables except V3 tables and foreign tables. | 弹性VW负载均衡功能开启后，作业未能路由到弹性VW，此为规格限制。 | | 弹性VW | Concurrent scaling is not supported because the statement does not support the cudesc streaming. | 弹性VW负载均衡功能开启后，作业未能路由到弹性VW，此为规格限制。 | | 弹性VW | Concurrent scaling is not supported because the resource pool associated with the statement disables the concurrency extension parameter. | 弹性VW负载均衡功能开启后，作业未能路由到弹性VW，此为规格限制。 | | 弹性VW | Concurrent scaling is not supported because the statement does not support cn retry. | 弹性VW负载均衡功能开启后，作业未能路由到弹性VW，此为规格限制。 | | 物化视图 | has others update base table, can not active matview. | 物化视图刷新告警，此为规格限制。 | #### 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状态时，算子执行信息存在显示不准的情况。 #### 通过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_spill_size desc; ``` - 定位未执行ANALYZE的语句： ``` SELECT * FROM pgxc_wlm_session_info WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' AND warning ilike '%statistics%'; ``` #### 监控perf作业级别TopSQL 业务中概率性地出现某SQL语句起初执行较快，随后执行时间变长的情况，此时可以利用TopSQL中的query_plan以及其他资源信息进行分析定位，query_plan中的信息越详细，越接近于explain performance，定位过程就更容易。query级别与perf级别的TopSQL差异主要在query_plan，perf级别的TopSQL的query_plan字段可显示算子的时间actual_time、memory、rows和buffer等信息。 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002476794725.png "点击放大") #### 监控operator算子级的TopSQL 在SQL执行过程中，有些用户更希望能对算子执行进度进行监控，对于长时间运行的SQL，能够看出哪个算子执行时间长，通过算子执行时间和已处理行数等信息，确定是否需要查杀SQL，此时需要将[resource_track_level](https://support.huaweicloud.com/devg-dws/dws_04_0922.html)参数设置为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。 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002476805897.png "点击放大") #### TopSQL与其他视图交互 不同视图从不同维度统计业务在数据库中的运行状态，各视图都包含部分同类字段，可用于后续关联分析，此处提供历史TopSQL视图常用的关联视图： 图2TopSQL关联视图 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002493689917.png "点击放大") **使用示例**：如果某语句在历史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); ``` #### 分析和处理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; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002443384458.png "点击放大") 终止此业务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 where start_time > 'xxxx-xx-xx xx:xx' and sql_hash = 'xxx' ORDER BY start_time desc limit 10; ``` 找到对应的快慢语句后，对比其执行计划query_plan，发现执行计划跳变严重。 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002443390606.png "点击放大") 3. 对相应的表执行ANALYZE后，恢复合理计划，语句性能恢复正常。 统计信息不准导致计划跳变是常见语句变慢原因，ANALYZE不影响读写操作，遇到语句变慢时可优先执行ANALYZE。 ``` ANALYZE dwrdim_dw1.dwr_dim_region_rc_d; ``` - **案例** **3** ：作业长时间运行不结束。 在作业无排队无死锁正常运行期间，发现作业长时间不结束，此时可查看算子级别的实时TopSQL监控，能够看出哪个算子执行时间长，通过算子执行时间和已处理行数等信息，确定是否需要终止SQL。 1. 开启实时算子监控： ``` SET resource_track_level = 'operator_realtime'; ``` ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002476912693.png "点击放大") 2. 用户根据实际业务情况，终止占用内存高的语句。 ``` SELECT * FROM pg_terminate_backend(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获取各个算子的内存使用情况。 ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002443553978.png "点击放大")