FYF
» HerBert
Toggle navigation
FYF
主页
1、用户
2、各种方案
3、备份恢复
4、表空间相关
5、OGG
6、文件管理
7、常见故障分析
8、DG
9、集群相关
HCCDP
实验题
About Me
归档
标签
五、GaussDB数据库性能调优笔记
无
2025-12-01 22:47:22
2
0
0
admin
[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
上一篇:
二、GaussDB数据库开发设计建议笔记
下一篇:
六、GaussDB数据库日常运维笔记
0
赞
2 人读过
新浪微博
微信
腾讯微博
QQ空间
人人网