5、GaussDB数据库性能调优

# GaussDB 数据库性能调优笔记

## 一、课程目标

* 掌握 GaussDB 系统性能调优方法与指标体系
* 熟悉 **WDR（Workload Diagnosis Report）报告生成与分析**
* 理解 **SQL 执行机制与优化策略**
* 掌握常见性能瓶颈（CPU、IO、内存、网络、锁）诊断思路

---

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

## 1. 系统性能调优概述

性能调优的目的是：

> 充分利用 **CPU、内存、I/O、网络资源**，避免冲突，提高系统吞吐量。

### 影响性能的主要因素

| 类型   | 示例           |
| ---- | ------------ |
| 硬件   | 服务器、存储、网络    |
| 系统规模 | 并发数、数据量      |
| 软件环境 | OS 内核参数      |
| 数据库  | 参数配置、索引、统计信息 |

> 越靠上（业务级）优化，成本高、效果弱；越靠下（系统级），成本低、效果明显。

---

## 2. 性能调优流程

1. 明确问题范围（系统级 or SQL级）
2. 判断压力是否到数据库层
3. 分析瓶颈来源（CPU / I/O / 内存 / 网络）
4. 定位线程池、并发、SQL问题
5. 对症优化

---

## 3. 系统性能问题分类

| 类别   | 场景           | 说明        |
| ---- | ------------ | --------- |
| 资源故障 | 节点/硬件/网络故障   | 导致业务阻塞、降级 |
| 资源过载 | CPU/IO/内存/网络 | 超过系统容量    |
| 资源管控 | 并发/内存/CPU 限制 | 排队严重，性能下降 |

---

## 4. 资源过载分析与解决方案

### （1）CPU 过载

**症状：** 响应慢、系统崩溃。
**常见场景：**

* 大量并发请求、循环或死锁
* 批量计算密集任务、JOIN/UNION 计算

**分析命令：**

```sql
-- 查看实时数据库活动
SELECT coorname, enqueue, count(*) 
FROM pgxc_stat_activity
WHERE state = 'active'
GROUP BY coorname, enqueue;

-- 查看高并发SQL
SELECT nodename, pid, stream_count 
FROM pgxc_wlm_session_statistics
ORDER BY stream_count DESC;
```

**历史分析：**

```sql
SELECT * 
FROM dbe_perf.get_global_full_sql_by_timestamp('2023-09-09 09:25','2023-09-09 23:54')
ORDER BY cpu_time DESC LIMIT 20;
```

**优化措施：**

* 建立缺失索引
* 减少硬解析（复用 SQL）
* 降低并发流数

---

### （2）IO 过载

**典型场景：**

| 类型  | 说明               |
| --- | ---------------- |
| 写IO | 批量插入、多索引写入、高并发事务 |
| 读IO | 聚合查询、报表扫描、脏数据膨胀  |

**排查方法：**

```sql
-- 查TOP IO SQL
SELECT query, average_peak_iops 
FROM pgxc_wlm_session_statistics 
ORDER BY average_peak_iops DESC LIMIT 50;

-- 物理读高
SELECT query, n_blocks_fetched, n_blocks_hit 
FROM dbe_perf.statement
ORDER BY (n_blocks_fetched-n_blocks_hit) DESC LIMIT 10;
```

**优化建议：**

* 增加索引、定期VACUUM
* 提升 buffer pool 大小
* 优化 IO 调度策略（RAID / async）

---

### （3）内存过载

**常见原因：**

* 内存估算过高
* 并发连接多
* 复杂查询 JOIN / 聚合

**排查命令：**

```sql
-- 查看全局内存使用
SELECT * FROM pgxc_total_memory_detail ORDER BY 1,2;

-- 查看占内存TOP SQL
SELECT query, pg_size_pretty(usedsize) 
FROM pv_session_memory_detail 
ORDER BY usedsize DESC LIMIT 50;
```

**优化措施：**

* 降低并发数
* 调整内存参数（work_mem, maintenance_work_mem）
* 主备切换释放堆积内存

---

### （4）网络过载

**常见问题：**

* 大结果集返回
* STREAM算子数据传输大
* 笛卡尔积 JOIN

**优化手段：**

* 减少返回数据量
* 使用游标分批获取
* 调整分布列或复制表策略

---

### （5）资源管控

| 管控类型 | 参数/机制                          | 说明              |
| ---- | ------------------------------ | --------------- |
| 内存   | 资源池内存百分比控制                     | 限制估算内存>32M的复杂作业 |
| CPU  | 共享/专属配额                        | 控制CPU分配策略       |
| 并发   | `max_active_statements`、资源池并发数 | 防止集群卡顿          |

**等待事件排查：**

```sql
SELECT wait_status, wait_event, count(*)
FROM pg_thread_wait_status
GROUP BY 1,2 ORDER BY 3 DESC;
```

---

# 二、内核指标体系

## 1. 性能指标层级

| 层级  | 指标类别                                             |
| --- | ------------------------------------------------ |
| 系统级 | OS、Instance、Memory、Session、Thread、Event、Utility  |
| 对象级 | Database、Table、Index、File、Lock、Sequence          |
| 应用级 | Statement、Active Session、Slow SQL、Full SQL Trace |

---

## 2. 系统级核心指标

| 模块            | 关键视图                                              | 功能                |
| ------------- | ------------------------------------------------- | ----------------- |
| OS            | `OS_RUNTIME`、`OS_THREADS`                         | CPU/负载/内存监控       |
| Instance Time | `INSTANCE_TIME`                                   | 系统耗时细分            |
| Memory        | `MEMORY_NODE_DETAIL`、`SHARED_MEMORY_DETAIL`       | 内存分配与上下文          |
| Session       | `SESSION_STAT`, `SESSION_MEMORY_DETAIL`           | 活跃会话资源使用          |
| Thread        | `THREAD_WAIT_STATUS`                              | 线程等待与锁            |
| Event         | `WAIT_EVENTS`                                     | IO/LOCK/LWLOCK 统计 |
| Utility       | `COMM_DELAY`, `POOLER_STATUS`, `REPLICATION_STAT` | 通信、复制、连接池状态       |

---

## 3. 对象级指标

* **Database**：`STAT_DATABASE`，事务数、逻辑读、死锁数。
* **Table**：`STAT_USER_TABLES`，扫描次数、缓冲命中率。
* **Index**：`STAT_USER_INDEXES`，索引使用率与返回行。
* **File**：`FILE_IOSTAT`，物理IO性能。
* **Lock**：`LOCKS`，锁等待与关系。

---

## 4. 应用级指标

| 模块             | 视图                                | 说明           |
| -------------- | --------------------------------- | ------------ |
| Statement      | `STATEMENT` / `STATEMENT_HISTORY` | SQL耗时、解析、行活动 |
| Active Session | `LOCAL_ACTIVE_SESSION`, `GS_ASP`  | 活跃会话采样与阻塞分析  |
| Full SQL Trace | 各级别 trace (L0-L2)                 | SQL全链路性能追踪   |

---

## 5. 核心指标矩阵（重点记忆）

| 类型  | 指标                                                      | 说明          |
| --- | ------------------------------------------------------- | ----------- |
| 时间  | `DB_TIME` / `CPU_TIME` / `EXECUTION_TIME` / `PLAN_TIME` | SQL 各阶段耗时   |
| IO  | `FILE_IOSTAT`, `STATIO_USER_TABLES`                     | 读写次数、延迟     |
| 内存  | `MEMORY_NODE_DETAIL`                                    | 动态/共享内存使用   |
| 网络  | `COMM_DELAY` / `COMM_SEND_STREAM`                       | 通信延迟与数据流    |
| 锁   | `LOCKS` / `WAIT_EVENTS`                                 | 锁等待链        |
| 负载  | `WORKLOAD_TRANSACTION`                                  | 事务分布        |
| SQL | `STATEMENT` / `STATEMENT_HISTORY`                       | SQL 分布与性能基线 |

---

# 三、WDR（Workload Diagnosis Report）

## 1. 概述

* 类似 Oracle AWR，用于分析时间段内性能。
* 定期采集性能视图（schema: `dbe_perf`）。
* 默认关闭，通过 `enable_wdr_snapshot` 开启。

**常用参数：**

| 参数                            | 说明        | 默认值  |
| ----------------------------- | --------- | ---- |
| `enable_wdr_snapshot`         | 开启 WDR 快照 | off  |
| `wdr_snapshot_interval`       | 快照间隔      | 60分钟 |
| `wdr_snapshot_retention_days` | 保存周期      | 8天   |

---

## 2. 生成报告

```sql
select generate_wdr_report(begin_snap_id, end_snap_id, report_type, report_scope, node_name);
```

**参数说明：**

| 参数           | 说明                     |
| ------------ | ---------------------- |
| report_type  | summary / detail / all |
| report_scope | cluster / node         |
| node_name    | 节点名称（仅node时需填）         |

---

## 3. 报告主要内容

### Summary 部分

* **Database Stat**：事务量、行活动
* **Load Profile**：SQL数量、CPU占比
* **Instance Efficiency**：缓存命中率
* **Top Wait Events**：按等待时间排序
* **IO Profile**：表/索引 IO 统计
* **Memory Stat**：共享内存使用

### Detail 部分

* **Time Model**：DB/CPU/IO耗时分布
* **SQL Statistics**：TOP SQL
* **Wait Events**：等待次数、时间
* **Object Stats**：表、索引、坏块信息
* **Configuration Settings**：GUC 参数

---

## 4. 常见 WDR 分析案例

### (1) P80/P95 响应时间异常

> Load Profile 中 P80/P95 高说明大量SQL延迟，可针对TOP SQL优化。

### (2) TOP SQL

> CPU Time排序分析，常见原因是顺序扫描 → 缺索引。

### (3) Buffer 命中率低

> `shared_buffers` 设置过小 → 增大参数。

### (4) 异常 Wait Event

> `LOGCTL_SLEEP` 频繁出现 → 流控参数过小或备机回放慢。

### (5) Plan Time 偏高

> 应用未使用 PBE 或 `plan_cache_mode` 强制自定义计划。

---

# 四、SQL 调优

## 1. 基本步骤

1. 收集统计信息（`analyze`）
2. 查看执行计划（`explain / explain analyze`）
3. 调整表结构和索引
4. 重写 SQL
5. Plan Trace 分析执行路径

---

## 2. SQL 执行机制

1. **Parser**：词法+语义解析
2. **Planner**：重写 + 路径生成
3. **Executor**：执行算子计算

**主要算子类型：**

| 算子          | 功能                          |
| ----------- | --------------------------- |
| Scan        | 表或索引扫描                      |
| Control     | 控制数据流（Limit/Union）          |
| Materialize | 临时缓存结果（Agg/Sort）            |
| Join        | HashJoin/MergeJoin/NestLoop |

---

## 3. 统计信息关键字段

| 字段                  | 含义    |
| ------------------- | ----- |
| `null_frac`         | 空值比例  |
| `n_distinct`        | 不同值比例 |
| `most_common_vals`  | 常见值   |
| `most_common_freqs` | 常见值频率 |
| `histogram_bounds`  | 直方图边界 |

---

## 五、考试重点速记

| 模块          | 高频考点                            | 答案  |
| ----------- | ------------------------------- | --- |
| WDR 快照周期    | 默认 1 小时                         | ✅ B |
| WDR 保留时间    | 默认 8 天                          | ✅ D |
| SQL 性能优化顺序  | 收集统计 → Explain → 重写             | ✅   |
| PLAN_TIME 高 | 未用PBE或plan_cache_mode错误         | ✅   |
| Buffer 命中率低 | shared_buffers过小                | ✅   |
| TOP SQL 分析  | SQL ordered by CPU Time         | ✅   |
| IO分析指标      | n_blocks_fetched - n_blocks_hit | ✅   |
| CPU分析指标     | cpu_time, plan_time, hard_parse | ✅   |

FYF

导航1234

最近发表

友情链接