五、GaussDB数据库性能调优笔记

[TOC]
## 一、系统性能调优及指标

### 1.1 系统性能调优概述

#### 1.1.1 性能调优定义
- 目的：充分利用CPU、内存、I/O和网络资源，避免资源冲突，提升系统吞吐量。
- 影响因素：
  - 硬件：服务器、存储、网络
  - 系统规模：并发、数据量
  - 软件环境：操作系统及参数配置
  - 数据库：内核参数、表结构、索引、统计信息等

#### 1.1.2 调优层次
- 程序优化
- 硬件提升
- 内存/IO/网络优化
- 操作系统内核参数优化
- 数据库系统优化
- SQL优化
- 业务系统优化

#### 1.1.3 调优层面
- 数据库配置和参数层面
- 数据库设计和结构层面
- 查询和优化层面

---

### 1.2 系统性能调优流程
- 确定调优范围：系统级调优 vs SQL级调优
- 定位问题来源：CPU、IO、内存、网络、线程池等
- 常见问题类型：
  - 并发问题
  - 数据库配置问题
  - 系统资源不足
  - 内核资源使用不优化
  - 不优化SQL

---

### 1.3 性能问题诊断

#### 1.3.1 资源故障
| 类别       | 场景                 | 说明                                                                 |
|------------|----------------------|----------------------------------------------------------------------|
| 硬件故障   | 硬盘、内存等损坏     | 修复或更换硬件，进行节点降级                                         |
| 节点故障   | 节点宕机             | 降级为备用节点，保证系统连续性                                       |
| 网络故障   | 网络中断、带宽不足   | 检查网络连接、设备状态，确保通信正常                                 |

**解决方案**：
- 监控系统性能
- 建立容灾和备份机制
- 检查网络连接和数据库配置

#### 1.3.2 资源过载
| 资源类型 | 常见高消耗场景                                                                 |
|----------|------------------------------------------------------------------------------|
| CPU      | 高并发请求、无限循环或死锁、大量计算密集型任务                                 |
| IO       | 磁盘IO压力大、数据库连接过多、文件操作频繁                                     |
| 内存     | 大对象占用内存、并发请求过多                                                 |
| 网络     | 网络带宽不足、连接数过多、延迟高                                             |

##### 高CPU消耗问题
- 常见场景：
  - 高并发请求（INSERT/COPY、UPSERT）
  - 无限循环或死锁
  - 大量计算密集型任务（union all、join、多级计算）
- 识别工具：
  - `pgxc_stat_activity`
  - `pgxc_wlm_session_statistics`
  - `pgxc_wlm_session_info`
- 解决案例：创建索引减少顺序扫描

##### 高IO消耗问题
- 常见场景：
  - 写IO：导入多索引数据、批量插入、高并发事务
  - 读IO：数据报表生成、频繁聚合查询、脏数据过多
- 识别工具：
  - `pgxc_stat_activity`
  - `pgxc_wlm_session_statistics`
  - `pgxc_wlm_session_info`
- 解决思路：
  - 优化SQL减少物理读
  - 及时Vacuum
  - 调整buffer pool

##### 高内存消耗问题
- 常见场景：
  - 内存估算过高、空闲连接占用内存
  - 大量数据加载和导入
  - 复杂作业（JOIN、聚合）
- 识别工具：
  - `pgxc_total_memory_detail`
  - `pv_session_memory_detail`
  - `pgxc_session_wlmstat`
- 解决思路：
  - 降低并发数
  - 处理内存堆积
  - 调整内存参数

##### 高网络消耗问题
- 常见场景：
  - 大结果集返回
  - STREAM大量数据
- 优化手段：
  - 减少返回数据量
  - 使用游标分批获取
  - 避免笛卡尔积
  - 调整分布列和复制表策略

---

#### 1.3.3 资源管控
| 管控类别   | 管控方法                     | 使用场景                                     |
|------------|------------------------------|----------------------------------------------|
| 内存管控   | 设置资源池内存百分比         | 防止内存不可用或滥用                         |
| CPU管控    | 共享配额、专属限额           | 资源紧张时优先保障重要业务                   |
| 并发管控   | 全局并发管控、资源池并发管控 | 防止资源耗尽、业务间并发影响                 |

---

### 1.4 内核指标体系概况

#### 1.4.1 系统级指标
- **OS**：CPU时间、负载、内存消耗（`dbe_perf.os_runtime`）
- **Instance Time**：DB_TIME、CPU_TIME、EXECUTION_TIME等（`dbe_perf.instance_time`）
- **Memory**：全局/动态/共享内存使用情况（`dbe_perf.memory_node_detail`、`dbe_perf.shared_memory_detail`）
- **Session**：会话负载、内存使用、活跃会话（`dbe_perf.session_stat`、`dbe_perf.session_memory`）
- **Thread**：线程等待状态（`dbe_perf.thread_wait_status`）
- **Events**：IO、LOCK、LWLOCK、STATUS等待事件统计（`dbe_perf.wait_events`）
- **Utility**：通信时延、主备同步、buffer pool状态（`dbe_perf.comm_delay`、`dbe_perf.replication_stat`）

#### 1.4.2 对象级指标
- **Database**：连接数、事务数、块读写（`dbe_perf.stat_database`）
- **Table**：扫描活动、行变更、缓存命中率（`dbe_perf.stat_user_tables`）
- **Index**：索引扫描次数、缓存效率（`dbe_perf.stat_user_indexes`）
- **File**：IO性能、Redo日志性能（`dbe_perf.file_iostat`）
- **Lock**：实时锁信息（`dbe_perf.locks`）
- **Sequence**：缓存效率（`dbe_perf.statio_user_sequences`）

#### 1.4.3 应用级指标
- **Statement**：SQL执行次数、时间模型、排序/HASH性能（`dbe_perf.statement`）
- **Active Session Profile**：活跃会话采样信息（`dbe_perf.local_active_session`、`gs_asp`）
- **Full SQL Trace**：支持三级跟踪（L0~L2），记录执行计划、锁等待等详细信息

#### 1.4.4 核心指标矩阵
| 分类     | Instance          | Session           | Unique Query      | DB/Table/Index    |
|----------|-------------------|-------------------|-------------------|-------------------|
| 时间细分 | INSTANCE_TIME     | SESSION_TIME      | STATEMENT         | -                 |
| 内存     | MEMORY_NODE_DETAIL| SESSION_MEMORY    | -                 | -                 |
| 网络     | COMM_DELAY等      | COMM_DELAY等      | STATEMENT         | -                 |
| IO活动   | -                 | -                 | -                 | FILE_IOSTAT       |
| 行活动   | -                 | SESSION_STAT      | STATEMENT         | STAT_USER_TABLES  |
| 负载     | WORKLOAD_TRANSACTION| SESSION_STAT     | STATEMENT         | STAT_DATABASE     |
| Events   | WAIT_EVENTS       | WAIT_EVENTS       | -                 | -                 |
| Lock     | LOCKS             | LOCKS             | STATEMENT_HISTORY | -                 |

---

## 二、WDR报告详解

### 2.1 WDR报告概述
- **WDR**：Workload Diagnosis Report（负载诊断报告）
- 作用：提供时间段内系统资源使用情况，类似数据库“体检报告”
- 组成：Snapshot（快照） + Report（报告）

### 2.2 Snapshot配置
- 默认关闭，通过`enable_wdr_snapshot`开启
- 默认采集周期：60分钟（`wdr_snapshot_interval`）
- 默认保留时间：8天（`wdr_snapshot_retention_days`）
- 支持手动创建：`create_wdr_snapshot()`

### 2.3 生成WDR报告
```sql
-- 连接postgres库
gsql -d postgres
\a
\t
\o /path/to/report.html
SELECT generate_wdr_report(begin_snap_id, end_snap_id, report_type, report_scope, node_name);
```

### 2.4 报告内容

#### 2.4.1 Summary部分
| 项目                          | 集群级 | 节点级 |
|-------------------------------|--------|--------|
| Database Stat                 | ✓      |        |
| Load Profile                  | ✓      |        |
| Instance Efficiency Percentages | ✓      | ✓      |
| Top 10 Events by Total Wait Time | ✓  |        |
| Wait Classes by Total Wait Time | ✓  |        |
| Host CPU                      | ✓      |        |
| IO Profile                    | ✓      | ✓      |
| Memory Statistics             | ✓      |        |

#### 2.4.2 Detail部分
| 项目                | 集群级 | 节点级 |
|---------------------|--------|--------|
| Time Model          |        | ✓      |
| SQL Statistics      | ✓      | ✓      |
| Wait Events         |        | ✓      |
| Cache IO Stats      | ✓      | ✓      |
| Utility status      |        | ✓      |
| Object stats        | ✓      | ✓      |
| Configuration settings |    | ✓      |
| SQL Detail          | ✓      | ✓      |

### 2.5 WDR分析案例
1. **P80/P95响应时间**：识别慢SQL比例
2. **TOP SQL分析**：按CPU、Elapsed Time等排序定位问题SQL
3. **Buffer命中率低**：调整`shared_buffers`
4. **异常Wait Events**：如`LOGCTL_SLEEP`（流控）需调整`recovery_time_target`
5. **时间模型分析**：`PLAN_TIME`高可能未使用PBE或参数设置不当

---

## 三、SQL优化

### 3.1 SQL调优基础

#### 3.1.1 SQL执行机制
1. **查询解析**：词法/语法解析 → 语义分析 → 查询树
2. **查询优化**：查询重写 → 路径/计划生成
3. **执行**：根据计划树访问数据

#### 3.1.2 SQL执行算子
| 类别       | 算子举例             | 特征                     |
|------------|----------------------|--------------------------|
| 扫描算子   | SeqScan、IndexScan   | 叶子节点、数据输入源     |
| 控制算子   | Limit、Union         | 控制数据流程             |
| 物化算子   | Sort、Agg            | 需缓存所有数据           |
| 连接算子   | HashJoin、NestLoop   | 关联查询                 |

#### 3.1.3 SQL统计信息
- **null_frac**：空值比例
- **n_distinct**：独立值数量
- **most_common_vals**：高频值列表
- **most_common_freqs**：高频值频率
- **histogram_bounds**：等高直方图边界
- 收集命令：`ANALYZE`

#### 3.1.4 SQL执行计划
- **三要素**：访问路径、连接顺序、连接方式
- **查看方式**：
  ```sql
  EXPLAIN [OPTIONS] statement;
  -- 可选选项：ANALYZE、PERFORMANCE、VERBOSE、COSTS、BUFFERS等
  ```

### 3.2 SQL调优方法

#### 3.2.1 内存参数调优
- **shared_buffers**：缓冲池大小，建议≤内存40%
- **work_mem**：控制算子下盘内存，避免频繁写临时文件

#### 3.2.2 算子类参数调优
- `enable_seqscan`、`enable_indexscan`：控制扫描方式
- `enable_hashjoin`、`enable_nestloop`：控制连接方式
- `enable_stream_operator`：控制分布式计划生成

#### 3.2.3 Plan Hint
```sql
SELECT /*+ hint(value) */ * FROM ...;
-- 例如：/*+ tablescan(table_name) */ 强制全表扫描
```

#### 3.2.4 调优思路
1. `EXPLAIN ANALYZE/PERFORMANCE`获取实际执行计划
2. 定位耗时高的算子
3. 检查实际行 vs 估算行偏差（统计信息问题）
4. 考虑算子替代（如NestLoop → HashJoin）
5. 检查内存参数设置
6. 考虑开启SMP并发

### 3.3 分布式SQL调优

#### 3.3.1 执行流程
1. 应用下发SQL给Coordinator
2. Coordinator生成执行计划
3. DN按计划处理数据，可能通过Stream流动
4. DN返回结果给Coordinator汇总
5. Coordinator返回最终结果

#### 3.3.2 分布列选择原则
1. **高基数**：数据均匀分布
2. **避免常量过滤列**：减少倾斜
3. **选择关联/聚合字段**：减少重分布

#### 3.3.3 优化手段
- **算子下推**：将函数或过滤条件下推到DN执行
- **减少数据重分布**：通过复制表或调整分布列
- **数据倾斜调优**：
  - 存储倾斜：调整分布列
  - 计算倾斜：使用RLBT或skew hint

### 3.4 示例与案例
1. **扫描算子不合理**：未使用索引 → 创建索引
2. **连接算子不合理**：NestLoop效率低 → 改用HashJoin
3. **内存参数问题**：work_mem过小导致下盘 → 调大参数
4. **数据倾斜**：存储或计算不均 → 调整分布列或使用RLBT

---

## 四、思考题答案（部分）
1. WDR snapshot默认执行周期：**B. 1小时**
2. WDR snapshot数据保留时间：**D. 8天**
3. Full SQL Trace L2级别记录：**C. Details**
4. 缓存命中率计算：**D. blks_hit/(blks_read + blks_hit)**
5. 内存持续上涨定位视图：**C. session_memory_detail**
6. Event分类：**A. IO event、B. Lock event、C. LWLock event**
7. Plan Hint位置：**B. False**（需在SELECT后立即指定）
8. 禁用seqscan：**C. set enable_seqscan=off**
9. SQL调优方法：**A. 算子类参数调优、B. Plan Hint调优、C. 内存参数调优、D. SMP调优**

---

## 五、缩略语
- **WDR**：Workload Diagnosis Report
- **IO**：Input/Output
- **MPP**：Massively Parallel Processing
- **RLBT**：Runtime Load Balance Technology

FYF

导航1234

最近发表

友情链接