2、历史TopSQL解析

# TopSQL 概述

## TopSQL 概述 (1)

- 将 SQL 的排队信息和运行时的信息（耗时、CPU、内存、IO、网络、空间）记录到一张系统表中，这称为 TopSQL，即作业级监控。
  
- TopSQL 可以帮助用户实现以下功能：
  - 确定影响数据库性能的资源最密集的 SQL 查询。
  - 监控和跟踪 SQL 查询随时间推移的性能变化。
  - 分析查询执行计划以确定潜在的优化。

### 类型与查询数据范围

| 级别   | 类型         | 查询数据范围            |
|--------|--------------|-------------------------|
| 实时   | 视图名称     | 当前 CN                |
|        | GS_WLM_SESSION_STATISTICS |                       |
| 历史   | 全部 CN      | PGXC_WLM_SESSION_STATISTICS |
|        | 当前 CN      | GS_WLM_SESSION_INFO     |
|        | 所有 CN      | PGXC_WLM_SESSION_INFO   |

---

## TopSQL 概述 (2)

- TopSQL 的前置参数配置及数据流转过程：
  - **实时 TopSQL**（运行中的语句记录）需关注以下参数：
    - `enable_resource_track`：开启资源实时监控。
    - `resource_track_cost`：执行代价阈值。
    - `resource_track_level`：资源监控等级。
  
  - **历史 TopSQL**（运行完成的语句记录）需关注以下参数：
    - `enable_resource_record`：资源监控记录归档。
    - `resource_track_duration`：作业运行时间阈值。
    - `topsql_retention_time`：历史数据老化周期。

![title](/api/file/getImage?fileId=682d73d72cbdfd19ee0006ba)

---

# 历史 TopSQL 视图和参数介绍

## 历史 TopSQL 视图和参数介绍 (1)

TopSQL 功能主要通过视图进行承载，其历史视图如下所示：

- **query**: 把 SQL 语句的计划信息（类似 explain 输出信息）记录到 TopSQL 中。
- **perf**: 把包含实际执行时间和执行行数的计划信息（类似 explain analyze 输出信息）记录到 TopSQL 中。
- **operator**: 不仅会把包含实际执行时间和执行行数的信息记录到 TopSQL 中，还会把算子级别执行信息刷新到 TopSQL 中。

| 视图级别   | 查询数据范围 | 视图名称                 |
|------------|--------------|--------------------------|
| query/perf | 当前 CN     | GS_WLM_SESSION_INFO      |
|            | 全部 CN     | PGXC_WLM_SESSION_INFO    |
| operator    | 当前 CN     | GS_WLM_OPERATOR_INFO     |
|            | 所有 CN     | PGXC_WLM_OPERATOR_INFO   |

历史 TopSQL 视图记录了作业运行结束时的资源使用情况（包括内存、下盘、CPU 时间等）和运行状态信息（包括报错、终止、异常等）以及性能告警信息。用户可以通过对历史语句运行数据的分析，筛选出执行时长较大的语句，查看语句执行计划是否有优化的空间，是否需要对表进行 analyze 或 vacuum 等操作。对于内存报错的情况，用户还可以分析内存占用高的语句是否合理，从执行计划上分析是否有优化空间。

---

## 历史 TopSQL 视图和参数介绍 (2)

视图中 SQL 及作业管理信息，通过以下字段可以获取对应的作业：

| 字段          | 字段说明                                      |
|---------------|-----------------------------------------------|
| username      | 下发作业的用户                               |
| query_band    | 用于标示作业类型，可通过 GUC 参数 query_band 设置，默认为空字符串 |
| queryid       | 语句执行时标识语句的 ID                      |
| query         | 执行的 SQL 语句                              |
| resource_pool | 用户使用的资源池                             |
| enqueue       | 作业负载管理状态                             |
| control_group | 作业所使用的 cgroup                          |
| query_plan    | 作业的告警信息及 SQL 自诊断调优相关告警     |
| stmt_type     | 作业的类型，如 INSERT、UPDATE、DELETE 等    |
| warning       | 自诊断信息                                   |

---

## 历史 TopSQL 视图和参数介绍 (3)

历史 TopSQL 作业属性信息示例：

查询语句：

```sql
SELECT username, query_band, queryid, resource_pool, enqueue, control_group, query_plan 
FROM pgxc_wlm_session_info;
```

执行结果：

![title](/api/file/getImage?fileId=682d74332cbdfd19ee0006bc)

---

## 历史 TopSQL 视图和参数介绍 (4)

视图中语句运行时资源信息：

| 指标                    | 具体列名                     | 描述                                     |
|-------------------------|------------------------------|------------------------------------------|
| 语句执行时间            | block_time/start_time        | 作业阻塞时长/作业开始运行时间          |
|                         | estimate_total_time/duration | 作业执行预估总时间/作业已经执行的时间 |
| DN 执行时长            | max_dn_time                  | 作业在所有 DN 上的执行时间（最小、最大、平均、倾斜率） |
|                         | max_cpu_time                 | 作业在所有 DN 上的运行占用 CPU 的时间（最小、最大、总和、倾斜率） |
| 作业占用内存          | estimate_memory               | 作业执行预估内存                        |
|                         | max_peak_memory              | 作业在所有 DN 上占用内存值峰值指标（最小、最大、平均、倾斜率） |
| 下盘量                  | spill_info                   | 作业在 DN 上的下盘信息 [a:b]：数量为 b 个 DN 中有 a 个 DN 下盘 |
|                         | max_spill_size               | 作业在所有 DN 上的下盘数据量（最小、最大、平均、倾斜率） |
| 读写 IO                | max_read_bytes/max_write_bytes| 作业在所有 DN 上的 IO 读/写字节数（最小、最大、平均） |
| 网络通信               | recv_pkg/send_pkg/recv_bytes/send_bytes | 各个 DN 上的网络收发包数量，收发数据量 |

---

## 历史 TopSQL 视图和参数介绍 (5)

历史 TopSQL 资源信息查询示例：

查询语句：

```sql
SELECT queryid, start_time, finish_time, estimate_total_time, estimate_memory, duration, status, abort_info 
FROM pgxc_wlm_session_info;
```

执行结果：

![title](/api/file/getImage?fileId=682d74682cbdfd19ee0006be)

---

## 历史 TopSQL 视图和参数介绍 (6)

涉及 TopSQL 的 GUC 参数：

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

- **RESOURCE_TRACK_COST (0)**  
  设置对当前会话的语句进行资源监控的最小执行代价。

- **RESOURCE_TRACK_LEVEL (QUERY)**  
  设置当前会话的资源监控的等级，默认为 query 级别。

- **RESOURCE_TRACK_DURATION (60S)**  
  设置实时 TopSQL 中记录的语句执行结束后进行历史信息转存的最小执行时间，该时间记录值的判断包含排队时间和运行时间。当排队时间 + 运行时间 > RESOURCE_TRACK_DURATION 时，TopSQL 历史视图会记录作业信息。

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

- **TOPSQL_RETENTION_TIME (30)**  
  历史 TopSQL 中 GS_WLM_SESSION_INFO 和 GS_WLM_OPERATOR_INFO 表中数据的保存时间，单位为天。

---

## 历史 TopSQL 视图和参数介绍 (7)

### TopSQL 使用场景示例

- **场景一**：某个用户作业执行的 SQL 执行慢，需要分析定位原因。
  - 查询 `pgxc_wlm_session_statistics` 查询 `duration` 和 `block_time`
  - 若 `block_time` 较大，而 `duration` 值并无明显变化，说明用户作业受其它作业影响，在真正开始执行前进行了较长时间的排队。接着需要查看当前视图，统计起始时间小于 `start_time`、结束时间大于 `finish_time` 的作业数量。
  - 若 `block_time` 较小，而 `duration` 值较大，说明用户作业执行时间增加较大原因是自己导致，需要继续分析数据量的变化情况和各 DN 的执行时间变化。

- **场景二**：某个用户作业下发的 SQL 执行报错，需要分析原因。
  - 查询历史视图 `pgxc_wlm_session_info`，根据用户下发的作业语句找到对应的 `query`，查看 `status` 和 `abort_info`，以检查作业是否异常结束，若异常，`abort_info` 还会有异常原因。

---

# 通过历史 TopSQL 定位性能问题

## 通过历史 TopSQL 定位性能问题 (1)

**问题场景 1**：某客户由于内存规格较小，经常出现内存不可用的报错，云上运维人员通过 autopilot 定期巡检时发现该集群内存周期性冲高，且存在单实例内存使用倾斜的问题，需定位原因。

![title](/api/file/getImage?fileId=682d74a22cbdfd19ee0006c0)

---

## 通过历史 TopSQL 定位性能问题 (2)

**处理过程**：
1. 通过历史 TopSQL 找到内存占用高的语句：

```sql
   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;
   ```

2. 根据 `unique_sql_id` 确认作业的历史执行情况。

![title](/api/file/getImage?fileId=682d74dc2cbdfd19ee0006c1)

---

## 通过历史 TopSQL 定位性能问题 (3)

**问题场景 2**：某客户在业务中封装了大量的存储过程和匿名块脚本，用于业务系统的调度，随着业务数据越来越多，存储过程和匿名块脚本执行越来越慢，需要对其中的脚本进行优化。

![title](/api/file/getImage?fileId=682d74ed2cbdfd19ee0006c2)

---

## 通过历史 TopSQL 定位性能问题 (4)

**处理过程**：
1. 查看历史 TopSQL，存储过程和匿名块的 `query_plan` 字段显示 `NoPlan`。
2. 设置 `enable_track_record_subsql` 为 `on`，该参数打开后可以记录存储过程和匿名块中的子语句和执行计划。
3. 重新执行慢的存储过程，根据 `query_id` 查看历史 TopSQL 中各个子语句的执行计划。

![title](/api/file/getImage?fileId=682d75032cbdfd19ee0006c4)

---

# 通过历史 TopSQL 识别慢 SQL

## 通过历史 TopSQL 识别慢 SQL (1)

- 识别 stream 数量多的语句：

```sql
  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
  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;
  ```

- 识别有自诊断优化建议的语句：

```sql
  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;
  ```

- 识别执行时间长的语句：

```sql
  SELECT * FROM pgxc_wlm_session_info 
  WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' 
  ORDER BY duration DESC;
  ```

- 识别不下推的语句：

```sql
  SELECT * FROM pgxc_wlm_session_info 
  WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' 
  AND warning LIKE '%SQL is not plan-shipping%';
  ```

- 识别高 CPU 语句：

```sql
  SELECT * FROM pgxc_wlm_session_info 
  WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' 
  ORDER BY max_cpu_time DESC;
  ```

- 识别下盘量的语句：

```sql
  SELECT * FROM pgxc_wlm_session_info 
  WHERE start_time > 'xxxx-xx-xx' AND start_time < 'xxxx-xx-xx' 
  ORDER BY max_cpu_time DESC;
  ```

---

## 通过历史 TopSQL 识别慢 SQL (2)

### 相关案例：

1. **某客户集群出现系统级性能问题，CPU 持续飙高，业务受阻**。

![title](/api/file/getImage?fileId=682d753c2cbdfd19ee0006c6)

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

```sql
   SELECT nodename, pid, (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;
   ```

![title](/api/file/getImage?fileId=682d756d2cbdfd19ee0006c8)

3. **针对此业务 SQL 进行下线并进行差分优化后，问题解决**。

DBLOG

导航1234

最近发表

友情链接