5、GaussDB数据库性能调优

# GaussDB 数据库性能调优 - 考试核心要点

## 一、 性能调优概述

### 1. 调优基本概念
* **性能调优**: 一个持续寻找系统瓶颈并解决瓶颈的过程，目的是让系统响应更快、吞吐量更大、资源利用率更高。
* **衡量指标**:
    * **响应时间**: 系统完成一个请求所需的时间。
    * **吞吐量**: 单位时间内系统处理的请求数量。
    * **资源利用率**: 系统资源（CPU、内存、I/O等）的繁忙程度。

### 2. 调优流程
性能调优遵循“自顶向下”的原则，分为四个层次：
1.  **硬件及OS层调优**: 是性能的基础，包括CPU、内存、网络、磁盘的配置和优化。
2.  **数据库层调优**: 对数据库的整体行为进行配置，如内存参数、I/O配置、日志等。
3.  **SQL调优**: 针对具体SQL语句的优化，是调优中最常见、效果最显著的部分。
4.  **应用层调优**: 优化应用程序与数据库的交互逻辑，如减少连接次数、使用绑定变量等。

---

## 二、 硬件及OS层调优

### 1. CPU
* **NUMA架构**: 在多CPU物理机上，CPU访问本地内存比访问远端内存快。
    * **GaussDB优化**: 支持**绑核**，可以将数据库线程绑定在指定的核心上运行，减少跨NUMA节点的内存访问延迟。
    * **绑核参数**: `numa_distribute_mode`，建议设置为`all`。

### 2. 内存
* **主要参数**:
    * `shared_buffers`: 共享缓冲区大小，用于缓存数据页。**是最重要的内存参数**，建议设置为物理内存的**25%~40%**。
    * `work_mem`: 单个算子（如排序、哈希）可使用的内存大小，超过会溢出到磁盘。
    * `maintenance_work_mem`: 维护性操作（如`VACUUM`, `CREATE INDEX`）可用的内存。
* **大页内存 (Huge Pages)**:
    * **优势**: 减少缺页中断，提高内存访问效率，避免内存交换（swapping）。
    * **开启建议**: 物理机部署时，强烈建议开启大页内存。
* **预留内存**: 需要为操作系统和其他进程预留足够内存，避免OOM (Out Of Memory)。

### 3. 网络
* **网卡绑核**: 将网卡中断绑定到特定的CPU核心，避免所有中断集中在一个核心上造成瓶颈。
* **网络参数**: 调整MTU（最大传输单元）为8192，调整网络队列长度等。

### 4. 磁盘I/O
* **I/O调度策略**:
    * **HDD (机械硬盘)**: 推荐使用 `deadline` 策略。
    * **SSD/NVMe (固态硬盘)**: 推荐使用 `noop` 策略。
* **预读**: 操作系统会提前将磁盘数据读入内存，对全表扫描场景友好。`blockdev --setra`命令可调整预读大小，建议设置为`16384`（8MB）。
* **磁盘挂载**: 使用`noatime`和`nodiratime`选项挂载文件系统，避免记录文件访问时间戳带来的不必要I/O。

---

## 三、 数据库层调优

### 1. 数据库日志
* **WAL (Write-Ahead Logging)**:
    * `wal_level`: 控制日志记录的详细程度。`hot_standby`是默认和推荐值。
    * `fsync`: 确保日志写入物理磁盘，**绝对不能关闭**，否则可能导致数据丢失。
    * `synchronous_commit`: 控制事务提交时等待日志刷盘的级别，影响性能和一致性的平衡。
    * `wal_writer_delay`: WAL writer进程的休眠间隔，默认200ms。
* **CLOG (Commit Log)**: 记录事务提交状态。瓶颈通常是CLOG的I/O竞争，GaussDB通过**CLOG分区**和**异步提交**机制进行优化。

### 2. 检查点 (Checkpoint)
* **目的**: 将脏页刷盘，推进Redo点，缩短数据库恢复时间。
* **触发条件**: `checkpoint_timeout` (超时) 和 `checkpoint_segments` (日志段数)。
* **调优**:
    * **增量检查点**: GaussDB**默认开启**，通过后台`PageWriter`线程平滑地刷脏页，避免全量检查点带来的I/O风暴。
    * **关键参数**: `enable_incremental_checkpoint=on`。

### 3. 后台写进程 (BgWriter)
* **作用**: 在后台**增量**地将脏页刷盘，减轻检查点和用户进程的刷盘压力。
* **调优**: 调整`bgwriter_delay`（休眠间隔）、`bgwriter_lru_maxpages`（每次扫描页数）等参数，在“及时刷脏”和“避免过度I/O”之间找到平衡。

---

## 四、 SQL调优

### 1. Explain命令
* **作用**: 显示SQL语句的执行计划，是SQL调优的**最重要工具**。
* **用法**:
    * `EXPLAIN <SQL>`: 显示估算的执行计划和代价。
    * `EXPLAIN ANALYZE <SQL>`: **实际执行**SQL并显示真实的执行时间和行数。
    * `EXPLAIN PERFORMANCE <SQL>`: 显示CN和DN上各算子的详细性能指标。

### 2. 调优方法
* **索引调优**:
    * 为`WHERE`子句、`JOIN`条件、`ORDER BY`子句中的列创建合适的索引。
    * 使用组合索引来满足多列查询条件。
    * **覆盖索引**: 索引包含查询所需的所有列，此时可以使用**Index Only Scan**，避免回表，性能极高。
    * **函数索引/表达式索引**: 对函数或表达式的结果创建索引。
* **SQL改写**:
    * **避免在索引列上使用函数或隐式类型转换**，这会导致索引失效。
    * 用`UNION ALL`代替`UNION`（如果不需要去重）。
    * 用`EXISTS`代替`IN`（特别是子查询结果集很大时）。
    * 在满足业务逻辑的前提下，将`OUTER JOIN`改写为`INNER JOIN`。
* **执行计划调优**:
    * **收集统计信息**: `ANALYZE table_name;`。准确的统计信息是优化器生成好计划的基础。
    * **Plan Hint**: 使用`/*+ ... */`注释强制优化器使用特定的扫描方式、连接方式或连接顺序。
        * `rows`: 调整估算的行数。
        * `scan`: 如`indexscan(t1)`, `seqscan(t2)`。
        * `join`: 如`hashjoin(t1 t2)`。
        * `leading`: 指定连接顺序。
    * **SQL Patch**: 将Plan Hint与特定SQL语句绑定，实现计划固化。

---

## 五、 应用层调优

### 1. 连接管理
* **使用连接池**: 避免频繁创建和销毁数据库连接的开销。
* **长连接 vs. 短连接**: 根据业务场景选择，高并发点查适合短连接，复杂分析适合长连接。

### 2. 结果集处理
* **减少返回数据量**: 只查询必要的列，使用`LIMIT`进行分页。
* **流式获取**: 对于大结果集，使用流式游标（`set cursor_option=1`）逐批获取数据，避免在客户端或数据库端占用大量内存。

### 3. 批量操作
* **批量INSERT**: 使用`COPY`命令或多values的`INSERT`语句，性能远高于单条`INSERT`。
* **批量UPDATE/DELETE**: 使用`CASE`语句或`IN`子句将多个更新/删除合并成一个SQL。

### 4. 绑定变量 (Prepared Statement)
* **作用**: 对SQL模板进行预编译，后续执行时只传输变量，可以**防止SQL注入**，并利用**计划缓存**（Gplan）减少解析和优化开销。
* **适用场景**: 高并发、重复执行的SQL模板。

FYF

导航1234

最近发表

友情链接