KingbaseES数据库性能调优指南.pdf

KingbaseES 数据库性能调优指南 金仓数据库管理系统 KingbaseES 文档版本：V9（V009R001C001B0024） 发布日期：2023 年 10 月 12 日 北京人大金仓信息技术股份有限公司 目 目 录 录 第 1 章 前言 1 1.1 适用读者 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.2 相关文档 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.3 术语 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.4 手册约定 2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 第 2 章 第一部分数据库性能概述 2.1 性能概述 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.1.1 什么是性能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 2.1.2 理解性能指标 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.1.2.1 性能指标模型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 2.1.2.2 数据库时间模型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 理解可扩展性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1.3.1 什么是可扩展性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 2.1.3.2 系统可扩展性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.1.3.3 影响可扩展性的因素 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.1.4 性能问题产生模型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 2.1.5 如何管理性能 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.1.5.1 被动性能管理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.1.5.2 主动性能管理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 新系统性能规划（仅供参考） . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.2.1 整体过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.2.2 系统体系架构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 硬件组件和软件组件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.2.2.1.1 硬件组件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 2.2.2.1.2 软件组件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 根据需要合理配置系统结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 应用程序设计原则 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.2.3.1 应用程序设计的简单性 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.2.3.2 数据建模 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.2.3.3 表和索引的设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 2.1.3 2.2 4 2.2.2.1 2.2.2.2 2.2.3 I 目 录 2.2.3.3.1 将列追加到索引 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 2.2.3.3.2 使用其他索引类型 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 2.2.3.3.3 查找索引的成本 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.2.3.3.4 对索引中的列排序 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.2.3.4 使用视图 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.2.3.5 SQL 执行效率 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 2.2.3.6 应用开发趋势 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 工作负载测试，建模及实施 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 2.2.4.1 确定数据大小 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 2.2.4.2 估计负载量 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 2.2.4.3 应用建模 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 2.2.4.4 测试，调试，验证设计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 性能问题调优 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 2.3.1 整体过程 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 2.3.2 定义性能目标 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 2.3.3 性能诊断，定位资源瓶颈 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 2.3.4 性能诊断，定位优化点 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 2.3.5 性能调优，解决性能问题 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 2.3.6 性能诊断与优化经验 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 2.2.4 2.3 第 3 章 第二部分性能诊断 29 3.1 性能诊断方法 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.2 操作系统资源分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 CPU 瓶颈分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 3.2.1.1 CPU 信息简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 3.2.1.2 CPU 资源分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 内存瓶颈分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.2.2.1 内存信息简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 3.2.2.2 内存资源分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 I/O 瓶颈分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.2.3.1 IO 信息简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 3.2.3.2 IO 资源分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 网络瓶颈分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 3.2.4.1 网络信息简介 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 3.2.4.2 网络资源分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 数据库资源分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 CPU 优化点分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 3.3.1.1 使用 sys_stat_statement 工具 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 3.3.1.2 使用 kbbadger 工具 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 等待事件分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 3.3.2.1 活跃会话连接 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 3.3.2.2 等待事件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 3.2.1 3.2.2 3.2.3 3.2.4 3.3 3.3.1 3.3.2 II 目 录 I/O 优化点分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 3.3.3.1 共享内存命中率分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 3.3.3.2 私有内存使用分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 3.3.4 锁优化点分析 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 3.3.5 自动负载信息库 SYS_KWR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 3.3.5.1 SYS_KWR 插件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 3.3.5.2 基本原理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 3.3.5.3 快速生成报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70 3.3.5.4 KWR 价值 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 3.3.5.5 使用 SYS_KWR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 3.3.5.5.1 配置 GUC 参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 3.3.5.5.2 快照查询 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 3.3.5.5.3 自动快照 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 手动快照 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 3.3.5.6.1 快照管理 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 3.3.5.6.2 生成 KWR 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 3.3.5.6.3 常见问题 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 查看 SYS_KWR 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 3.3.5.7.1 报告结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 3.3.5.7.2 查看报告头 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79 3.3.5.7.3 分析数据库时间 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 3.3.5.7.4 查看报告摘要 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 3.3.5.7.5 负载性能表 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 3.3.5.7.6 实例效率百分比 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 3.3.5.7.7 Top 10 前台等待事件 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 3.3.5.7.8 Top 10 前台等待事件分类 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 3.3.5.7.9 主机 CPU 使用统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 3.3.5.7.10 主机 IO 使用统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 3.3.5.7.11 主机内存使用统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 3.3.5.7.12 主机网络使用统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 3.3.5.7.13 数据实例 IO profile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 3.3.5.7.14 内存统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 3.3.5.7.15 SQL 语句统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 3.3.5.7.16 主机 CPU 详细统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 3.3.5.7.17 时间模型统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 3.3.5.7.18 前后台等待事件统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 3.3.5.7.19 数据库执行时间 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 3.3.5.7.20 SQL 报文执行时间 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 3.3.5.7.21 内存统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 3.3.5.7.22 实例 IO 统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96 3.3.5.7.23 锁活动统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 3.3.3 3.3.5.6 3.3.5.7 3.3.5.7.24 关键实例活动统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 III 目 3.3.5.7.25 TOP SQL 录 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 3.3.5.7.26 检查点和后台写统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 3.3.5.7.27 数据库统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 3.3.5.7.28 数据库对象统计 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107 3.3.5.7.29 快照期间配置参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 3.3.5.7.30 KSH 报告主体 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 3.3.6 3.3.7 3.3.8 活跃会话历史报告 SYS_KSH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 3.3.6.1 SYS_KSH 的配置 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111 3.3.6.2 SYS_KSH 采集数据说明 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 3.3.6.3 使用 SYS_KSH . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 3.3.6.4 查看 SYS_KSH 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 3.3.6.5 KWR 和 KSH 比较 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 自动诊断和建议报告 SYS_KDDM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 3.3.7.1 快速生成 KDDM 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 3.3.7.2 使用 KDDM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 3.3.7.2.1 配置 GUC 参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 3.3.7.2.2 生成 KDDM 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 3.3.7.3 KDDM 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 3.3.7.4 GUC 参数建议 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 KWR 运行期对比报告 KWR DIFF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128 3.3.8.1 快速生成 Diff 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128 3.3.8.2 GUC 参数说明 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 3.3.8.3 用户接口说明 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 3.3.8.4 查看 Diff 报告 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 3.3.8.4.1 报告结构 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 3.3.8.4.2 报告内容 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 第 4 章 第三部分性能优化 132 4.1 性能优化方法 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 4.2 CPU 资源优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 4.2.1 CPU 常用优化手段 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 4.2.2 CPU 优化参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 4.2.2.1 4.3 bindcpulist . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 IO 资源优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 4.3.1 优化数据库内存参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 4.3.1.1 4.3.1.2 4.3.2 共享内存参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 4.3.1.1.1 shared_buffers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 4.3.1.1.2 wal_buffers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 私有内存区参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 4.3.1.2.1 work_mem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 4.3.1.2.2 maintenance_work_mem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 优化数据库 I/O 相关参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 IV 目 4.4 4.5 录 4.3.2.1 fsync . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 4.3.2.2 full_page_writes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 4.3.2.3 commit_delay . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 4.3.2.4 commit_siblings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 4.3.2.5 checkpoint_timeout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 4.3.2.6 bgwriter_delay . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 4.3.2.7 bgwriter_lru_maxpages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 4.3.2.8 bgwriter_lru_multiplier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 4.3.3 调整 IO 调度策略 4.3.4 多 IO 设备分担压力 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 4.3.5 优化文件系统挂载 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 4.3.6 配置预读和 IO 请求队列 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 网络资源优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 4.4.1 网络常用优化手段 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 4.4.2 网络优化相关参数 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 4.4.2.1 enable_autocommit_fetch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 4.4.2.2 enable_autocommit_crossquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 数据库等待资源、锁资源优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 4.5.1 开启 XLogInsertLockFree 优化 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 版权声明 142 服务周期承诺 143 V 第 1 章 前言 1 第 章 前言 本文档描述了数据库性能概述，性能诊断方法、资源分析，以及性能优化的方向。 前言部分包含以下主题： • 适用读者 • 相关文档 • 术语 • 手册约定 1.1 适用读者 KingbaseES 数据库性能调优指南面向所有使用 KingbaseES 的用户，主要是数据库管理员和应用程序开发人员。 1.2 相关文档 有关 KingbaseES 数据库性能调优指南的更多信息，请参阅以下资源： • KingbaseES 数据库概念，全面介绍本手册中使用的概念和术语 • 数据库管理员指南，了解有关管理 KingbaseES 数据库的信息 • SQL 语言，用于了解 KingbaseES sql 命令和函数的信息 • 数据库管理员指南，了解有关管理 KingbaseES 数据库的信息 • KingbaseES 插件参考手册 kwr，ksh，kddm 章节 1.3 术语 1 第 1 章 前言 术语 定义 KingbaseES 人大金仓企业级版本，本文指代单机服务版本，也被称作单机版或者 KingbaseES 单机版，单机版其成员可能包括数据节点（Data Node）、备份节点 （Repo Node）。 SYS_KWR SYS_KWR 是 KingbaseES 自动负载信息库（Kingbase Auto Workload Repertories）的简称，它通过周期性自动记录性能统计相关的快照，分析出 KingbaseES 的操作系统运行环境、数据库时间组成、等待事件和 TOPSQL 等性能指标，为 数据库性能调优提供指导。 SYS_KSH KingbaseES 在 V9 中引入了明细会话历史 (Kingbase Session History) 和相应的 报告工具。用户可以使用该工具进行会话历史的分析，并针对报告呈现的性能瓶 颈进行优化。 SYS_KDDM KDDM 是 KingbaseES 性能自动诊断和建议的报告。它基于 KWR 快照采集的 性能指标和数据库时间模型（DB Time），自动分析等待事件、IO、网络、内存 和 SQL 执行时间等，给出一系列性能优化建议。 1.4 手册约定 本文档中可能出现“注意、提示、警告、另请参阅”等标志，它们所代表的含义如下： 注意: 用于突出重要/关键信息、最佳实践等。 提示: 用于突出小窍门、捷径等。 警告: 用于传递设备或环境安全警示信息，若不避免，可能会导致设备损坏、数据丢失、设备性能降低或其 它不可预知的结果。 另请参阅: 用于突出参考、参阅等。 以下程序代码书写约定适用于本文档： 2 第 1 章 前言 符号 说明 [] 表示包含一个或多个可选项。不需要输入中括号本身。 {} 表示包含两个以上（含两个）的候选，必须在其中选取一个。不需要输入花括号本身。 | 分割中括号或者花括号中的两个或两个以上选项。不需要输入“|”本身。 ... 表示其之前的元素可以被重复。 斜体 表示占位符或者需要提供特定值的变量。 大写 表示系统提供的元素，以便与用户定义的元素相互区分。除出现在方括号中的元素外，应当按 照顺序逐字输入。当然，部分元素在系统中是大小写不敏感的，因此用户可以根据系统说明以 小写形式输入。 小写 表示由用户提供的元素。 3 第 2 章 第一部分数据库性能概述 2 第 章 第一部分数据库性能概述 随着互联网技术在商业应用中的广泛普及，数据库应用日益庞大复杂，随之数据库性能表现的重要性也越发凸 显。凭借多年的性能调优经验，KingbaseES 总结出了一套数据库性能方法论供读者使用。 本部分将包含以下主题： • 性能概述 ：性能的理论基础 • 新系统性能规划（仅供参考） ：新系统性能应该如何设计。 • 性能问题调优 ：现有系统的性能问题如何调整。 2.1 性能概述 本章节包含以下内容： • 什么是性能 • 理解性能指标 • 理解可扩展性 • 性能问题产生模型 • 如何管理性能 2.1.1 什么是性能 性能是一种指标，表明软件系统对于其及时性要求的复合程度。及时性用响应时间或者吞吐量来衡量。响应时间 是对请求做出响应所需要的时间。对于单个事务可以是事务完成所需要的时间；对于用户任务，则是端对端的时间。 例如，一个在线系统要求在用户按下回车键后的 0.5 秒内产生结果。系统吞吐量是指特定时间内能够处理的请求数 量。例如，对电话交换机的要求则是每秒钟能够处理 1000000 次呼叫。 软件性能的及时性包括两个重要方面：响应性和可扩展性。 • 响应性是系统实现其响应时间或者吞吐量目标的能力 4 第 2 章 第一部分数据库性能概述 • 可扩展性则是系统在对其软件功能的要求增加的情况下，继续实现其响应时间或吞吐量目标的能力 注意: 参考《软件性能工程》定义。 2.1.2 理解性能指标 2.1.2.1 性能指标模型 数据库请求响应时间：即一个数据库请求从向数据库发起，到用户收到最后一条结果数据间的时间间隔，其大小 = CPU 计算时间 + 非空闲等待时间（如 IO 时间、数据库锁时间）。 并发吞吐量：即单位时间内，数据库能够处理的请求数量。该指标与前一指标的关系是：假设系统中只有一个会 话，则只要该会话持续发起请求，即请求之间没有任何时间间隔，系统即可以达到其最大并发吞吐量 = 1 / 数据库请 求响应时间。相应的，假设系统中有 N 个会话，则系统的理论上最大并发吞吐量 = N / 数据库请求响应时间——这 里达到最大吞吐量的前提是：多个会话之间没有任何相互等待；系统的各种硬件资源均能够支撑这 N 个会话的资源 需求，即最大吞吐量 = min（各类资源的处理能力 / 单个负载请求对本资源的处理能力需求）。 注意: 这里的资源包括：CPU、内存、磁盘等存储设备、网络设备等硬件资源，数据库锁、应用锁等软件资源。 2.1.2.2 数据库时间模型 性能优化的第一步是确定性能问题的根本原因，然后才有可能给出有效的调优建议来解决或缓解该问题。 很显然，在进行性能问题诊断时，如果我们要找出用户请求中的问题，自上而下的分析肯定是最容易理解的。根 据上文中对数据库请求响应时间、并发吞吐量的指标概念及相互关系的分析，我们可以看出，在用户关心的性能指标 方面，数据库请求响应时间可以作为一个基础度量，进而用以推导出吞吐量等数据。 因此，我们在数据库中引入时间模型来解决该问题。时间模型使用时间来识别数据库的各种操作对性能的量化影 响。时间模型中最重要的就是数据库时间（DB TIME），DB TIME 定义为在数据库中处理用户请求所花费的时间总 和。这里我们给出数据库时间模型的量化公式： 数据库时间 = CPU+ 等待时间 数据库调优的根本目标则是缩短数据库时间：CPU 或等待时间，随之吞吐量也自然提高。 注意: 数据库时间不等于响应时间，它只是用户感知到的响应时间的一部分，因为它不包含中间层（网络、中间件 等）花费的时间。 5 第 2 章 第一部分数据库性能概述 2.1.3 理解可扩展性 “可扩展性”一词在开发环境中被广泛使用。本节内容为应用设计者以及性能调优专家提供了一种“可扩展性” 的新定义。本节包含以下内容： • 什么是可扩展性 • 系统可扩展性 • 影响可扩展性的因素 2.1.3.1 什么是可扩展性 可扩展性是当系统资源使用量成倍增加，系统处理更多负载的能力。在可扩展性系统中，如果将负载翻倍，那么 使用的系统资源也应变为原来的两倍。这看上去理所应当，但由于系统内部的各种冲突，实际资源使用情况很可能超 过原来的两倍。 下面列举出系统资源冲突导致可扩展性差的几个例子： • 由于用户数量的增长，应用要求出色的并发处理能力 • 锁活动的增加 • 数据一致性负载的增加 • 操作系统负载的增加 6 第 2 章 第一部分数据库性能概述 • 由于数据规模的增大，对于数据的访问增加 • SQL 或索引的拙劣设计使得大量 I/O 增加 如果一个应用程序耗尽了系统资源，以至于当它的工作负载增加时不可能有更多的吞吐量，那么它就是不可扩展 的。这样的应用程序必然导致不可增加的吞吐量和较差的响应时间。 下面列举出系统资源耗尽的几个例子： • 硬件资源的耗尽 • 大规模的表扫描导致不可避免的磁盘 I/O 短缺 • 过多的网络请求，导致网络瓶颈 • 内存分配过低导致分页和交换 • 过多的进程和线程分配导致操作系统崩溃 这意味着应用程序设计人员必须创建一个通用的资源设计，无论用户数量和数据量如何都不会使系统资源上的负 载超出其限制。 2.1.3.2 系统可扩展性 可通过互联网访问的应用程序具有更复杂的性能和可扩展性要求。有些应用程序是专为互联网使用而设计和编写 的，但即使是典型的后台应用程序（如总账应用程序）也可能需要部分或全部数据才能在线使用。 互联网时代应用的特点包括： • 需要一天 24 小时，一年 365 天持续在线 • 不可预测且不精确的并发用户数 • 容量规划的困难 • 提供任何类型查询的可用性 • 多层体系结构 • 无状态中间件 • 快速增长的时间规模 • 较短的测试时间 下图显示了典型的工作负载增长曲线，需求以不断增长的速度增长。应用程序必须随着工作负载的增加而扩展， 并且在添加额外的硬件时，也要提供相应支持。无论拥有多少额外的硬件资源或重新设计，设计错误都可能导致曲线 达到其最大值。 7 第 2 章 第一部分数据库性能概述 应用程序的开发时间非常短，测试和评估时间有限。然而，糟糕的设计通常意味着必须稍后重新设计并重新实现 系统。如果在互联网上部署一个具有已知体系结构和实现限制的应用程序，并且如果工作负载超过预期的需求，那么 很有可能会失败。从商业角度来看，业绩不佳可能意味着失去客户。根据已有调查显示，如果互联网用户在 7 秒钟内 没有得到响应，那么用户的注意力可能永远消失。 在许多情况下，重新设计系统的成本以及迁移到新实现时的相关停机成本超过了正确构建原始系统的成本。该例 子的寓意很简单：设计和实现从一开始就应该考虑到可伸缩性。 2.1.3.3 影响可扩展性的因素 在构建应用程序时，设计师和架构师应该尽可能地追求接近完美的可扩展性。这有时被称为线性可扩展性，即系 统吞吐量与 CPU 数量成正比。 在实际操作中，完美的线性可扩展性实现是不可能的，因为超出了设计者的控制范围。但是，应该尽可能使应用 程序的设计和实现具有可扩展性，确保当前和未来的性能目标可以通过扩展硬件组件和改进 CPU 技术来实现。 影响线性可扩展性的因素包括： • 糟糕的应用程序设计、实现和配置 应用程序对可扩展性的影响最大，例如： – 糟糕的模式设计会导致重要的 SQL 语句无法扩展 – 糟糕的事务设计会导致锁和序列化问题 – 连接管理不善会导致响应时间增加和系统不可靠 8 第 2 章 第一部分数据库性能概述 • 设计不是唯一问题。应用的物理实现同样可能是薄弱环节。 例如： – 不良 I/O 策略的生产环境 – 生产环境与测试环境中生成的执行计划不同。 – 内存密集型应用程序分配大量内存而不考虑在运行时释放内存，这可能会导致内存过度使用。 – 低效率的内存使用和内存泄漏给正在运行的虚拟内存子系统带来了很大的压力。这会影响性能和可用性。 – 不正确的硬件组件大小 随着硬件价格的相对下降，硬件组件的不良容量规划问题变得不那么严重。但 是，当系统的工作负载增加时，过多的容量会掩盖可伸缩性问题。 – 软件组件的限制 所有软件组件都有可扩展性和资源使用限制。这适用于应用服务器、数据库服务器和操 作系统。应用程序设计不应该对软件提出超出其处理能力的要求。 – 硬件组件的限制 硬件不是完全可扩展的。大多数多处理器计算机可以在有限数量的 CPU 上接近线性扩 展，但是在某一点之后，每个额外的 CPU 可以提高整体性能，但不是成比例的。可能会有一段时间， 额外的 CPU 不会提高性能，甚至会降低性能。此行为与工作负载和操作系统设置密切相关。 2.1.4 性能问题产生模型 性能问题是由于某些系统资源争用或耗尽的结果。当系统资源耗尽时，系统无法扩展到更高的性能级别。 而资源争用或不足的根本原因，是对资源的使用不当，或资源的处理能力已经无法满足系统的负载需要。 数据库需要的软硬件资源包括：CPU、IO、内存、网络、锁等。这些资源在达到处理能力上限的时候，通常会 产生性能问题。常见的资源瓶颈现象可能包括： • CPU 利用率接近 100% • IO 利用率接近 100% • 内存不够，开始写 swap • 网络流量达到带宽上限、网络传输存在大量错误 • 数据库内部有大量的时间在等待 注意: 在进行性能调优时，DBA 主要的目标是找出并优化对资源的不当使用；如果没有不当使用，则说明资源的处 理能力已经无法满足系统的负载需要，此时应给出资源需求估计。 9 第 2 章 第一部分数据库性能概述 2.1.5 如何管理性能 2.1.5.1 被动性能管理 下面的说法中有没有听起来比较熟悉？ • “把它构建起来，看它做的怎么样” • “我们以后会调整，现在没时间考虑性能” • “在没有能测量的东西之前，做不了任何有关性能的事” • “性能？那是发布版本 2 时要做的事情” 如果有，那么你很有可能是在被动的进行软件性能管理。被动的性能管理等待性能问题出现，然后响应式的解 决，可能带来超支、不能按期交付、项目失败等潜在的风险。 有关性能问题的解决，参见性能问题调优 。 2.1.5.2 主动性能管理 主动性能管理可以预见潜在的性能问题，并在开发早期纳入有关技术。主动性能管理应该遍布开发和生产过程。 主动性能管理的全过程较多，本文仅对新系统的主动性能管理做介绍，有关内容参见新系统性能规划（仅供参 考） 。 2.2 新系统性能规划（仅供参考） 最优的系统性能从设计开始，并贯穿于系统的整个生命周期。在初始设计阶段仔细考虑性能问题，以便在生产期 间更容易地调优系统。本章涵盖以下小节： • 整体过程 • 系统体系架构 • 应用程序设计原则 • 工作负载测试，建模及实施 2.2.1 整体过程 KingbaseES 新系统性能规划的一般情况下的整体过程如下： 1) 定义应用负载特征、性能目标 如数据规模、用户并发规模、应用操作特点、响应时间和吞吐量等。 2) 根据启发式原则，确定软件部署架构 10 第 2 章 第一部分数据库性能概述 根据同类产品的启发式原则经验，来确定软件部署架构，可能的架构包括但不限于： • 应用、数据库等资源在单一服务器 • 应用服务和数据服务分离 • 使用缓存改善性能 • 使用应用服务器集群 • 使用数据库读写分离 • 使用分布式数据库 • 使用 nosql 和搜索引擎 • 业务拆分 3) 根据负载特征、系统架构，估算硬件资源需求 参见系统体系架构 4) 设计和开发应用 参见应用程序设计原则 5) 用模拟负载，或实际负载，测量实际资源耗用 参见工作负载测试，建模及实施 6) 确定步骤 1 提出的目标是否达成，如果没有达成，重复 3，4，5 步骤 2.2.2 系统体系架构 系统体系架构主要包含两个部分： • 硬件和软件 • 根据需要合理配置系统架构 2.2.2.1 硬件组件和软件组件 系统结构主要包含硬件组件和软件组件： • 硬件组件 • 软件组件 2.2.2.1.1 硬件组件 当今的设计师和架构师需要负责多层环境中每一层硬件的规模和容量规划。架构师的职责是实现一个平衡性设 计。这类似于桥梁设计师，必须考虑桥梁的各种有效载荷和结构要求。一座桥的坚固程度取决于最薄弱的部分。因 此，桥梁的设计是平衡的，以便所有组件同时达到其设计极限。 11 第 2 章 第一部分数据库性能概述 以下是系统的主要硬件组件。 • CPU：计算机系统的运算和控制核心，CPU 主要关注的因素包括： – 架构：常见的包括 X86（英特尔、AMD、海光、兆芯等）、MIPS（龙芯）、ARM（飞腾、麒麟 等）、Alpha（神威） – 同等架构和工艺情况下，核心数越多，扩展性越好 – 同等架构和工艺情况下，主频越高，性能越好 注意: 架构会带来效率上的差异，同等核心数量情况下，一般来说 X86 性能 > ARM 性能 > MIPS 性能。另外，核心数越多，能够支撑的并发连接越多，吞吐量也就越大。 • 内存：数据库和应用服务器需要大量内存来缓存数据并避免耗时的磁盘访问。 – 同等主频情况下，容量越高越好。 • I/O 子系统：I/O 子系统在不同的硬件上表现不同。影响硬盘性能的因素包括： – 硬盘类型： 常见的硬盘可以分为 2 大类，机械硬盘和固态硬盘。相比机械硬盘，固态硬盘的寻址时间较短，能够带来 更高的 IOPS 和 IO 带宽。 – 硬盘转速： 机械硬盘通过转动磁头进行寻址，所以转速越快则 IOPS 越高，常见转速包括 5400、7200、15000。 – 硬盘个数： 使用磁盘整列或者人为控制多个硬盘资源，可以带来 IO 上的线性提升，个数越多，能够提供的 IOPS 越 大。 • 网络：系统中的所有计算机都连接到网络，从调制解调器线路连接到高速内部局域网。网络的主要关注因素是 带宽（容量）和延迟（速度）。 2.2.2.1.2 软件组件 就像计算机有相同的硬件组件一样，应用程序也有共同的功能组件。通过将软件开发划分为功能组件，可以更好 地理解应用程序设计和体系结构。系统的某些组件由现有软件执行，以加速应用实现，或者避免共同组件的重新开 发。 软件组件和硬件组件的区别在于，一个硬件组件只执行一个任务，一个软件却可以执行各种软件组件的角色。例 如，磁盘驱动器只存储和检索数据，但客户端程序既可以管理用户界面又可以执行业务逻辑。 大部分应用包含以下几个软件组件： • 用户界面： 该组件是用户最直接看到的，主要包含： 12 第 2 章 第一部分数据库性能概述 – 向用户展示界面 – 收集用户信息并传递给业务逻辑部分 – 允许数据入口 – 控制应用程序的状态或级别 • 业务逻辑： 该组件实现对应用程序功能至关重要的核心业务规则。在这个组件中发生的错误修复起来代价会非常 高。这个组件是通过声明性方法和过程性方法实现的。声明性方法的一个例子是定义唯一键和外键。 过程性方法的一个例子是实现折扣策略。主要包含： – 将数据模型移动到关系表结构 – 在关系表结构中定义约束 – 编写过程逻辑以实现业务规则 • 管理用户请求的资源： 这个组件是在所有软件中实现的。但是，有些请求和资源可能会受到应用程序设计的影响，有些则不 会。在多用户应用程序中，大多数由用户请求分配的资源由数据库服务器或操作系统处理。然而，在 用户数量和使用模式未知或增长迅速的大型应用程序中，系统架构师必须主动确保没有单个软件组件 变得过载和不稳定。主要包含： – 数据库的连接管理 – 高效执行 SQL（游标和 SQL 共享） – 管理客户端状态信息 – 在硬件资源之间平衡用户请求的负载 – 为硬件和软件组件设置操作目标 – 异步执行任务的持久队列 • 数据和事务： 该组件对于数据库服务器和操作系统至关重要，主要包含： – 使用锁和事务语义提供对数据的并发访问 – 使用索引和内存缓存提供对数据的优化访问 – 确保在发生硬件故障时记录数据更改 – 强制执行为数据定义的任何规则 2.2.2.2 根据需要合理配置系统结构 配置初始系统架构很大程度上是一个反复的过程。系统架构师必须在预算和进度限制内满足系统需求。如果系统 要求交互用户根据数据库的内容处理业务决策，那么该体系结构由用户需求驱动。如果系统上很少有交互用户，那么 13 第 2 章 第一部分数据库性能概述 体系结构是进程驱动的。 用户交互式应用举例： • 会计和簿记应用程序 • 订单输入系统 • 电子邮件服务器 • 基于 Web 的零售应用程序 • 交易系统 进程驱动式应用举例： • 公用事业计费系统 • 欺诈检测系统 • 直邮系统 在许多方面，进程驱动的应用程序比多用户应用程序更容易设计，因为用户界面元素被消除了。然而，由于目标 是面向过程的，系统架构师不习惯处理大数据量和不同的继承因素可能会造成混乱。进程驱动的应用程序从基于用户 的应用程序和数据仓库中使用的方法集中构建。因此，本手册的重点是为交互式用户应用设计系统架构。 注意: 生成系统架构不是一个确定的过程。需要仔细考虑业务需求、技术选择、现有基础设施和系统，以及实际的物 理资源，如预算和人力。 以下问题应该会激发对系统架构的思考，尽管它们不是系统架构的明确指南。这些问题演示了业务需求如何影响 系统的体系结构、易实现性以及总体性能和可用性。例如： • 系统必须支持的用户数量为多少？ 大部分应用都设计成了下面某一种类别： – 很少的用户或者专用计算机 对于这种类型的应用程序，通常只有一个用户。应用程序设计的重点是通过提供良好的响应时间使单 个用户尽可能高效，同时使应用程序只需要最少的管理。这些应用程序的用户很少相互干扰，资源冲 突也很少。 – 中等规模或者大规模用户共享应用 对于这种类型的应用程序，用户受通过系统实际处理业务的公司员工数量的限制。因此，用户数量是 可以预测的。然而，提供可靠的服务对业务至关重要。用户必须共享资源，因此设计工作必须解决系 统负载较重时的响应时间、每次会话使用的资源升级以及未来增长的空间。 – 互联网上大量分布式用户 对于这种类型的应用，需要额外的工作来确保没有系统组件超过其设计限制。这就造成了一个瓶颈， 阻碍或破坏了系统的稳定。这些应用程序需要复杂的负载平衡、无状态应用服务器和高效的数据库连 14 第 2 章 第一部分数据库性能概述 接管理。此外，如果数据库由于系统过载而无法满足用户的请求，则使用统计信息和调控器（governor）来确保用户收到反馈。 • 用户交互的方法是什么？ 用户界面选择范围从简单的 Web 浏览器到自定义的客户端程序。 • 用户的分布是如何的？ 用户之间的距离会影响应用程序如何处理网络延迟。当无法执行批处理或系统维护功能时，用户分布 还会影响一天中高峰期的出现时间。 • 网络速度是什么？ 网络速度影响数据量和用户界面与应用程序和数据库服务器的对话性质。一个高度对话的用户界面可 以在每一次键盘键入或字段级验证时与后端服务器通信。一个较少对话的界面在屏幕发送和屏幕接收 模型上工作。在慢速网络上，用一个高度对话的用户界面是不可能达到高数据输入速度的。 • 用户需要获取的数据量为多少，有多少数据是只读的？ 在线查询的数据量影响设计的各个方面，从表、索引设计到表示层。设计工作必须确保用户响应时间 不依赖于数据库的大小。如果应用程序基本上是只读的，那么复制和数据分发到应用服务器中的本地 缓存就成为一个可行的选择。这也减少了核心事务服务器上的工作负载。 • 用户响应时间的要求是什么？ 考虑用户类型很重要。如果用户是一名执行者，他需要准确的信息来做出瞬间的决定，此时就需要很 高的性能。其他类型的用户（例如执行数据输入活动的用户）可能不需要如此高的性能。 • 用户需要 24 小时服务吗？ 这对于如今的互联网应用程序是强制性的要求，在这些应用程序中，交易是 24 小时进行的。然而， 在单一时区运行的公司系统可能能够容忍下班后的停机时间。可以在下班后使用此停机时间来运行批 处理进程或执行系统管理。在这种情况下，不运行完全可用的系统可能更经济高效。 • 所有的变更都要实时生效吗？ 确定事务是否必须在用户响应时间内执行非常重要，或者是否可以排队等待异步执行。 以下是一些次要考虑的问题，这些问题也会影响设计，但实际上对预算和实施的方便性有更大的影响。例如： • 数据库需要设计多大？ 这会影响数据库服务器的大小。在数据库非常大的服务器上，可能需要有一台比工作负载所要求的 更大的计算机。这是因为大型数据库的管理开销很大程度上依赖于数据库的大小。随着表和索引的增 长，要在可接受的时间限制内完成表重组和索引构建，需要相应地增加 CPU。 – 业务事务需要的吞吐量是多少？ – 可用性的要求是什么？ – 是否存在建立和管理应用程序的技术？ – 由于预算限制需要做出哪些妥协？ 15 第 2 章 第一部分数据库性能概述 2.2.3 应用程序设计原则 该部分描述了构建应用时可考虑的设计决策： • 应用程序设计的简单性 • 数据建模 • 表和索引的设计 • 使用视图 • SQL 执行效率 • 实现应用程序 • 应用开发趋势 2.2.3.1 应用程序设计的简单性 应用程序与任何其他设计和工程产品没有区别。一般来说，如果设计看起来是正确的，那么它很可能是正确的。 在构建应用程序时，应始终牢记这一原则。在设计时应考虑以下几点： • 如果表的设计很复杂，以至于没有人能完全理解它，那么表可能设计得很差。 • 如果 sql 语句太长，涉及的范围太大，任何优化器都不可能实时有效地优化它，那么可能存在一个错误的语句、 底层事务或表设计。 • 如果表上有索引，并且相同列被重复索引，那么可能是索引设计不好。 • 如果提交的查询没有提供给在线用户及时快速的响应，那么可能存在不良的用户界面或事务设计。 • 如果对数据库的调用被许多软件层从应用程序逻辑中抽象出来，那么可能存在一种糟糕的软件开发方法。 2.2.3.2 数据建模 数据建模对于成功的关系应用程序设计非常重要。必须以快速表示业务实践的方式执行此模型。关于正确的数据 模型可能会发生激烈的争论。重要的是对那些被最频繁的业务事务影响的实体应用进行建模。在建模阶段，有一个巨 大的误区，就是花太多的时间对非核心数据元素建模，这会导致开发提前期时间的增加。使用建模工具可以快速生成 模式定义，并且在需要快速原型时非常有用。 2.2.3.3 表和索引的设计 表设计在很大程度上是核心事务的灵活性和性能之间的折衷。为了保持数据库的灵活性并且能够适应未知的工作 负载，表设计应该与数据模型非常相似，并且应该将其至少规范化为第三范式。但是，用户所需的某些核心事务可能 需要出于性能目的的选择性非规范化。 16 第 2 章 第一部分数据库性能概述 同样，应该将重点和资源花在业务关键表上，以便实现最佳性能。对于非关键表，可以采用设计中的快捷方式来 实现更快速的应用程序开发。但是，如果非核心表的原型制作和测试成为一个性能问题，那么应该立即进行补救设计 工作。 索引设计很大程度上也是一个迭代的过程。可以从构建索引开始，在已知的访问对象（如人名）上添加主键约束 和索引。随着应用程序的发展，以及在实际数据量上执行测试，可能需要通过构建更好的索引来提高特定查询的性 能。当构建一个新索引时，考虑下面的索引设计思想： • 将列追加到索引 • 使用其他索引类型 • 查找索引的成本 • 对索引中的列排序 2.2.3.3.1 将列追加到索引 加快查询速度的最简单方法之一是通过从执行计划中消除全表扫描来减少逻辑 I/O 的数量。这可以通过将查询 引用的所有列追加到索引来完成。这些列是“选择列表”列以及任何必需的联接或排序列。当减少了耗时的 I/O 时， 此技术在加快在线应用程序响应时间方面特别有用。当第一次使用适当大小的数据测试应用程序时，最好使用这种方 法。 2.2.3.3.2 使用其他索引类型 有多种索引类型可用，不同的索引类型适用于不同的情况。下面给出每种索引类型及其适用的场景描述。 B-树索引：这种索引是标准的索引类型，对于主键和频繁选择索引都是非常适用的。作为连接索引使用，数据库 可以使用 B-树索引检索按索引列排序的数据。 位图索引：这种索引类型将相同的数据以元组 ID 位图的形式保存在磁盘上。具有占用空间小，查找迅速的特 点。适合没有 Update 操作且基数值较低 (1-10000) 的数据列。 基于函数的索引：这种索引允许通过 B-树访问从原始数据中通过函数派生的值。基于函数的索引在使用空值方 面有一些限制，并且该索引的使用要求启用查询优化器。 在查询组合列或克服由于数据在数据库中的存储方式带来的限制时，基于函数的索引特别有用。一个例子是查询 订单中超过某个值的行，计算方式为（销售价格-折扣）x 数量。另一个例子是将 upper 函数应用于数据，以允许不 区分大小写的搜索。 分区索引：分区全局索引允许在索引访问中进行分区修剪，从而减少 I/O。根据良好的范围定义或列表分区，对 正确索引分区的快速索引扫描可以执行非常快的查询时间。 反向索引：这种索引旨在消除插入型应用程序上的索引热点。这些索引对于插入性能非常好，但它们受到限制， 因为数据库无法将它们用于索引范围扫描。 17 第 2 章 第一部分数据库性能概述 2.2.3.3.3 查找索引的成本 构建和维护索引结构可能会很昂贵，而且会消耗磁盘空间、CPU 和 I/O 容量等资源。设计人员必须确保索引带 来的优势可以弥补其维护带来的开销。 使用这个简单的估算方法来计算索引维护的成本：通过插入、删除或更新索引所维护的键所需的资源大约是在表 上实际进行 DML 操作所需资源的三倍。因此，如果插入具有三个索引的表，则插入的速度大约是没有索引的表插入 的 10 倍。对于 DML，特别是对于插入量较大的应用程序，应该认真检查索引设计，这可能需要在查询和插入性能之 间进行折中。 2.2.3.3.4 对索引中的列排序 设计者应该灵活地定义索引构建的规则。根据不同情况，使用以下两种方法之一对索引中的键进行排序： • 以被选择的频繁程度顺序排列列。 此方法是最常用的方法，因为它提供了对实际所需元组的最快访问和最少的 I/O。这种技术主要用于 主键和选择频率较高的范围扫描。 • 通过对数据进行分组或排序以减少 I/O。 在大范围扫描中，I/O 通常可以通过以低访问度对列进行排序，或者以检索方式对列进行排序的方式 来减少。 2.2.3.4 使用视图 视图可以加快和简化应用程序设计。一个简单的视图定义可以屏蔽数据模型的复杂性，对于编程人员重要的是检 索、显示、收集和存储数据。 然而，虽然视图提供了干净的编程接口，但它们可能会导致次优的资源密集型查询。最糟糕的视图使用类型是一 个视图引用其他视图，以及当它们在查询中进行了联接。在许多情况下，开发人员可以不使用视图，直接从表中满足 查询。通常，由于视图的固有属性，优化器很难生成最佳执行计划。 2.2.3.5 SQL 执行效率 在任何系统开发的设计和体系结构设计阶段，都应该注意确保应用程序开发人员了解 SQL 的执行效率。为了实 现这一目标，开发环境必须支持以下特性： • 数据库连接的有效管理 连接到数据库是一个扩展性很差的高消耗操作。因此，应该尽可能减少到数据库的并发连接数。一个 简单的系统，理想情况下用户应该在应用程序初始化时连接。然而，在基于 web 或多层应用程序中， 应用服务器用于多路传输到用户的数据库连接会变得很困难。对于这些类型的应用程序，设计工作应 确保数据库连接是共用的，并且不会为每个用户请求重新建立。 • 游标的有效使用与管理 18 第 2 章 第一部分数据库性能概述 维护用户连接对于最小化系统上的解析活动同样重要。解析是解释 SQL 语句并为其创建执行计划 的过程。这个过程有许多阶段，包括语法检查、安全检查、执行计划生成和将共享结构加载到共享池 中。有两种类型的分析操作： • 硬解析：若是首次提交 SQL 语句，并且在共享池中找不到匹配项则使用硬解析。硬解析是最耗费资源和不可扩 展的，因为硬解析执行解析中涉及的所有操作。 • 软解析：若是首次提交 SQL 语句，并在共享池中找到匹配项则使用软解析。匹配项可能是其他用户以前执行的 结果。SQL 语句是共享的，这有利于提高性能。但是，软解析并不理想，因为仍然需要语法和安全检查，这会 消耗系统资源。 因为解析应该尽可能地最小化，所以应用程序开发人员应该将应用程序设计成仅解析一次 SQL 语句并多次执行 它们。这可以通过游标完成。有经验的 SQL 开发者应该熟悉打开和重新执行游标的概念。 应用程序开发人员还必须确保在共享池中共享 SQL 语句。为了实现这个目标，使用绑定变量来表示查询中从执 行到执行的部分。如果不这样做，那么 SQL 语句很可能被解析一次，而不会被其他用户重用。要确保 SQL 是共享 的，可以使用 bind 变量，并且不要对 SQL 语句使用字符串文本。 例如： 使用字符串文本的语句 SELECT * FROM employees WHERE last_name LIKE 'KING'; · 使用 bind 变量的语句 SELECT * FROM employees WHERE last_name LIKE :1; 下面展示了一个简单的 OLTP 应用的测试结果： Test #Users Supported No Parsing all statements 270 Soft Parsing all statements 150 Hard Parsing all statements 60 Re-Connecting for each Transaction 30 该测试在 4 个 CPU 的机器上进行。随着系统上 CPU 数量的增加，差异也随之增加。 • 实现应用程序 开发环境和编程语言的选择很大程度上取决于开发团队的技能和在指定应用程序时所做的架构决策。 但是，有一些简单的性能管理规则可以产生可伸缩的高性能应用程序。 1. 选择一个适合于软件组件的开发环境，不要让它限制性能决策设计。如果是这样，那么可能选择了 错误的语言或环境。 – 用户界面 编程模型可以在 HTML 生成和直接调用窗口系统中拥有不同的表现形式。开发方法应该关注 用户界面代码的响应时间。如果 HTML 或 Java 是通过网络发送的，请尽量减少网络容量和 交互。 19 第 2 章 第一部分数据库性能概述 – 业务逻辑 解释语言，如 java 和 PL/ SQL，是对业务逻辑进行编码的理想选择。它们是完全可移植 的，这使得升级逻辑相对容易。这两种语言在语法上都很丰富，使得代码易于阅读和解释。 如果业务逻辑需要复杂的数学函数，则可能需要编译的二进制语言。业务逻辑代码可以在客 户机、应用服务器和数据库服务器上。其中，应用服务器是业务逻辑最常应用的地方。 – 用户请求和资源分配 其中大部分不受编程语言的影响，但是屏蔽数据库连接和游标管理的工具和第四代语言可能 使用低效的机制。在评估这些工具和环境时，请检查它们的数据库连接模型以及它们对游标 和绑定变量的使用。 – 数据管理及事务 其中大部分不受编程语言的影响。 2. 当实现一个软件组件时，实现它的功能，而不是实现与其他组件相关联的功能。实现另一个组件的 功能会导致次优的设计和实现。这适用于所有组件。 3. 不要在功能上留白，也不要在设计、实现或测试中对软件组件进行研究。在许多情况下，直到应用 程序在实际中推出或测试之后，才会发现差距。这通常是体系结构或初始系统规范不佳的迹象。在最 初的系统设计、构建和实现过程中，数据存档和清除模块最常被忽略。 4. 在实现过程逻辑时，使用过程语言（如 C、Java 或 PL/SQL）实现。在实现数据访问（查询）或 数据更改（DML）时，请使用 SQL。此规则特定于代码的业务逻辑模块，其中过程代码与数据访问 （非过程 sql）代码混合。在 SQL 访问中加入过程逻辑是很让人认为理所应当的，但是这往往会导致 资源密集型的 SQL。带有 decode 的 SQL 语句通常是优化的候选语句，具有大量 or 谓词或 set 运算 符（如 union 和 minus）的语句也是如此。 5. 缓存经常访问，很少更改数据，重复检索代价高昂。一方面要使这种缓存机制易于使用，另一方面 要确保它确实比访问原始方法钟的数据更加高效。这适用于所有模块，其中经常使用的数据值应在本 地缓存或存储，而不是从远程或昂贵的数据存储中重复检索。 本地缓存候选的最常见示例包括： – 当日的日期。SELECT SYSDATE FROM DUAL 可以占数据库工作负载的 60% 以上 – 当前用户名 – 重复的应用程序变量和常量，如税率、折扣率或位置信息 – 本地缓存数据可以进一步扩展到将本地数据缓存构建到应用服务器中间层。这有助于 减轻中央数据库服务器的负载。但是，在构建本地缓存时应小心，以免它们变得更加复 杂，以至于不再提供性能增益 – 本地序列的生成，应该考虑使用缓存的设计影响。例如，如果用户在午夜连接并且缓存 了日期，则用户的日期值将变为无效。 6. 优化组件之间的接口，并确保所有组件在可伸缩强的环境下被使用。此规则需要最少的解析时 间，适用于所有模块及其接口。 20 第 2 章 第一部分数据库性能概述 7. 使用外键引用。通过应用程序强制引用完整性是代价高昂的。通过从父对象中选择子列的值并确保 其存在，可以维护外键引用。 8. 考虑在应用程序中设置操作和模块名称，以便与端到端应用程序跟踪一起使用。这允许更大的灵活 性来跟踪工作量问题。 2.2.3.6 应用开发趋势 当今应用程序开发中存在两个巨大的挑战，一是使用 Java 来取代由 C 或 C++ 编译的应用程序，二是日益增多 的面向对象技术的使用，影响了模式设计。 Java 为程序员提供了更好的代码可移植性和可用性。然而，与 JAVA 相关的性能问题有很多。由于 JAVA 是一 种解释语言，它在执行类似逻辑方面比编译语言（如 c）要慢。因此，客户端计算机的资源使用量增加。这需要在客 户端或中间层计算机中应用更强大的 CPU，并且需要程序员更加小心地生成高效的代码。 由于 JAVA 是一种面向对象的语言，它鼓励将数据访问隔离到不执行业务逻辑的类中。因此，程序员可能在不知 道所使用的数据访问方法效率的情况下调用方法。这往往会导致数据库访问最多，并使用最简单和最粗糙的数据库接 口。 对于这种类型的软件设计，查询并不总是包括所有有效的 where 谓词，行过滤是在 JAVA 程序中执行的，这是 非常低效的。此外，对于 DML 操作，特别是对于 insert，将执行单个 insert，从而无法使用数组接口。在某些情况 下，过程调用会使效率更低。与实际的数据库调用相比，在数据库中来回移动数据所使用的资源更多。 一般来说，最好将数据访问调用放在业务逻辑旁边，以实现最佳的总体事务设计。 在编程级别使用面向对象技术已经支持在 KingbaseES 服务器中创建面向对象的数据库。这表现在许多方面，比 如 BLOB 中存储的对象结构，比如仅将数据库有效地用作索引卡文件，再比如使用 KingbaseES 数据库对象关系特 性。 如果采用面向对象的方法来进行模式设计，则需要确保不丢失关系存储模型的灵活性。在许多情况下，面向对象 的模式设计方法最终会在一个高度非规范化的数据结构中结束，这需要大量的维护和与对象相关联的 REF 指针。通 常，这些设计代表了一种倒退到层次结构和网络数据库设计，而这些设计已经被关系存储方法所取代。 综上所述，如果长期将数据存储在数据库中，并且如果在同一模式下预想一定程度的自组织查询或应用程序开 发，则关系存储方法可能提供最佳性能和灵活性。 2.2.4 工作负载测试，建模及实施 本节描述工作负载估计、建模、实现和测试。本节包括以下主题： • 确定数据大小 • 估计负载量 • 应用建模 • 测试，调试，验证设计 21 第 2 章 第一部分数据库性能概述 2.2.4.1 确定数据大小 如果使用较差的样本集，在处理可变长度数据时可能会遇到大小估计错误的问题。随着数据量的增长，键的长度 可能会显著增长，从而改变对列大小的假设。 当系统开始运行时，预测数据库增长变得更加困难，特别是对于索引。表随着时间的推移而增长，索引的变化在 密钥生成、插入模式和行删除方面取决于应用程序的个别行为。最糟糕的情况是使用升序键插入，然后从左侧删除大 多数行，但不是所有行。这就留下了空白和浪费的空间。如果有这样的索引使用，要确保知道如何使用联机索引重建 工具。 数据库管理人员应该监视每个对象的空间分配，并查找可能失控的对象。对应用程序的良好理解可以找出那些可 能快速增长或不可预测的对象。这是任何系统性能和可用性规划的关键部分。在实现生产数据库时，设计应尝试确保 在交互用户使用应用程序时进行最小空间管理。这适用于所有数据、临时对象和回滚段。 2.2.4.2 估计负载量 考虑到所涉及的变量的数量，估计用于容量规划和测试的工作负载是非常困难的。但是，设计人员必须指定具有 CPU、内存和磁盘驱动器的计算机，并最终推出应用程序。这里提出几种方法，每种方法都有优点。在确定规模时， 最好使用以下方法验证决策过程并提供支持文档。 • 从类似系统推断 这是一个完全基于经验的方法，现有的类似特征和已知性能的系统被用作基础系统。然后由相关专家 根据已知的差异修改该系统的规格。这种方法的优点在于它与现有系统相关，但是在处理差异时它几 乎没有提供帮助。在准备工程项目（如大型建筑、船舶、桥梁或石油钻机）的成本时，几乎所有大型 工程学科都使用这种方法。如果参考系统的大小与预期系统的大小相差一个数量级，则某些部件可能 已超过其设计极限。 • 基准测试 基准测试过程既耗费资源又耗费时间，而且可能不会产生正确的结果。通过在早期开发或原型形式中 模拟应用程序，有可能测量到与实际生产系统不符的情况。这看起来很奇怪，但是在多年来与数据库 开发组织进行客户应用程序基准测试的过程中，KingbaseES 还没有看到基准测试应用程序与实际生 产系统之间的可靠关联。这主要是由于在开发过程中引入了大量应用程序使得效率低下。 然而，现在已经可以成功地使用基准测试来确定系统的大小，使其达到可接受的精度水平。特别是， 基准测试非常擅长确定实际的 I/O 需求，并在系统完全加载时测试恢复过程。 基准按其性质强调所有系统组件的极限。当基准测试涉及所有组件时，基准测试时应用程序设计和实 现一定会产生一定的错误。基准测试还可以测试数据库、操作系统和硬件组件。因为大多数基准测试 都是快速执行的，所以当一个系统组件出现故障时，预计会遇到瓶颈和问题。基准测试是一项负载压 力很大的任务，从基准测试中获得最大收益需要相当多的经验。 22 第 2 章 第一部分数据库性能概述 2.2.4.3 应用建模 无论是复杂的数学建模练习，还是在信封背面执行的经典简单计算，对应用的建模都可以适用。不同的建模方法 都有优点，有的方式试图非常精确地建模，有的方法则进行粗略估计。这些建模方法的缺点是它们都不允许实现错误 和低效率。 估计和确定数据大小过程是一门不精确的科学。然而，通过调查这个过程，可以做出一些智能的估计。整个估计 过程不考虑由糟糕的 SQL、索引设计或游标管理导致的应用程序效率低下。一个规模测定工程师应该为应用程序的 低效性留出空间。一个性能调优工程师应该发现效率低下的地方，并使评估看起来切合实际。KingbaseES 性能方法 描述了如何发现应用程序的低效性。 2.2.4.4 测试，调试，验证设计 测试过程主要包括功能测试和稳定性测试。在过程中的某个时刻，执行性能测试。 下面列出了一些用于性能测试应用程序的简单规则。如果记录正确，则此列表将在应用程序启动后为生产应用程 序和容量规划过程提供重要信息。 1) 使用真实的数据量和分布进行测试 所有测试必须使用完全填充的表完成。测试数据库应包含代表生产系统的数据，包括数据量和表之间的基数。 应构建所有生产索引，并正确填充架构统计信息。 2) 测试单个用户的性能 在空闲或少量使用的数据库上测试单个用户以获得可接受的性能。如果一个用户在理想条件下不能达到可接受 的性能，那么多个用户在实际条件下更加就不能达到可接受的性能。 3) 获取并记录所有 SQL 语句的计划 获取每个 SQL 语句的执行计划。使用此过程可验证优化器是否获得了最佳执行计划，以及是否根据 CPU 时间 和物理 I/O 了解了 SQL 语句的相对成本。此过程有助于确定将来需要进行最多调整和性能工作的大量事务。 4) 尝试多用户测试 这个过程很难准确执行，因为用户工作负载和配置文件可能无法完全量化。但是，应该测试执行 DML 语句的 事务，以确保没有锁定冲突或序列化问题。 5) 使用正确的硬件配置进行测试 使用尽可能靠近生产系统的配置进行测试。使用真实的系统对于网络延迟、I/O 子系统带宽、处理器类型和速 度特别重要。如果不使用这种方法，可能会导致对潜在性能问题的错误分析。 6) 测量稳态性能 在进行基准测试时，测量稳态条件下的性能非常重要。每个基准测试运行都应该有一个提升阶段，用户连接到 应用程序并逐渐开始对应用程序执行工作。此过程允许将频繁缓存的数据初始化到缓存中，并允许在稳态条件 之前完成诸如解析之类的单次执行操作。同样，在基准测试运行结束时，应该有一个缓冲期，在这段时间内， 系统释放资源，用户停止工作并断开连接。 23 第 2 章 第一部分数据库性能概述 2.3 性能问题调优 2.3.1 整体过程 KingbaseES 实例性能调优的整体过程如下： 1. 定义性能目标 从用户那里获得关于性能问题范围的直接反馈，定义我们要达成的性能目标。 准确确定应用场景，包括相关的测试用例。 对于定义性能目标，最低的目标是满足应用需求，如果已经满足了应用要求可能就不需要做性能诊断 和调优。 对于 pk 的场景，性能目标是性能越高越好，直到无法再调优或者很难调优。 参见定义性能目标 2. 测试并检查资源使用情况，定位性能瓶颈，找出性能问题的直接原因 收集从测试程序到数据库之间完整的操作系统资源负载信息、数据库内部统计信息、性能指标信息 后，检查数据是否有任何性能问题的迹象。 参见性能诊断，定位资源瓶颈 3. 检查资源使用的分布情况，找出性能问题的根本原因 在定位到性能瓶颈后，还需要进一步分析各种软硬件资源使用的分布情况（尤其是瓶颈资源），找出 资源使用的不当情况（或者没有不当使用但是无法满足当前性能指标要求），才能找出性能问题的根 本原因，使得性能收益最大化。 参见性能诊断，定位优化点 4. 对于不合理的资源使用，合理化运用调优手段 在定位到性能问题的根本原因后，合理运用数据库的现有手段来解决性能问题。 参见性能调优，解决性能问题 5. 除了 2、3、4 自顶向下的分析之外，也可以考虑检查一些常见的一些优化经验 参见性能诊断与优化经验 6. 提出需要进行的变更和实施变更的预期结果。然后，实现这些更改并监测应用程序的性能表现 7. 确定步骤 1 提出的问题是否解决，如果没有解决，重复 2，3，4，5 步骤 2.3.2 定义性能目标 在尝试实现解决方案之前，必须充分了解性能调优的目标和问题的性质。如果没有准确的理解，则不太可能实施 有效的优化措施。在这一阶段收集的信息有助于确定下一步要采取的措施。 24 第 2 章 第一部分数据库性能概述 需要收集的信息包括： 1. 确定性能指标 可接受性能指标是什么？如响应时间、吞吐量的要求 注：可扩展性问题通常需要对架构设计做调整，参见新系统性能规划（仅供参考） 。 2. 确定问题涉及的范围 由于执行的缓慢影响到了什么？例如，整个实例是否缓慢？是否影响到特定的应用、程序、特定的操 作还是单个用户？ 3. 确定问题发生的时间 这个问题只在高峰时段出现吗？一天中的性能表现会变差吗？在数月或数个星期的时间段内是逐渐缓 慢还是突然缓慢？ 4. 量化性能差距 这有助于确定问题的严重程度，而且可以作为所做优化工作是否确实生效的衡量依据。找到可以衡量 响应时间的度量单位，然后衡量和正常运行状态（预期状态）相比响应时间差了有多少？ 5. 如果是确定发生了哪些改变 确定自性能达标以来发生了哪些变化。这可能会迅速缩小潜在原因的范围。例如，操作系统软件、硬 件、应用程序软件或 KingbaseES 数据库版本是否已升级？系统中是否加载了更多的数据，或者数据 量和用户数量是否增加？ 2.3.3 性能诊断，定位资源瓶颈 性能问题是由于某些系统资源争用或耗尽的结果。测试程序/应用与数据库之间通常会有多个主机，如测试主 机、负载均衡设备、应用服务器、数据库服务器等。 常见的性能指标响应时间链条会比较长，性能瓶颈的定位也就需要收集所有主机的软硬件资源的监控数据和数据 库内部的统计信息。 性能调优的首要目标就是定位性能问题产生的根本原因，也就是性能瓶颈。性能瓶颈的定位可以起到事倍功半的 效果。 常见的性能瓶颈现象可能包含： • CPU 利用率接近 100% • IO 利用率接近 100% • 内存不够，开始写 swap • 网络流量达到带宽上限、网络传输存在大量错误 • 数据库内部有大量的时间在等待 25 第 2 章 第一部分数据库性能概述 注意: 有时候，可能无法观察到明显的性能瓶颈，则表明压力在前段没有释放，虽然在数据库端调优也能缩小一定的 响应时间，但是效果不够显著。 2.3.4 性能诊断，定位优化点 在定位到性能瓶颈后，还需要进一步分析瓶颈资源使用的分布，找到不合理的资源使用情况，然后加以调优。 资源分布情况的定位方法： • CPU 分布分析 – 耗时语句时间分布：top SQL – SQL 执行计划分析 • IO 分布分析 – iostat – pidstat – sys_statio_xxx • 内存分布分析 – � 共享内存组成 26 第 2 章 第一部分数据库性能概述 – 本地内存组成 • 网络分布分析 – sar – sys_stat_statement 里的 rows 统计 • 封锁分布分析 – sys_locks 多次采样 – waitevent 多次采样 有关资源分布情况的详细定位方法将在第二部分性能诊断 以描述 2.3.5 性能调优，解决性能问题 在定位到性能优化点之后，性能调优可以从以下几个方面着手： 1. 数据库参数优化 主 要 是 根 据 不 同 应 用 调 节 不 同 内 核 参 数 进 行 优 化， 比 如 经 常 有 大 量 数 据 频 繁 访 问 可 以 调 大 shared_buffers，有写临时文件的情况调大 work_mem。 2. SQL 优化 可以考虑创建合适的索引、使用分区、通过 HINT 控制优化器使用最优的执行计划、使用并行等方 式。 3. 操作系统优化 考虑磁盘的调度策略的优化，以及预读的优化，以及启动磁盘写缓存的策略等方式。 4. 应用优化 考虑改写 SQL 语句、使用 PBE 缓存执行计划、错峰执行等情况。 5. 硬件优化 扩展 CPU、内存、存储、网络等硬件能力。 6. 架构优化 使用缓存、读写分离、分库分表等架构来进行优化。 有关性能调优方法的运用将在第三部分性能优化 予以描述 2.3.6 性能诊断与优化经验 除了上述从瓶颈分析、根因分析到实施调优手段这种自顶向下追溯原因的方法，KingbaseES 也有一些常见的优 化经验供用户参考： • 如果遇见查询计划不准的情况，首先需要考虑是否做了 analyze。 27 第 2 章 第一部分数据库性能概述 • 数据量大性能慢的情况，首先需要考虑 shared_buffers 是否开小了。 • 一般有排序和 join 的查询慢的情况，只要数据量稍微大点就需要考虑 work_mem 是否设置了比较大，确保不 写临时文件，不过如果并发特别多设置太大可能导致内存不够。 • 全表扫描慢的情况，可以考虑采用索引，尤其是表达式索引和条件索引，调整 SQL 的执行计划，选择合适的索 引。 • 多表 join 的慢的情况，可以考虑 join 顺序问题，调整执行计划得到最优的 join 的顺序 • 表的 join 慢的情况，比如原来采用了 nestloop, 数据无序可以考虑采用 hashjoin, 有序可以考虑 mergejoin。 • 对于慢语句，在 cpu 资源比较充裕的情况下，可以考虑并行技术 • 数据库层优化完还不行，可以考虑操作系统调优，比如 IO 问题考虑磁盘的调度策略，数据库一般采用 deadline, 还有操作系统预读和 IO 请求队列的调整 • 操作系统问题考虑完了，可以考虑升级硬件，提高磁盘 IO 和 cpu 的吞吐量 • IO 是问题，但 CPU 不是问题还可以考虑数据压缩技术 • 对于同类请求比较多的情况，可以尝试通过 PBE 进行 SQL 的变量绑定，缓解 SQL 的硬解析, 当遇到成千上万 的查询操作时，能够不经过解析过程直接使用缓存的执行计划，那效率可以提高 n 倍。 • 数据量大还可以考虑分区。 • 数据库能够高效的运行，最关建的因素需要内存足更大了，能缓存住数据，更新也可以在内存先完成。但不同 的业务对内存需要强度不一样，一推荐内存要占到数据的 15-25% 的比例，特别的热的数据，内存基本要达到 数据库的 80% 大小。 • 单机撑不住的时候可以考虑读写分离 • 连接数过多造成资源争用严重的，考虑使用连接池来减缓竞争压力 28 第 3 章 第二部分性能诊断 3 第 章 第二部分性能诊断 性能诊断的目的是找出影响性能目标的关键因素、给出性能调优的方向。本部分主要阐述了从性能瓶颈分析到性 能优化点定位的诊断方法。本部分将包含以下主题： • 性能诊断方法 ：性能诊断的一般流程 • 操作系统资源分析 ：操作系统资源分析的工具和方法介绍 • 数据库资源分析 ：数据库资源分析的工具和方法介绍 3.1 性能诊断方法 当我们进行性能诊断时，要优先分析瓶颈问题，然后从整体到局部逐渐分解，逐步向下找到问题所在，往往能够 起到事倍功半的效果。 性能诊断的流程如下： 29 第 3 章 第二部分性能诊断 1. 全面检查应用、数据库、以及可能存在的中间服务器的操作系统资源是否存在性能瓶颈。如果问题出在应用程 序上，则将分析转移到应用程序调试。否则，请继续进行数据库服务器分析。 • 各主机 CPU 资源是否饱和，如果饱和是否是数据库占用了过多的 CPU 的 CPU。 方法参见CPU 瓶颈分析 • 各主机网络是否稳定，流量是否饱和，如果饱和是否是数据库占用了过多的网络资源 方法参见网络瓶颈分析 • 各主机 I/O 资源是否饱和，如果饱和是否是应用或数据库占用的 I/O。 方法参见I/O 瓶颈分析 • 数据库内部是否存在等待事件和锁瓶颈 方法参见等待事件分析 注意: 此时我们有可能已经发现了问题，比如操作系统磁盘调度问题，其他活动占用了资源。很多情况下我们看到的 操作系统层面反映出来的瓶颈，还不能够让我们找出问题的根本问题，此时就要向下分析。 2．对于数据库问题，分析各种资源的使用分布，分析占优较大资源的使用是否合理。 • 如果是数据库服务器占用的 CPU 即将饱和，则需确定占用较多 CPU 的进程是否为 KingbaseES 进程。如果不 是 KingbaseES 进程，那么需要确定它们占用那么多 CPU 资源是否合理。如果合理，确定它们的执行是否可以 被推迟到非高峰时间。对于 CPU 时间分布的进一步分析，可以考虑： 30 第 3 章 第二部分性能诊断 – 找到耗时 SQL 语句 方法参见CPU 优化点分析 – 对耗时 SQL 语句做 SQL 优化分析 比如统计信息是否及时准确、是否缺少索引、是否有更优的执行计划、是否可以使用并行、 是否可以有更高效的 SQL 改写方式 有关 SQL 调优的内容请参见《KingbaseES 数据库 SQL 调优指南》 • 如果是数据库占用 IO 子系统即将饱和，则需要考虑 IO 问题的根源。如： – 共享内存不够 可以通过查看数据库查询命中率的方式来判断是否存在共享内存不够的问题。 方法参见I/O 优化点分析 – 私有内存不够 可以通过查看语句的执行计划，或者数据库的 log 输出判断排序等操作是否使用了临时文 件。 方法参见私有内存使用分析 – 数据页面扩展压力太大 数据页面的扩展可以通过查看 waitevent 查看是否有很多的数据 extern 等待。 方法参见等待事件分析 – Checkpoint 压力太大 可以通过使用 pidstat 查看 checkpoint 进程的 i/o 情况，判断 checkpoint 进程压力。 方法参见I/O 瓶颈分析 – 日志压力太大 与 checkpoint 相似，可以通过使用 pidstat 查看日志进程的 i/o 情况，判断日志进程压力。 方法参见I/O 瓶颈分析 • 如果是数据库占用网络子系统即将饱和，则需要分析网络流量来源于哪些 SQL 语句。 详细请参阅本指南使用 sys_stat_statement 工具 ，该工具中包含有语句返回条数信息。 • 如果是数据库内部的资源等待占用了较多时间，则需要通过分析造成资源等待的情况。 方法参见等待事件分析 、锁优化点分析 31 第 3 章 第二部分性能诊断 3.2 操作系统资源分析 由于数据库运行在操作系统之上，所以操作系统各种资源的性能表现会对数据库性能问题的分析，尤其是性能瓶 颈的定位有很大帮助。 操作系统比较重要的性能资源包括：CPU、内存、IO、网络。其性能问题表现可能包含： • CPU 利用率是否已经达到了或者接近了 100% • 内存是否存在 swap 的情况 • 磁盘 I/O 的利用率是否已经达到了或者接近了 100% • 网络 I/O 带宽是否已经存在达到了上限，是否存在丢包等情况 下文将对这些资源做具体分析，分析工具如下图所示，大部分功能在 nmon 中都可以获得，建议读者使用。 • CPU 瓶颈分析 • 内存瓶颈分析 • I/O 瓶颈分析 • 网络瓶颈分析 32 第 3 章 第二部分性能诊断 3.2.1 CPU 瓶颈分析 cpu 信 息 收 集 工 具 主 要 有：mpstat、iostat、nmon、oprofile,、perf、sar、iostat、vmstat、uptime、/ proc/ stat、/proc/loadavg 等。 3.2.1.1 CPU 信息简介 CPU 资源监控的工具较多，大部分工具的监控内容相似，本节以 mpstat 为例做重点介绍。mpstat 是 Multiprocessor Statistics 的缩写，是实时系统监控工具。其报告与 CPU 的一些统计信息，这些信息存放在/proc/stat 文件 中。在多 CPUs 系统里，其不但能查看所有 CPU 的平均状况信息，而且能够查看特定 CPU 的信息。 用法： mpstat [-P {|ALL}] [internal [count]] • -P {|ALL} 表示监控哪个 CPU，cpu 在 [0，cpu 个数-1] 中取值 • internal 相邻的两次采样的间隔时间 • count 采样的次数，count 只能和 delay 一起使用 示例： [jwang@ha1 ~]$ mpstat -P 0,1 2 4 Linux 3.10.0-693.21.1.el7.x86_64 (ha1) 2020 年 03 月 21 日 _x86_64_ (32 CPU) 11 时 52 分 38 秒 CPU %usr %nice %sys %iowait %irq %soft %steal %guest %gnice %idle 11 时 52 分 40 秒 0 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 11 时 52 分 40 秒 1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 100.00 字段说明： • %user ：在 internal 时间段里，用户态的消耗的 CPU 时间比例 • %sys：在 internal 时间段里，系统内核消耗的 CPU 时间比例 • %nice：在 internal 时间段里，带 NICE 优先级的用户态消耗的时间比例 （nice: 在用户空间内，通过 nice 或 setpriority 调用改变过优先级进程的 cpu 占用率。在同等情况下，优 先级高 (值越小) 的进程会比优先级低的得到优先调度。nice 不为 0，表明发生了低优先级进程抢占高优先 级进程的情况） • %iowait：在 internal 时间段里，CPU 等待 IO 操作完成时间的百分比。 • %irq ：在 internal 时间段里，硬中断时间（%） • %soft ：在 internal 时间段里，软中断时间（%） 33 第 3 章 第二部分性能诊断 • %steal ：在 internal 时间段里，管理程序维护另一个虚拟处理器时，虚拟 CPU 的等待时间比例。 • %idle ：在 internal 时间段里，CPU 时间闲置时间（%） • intr/s：在 internal 时间段里，每秒 CPU 接收的中断的次数 3.2.1.2 CPU 资源分析 主要分析： • %usr ：用户态消耗时间。如果比较大，说明用户程序本身有 cpu 瓶颈，需要优化程序本身。 • %sys ：系统内核消耗时间，如果比较大，说明系统调用函数花费时间很多，系统调用有 cpu 瓶颈，可能是程序 非常多次的调用系统内核函数导的，这个可能需要修改代码方式进行优化。 • %iostat: 等待 io 的 cpu 时间，如果比较大，说明 IO 等待越严重，可能由于磁盘大量随机访问造成，也有可能 磁盘出现瓶颈。 • %idle: 空间 cpu，如果比较大，说明不是瓶颈。 CPU 问题的进一步分析，参见CPU 优化点分析 。 3.2.2 内存瓶颈分析 内存常用分析工具：top，free, nmon, /proc/zoneinfo, /proc/buddyinfo,/proc/meminfo, /proc/slabinfo, /proc/ vmstat, /proc//maps 等。 3.2.2.1 内存信息简介 内存资源监控的工具较多，大部分工具的监控内容相似，本节以 top 为例做重点介绍。top 命令可以显示 CPU、 内存等信息： top - 14:32:45 up 43 days, 8:28, 21 users, load average: 0.19, 0.30, 0.30 Tasks: 630 total, 1 running, 628 sleeping, 0 stopped, 0 zombie %Cpu(s): 0.0 us, 0.1 sy, 0.0 ni, 99.9 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st KiB Mem : 26372547+total, 20976670+free, 4104164 used, 49854604 buff/cache KiB Swap: 16777212 total, 16777212 free, 0 used. 25713113+avail Mem 跟内存相关的 2 个段为 Mem 和 Swap，分别表示物理内存和交换内存。 字段说明如下： • Total：物理/交换内存总量 • Used：使用的物理/交换内存总量 • Free：空闲物理/交换内存总量 • Buffers：用作内核缓存的内存量 34 第 3 章 第二部分性能诊断 • Cached：缓冲的交换区总量 内存资源分析 3.2.2.2 主要分析： • swap 是否被使用，如果使用了那就会拖累性能，消耗 cpu 和 io 时间。 例如：测试 tpcc 时有几个 g 的 swap 使用，导致峰值上不去，然后调整了 shared_buffers，变小一些, 然后就不用交换分区了，然后峰值就上去了。原因是一些临时的数据可能比较多的情况，然后放不下 内存就用了交换分区。 • 空闲内存是否比较少，一般来说如果空闲内存/物理内存 >70%，内存性能优，如果小于 20%，则性能差，需要 添加内存。 • 如果内存用的很少，查询比较慢, 而且数据量很大，并且很多 io，那可以考虑调大 shared_buffers, 提高命中 率。 3.2.3 I/O 瓶颈分析 I/O 是数据库服务器性能影响关键的因素之一。随着数据量的增大，采用高性能的 I/O 设备有时会是解决性能问 题的关键。 测量 I/O 的工具包括：iostat, nmon, sar, vmstat, lsof, /proc/diskstat,/proc/partitions 3.2.3.1 IO 信息简介 I/O 资源监控的工具较多，大部分工具的监控内容相似，本节以 iostat 为例做重点介绍。iostat 可以显示 CPU 和 I/O 系统的负载情况及分区状态信息。 用法: iostat [ 选项] [ < 时间间隔 >[< 次数 > ] ] • 选项：选项就较多，建议查看 help。其中，-x 可以显示更为全面的信息。 • 时间间隔：信息的输出间隔 • 次数：输出信息的次数 示例： 35 第 3 章 第二部分性能诊断 iostat 不仅可以监控 I/O，还可以监控 CPU。 IO 相关的字段说明如下： • rrqm/s: 每秒进行 merge 的读操作数目。即 delta(rmerge)/s • wrqm/s: 每秒进行 merge 的写操作数目。即 delta(wmerge)/s • r/s: 每秒完成的读 I/O 设备次数。即 delta(rio)/s • w/s: 每秒完成的写 I/O 设备次数。即 delta(wio)/s • rsec/s: 每秒读扇区数。即 delta(rsect)/s • wsec/s: 每秒写扇区数。即 delta(wsect)/s • rkB/s: 每秒读 K 字节数。是 rsect/s 的一半，因为每扇区大小为 512 字节 • wkB/s: 每秒写 K 字节数。是 wsect/s 的一半 • avgrq-sz: 平均每次设备 I/O 操作的数据大小 (扇区), 即 delta(rsect+wsect)/delta(rio+wio) • avgqu-sz: 平均 I/O 队列长度。即 delta(aveq)/s/1000(因为 aveq 的单位为毫秒) • await: 平均每次设备 I/O 操作的等待时间 (毫秒)。即 delta(ruse+wuse)/delta(rio+wio) • svctm: 平均每次设备 I/O 操作的服务时间 (毫秒)。即 delta(use)/delta(rio+wio) • %util: 一秒中有百分之多少的时间用于 I/O 操作，或者说一秒中有多少时间 I/O 队列是非空的。即 delta(use)/ s/1000 (因为 use 的单位为毫秒) 3.2.3.2 IO 资源分析 比较重要的参数分析： • %util：io 的使用率，主要看是否已经接近或者超过 100% 如果%util 接近 100%，说明产生的 I/O 请求太多，I/O 系统已经满负荷，该磁盘可能存在瓶颈。 • svctm: 时间，主要说明磁盘本身的读写性能快慢，比如 tpcc 测试一般盘阵服务时间在 0.25ms 左右。如果太大 那就是磁盘性能问题，不过 CPU/内存的负荷也会对其有点影响。 • await: 主要说明平均每次 IO 响应时间，一般小于 5ms。 36 第 3 章 第二部分性能诊断 – 其中 svctm 一般要小于 await (因为等待时间会算入 svctm)，await 的大小一般取决于服务时间 (svctm) 以 及 I/O 队列的长度（avgqu-sz）和 I/O 请求的发出模式。 – 如果 svctm 比较接近 await，说明 I/O 几乎没有等待时间； – 如果 await 远大于 svctm，说明 I/O 队列太长，应用得到的响应时间变慢。 – 如果响应时间超过了用户可以容许的范围，这时可以考虑更换更快的磁盘，调整内核 IO 调度的 elevator 算法，优化应用，或者升级 CPU。 – 例如：对于 tpcc 一般采用 deadline 方式比较合适（IO 调度算法默认是 cfq）Linux 有四种 IO 调度算法： CFQ，Deadline，Anticipatory 和 NOOP，CFQ 是默认的 IO 调度算法。完全随机的访问环境下，CFQ 与 Deadline，NOOP 性能差异很小，但是一旦有大的连续 IO，CFQ 可能会造成小 IO 的响应延时增加，所 以数据库环境建议修改为 deadline 算法，表现更稳定。我们的环境统一使用 deadline 算法。 • avgqu-sz：也可作为衡量系统 I/O 负荷的指标，但由于 avgqu-sz 是按照单位时间的平均值，所以不能反映瞬间 的 I/O 洪水。不过如果分析发现 avgqu-sz 一直特别大，说明读写 IO 请求排队数量比较多，另外要和 avgrq-sz 的情况来判断 IO 量的情况，比如 avgrq-sz 比较大，说明平均每次读写请求的数据量比较多，两者都大那一定 是 IO 问题。 • avgrq-sz：读写请求的数据量 (扇区数)，上面已经说明和 avgqu-sz 一起决定 IO 问题，如果 avgqu-sz 排队数量 多，但请求的数据小的话，其实 IO 也会很小。如果请求数据大的计划，IO 的数据才会高。 对于进一步分析 IO 的使用分布，我们可以使用 pidstat 进一步的观察，从而快速的找到 checkpoint、wal 日志等 I/O 分布情况。 例如： pidstat -d -l -p ALL 1 1 |grep kingbase 本例中我们可以看到 checkpoint 的 i/o 是进程这些中最高的，每秒的写入为 58M/s，因此我们可以考虑把 checkpoint 错峰或者分摊到尽可能长的 checkpoint 周期内等方式来缓解该部分 IO 压力。 3.2.4 网络瓶颈分析 网络信息相关的工具主要包括：ping, nmon, iftop, sar, netstat, ethtool, ifconfig,mii-tool。 37 第 3 章 第二部分性能诊断 3.2.4.1 网络信息简介 网络资源监控的工具较多，大部分工具的监控内容相似，本节以 sar 为例做重点介绍。sar（System Activity Reporter）是系统活动情况报告的缩写。它是目前 Linux 上最为全面的系统性能分析工具之一，可以从多方面对系统的 活动进行报告，包括：文件的读写情况、系统调用的使用情况、磁盘 I/O、CPU 效率、内存使用状况、进程活动及 IPC 有关的活动等。 1、使用 Sar –n DEV 监控网络传输情况： 字段说明： • IFACE：网络设备名 • rxpck/s：每秒钟接收到的包数目 • txpck/s：每秒钟发送出去的包数目 • rxbyt/s：每秒钟接收到的字节数 • txbyt/s：每秒钟发送出去的字节数 • rxcmp/s：每秒钟接收到的压缩包数目 • txcmp/s：每秒钟发送出去的压缩包数目 • txmcst/s：每秒钟接收到的多播包的包数目 • rxmcst/s：每秒接收的多播（multicast）包的总数 2、使用 EDEV 关键字查看网络传输的失败情况： 38 第 3 章 第二部分性能诊断 字段说明： • rxerr/s：每秒钟接收到的损坏的包的数目 • txerr/s：当发送包时，每秒钟发生的错误数 • coll/s：当发送包时，每秒钟发生的冲撞 (collisions) 数（这个是在半双工模式下才有） • rxdrop/s：由于缓冲区满，网络设备接收端，每秒钟丢掉的网络包的数目 • txdrop/s：由于缓冲区满，网络设备发送端，每秒钟丢掉的网络包的数目 • txcarr/s：当发送数据包时，每秒钟载波错误发生的次数 • rxfram/s：在接收数据包时，每秒钟发生的帧对齐错误的次数 • rxfifo/s：在接收数据包时，每秒钟缓冲区溢出错误发生的次数 • txfifo/s：在发送数据包时，每秒钟缓冲区溢出错误发生的次数 3.2.4.2 网络资源分析 主要分析： • 查看 rxbyt/s 和 txbyt/s 的收发字节数是否已经达到了瓶颈。需要注意的是，网络的性能取决于源端和目的端以 及中间设备（网线、路由器、交换机等）的整体表现。例如：源端和目的端都是千兆网卡，但是它们之间的交 换机为百兆，则两端的网络传输上限只能是百兆。为了更准确的了解网络性能的真实表现，可以通过 scp 或者 iperf 来做测试。 • 查看是否有网络传输错误，如果存在则需要做更进一步分析。 网络资源的使用分布分析可以参见使用 sys_stat_statement 工具，该工具中包含有语句返回条数信息。 3.3 数据库资源分析 数据库运行时的状态对分析某些性能问题的分析至关重要，我们可用通过对系统状态统计信息、日志信息的分 析，来监控整个数据库的运行状态，并能从其中发现一些性能相关的问题。 跟数据库性能相关的信息主要包括： 整体分析 sys_kwr（基本覆盖所有优化点分析） sys_ksh sys_kddm CPU 优化点分析 sys_stat_statements kbbadger 见续表 39 第 3 章 第二部分性能诊断 表 3.3.1 – 续表 会话/负载/等待事件分析 sys_stat_activity 视图（实时等待事件） sys_ksh 报告工具（时间区间） 锁优化点分析 sys_locks 视图 IO 优化点分析分析 sys_statio_user_tables 视图 sys_statio_user indexes 视图 sys_stat_wal_buffer 视图 应用特点分析 sys_stat_user_tables 视图 sys_stat_user_indexes 视图 sys_stat_database 视图 数据库内部提供了性能视图和日志系统来辅助做性能诊断，一般思路如下： 1. 查看活跃连接和等待时间，看是否存在存在等待较多的等待事件。可能的等待事件包括：数据文件读/写、日志 文件写、网络读、封锁等时间比较长的问题。 2. 查看耗时语句，从高到底优化耗时语句的执行时间。 3. 查看锁信息，看是否有锁无法获得的情况。 4. 查看表和索引的命中率，看是否有命中率较低的情况。如果命中率较低，考虑提高 shared_buffers。 5. 查看是否有写临时文件的情况（设置 log_temp_files 参数）。如果有，是否是 work_mem 设置的太小了，导 致排序内存放不下就写临时文件了，调整大一些（和查询的数据量有关）然后观察是否还写临时文件。 6. PB 报文的比例是否正常，如果 PB 的比例相当，或者大量使用了 Q 报文，考虑将应用改写为规范的 PBE 协 议，尽量复用执行计划。 • CPU 优化点分析 • I/O 优化点分析 • 锁优化点分析 • 自动负载信息库 SYS_KWR • 活跃会话历史报告 SYS_KSH • 自动诊断和建议报告 SYS_KDDM • KWR 运行期对比报告 KWR DIFF 3.3.1 CPU 优化点分析 在数据库遭遇 CPU 瓶颈时，通过分析耗时 SQL 语句的执行计划可以有效的降低数据库的响应时间，提高吞吐 量。 40 第 3 章 第二部分性能诊断 目前 KingbaseES 内部提供了 2 种工具来做耗时语句统计（kwr 也集成了类似能力）： sys_stat_statement sys_stat_statement 是一个插件，通过共享内存来做数据记录，其特点如下： • 对于同一类语句只做一条记录，有效节省时间和刷盘时间 • 结果可以实时查看 • 无法查看语句中的参数信息、事务信息、执行时间等信息 kbbadger kbbadger 依赖于 KingbaseES 的日志系统来做记录，其特点如下： • 同一类语句的信息都可以做记录，会带来 IO 压力 • 需要将日志通过工具分析后才可以查看 • 还可以额外记录临时文件、checkpoint、绑定信息等 在得到耗时语句后，则需要对耗时语句做 SQL 性能分析，一般的分析包括： • 统计信息是否够及时 • 是否有数据、索引膨胀的情况 • 是否缺少索引、分析、物化视图等访问结构 • Join 顺序、join 算法等是否合理 • 是否有更高效的改写方式 有关 SQL 调优更详细的内容参见《KingbaseES 数据库 SQL 调优指南》 3.3.1.1 使用 sys_stat_statement 工具 sys_stat_statement 是 KingbaseES 系统的一个扩展组件，它提供了所有执行语句的统计信息，可以帮助找出哪 种类型的查询很慢以及多久调用一次查询。 使用这个模块的执行步骤： 1. 在 kingbase.conf 里添加预加载项： shared_preload_libraries = 'liboracle_parser, sys_stat_statements' sys_stat_statements.track = 'top' 2. 重启数据库服务器 3. 在执行的数据库里创建扩展： CREATE EXTENSION sys_stat_statements; 41 第 3 章 第二部分性能诊断 注意: 从 V9 版本开始，sys_stat_statements 插件已经内置化，初始化数据库实例的时候，就会被自动加载。 该功能默认关闭，需要设置：sys_stat_statements.track = ’top’ 才能正常使用。 该插件的最新版本是 1.10。 sys_stat_statements 视图结构： 表 3.3.2: 视图结构 名称 类型 描述 userid oid 执行该语句的用户的 OID dbid oid 在其中执行该语句的数据库的 OID queryid bigint 内部哈希码，从语句的解析树计算得来 query text 语句的文本形式 parses bigint 该语句被解析的次数 total_parse_time double precision 在该语句中解析花费的总时间，以毫秒计 min_parse_time double precision 在该语句中解析花费的最小时间，以毫秒计 max_parse_time double precision 在该语句中解析花费的最大时间，以毫秒计 mean_parse_time double precision 在该语句中解析花费的平均时间，以毫秒计 stddev_parse_time double precision 在该语句中解析花费时间的总体标准偏差，以毫秒计 plans bigint 该语句执行 Plan 的次数 total_plan_time double precision 在该语句中执行 Plan 花费的总时间，以毫秒计 min_plan_time double precision 在该语句中执行 Plan 花费的最小时间，以毫秒计 max_plan_time double precision 在该语句中执行 Plan 花费的最大时间，以毫秒计 mean_plan_time double precision 在该语句中执行 Plan 花费的平均时间，以毫秒计 stddev_plan_time double precision 在该语句中执行 P lan 花费时间的总体标准偏差，以毫秒计 calls bigint 被执行的次数 total_exec_time double precision 在该语句中花费的总时间，以毫秒计（该字段在 V9R5 里面为：total_time） 见续表 42 第 3 章 第二部分性能诊断 表 3.3.2 – 续表 名称 类型 描述 min_exec_time double precision 在该语句中花费的最小时间，以毫秒计（该字段在 V9R5 里面为： min_time） max_exec_time double precision 在该语句中花费的最大时间，以毫秒计（该字段在 V9R5 里面为： max_time） mean_exec_time double precision 在该语句中花费的平均时间，以毫秒计（该字段在 V9R5 里面为： mean_time） stddev_exec_time double precision 在该语句中花费时间的总体标准偏差，以毫秒计（该字段在 V9R5 里面为：stddev_time） rows bigint 该语句检索或影响的行总数 shared_blks_hit bigint 该语句造成的共享块缓冲命中总数 shared_blks_read bigint 该语句读取的共享块的总数 shared_blks_dirtied bigint 该语句弄脏的共享块的总数 shared_blks_written bigint 该语句写入的共享块的总数 local_blks_hit bigint 该语句造成的本地块缓冲命中总数 local_blks_read bigint 该语句读取的本地块的总数 local_blks_dirtied bigint 该语句弄脏的本地块的总数 local_blks_written bigint 该语句写入的本地块的总数 temp_blks_read bigint 该语句读取的临时块的总数 temp_blks_written bigint 该语句写入的临时块的总数 blk_read_time double precision 该语句花在读取块上的总时间，以毫秒计 blk_write_time double precision 该语句花在写入块上的总时间，以毫秒计 sys_stat_statements_all 视图结构： 43 第 3 章 第二部分性能诊断 表 3.3.3: 视图结构 名称 类型 描述 userid oid 执行该语句的用户的 OID dbid oid 在其中执行该语句的数据库的 OID queryid bigint 内部哈希码，从语句的解析树计算得来 parent_queryid bigint 上一层 SQL 的哈希码 query text 语句的文本形式 parses bigint 该语句被解析的次数 total_parse_time double precision 在该语句中解析花费的总时间，以毫秒计 min_parse_time double precision 在该语句中解析花费的最小时间，以毫秒计 max_parse_time double precision 在该语句中解析花费的最大时间，以毫秒计 mean_parse_time double precision 在该语句中解析花费的平均时间，以毫秒计 stddev_parse_time double precision 在该语句中解析花费时间的总体标准偏差，以毫秒计 plans bigint 该语句执行 Plan 的次数 total_plan_time double precision 在该语句中执行 Plan 花费的总时间，以毫秒计 min_plan_time double precision 在该语句中执行 Plan 花费的最小时间，以毫秒计 max_plan_time double precision 在该语句中执行 Plan 花费的最大时间，以毫秒计 mean_plan_time double precision 在该语句中执行 Plan 花费的平均时间，以毫秒计 stddev_plan_time double precision 在该语句中执行 P lan 花费时间的总体标准偏差，以毫秒计 calls bigint 被执行的次数 total_exec_time double precision 在该语句中花费的总时间，以毫秒计（该字段在 V9R5 里面为：total_time） min_exec_time double precision 在该语句中花费的最小时间，以毫秒计（该字段在 V9R5 里面为： min_time） max_exec_time double precision 在该语句中花费的最大时间，以毫秒计（该字段在 V9R5 里面为： max_time） mean_exec_time double precision 在该语句中花费的平均时间，以毫秒计（该字段在 V9R5 里面为： mean_time） 见续表 44 第 3 章 第二部分性能诊断 表 3.3.3 – 续表 名称 类型 描述 stddev_exec_time double precision 在该语句中花费时间的总体标准偏差，以毫秒计（该字段在 V9R5 里面为：stddev_time） rows bigint 该语句检索或影响的行总数 shared_blks_hit bigint 该语句造成的共享块缓冲命中总数 shared_blks_read bigint 该语句读取的共享块的总数 shared_blks_dirtied bigint 该语句弄脏的共享块的总数 shared_blks_written bigint 该语句写入的共享块的总数 local_blks_hit bigint 该语句造成的本地块缓冲命中总数 local_blks_read bigint 该语句读取的本地块的总数 local_blks_dirtied bigint 该语句弄脏的本地块的总数 local_blks_written bigint 该语句写入的本地块的总数 temp_blks_read bigint 该语句读取的临时块的总数 temp_blks_written bigint 该语句写入的临时块的总数 blk_read_time double precision 该语句花在读取块上的总时间，以毫秒计 blk_write_time double precision 该语句花在写入块上的总时间，以毫秒计 出于安全原因，非超级用户不允许查看其他用户执行的 SQL 文本或 queryid。但是，如果视图已安装在数据库 中，则他们可以查看统计信息。 只要有计划的查询（即 SELECT，INSERT，UPDATE 和 DELETE）根据内部哈希计算具有相同的查询结构， 它们就会组合到单个 sys_stat_statements 条目中。通常，如果两个查询在语义上等效，则两个查询在此意义上相 同，只是出现在查询中的文字常量的值除外。但是，将严格根据实用程序命令（即所有其他命令）的文本查询字符串 进行比较。 当为了将查询与其他查询匹配而忽略了常量的值时，该常量将替换为？。在 sys_stat_statements 显示中，查询 文本的其余部分是第一个查询的文本，该查询具有与 sys_stat_statements 条目关联的特定 queryid 哈希值。 在某些情况下，文本明显不同的查询可能会合并到一个 sys_stat_statements 条目中。通常，这仅会在语义上等 效的查询中发生，但是散列冲突会导致不相关的查询合并到一个条目中的可能性很小。（但是，这对于属于不同用户 或数据库的查询不会发生。） UTILITY 语句中的 CALL 语句与有计划的查询（即 SELECT，INSERT，UPDATE 和 DELETE）具有类似的 获取 queryid 和常量替换逻辑。需要说明：PLSQL/PLPGSQL 由于结果集缓存，执行 sys_stat_statements_reset() 后，部分 CALL proc(xxx); 语句中的常量无法替换。 45 第 3 章 第二部分性能诊断 该组件的主要配置参数： • sys_stat_statements.max（整数） sys_stat_statements.max 是模块跟踪的最大语句数（即 sys_stat_statements 视图中的最大行数）。如果观察 到的语句不同，则将丢弃关于执行最少的语句的信息。默认值为 5000。只能在服务器启动时设置此参数。 • sys_stat_statements.track（枚举） sys_stat_statements.track 控制模块计算哪些语句。指定 top 以跟踪顶级语句（由客户端直接发出的语 句），全部也可以跟踪嵌套语句（例如在函数内调用的语句），或者不指定以禁用语句统计信息收集。默认值 为’none’。仅超级用户可以更改此设置。 注意：该参数在 V9R5 里默认值为’top’，如果需要使用 sys_stat_statements 功能，需要将其配置为’top’。 • sys_stat_statements.track_utility（boolean） sys_stat_statements.track_utility 控制模块是否跟踪实用程序命令。实用程序命令是除 SELECT，INSERT， UPDATE 和 DELETE 之外的所有命令。默认值为 on。仅超级用户可以更改此设置。 • sys_stat_statements.save（boolean） sys_stat_statements.save 指定是否在服务器关闭时保存语句统计信息。如果关闭，则统计信息不会在关闭时保 存，也不会在服务器启动时重新加载。默认值为 on。只能在 kingbase.conf 文件或服务器命令行中设置此参数。 该模块需要与 sys_stat_statements.max 成比例的附加共享内存。请注意，即使 sys_stat_statements.track 设置 为 none，只要加载模块，就会消耗此内存。 这些参数必须在 kingbase.conf 中设置。典型用法可能是： shared_preload_libraries ='liboracle_parser, sys_stat_statements' sys_stat_statements.max = 10000 sys_stat_statements.track = 'top' 查询和管理函数： 1、sys_stat_statements(showtext boolean) 功能：查询 TOP SQL 列表 参数：showtext boolean：是否显示 SQL 字符串 返回值：TOP SQL 列表 2、sys_stat_statements_all(showtext boolean) 功能：查询全部 SQL 列表 参数：showtext boolean：是否显示 SQL 字符串 返回值：全部 SQL 列表 3、sys_stat_statements_limit_len(showtext boolean, limit_query_len int) 功能：查询 TOP SQL 列表，可以限制返回的 SQL 字符串最大长度。该函数为 1.9 版本新增。 46 第 3 章 第二部分性能诊断 参数：showtext boolean：是否显示 SQL 字符串 limit_query_len：返回 SQL 字符串最大长度，超过该长度的字符串被截断 返回值：TOP SQL 列表 4、sys_stat_statements_reset() 功能：重置（清空）TOP SQL 列表 3.3.1.2 使用 kbbadger 工具 Kbbadger 可以分析大型的日志文件。当日志文件足够长时，kbbadger 可以自动检测日志文件的格式（syslog， stderr，csvlog 或 jsonlog）。 由 kbbadger 生成的所有图表都是可缩放的并且可单独下载为 PNG 文件。另外，在生成的报告中 SQL 查询将突 出显示。 Kbbadger 生成的报告中包含的关于 SQL 查询的信息有： • 总体统计 • 占用时间最多的查询 • 最常见的查询 • 最常见的错误 • 查询时间直方图 • 会话时间直方图 • 参与顶级查询的用户 • 涉及顶级查询的应用程序 • 生成最多取消的查询 • 大多数查询已取消 • 最耗时的准备/绑定查询 报告也会提供每小时的统计图表，其内容包括： • SQL 查询统计信息 • 临时文件统计 • 检查点统计 • 自动 vacuum 和自动分析统计 • 已取消查询 • 错误事件（死机、致命、错误和警告） • 错误的类分布 47 第 3 章 第二部分性能诊断 在 kbbadger 的使用过程中，可以使用命令行选项来启用并行处理模式从而加快日志解析速度，使用-j 命令行选 项来指定解析使用的 CPU 核心数，使用-J 命令行选项来并行解析的文件数，两个选项可以同时使用。另外，可以使 用-A 命令行选项来调整直方图的粒度。默认情况下，它们将报告每小时发生的每个主要查询/错误的平均值。 Kbbadger 使用方法 1. 配置 kingbase.conf 在开始之前，必须在 kingbase.conf 中启用并设置一些配置指令。 您必须首先启用 SQL 查询日志记录才能进行解析： log_min_duration_statement = 0 在这里，每条语句都会被记录，在繁忙的服务器上，您可能需要增加此值以仅记录持续时间较长的查询。请注 意，不要启用 log_statement，因为 kbBadger 不会解析它的日志格式并且如果您将 log_statement 设置为’all’，则 不会通过 log_min_duration_statement 指令记录任何内容。如果要保证系统性能，也可以使用 log_duration 来仅提 供有关持续时间和查询数量的报告。有关更多信息，请参见下一章。 kbBadger 支持任何设置到 kingbase.conf 文件的 log_line_prefix 中的自定义格式，只要它指定时间转义序列（％ t，％m 或％n）和与进程相关的转义序列（％p 或％c）。 例如： --使用“ stderr”日志格式，log_line_prefix 必须至少为： log_line_prefix = '%t [%p]: ' --日志行前缀可以添加用户，数据库名称，应用程序名称和客户端 IP 地址，如下所示： log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h ' --或用于 syslog 日志文件格式： log_line_prefix = 'user=%u,db=%d,app=%a,client=%h ' --stderr 输出的日志行前缀也可以是： log_line_prefix = '%t [%p]: db=%d,user=%u,app=%a,client=%h ' --或用于 syslog 输出： log_line_prefix = 'db=%d,user=%u,app=%a,client=%h ' --您也可以在 kingbase.conf 中启用其他参数，以从日志文件中获取更多信息： log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_temp_files = 0 log_autovacuum_min_duration = 0 log_error_verbosity = default 48 第 3 章 第二部分性能诊断 注意: 数据库日志中的内容必须为英文，否则 kbbadger 可能无法解析日志中的内容： lc_messages='en_US.UTF-8' 2. 使用 kbBadger 生成统计信息 kbbadger /var/log/kingbase.log kbbadger /var/log/kingbase.log.2.gz /var/log/kingbase.log.1.gz /var/log/kingbase.log kbbadger /var/log/kingbase/kingbase-2012-05-* kbbadger --exclude-query="^(COPY|COMMIT)" /var/log/kingbase.log kbbadger -b "2012-06-25 10:56:11" -e "2012-06-25 10:59:11" /var/log/kingbase.log cat /var/log/kingbase.log | kbbadger kbbadger --prefix '%t [%p]: user=%u,db=%d,client=%h ' /sys_log/kingbase-2012-08-21* kbbadger --prefix '%m %u@%d %p %r %a : ' /sys_log/kingbase.log # Log line prefix with syslog log output kbbadger --prefix 'user=%u,db=%d,client=%h,appname=%a' /sys_log/kingbase-2012-08-21* # Use my 8 CPUs to parse my 10GB file faster, much faster kbbadger -j 8 /sys_log/kingbase-10.1-main.log 详细请参见–help 3. 举例说明 $ ./kbbadger ./ sys_log/kingbase-2019-10-26_1544* -f stderr -J 12 -j 32 [========================>] Parsed 283210387 bytes of 283210387 (100.00%), queries: 327810, events: 51 LOG: Ok, generating html report... 生成的统计信息效果图如下： 49 第 3 章 第二部分性能诊断 50 第 3 章 第二部分性能诊断 等待事件分析 3.3.2 为了了解数据库当前的运行时状态，管理员可以查看系统视图 sys_stat_activity 来进行查看。该视图能够知道 数据库目前正在发生写什么：比如有多少个连接，客户端的情况，每个连接的状态，每个连接上的等待事件等。 注意: 当前执行的查询语句和等待事件状态需要在开启 track_activities 参数的情况下才可以查看。开启该参数会带 来一定的性能损耗。 3.3.2.1 活跃会话连接 sys_stat_activity 视图结构如下： 表 3.3.4: 视图结构 字段名 类型 说明 datid oid 数据库 OID datname name 数据库名称 见续表 51 第 3 章 第二部分性能诊断 表 3.3.4 – 续表 字段名 类型 说明 pid integer 服务于这个连接的进程 ID usesysid oid 用户 ID usename name 用户名 application_name text 应用名称 client_addr inet 客户端地址 client_hostname text 客户端主机名 client_port integer 客户端端口号 backend_start timestamp with time zone 该连接的启动时间 xact_start timestamp with time zone 当前事务开始时间 query_start timestamp with time zone 当前查询开始时间 state_change timestamp with time zone 当前状态改变时间 wait_event_type text 当前等待事件的类型 wait_event text 当前等待事件 state text 当前的状态 backend_xid xid 这个后端的顶层事务标识符 backend_xmin xid 这个进程的当前事务被启动的时间 query text 当前查询语句 backend_type text 后端进程的类型 其中，state 为当前连接的状态，其可能的值主要有： • active：后端正在执行一个查询。 • idle：后端正在等待一个新的客户端命令。 • idle in transaction：后端在一个事务中，但是当前没有正在执行一个查询。 • idle in transaction (aborted)：这个状态与 idle in transaction 相似，不过在该事务中的一个语句导致了一个错 误。 • fastpath function call：后端正在执行一个 fast-path 函数。 • disabled：如果在这个后端中 track_activities 被禁用，则报告这个状态。 52 第 3 章 第二部分性能诊断 注意: 如果一个查询或者事务执行的时间过长而一直没有结束，很有可能会拖累整个系统的性能表现，需要具体分析 问题的原因并采取合适的办法。 3.3.2.2 等待事件 等待事件是数据库内部记录的一种统计信息，该信息出现表明数据库的服务进程必须在等待事件完成后才能继续 处理。等待事件揭示了可能影响性能的各种问题症状，比如缓冲区中争用、锁争用等。 sys_stat_activity 里记录的等待事件是瞬时信息，没有对等待事件的时间进行累计，所以量化等待事件上存在一 些问题。用户可以考虑多次人工采样来收集等待事件信息，如果在等待的连接较多，则需要等待事件对系统性能造成 了较大影响，需要做具体分析。 注意: 当前执行的查询语句和等待事件状态需要在开启 track_activities 参数的情况下才可以查看。开启该参数会带 来一定的性能损耗。 sys_stat_activity 视图中，wait_event_type 和 wait_event 字段记录了等待时间相关的内容。当一个会话处于 等待状态时，wait_event 与 wait_event_type 两列非空，表示会话正在等待的事件和等待事件类型。 wait_event_type 的可能值有： • LightLock：轻量级锁 • Lock：重量级锁 • BufferPin：数据缓冲区等待 • Activity：后台辅助进程活动等待 • Client：客户端等待 • Extension：扩展插件等待 • IPC：进程间通讯等待 • TimeOut：超时等待 • IO：IO 等待 wait_event 为等待事件名称，包括： 等待事件类型 等待事件名称 描述 LightLock ShmemIndexLock 正等待在共享内存中查找或者分配空间。 OidGenLock 正等待分配或者赋予一个 OID。 见续表 53 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 XidGenLock 正等待分配或者赋予一个事务 ID。 ProcArrayLock 正等待在事务结尾得到一个快照或者清除事务 ID。 SInvalReadLock 正等待从共享无效消息队列中检索或者移除消息。 SInvalWriteLock 正等待在共享无效消息队列中增加一个消息。 WALBufMappingLock 正等待在 WAL 缓冲区中替换一个页面。 WALWriteLock 正等待 WAL 缓冲区被写入到磁盘。 ControlFileLock 正等待读取或者更新控制文件或创建一个新的 WAL 文件。 CheckpointLock 正等待执行检查点。 CLogControlLock 正等待读取或者更新事务状态。 SubtransControlLock 正等待读取或者更新子事务信息。 MultiXactGenLock 正等待读取或者更新共享多事务状态。 MultiXactOffsetControlLock 正等待读取或者更新多事务偏移映射。 MultiXactMemberControlLock 正等待读取或者更新多事务成员映射。 RelCacheInitLock 正等待读取或者写入关系缓冲区初始化文件。 CheckpointerCommLock 正等待管理 fsync 请求。 TwoPhaseStateLock 正等待读取或者更新预备事务的状态。 TablespaceCreateLock 正等待创建或者删除表空间。 BtreeVacuumLock 正等待读取或者更新一个 B-树索引的 vacuum 相关的信息。 AddinShmemInitLock 正等待管理共享内存中的空间分配。 AutovacuumLock 自动清理工作者或者启动器正等待更新或者读取自动清理工作 者的当前状态。 AutovacuumScheduleLock 正等待确认选中进行清理的表仍需要清理。 SyncScanLock 正等待为同步扫描得到一个表上扫描的开始位置。 RelationMappingLock 正等待更新用来存储目录到文件节点映射的关系映射文件。 AsyncCtlLock 正等待读取或者更新共享通知状态。 见续表 54 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 AsyncQueueLock 正等待读取或者更新通知消息。 SerializableXactHashLock 正等待检索或者存储有关可序列化事务的信息。 SerializableFinishedListLock 正等待访问已结束可序列化事务的列表。 SerializablePredicateLockListLock正等待在由可序列化事务持有的所列表上执行一个操作。 OldSerXidLock 正等待读取或者记录冲突的可序列化事务。 SyncRepLock 正等待读取或者更新有关同步复制的信息。 BackgroundWorkerLock 正等待读取或者更新后台工作者状态。 DynamicSharedMemoryControlLock 正等待读取或者更新动态共享内存状态。 AutoFileLock 正等待更新 kingbase.auto.conf 文件。 ReplicationSlotAllocationLock 正等待分配或者释放一个复制槽。 ReplicationSlotControlLock 正等待读取或者更新复制槽状态。 CommitTsControlLock 正等待读取或者更新事务提交时间戳。 CommitTsLock 正等待读取或者更新事务时间戳的最新设置值。 ReplicationOriginLock 正等待设置、删除或者使用复制源头。 MultiXactTruncationLock 正等待读取或者阶段多事务信息。 OldSnapshotTimeMapLock 正等待读取或者更新旧的快照控制信息。 LogicalRepWorkerLock 正等待逻辑复制工作者上的动作完成。 CLogTruncationLock 等待执行 txid_st atus 或更新可用的最新事务 id。 clog 正在等地 clog (事务状态) 缓冲区上的 I/O。 commit_timestamp 正等待提交时间戳缓冲区上的 I/O。 subtrans 正等待子事务缓冲区上的 I/O。 multixact_offset 正等待多事务偏移缓冲区上的 I/O。 multixact_member 正等待多事务成员缓冲区上的 I/O。 async 正等待 async（通知）缓冲区上的 I/O。 见续表 55 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 oldserxid 正等待 oldserxid 缓冲区上的 I/O。 wal_insert 正等待把 WAL 插入到一个内存缓冲区。 buffer_content 正等待读取或者写入内存中的一个数据页。 buffer_io 正等待一个数据页面上的 I/O。 replication_origin 正等待读取或者更新复制进度。 replication_slot_io 正等待一个复制槽上的 I/O。 proc 正等待读取或者更新 fast-path 锁信息。 buffer_mapping 正等待把一个数据块与缓冲池中的一个缓冲区关联。 lock_manager 正等待增加或者检查用于后端的锁，或者正等待加入或者退出 一个锁定组（并行查询使用）。 predicate_lock_manager 正等待增加或者检查谓词锁信息。 serializable_xact 等待在并行查询中对一个可序列化事务执行操作。 parallel_query_dsa 正等待并行查询动态共享内存分配锁。 tbm 正等待 TBM 共享迭代器锁。 parallel_append 在 Parallel Append 计划执行期间等待选择下一个子计划。 parallel_hash_join 在 Parallel Hash 计划执行期间等待分配或交换一块内存或者更 新计数器。 Lock relation 正等待获得一个关系上的锁。 extend 正等待扩展一个关系。 page 正等待获得一个关系上的页面的锁。 tuple 正等待获得一个元组上的锁。 transactionid 正等待一个事务结束。 virtualxid 正等待获得一个虚拟 xid 锁。 speculative token 正等待获取一个 speculative insertion lock。 object 正等待获得一个非关系数据库对象上的锁。 见续表 56 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 userlock 正等待获得一个用户锁。 advisory 正等待获得一个咨询用户锁。 BufferPin BufferPin 正等待在一个缓冲区上加 pin。 Activity ArchiverMain 正在归档进程的主循环中等待。 AutoVacuumMain 正在 autovacu um 启动器进程的主循环中等待。 BgWriterHibernate 正在后台写入器进程中等待，休眠中。 BgWriterMain 正在后台写入器进程的后台工作者的主循环中等待。 CheckpointerMain 正在检查点进程的主循环中等待。 LogicalApplyMain 正在逻辑应用进程的主循环中等待。 LogicalLauncherMain 正在逻辑启动器进程的主循环中等待。 PgStatMain 正在统计收集器进程的主循环中等待。 RecoveryWalAll 在 恢 复 时 等 待 来 自 于 任 意 类 型 来 源 （本 地、 归 档 或 流） 的 WAL。 Client RecoveryWalStream 在恢复时等待来自于一个流的 WAL。 SysLoggerMain 正在系统日志进程的主循环中等待。 WalReceiverMain 正在 W AL 接收器进程的主循环中等待。 WalSenderMain 正在 W AL 发送器进程的主循环中等待。 WalWriterMain 正在 W AL 写入器进程的主循环中等待。 KshMain 在 KSH 进程的主循环中等待。 KwrMain 在 KWR 进程的主循环中等待。 ClientRead 正等待从客户端读取数据。 ClientWrite 正等待向客户端写入数据。 LibPQWalReceiverConnect 正在 WAL 接收器中等待建立与远程服务器的连接。 LibPQWalReceiverReceive 正在 WAL 接收器中等待从远程服务器接收数据。 SSLOpenServer 正在尝试连接期间等待 SSL。 见续表 57 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 WalReceiverWaitStart 正等待 start up 进程发送流复制的初始数据。 WalSenderWaitForWAL 正在 WA L 发送器进程中等待 WAL 被刷写。 WalSenderWriteData 在 WAL 发送器进程中处理来自 WAL 接收器的回复时等待任意 活动。 Extension Extension 正在一个扩展中等待。 IPC BgWorkerShutdown 正等待后台工作者关闭。 BgWorkerStartup 正等待后台工作者启动。 BtreePage 正等待继续并行 B-树扫描所需的页号变得可用。 CheckpointDone 等待检查点完成。 CheckpointStart 等待检查点开始。 ClogGroupUpdate 正等待组领袖在事务结束时更新事务状态。 ExecuteGather 在执行 Gather 节点时等待来自子进程的活动。 Hash/Batch/Allocating 正等待一个选出的 Parallel Hash 参与者分配哈希表。 Hash/Batch/Electing 正在选出一个 Parallel Hash 参与者来分配一个哈希表。 Hash/Batch/Loading 正等待其他 Parallel Hash 参与者完成装载哈希表。 Hash/Build/Allocating 正等待一个选出的 Parallel Hash 参与者分配初始哈希表。 Hash/Build/Electing 正在选出一个 Parallel Hash 参与者以分配初始哈希表。 Hash/Build/HashingInner 正等待其他 Parallel Hash 参与者完成对内关系的哈希操作。 Hash/Build/HashingOuter 正等待其他 Parallel Hash 参与者完成对外关系的哈希操作。 Hash/ GrowBatches/ Allocat- 正等待一个选出的 Parallel Hash 参与者分配更多批次。 ing Hash/GrowBatches/Deciding 正在选出一个 Parallel Ha sh 参与者决定未来的批次增长。 Hash/GrowBatches/Electing 正在选出一个 Parallel Hash 参与者分配更多批次。 Hash/GrowBatches/Finishing 正在等待一个选出的 Parallel Ha sh 参与者决定未来的批次增 长。 见续表 58 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 Hash/ GrowBatches/ Reparti- 正等待其他 Parallel Hash 参与者完成重新分区。 tioning Hash/ GrowBuckets/ Allocat- 正等待一个选出的 Parallel Hash 参与者完成更多桶的分配。 ing Timeout Hash/GrowBuckets/Electing 正在选出一个 Parallel Hash 参与者分配更多桶。 Hash/ GrowBuckets/ Reinsert- 正等待其他 Parallel Hash 参与者完成将元组插入到新桶的操 ing 作。 LogicalSyncData 正等待逻辑复制的远程服务器发送用于初始表同步的数据。 LogicalSyncStateChange 正等待逻辑复制的远程服务器更改状态。 MessageQueueInternal 正等待其他进程被挂接到共享消息队列。 MessageQueuePutMessage 正等待把一个协议消息写到一个共享消息队列。 MessageQueueReceive 正等待从一个共享消息队列接收字节。 MessageQueueSend 正等待向一个共享消息队列中发送字节。 ParallelBitmapScan 正等待并行位图扫描被初始化。 ParallelCreateIndexScan 正等待并行 CREATE INDEX 工作者完成堆扫描。 ParallelFinish 正等待并行工作者完成计算。 ProcArrayGroupUpdate 正等待组领袖在事务结束时清除事务 ID。 Promote 等待备用节点升级。 ReplicationOriginDrop 正等待一个复制源头变得不活跃以便被删除。 ReplicationSlotDrop 正等待一个复制槽变得不活跃以便被删除。 SafeSnapshot 正等待一个用于 READ ONLY DEFERRABLE 事务的快照。 SyncRep 正在同步复制期间等待来自远程服务器的确认。 BaseBackupThrottle 当有限流活动时在基础备份期间等待。 PgSleep 正在调用 sys_sleep 的进程中等待。 RecoveryApplyDelay 在恢复时等待应用 WAL，因为它被延迟了。 见续表 59 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 IO BufFileRead 正等待从一个缓存的文件中读取。 BufFileWrite 正等待向一个缓存的文件中写入。 ControlFileRead 正等待从控制文件中读取。 ControlFileSync 正等待控制文件到达稳定存储。 ControlFileSyncUpdate 正等待对控制文件的更新到达稳定存储。 ControlFileWrite 正等待一个对控制文件的写入。 ControlFileWriteUpdate 正等待一个写操作更新控制文件。 CopyFileRead 正在文件拷贝操作期间等待一个读操作。 CopyFileWrite 正在文件拷贝操作期间等待一个写操作。 DataFileExtend 正等待一个关系数据文件被扩充。 DataFileFlush 正等待一个关系数据文件到达稳定存储。 DataFileImmediateSync 正等待一个关系数据文件的立即同步到达稳定存储。 DataFilePrefetch 正等待从一个关系数据文件中的一次异步预取。 DataFileRead 正等待一次对一个关系数据文件的读操作。 DataFileSync 正等待对一个关系数据文件的更改到达稳定存储。 DataFileTruncate 正等待一个关系数据文件被截断。 DataFileWrite 正等待一次对一个关系数据文件的写操作。 DSMFillZeroWrite 等待向一个动态共享内存备份文件中写零字节。 LockFileAddToDataDirRead 在向数据目录锁文件中增加一行时等待一个读操作。 LockFileAddToDataDirSync 在向数据目录锁文件中增加一行时等待数据到达稳定存储。 LockFileAddToDataDirWrite 在向数据目录锁文件中增加一行时等待一个写操作。 LockFileCreateRead 在创建数据目录锁文件期间等待读取。 LockFileCreateSync 在创建数据目录锁文件期间等待数据到达稳定存储。 LockFileCreateWrite 在创建数据目录锁文件期间等待一个写操作。 见续表 60 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 LockFileReCheckDataDirRead 在重新检查数据目录锁文件的过程中等待一个读操作。 LogicalRewriteCheckpointSync 在一个检查点期间等待逻辑重写映射到达稳定存储。 LogicalRewriteMappingSync 在一次逻辑重写期间等待映射数据到达稳定存储。 LogicalRewriteMappingWrite 在一次逻辑重写期间等待对映射数据的写操作。 LogicalRewriteSync 正等待逻辑重写映射到达稳定存储。 LogicalRewriteWrite 正等待对逻辑重写映射的写操作。 RelationMapRead 正等待对关系映射文件的读操作。 RelationMapSync 正等待关系映射文件到达稳定存储。 RelationMapWrite 正等待对关系映射文件的写操作。 ReorderBufferRead 在重排序缓冲区管理期间等待一个读操作。 ReorderBufferWrite 在重排序缓冲区管理期间等待一个写操作。 ReorderLogicalMappingRead 在重排序缓冲区管理期间等待对一个逻辑映射的读操作。 ReplicationSlotRead 正等待对一个复制槽控制文件的读操作。 ReplicationSlotRestoreSync 在把一个复制槽控制文件恢复到内存的过程中等待它到达稳定 存储。 ReplicationSlotSync 正等待一个复制槽控制文件到达稳定存储。 ReplicationSlotWrite 正等待对一个复制槽控制文件的写操作。 SLRUFlushSync 在检查点或者数据库关闭期间等待 SLRU 数据到达稳定存储。 SLRURead 正等待对一个 SLRU 页面的读操作。 SLRUSync 正等待 SLRU 数据在一个页面写之后到达稳定存储。 SLRUWrite 正等待一个 SLRU 页面上的写操作。 SnapbuildRead 正等待一个序列化历史目录快照的读操作。 SnapbuildSync 正等待一个序列化历史目录快照到达稳定存储。 SnapbuildWrite 正等待一个序列化历史目录快照的写操作。 StatFileRead 等待读统计文件完成。 见续表 61 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 StatFileWrite 等待读统计文件完成。 AuditFileWrite 等待写审计文件完成。 TimelineHistoryFileSync 正等待一个通过流复制接收到的时间线历史文件到达稳定存 储。 TimelineHistoryFileWrite 正等待一个通过流复制接收到的时间线历史文件的读操作。 TimelineHistoryRead 正等待一个时间线历史文件上的读操作。 TimelineHistorySync 正等待一个新创建的时间线历史文件达到稳定存储。 TimelineHistoryWrite 正等待一个新创建的时间线历史文件上的写操作。 TwophaseFileRead 正等待一个两阶段状态文件的读操作。 TwophaseFileSync 正等待一个两阶段状态文件到达稳定存储。 TwophaseFileWrite 正等待一个两阶段状态文件的写操作。 WALBootstrapSync 在自举期间等待 WAL 到达稳定存储。 WALBootstrapWrite 在自举期间等待一个 WAL 页面的写操作。 WALCopyRead 在通过拷贝一个已有 WAL 段创建一个新的 WAL 段时等待一个 读操作。 WALCopySync 正等待一个通过拷贝已有 WAL 段创建的新 WAL 段到达稳定存 储。 WALCopyWrite 在通过拷贝一个已有 WAL 段创建一个新的 WAL 段时等待一个 写操作。 WALInitSync 正等待一个新初始化的 WAL 文件到达稳定存储。 WALInitWrite 在初始化一个新的 WAL 文件期间等待一个写操作。 WALRead 正等待一次对一个 WAL 文件的读操作。 WALSenderTimelineHistoryRead在 walsender 的时间线命令期间等待对一个时间线历史文件的读 操作。 WALSync 等待 WAL 文件到达持久的存储空间。 WALSyncMethodAssign 在指派 WAL 同步方法时等待数据到达稳定存储。 见续表 62 第 3 章 第二部分性能诊断 表 3.3.5 – 续表 等待事件类型 等待事件名称 描述 WALWrite 正等待一次对一个 WAL 文件的写操作。 关于等待事件，可以使用下面例子中的 SQL 语句对其进行查询： SELECT wait_event_type, wait_event, state, count(*) FROM sys_stat_activity group by wait_event_type, wait_ event, state; 如果某个活跃连接经常出现长时间的等待事件，则需要对其进行进一步的分析。 例如：我们在跑高并发的 tpcc 时，可能看到如下等待事件： SELECT wait_event_type, wait_event, state, count(*) FROM sys_stat_activity group by wait_event_type, wait_ event, state order by 4 desc; 63 第 3 章 第二部分性能诊断 采样几次以后，发现等待事件 DataFileRead，DataFileWrite 一直过高。 DataFileRead、DataFileWrite 是 等 待 读、 写 一 个 关 系 表 的 等 待 事 件， 说 明 IO 很 频 繁， 可 以 通 过 调 大 shared_buffers 让更多的数据页缓存在共享内存里来减少 IO。 3.3.3 I/O 优化点分析 3.3.3.1 共享内存命中率分析 系统表 sys_statio_user_tables 和 sys_statio_user_indexes 从 I/O 的角度记录用户表和用户索引的信息。如果 命中率过低，则可以考虑加大 shared_buffers。 系统视图 sys_stat_wal_buffer 统计了 WAL 日志缓冲区实时的运作情况，对 WAL BUFFER 做好监控和管理， 合理调整 wal_buffers 参数大小。日志缓冲区使用率高且写速率较低时，可考虑加大 wal_buffers 值。日志缓冲区缓 冲区利用率处于较低值，此时造成了资源浪费，则考虑缩小 wal_buffers。 1、sys_statio_user_tables 视图内容：当前数据库中用户表上的 I/O 统计信息。 64 第 3 章 第二部分性能诊断 列名 类型 描述 relid oid 该表的 oid schemaname name 表空间名 relname name 表名 heap_blks_read bigint 从该表读取的磁盘块数 heap_blks_hit bigint 该表中的缓冲区命中数 idx_blks_read bigint 从该表上所有索引读取的磁盘块数 idx_blks_hit bigint 该表上所有索引中的缓冲区命中数 toast_blks_read bigint 从此表的 TOAST 表中读取的磁盘块数（如果有） toast_blks_hit bigint 该表的 TOAST 表中的缓冲区命中数（如果有） tidx_blks_read bigint 从此表的 TOAST 表索引中读取的磁盘块数（如果有） tidx_blks_hit bigint 该表的 TOAST 表索引中的缓冲区命中数（如果有） 2、sys_statio_user_indexes 视图内容：当前数据库中用户表上的索引上的 I/O 统计信息。 列名 类型 描述 relid oid 拥有该索引表的 oid indexrelid oid 该索引的 oid schemaname name 表空间名 relname name 拥有该索引表的名字 indexrelname name 索引名 idx_blks_read bigint 从该索引读取的磁盘块数 idx_blks_hit bigint 该索引中的缓冲区命中数 3、sys_stat_wal_buffer 视图内容：统计了 WAL BUFFER 实时的运作情况。 65 第 3 章 第二部分性能诊断 表 3.3.6: sys_stat_wal_buffer 列名 类型 描述 name text WAL 日志缓冲区名称，固定显示为“WAL BUFFER”。 bytes bigint WAL BUFFER 缓存空间总长度，此字段值与 Kingbase.conf 配置文件 中 wal_buffers 参数关联，单位为字节。 copied_to text 后台进程最近写入 WAL BUFFER 的 WAL 日志在 WAL BUFFER 中 的偏移。注意的是，WAL BUFFER 是环形的数据结构，此字段值会 在区间 [1, bytes] 之间循环取值。 copied_to_lsn sys_lsn 后台进程最近写入 WAL BUFFER 的 WAL 日志的结束 LSN。此字段 值在 KES 数据库运行过程中递增。 coping_data_len bigint 查询时刻后台进程正在写入 WAL BUFFER 的 WAL 日志总长度。 written_to text 最近一个从 WAL BUFFER 写入磁盘的 WAL 日志在 WAL BUFFER 中的偏移。注意的是，WAL BUFFER 是环形的数据结构，此字段值 会在区间 [1, bytes] 之间循环取值。 written_to_lsn sys_lsn 最近一个从 WAL BUFFER 写入磁盘的 WAL 日志的 LSN。此字段值 在 KES 数据库运行过程中递增。 writing_data_len bigint 查询时刻正在从 WAL BUFFER 向磁盘刷写的 WAL 日志总长度。 utilization_rate text WAL BUFFER 缓冲区使用率，此字段值表示当前 WAL BUFFER 中 待落盘的 WAL 日志总长度与整个 WAL BUFFER 缓冲区总长度之间 的比值。如果此字段值长时间接近 100%，那么需要从两方面考虑来改 善现状： （1）检查 WAL 日志文件所在磁盘 IO 状况。如果发现磁盘 IO 是性 能瓶颈，那么就应该考虑使用磁盘 IO 性能更好的磁盘来存储 WAL 日 志文件。 （2）如果 WAL 日志文件所在磁盘 IO 不是性能瓶颈，那么可尝试适 当增加 WAL BUFFER 的尺寸来提升 WAL 日志的写性能。 write_rate text 两次相邻查询的时间间隔内，从 WAL BUFFER 向磁盘的平均 WAL 日志刷写速率。如果需要更精确的 write rate 值，那么可以将当前视 图的查询时间间隔调小。 3.3.3.2 私有内存使用分析 当一些 Query 的操作，使用的内存量大于指定阈值时，就会触发使用临时文件。包括排序，DSTINCT， MERGE JOIN，HASH JOIN，哈希聚合，分组聚合，递归查询等操作。 66 第 3 章 第二部分性能诊断 要统计数据库运行中是否触发了临时文件，可以通过修改参数 log_temp_files（整数）记录 tempfile 统计到日志 中。 log_temp_files： 控制临时文件名和大小的日志记录。可以为排序，哈希和临时查询结果创建临时文件。删除每个临时文件后，都 会为其创建一个日志条目。零值记录所有临时文件信息，而正值仅记录大小大于或等于指定千字节数的文件。默认设 置为-1，它将禁用此类日志记录。仅超级用户可以更改此设置。 例如： ALTER SYSTEM SET log_temp_files TO 0; SELECT SYS_RELOAD_CONF(); 执行查询进行 order by： EXPLAIN ANALYZE SELECT * FROM bmsql_stock order by s_dist_10; 日志中输出临时文件信息 67 第 3 章 第二部分性能诊断 3.3.4 锁优化点分析 视图 sys_locks 提供对有关数据库服务器内活动进程所持有的锁的信息的访问。 Name Type Description locktype text 可锁对象的类型：relation, extend, page, tuple,transactionid, virtualxid, object, userlock 或者 advisory database oid 锁目标存在的数据库的 OID，如果目标是一个共享对象则为 0，如 果目标是一个事务 ID 则为空 relation oid 作为锁目标的关系的 OID，如果目标不是一个关系或者只是关系的 一部分则此列为空 page integer 作为锁目标的页在关系中的页号，如果目标不是一个关系页或元组 则此列为空 tuple smallint 作为锁目标的元组在页中的元组号，如果目标不是一个元组则此列 为空 virtualxid text 作为锁目标的事务虚拟 ID，如果目标不是一个虚拟事务 ID 则此列 为空 transactionid xid 作为锁目标的事务 ID，如果目标不是一个事务 ID 则此列为空 ID classid oid 包含锁目标的系统目录的 OID，如果目标不是一个普通数据库对象 则此列为空 objid oid 锁目标在它的系统目录中的 OID，如果目标不是一个普通数据库对 象则为空 objsubid smallint 锁的目标列号（classid 和 objid 指表本身），如果目标是某种其他 普通数据库对象则此列为 0，如果目标不是一个普通数据库对象则 此列为空 virtualtransaction text 保持这个锁或者正在等待这个锁的事务的虚拟 ID pid integer 保持这个锁或者正在等待这个锁的服务器进程的 PID，如果此锁被 一个预备事务所持有则此列为空 mode text 此进程已持有或者希望持有的锁模式 granted boolean 如果锁已授予则为真，如果锁被等待则为假 fastpath boolean 如果锁通过快速路径获得则为真，通过主锁表获得则为假 sys_locks 每个活动的可锁定对象，请求的锁定模式和相关进程包含一行。因此，如果多个进程正在持有或等待 对其进行锁定，则同一可锁定对象可能会出现多次。但是，当前没有锁的对象将根本不会出现。 68 第 3 章 第二部分性能诊断 有几种不同类型的可锁定对象：整个关系（例如表），单个关系页面，单个元组关系，事务 ID（虚拟 ID 和永久 ID）以及通用数据库对象（由 OID 类和 OID 对象标识，以与 sys_description 或 sys_depend 相同的方式）。同样， 扩展关系的权利也表示为单独的可锁定对象。另外，可以对具有用户定义含义的数字进行“建议”锁定。 3.3.5 自动负载信息库 SYS_KWR SYS_KWR 是 KingbaseES 自动负载信息库（Kingbase Auto Workload Repertories）的简称，它通过周期性自 动记录性能统计相关的快照，分析出 KingbaseES 的操作系统运行环境、数据库时间组成、等待事件和 TOP SQL 等 性能指标，为数据库性能调优提供指导。 3.3.5.1 SYS_KWR 插件 KWR 以插件的形式存在于 KingbaseES 产品中，目前 V9 中的最新版本是 1.7。 如果开启了 KWR 自动快照功能，会自动在 kingbase 库上创建该插件。可以通过 SQL 语句在目标库上创建： CREATE EXTENSION sys_kwr; KWR 依赖 sys_stat_statements 插件（1.10 版本），该插件是内置插件，会在所有库上自动被创建。 以下是创建 KWR 插件后查到的信息： 69 第 3 章 第二部分性能诊断 3.3.5.2 基本原理 KWR 的基本原理：数据库实例运行过程中不断产生一些统计数据，比如对某个表的访问次数，数据页的内存命 中次数，某个等待事件发生的次数和总时间，SQL 语句的解析时间等，这些统计数据被一个叫做 KWR collector 的 后台性能监控进程周期性地（默认每小时）自动采集，存储到 KWR 快照库里面，这些快照默认保存 8 天，到期后那 些旧的快照被自动删除。 当出现性能问题的时候，可以通过指定时间段来查询相关快照列表，生成 KWR 报告，定位性能问题的根本原 因。 其他性能组件比如 KDDM，基于 KWR 快照提供自动建议。 3.3.5.3 快速生成报告 配置文件 kingbase.conf，开启统计开关，其中 shared_preload_libraries 根据实际需要设置即可，推荐参数如 下： 70 第 3 章 第二部分性能诊断 shared_preload_libraries = 'liboracle_parser, sys_kwr, sys_stat_statements' track_sql = on track_instance = on -- KWR 1.3 新增参数 track_wait_timing = on track_counts = on track_io_timing = on track_functions = 'all' sys_stat_statements.track = 'top' 重启服务器。通过 KSQL 连接，创建 KWR 插件，创建快照，执行 SQL 后再次创建快照，就可以生成 KWR 报 告了： CREATE EXTENSION sys_kwr; SELECT * FROM perf.create_snapshot(); -- 获得快照 1 CREATE TABLE IF NOT EXISTS t1(id int); -- 创建一个示例表 SELECT count(*) FROM t1; -- 执行一些 SQL SELECT * FROM perf.create_snapshot(); -- 获得快照 2 SELECT * FROM perf.kwr_report(1,2); -- 生成 TEXT 版本报告 SELECT * FROM perf.kwr_report(1,2, 'html'); -- 生成 HTML 版报告 说明：生成的 KWR 报告会自动保存到 DATA 目录下的 sys_log 子目录下。 推荐使用 HTML 格式，因为它更便于阅读： 可以使用以下 SQL 函数将生成的 html 文件保存到指定文件路径。 SELECT * FROM perf.kwr_report_to_file(1,2, 'html', '/home/kingbase/kwr.html'); 3.3.5.4 KWR 价值 KWR 通过自动采集操作系统和数据库实例的性能数据，将其存储为 KWR 快照，并依此来生成 KWR 报告为 DBA 性能调优提供参考，其价值包括： 71 第 3 章 第二部分性能诊断 • 自动采集操作系统统计信息，不需要额外的性能监控工具 • 感知数据库运行环境，排查数据库实例外部原因造成的性能问题 • 通过统一的 DB Time 模型，度量数据库关键活动耗时 • 通过 query ID 将 SQL 执行时间、等待时间和资源消耗关联起来，进行语句级分析 • 从多个维度（时间、IO、内存、锁、实例、库对象等）分析数据库实例的性能问题 • 自动生成快照，便于回溯之前发生的性能问题 • 为 KDDM 等自动诊断和建议提供基础数据 3.3.5.5 使用 SYS_KWR 3.3.5.5.1 配置 GUC 参数 KWR 依赖内核统计模块采集性能统计数据，建议开启以下 GUC 参数，否则 KWR 报告里会缺失部分内容： track_sql = on track_instance = on track_wait_timing = on track_counts = on track_io_timing = on track_functions = 'all' sys_kwr.track_objects = on -- KWR 1.4 新增参数 sys_kwr.track_os = on -- KWR 1.4 新增参数 几个参数的说明如下： • track_sql：统计 SQL 时间、SQL 等待事件、SQL IO，默认为 off，建议开启 • track_instance：统计实例级 IO、锁、关键活动，默认为 off，建议开启 • track_wait_timing：统计累积式等待事件的时间，默认为 on，建议开启 • track_counts：统计数据库活动，默认为 on • track_io_timing：统计 IO 耗时，默认为 off，建议开启 • track_functions：统计用户自定义函数使用情况，默认为’none’，建议’all’ KWR 依赖 sys_stat_statements 插件采集 TOP SQL 统计数据，建议开启以下 GUC 参数，否则会 提示警告，且部分报告（Top SQL）没有内容： sys_stat_statements.track = 'top' • sys_stat_statements.track: 设置 sys_stat_statements.track 控制哪个语句可以被该模块跟踪，声明’top’ 来跟 踪顶级（直接通过客户端发出）的语句，’all’ 跟踪嵌套的语句，’none’ 禁用语句状态收集。建议使用’top’。 • sys_kwr.track_objects: 统计数据库对象使用情况, 默认为 on, 建议开启 72 第 3 章 第二部分性能诊断 • sys_kwr.track_os: 统计系统数据，默认为 on，建议开启 KWR、KWR diff 相关参数： sys_kwr.enable = off sys_kwr.topn = 20 sys_kwr.history_days = 8 sys_kwr.interval = 60 sys_kwr.language = 'chinese' -- KWR 1.3 新增参数 • sys_kwr.enable：开启 kwr 自动快照默认为关 • sys_kwr.topn：显示 kwr 报告中排名前 n 条的信息，默认为 20，最少为 10，最多为 100 • sys_kwr.history_days：快照保留日期，默认为 8 天，最少 1 天，最多 1000 天 • sys_kwr.interval：自动快照间隔，默认 60 分钟，最短 10 分钟，最长 144000 分钟（100 天） • sys_kwr.language：KWR 报告、KWR diff 报告使用语言，默认为中文（chinese 或 chn），可选为英文 (english 或 eng) • sys_kwr.track_windows_os_info：Windows 平台下是否跟踪操作系统统计信息，默认为 on。开启该开关后， 每次连接 KWR 插件所在数据库会消耗大约 300 毫秒来初始化 Windows 下的 WMI 库。 注意: 1. 要打开自动快照功能，需要将 sys_kwr.enable 设置为 on 2. 任何情况下都可以执行：SELECT * FROM perf.create_snapshot(); 来手工创建快照 3. 自动快照会在 kingbase 库上创建 sys_kwr 插件 4. shared_preload_libraries 里至少包含：liboracle_parser, sys_kwr, sys_stat_statements 3.3.5.5.2 快照查询 快照的查询通过以下命令： SELECT * FROM perf.kwr_snapshots; 73 第 3 章 第二部分性能诊断 3.3.5.5.3 自动快照 只需要将 sys_kwr.enable 设置为 true（需要重启服务器），就开启了自动快照功能。 后台进程 kwr collector 每分钟检查最后一次快照的时间，跟当前的时间间隔是否大于 sys_kwr.interval。如果大 于则立刻创建新的快照，并更新最后快照时间。 如果本次自动快照没有创建成功，则在下一个检查周期（一分钟后）继续尝试创建快照。 3.3.5.6 手动快照 配置好相关的 GUC 参数后，执行 perf.create_snapshot() 创建手工快照，返回新的快照号，同时也在 perf.kwr_snapshots 里添加一条记录。 3.3.5.6.1 快照管理 一 般 情 况 下， 不 需 要 手 工 管 理 已 经 生 成 的 快 照，KWR 会 在 每 次 创 建 新 的 快 照 时， 自 动 删 除 已 经 超 过 sys_kwr.history_days 天的快照数据，避免快照数据过多导致性能问题。 删除部分快照： SELECT * FROM perf.drop_snapshots(1, 3); 清理全部快照： SELECT * FROM perf.reset_snapshots(); 3.3.5.6.2 生成 KWR 报告 生成 TEXT 格式的 KWR 报告： SELECT * FROM perf.kwr_report(1,2,'text'); 生成 HTML 格式的 KWR 报告： SELECT * FROM perf.kwr_report(1,2,'html'); 生成的报告自动保存到 data 目录的 sys_log 子目录下，可以通过图形化工具打开查看。 如果想将生成 KWR 报告保存到指定的磁盘目录下，用 kwr_report_to_file() 函数，第 4 个参数指定文件全路 径： SELECT * FROM perf.kwr_report_to_file(1,2,'html', '/home/kingbase/kwr_1_2.html'); 74 第 3 章 第二部分性能诊断 3.3.5.6.3 常见问题 1. KWR 目前无法在高可用备节点上创建快照： SELECT * FROM perf.create_snapshot(); 也无法在备节点上生成 KWR 报告： SELECT * FROM perf.kwr_report(1,2,'html'); 2. 如果别的会话正在当前库上创建快照（还没有完成），为了防止快照管理数据混乱，此时不允许创建快照，必 须等正在创建的快照完成： SELECT * FROM perf.create_snapshot(); 3. KWR 完整报告依赖三个重要统计开关：track_sql = on, track_instance = on, sys_stat_statements.track = ’top’，否则提示以下警告（KWR 1.2 版本会报错），且生成报告的时候，部分报告内容为空： SELECT * FROM perf.create_snapshot(); 75 第 3 章 第二部分性能诊断 4. 如果指定的快照不存在，生成报告时会报错： SELECT * FROM perf.kwr_report(1,2,'html'); 5. KWR 目前不能在 Windows 平台采集部分统计信息，报告中的内容会为空。 6. 创建快照时提示字符串拼接语法错误： SELECT * FROM perf.create_snapshot(); 解决：添加 liboracle_parser，因为 KWR SQL 里使用了字符串拼接语法 shared_preload_libraries = 'liboracle_parser, sys_kwr, sys_stat_statements' 7. 删除快照时最新的快照作为 baseline 不能被删除： select * from perf.drop_snapshots(3,5); 3.3.5.7 查看 SYS_KWR 报告 3.3.5.7.1 报告结构 KWR 报告主要由三部分组成： 1、报告头： 这部分主要是列出数据库实例的版本、运行环境和快照信息。 2、报告摘要： 76 第 3 章 第二部分性能诊断 这是整个报告的精华所在，大部分的性能问题都能够从这部分报告里看到。看这部分内容的时候，如果有必要， 还可以结合后面的详细报告具体分析问题。 这部分最重要的几个报告是：负载分析、实例效率百分比（目标 100%）、Top 10 前台等待事件、主机环境统计 （主机 CPU、IO、内存和网络）。 报告摘要部分报告列表： • 负载分析 • 实例效率百分比（目标 100%） • Top 10 前台等待事件 • Top 10 前台等待事件分类 • 主机 CPU • 主机 IO • 主机内存 • 主机网络 • IO 分析 • 内存统计 • SQL 类型统计 3、报告主体： 报告主体提供了更加全面的性能指标，主要包括：DB Time 模型、等待事件、内存统计、实例 IO 统计、锁活动 统计、关键活动统计、SQL 报文统计、TOP SQL 统计、后台写统计、数据库对象统计和配置参数。 报告主体报告列表： • CPU 统计 – 主机 CPU 详细 • 时间模型和等待事件统计 – 时间模型统计 – 前台等待事件分类 – 前台等待事件 – 后台等待事件 – 数据库执行时间 – SQL 报文执行时间 • 内存统计 – Top 10 共享内存统计 77 第 3 章 第二部分性能诊断 • 实例 IO 统计 – 实例 IO 按进程类型统计 – 实例 IO 按文件类型统计 – 实例 IO 按数据库名统计 – 实例 IO 按表空间统计 – 实例 IO 按数据库对象类型统计 – Top 10 读写的数据库对象统计 • 锁活动统计 – Top 10 请求次数的锁活动 – Top 10 等待时间的锁活动 • 关键活动统计 – 关键活动按类型统计 – 关键活动按执行次数统计 – 关键活动按执行时间统计 • Top SQL 统计 – 按数据库时间排序的 SQL 语句 – 按 CPU 时间排序的 SQL 语句 – 按解析时间排序的 SQL 语句 – 按计划时间排序的 SQL 语句 – 按执行时间排序的 SQL 语句 – 按执行次数排序的 SQL 语句 – 按返回元组数排序的 SQL 语句 – 按 I/O 时间排序的 SQL 语句 – 按逻辑读块数排序的 SQL 语句 – 按物理读块排序的 SQL 语句 – 按逻辑写块数排序的 SQL 语句 – 按物理写块数排序的 SQL 语句 – 按临时数据块使用排序的 SQL 语句 – 按本地数据块使用排序的 SQL 语句 – 完整 SQL 列表 • 后台写统计 78 第 3 章 第二部分性能诊断 • 数据库统计 • 数据库对象统计 – 按顺序扫描页数排序的关系表 – 按逻辑读页数排序的关系表 – 按物理读页数排序的关系表 – 按 DML 行数排序的关系表 – 按命中率排序的关系表 – 按逻辑读页数排序的索引 – 按物理读页数排序的索引 – 按命中数排序的索引 – 存在未使用的索引 – 按执行时间排序的函数 – 按执行次数排序的函数 • 快照期间参数配置 4、KSH 报告主体： KSH 报告主体提供了 KSH 报告中的部分报告，主要包括：TOP 用户事件，分段时间等待事件统计。 KSH 报告主体报告列表： • Top 用户事件 • 分段时间等待事件统计 下面以 TPCC 测试（100 个仓库、100 个并发）期间产生的 KWR 为例，说明报告的各部分内容。 3.3.5.7.2 查看报告头 报告头部分主要由三部分组成： 79 第 3 章 第二部分性能诊断 1. 数据库实例和版本信息 字段说明： • 当前库：KWR 插件当前所在库 • 启动时间：数据库实例启动时间 • 发行版本：数据库版本号 2. 主机环境 这部分主要说明数据库实例的运行环境，包括 OS 信息和一些硬件信息。 字段说明： • 主机名：主机名称 • 平台：操作系统名称 • 处理器型号：处理器型号 • 处理器数：逻辑 CPU 个数 • 核心数：CPU 核心数 • 处理器槽位：CPU 物理槽位数 • 内存：主机总共的内存大小 3. 快照信息 字段说明： • 快照 ID：快照 ID • 快照时间：快照时间 • 会话数：快照时刻客户端会话连接数 统计项说明： • Begin Snap：开始快照统计 • End Snap：结束快照统计 • Elapsed：开始和结束快照经历的时间，单位为分钟 • DB Time：开始和结束快照期间花费在数据库上的时间总和 3.3.5.7.3 分析数据库时间 DB Time，就是数据库时间，它用来衡量数据库的繁忙程度，DB Time 越高，说明数据库业务越繁忙。 80 第 3 章 第二部分性能诊断 如报告所示，在 1.32 分钟的快照时间内，有 131.09 分钟在执行数据库任务，说明数据库业务已经比较繁忙了。 从负载分析里看平均每秒的 DB Time 是 99.57，说明总共 128 个 CPU 里，有 99.57 个 CPU 工作在当前数据库 实例上。满负荷的情况下，每秒的 DB Time 会接近 CPU 个数。当然服务器上的其它进程比如 TPCC 的 JAVA 进程 也会占用 10 到 20 个 CPU 左右。 可以从详细报告的时间模型统计里看数据库时间的各组成部分： 81 第 3 章 第二部分性能诊断 3.3.5.7.4 查看报告摘要 报告摘要主要从总体的角度描述数据库性能，包括数据库实例的整体负载情况，实例效率百分比，排名靠前的等 待事件，主机环境、IO 和共享内存等性能指标。 一般来说，分析完报告摘要部分就能大体了解性能情况，甚至知道可能性能问题所在，直接就能调优了。也可以 进一步结合详细报告来验证和分析问题。 以下是报告摘要的内容列表： 3.3.5.7.5 负载性能表 负载性能表从总体上描述数据库负载情况，其中比较重要的性能指标参数有：DB Time, DB CPU, Foreground Wait Time, WAL Size, Blocks Read/Write Size 等。 82 第 3 章 第二部分性能诊断 字段说明： • 显示数据库快照期间的负载信息统计 • 字段” 每秒”：测量出来的总值除以快照时间（秒数） • 字段” 每事务”：测量出来的总值除以快照期间事务总数 • 字段” 每执行”：测量出来的总值除以快照期间 SQL 语句执行总次数 • 快照总时间：用于计算每秒统计值 • 总事务数：用于计算每事物统计值 统计项分 6 个部分说明： 1. DB Time 统计 • DB Time(s)：数据库时间，是 Backend 进程非空闲等待的时间之和，单位为秒 • DB CPU(s)：数据库 CPU 时间，等于数据库时间减去 Backend 进程非空闲等待时间，单位为秒 • Foreground Wait Time(s)：前台等待时间，即 Backend 进程非空闲等待时间，单位为秒 2. IO 统计 • WAL Size(MB)：写入的 WAL 日志大小，单位为 MB • Blocks Hit：共享、本地和临时 blocks 命中块数 83 第 3 章 第二部分性能诊断 • Blocks Read：共享、本地和临时 blocks 物理读块数 • Blocks Dirty：共享、本地和临时 blocks 在内存中发生了更改的块数 • Blocks Write：共享、本地和临时 blocks 物理写块数 • Blocks Read Size(MB)：共享、本地和临时 blocks 物理读的大小，单位为 MB • Blocks Write Size(MB)：共享、本地和临时 blocks 物理读的大小，单位为 MB IO 方面需要重点关注的指标有 WAL Size - WAL 日志 IO 大小，和 Blocks Read/Write Size - 数据页读和写的大 小，这是物理读写的大小。 3. 解析和执行次数统计 • Parse Calls：SQL 解析（语法解析、语义分析）执行的次数 • Plan Calls：生成执行计划（Oracle 中的软解析、硬解析）的次数 • Execute Calls：SQL Execute 的次数 解析方面需要关注解析次数和执行次数的比例情况，如果解析过多，则必然 Parser Calls 跟 Execute Calls 更接 近，说明解析效率不高。Plan 也是。 4. 元组统计 • Tuples Return：返回元组的数量 • Tuples Fetch：Fetch 元组的数量 • Tuples Insert：Insert 元组的数量 • Tuples Update：Update 元组的数量 • Tuples Delete：Delete 元组的数量 这里可以了解快照期间数据库的业务类型，是查询为主，还是增删改为主。 5. 事务统计 • Commits：完成事务提交的数量 • Rollbacks：回滚事务的数量 • Transactions：事务总数（Commits + Rollbacks） 事务方面看看回滚的事务是不是比例高，如果比例高是不是需要分析一下业务逻辑。 6、其他 • Logon：登录次数 • Logoff：登出次数 • DeadLock：死锁次数 84 第 3 章 第二部分性能诊断 3.3.5.7.6 实例效率百分比 实例效率百分比展示数据库实例跟重复利用数据缓存页、解析、计划的效率百分比情况，这些值在优化的比较好 的测试环境里几乎都接近 100%，比如 BenchmarkSQL 测试。 如果低于 90%，说明数据页缓存不够（Buffer Hit），需要加大 Shared_buffers，让更多的数据页通过缓存级能 命中，较少 IO；或者说明解析和计划效率不高，发生了大量的硬解析的，需要从业务端把简单 SQL 报文协议（Simple Query 即 Q 报文）通过扩展 SQL 协议（P、B、D、E、S 报文）来执行，即绑定变量的方式来执行。 字段说明： • 效率：效率统计项 • 百分比：效率百分比，越高越好 统计项说明： • Buffer Hit %：Shared 和 Local 内存块的命中率，计算公式为：Blocks Hit * 100.0 / (Blocks Hit + Read) • Parse Reused %：解析重用率，这个值高说明 SQL 解析重用率高，计算公式为：100 * (1 - Parses / Executes) • Plan Reused %：计划重用率，这个值高说明 SQL 计划重用率高，计算公式为：100 * (1 - Parses / Executes) • Buffer NoWait %：获取数据缓冲区未等待百分比 • WAL buffer NoWait %：获取 WAL 日志缓冲区未等待百分比 • LightLock Hit %：LightLockAcquire() 第一时间就获得锁的比例 • Cached Plan %：软解析百分比，即从缓存中获得执行计划的比例，越高越好 3.3.5.7.7 Top 10 前台等待事件 该报表显示花费等待时间最多的 10 个前台等待事件，从该报告可以看出数据库实例在主要在等待什么。当出现 性能问题的时候，需要首先分析占比过高的前台等待事件，尤其那种大约 DB Time 5% 以上的等待事件，往往是发 现性能问题的关键线索。 85 第 3 章 第二部分性能诊断 这个报告里可以看到 transactionid 等待事件发生了 947700 次，DB Time 比例超过 8.20%，说明未事务锁冲突比 较严重。在 TPCC 场景，可以通过创建更多的仓库（Warehouse），比如 1000 个，让 SQL 语句执行的时候减少事 务冲突来提升性能。 另外，平均等待时间也值得关注，比如报告里的 extend 的平均等待时间是 11.23 毫秒，说明数据文件扩展比较 慢，可以通过对数据文件进行预扩展来提升性能。 在详细报告里可以进一步看到前台等待事件的信息。超过 5% 的都需要重点分析。 字段说明： • 等待事件：等待事件名称 • 等待次数：等待事件发生次数 • 总时间 (s)：总等待事件，单位为秒 • 平均时间 (ms)：平均一次等待的时间，单位为毫秒 • % 数据库时间：等待事件占数据库时间的百分比 • 事件类型：等待事件所属分类 3.3.5.7.8 Top 10 前台等待事件分类 该报表显示花费等待时间最多的 10 个前台等待事件分类。 从该报告可以方便地看出哪一类型的等待事件消耗了最多的 DB time 时间，比如锁冲突，IO 等待，进程间通信 等。 86 第 3 章 第二部分性能诊断 3.3.5.7.9 主机 CPU 使用统计 该报表显示快照期间主机 CPU 的使用情况，可以了解数据库实例的运行环境。当主机上没有其他进程消耗较大 CPU 的时候，该值可以近似地认为是数据库实例的 CPU 使用情况。该值可以跟 OS 的 TOP 命令做对比。 字段说明： • % 用户：用户态 CPU 时间的百分比 • % 优先：低优先级用户态（Nice）CPU 时间的百分比 • % 系统：内核态 CPU 时间的百分比 • % 等待 IO：CPU 等待 IO 时间（wait IO）的百分比 • % 空闲：CPU 空闲等待时间的百分比 • 开始平均负载：起始快照时刻 CPU 负载值（Load） • 结束平均负载：结束快照时刻 CPU 负载值（Load） 3.3.5.7.10 主机 IO 使用统计 该报告显示主机上各磁盘 IO 读写统计数据，可以反映数据库实例所在环境的 IO 繁忙程度。 字段说明： • 设备：磁盘设备名称，快照期间每个发生了 IO 读写的设备都有一行记录 • 读取次数/s：每秒读次数 • 读取大小/s：每秒读大小 • 读取时间 (s)：IO 读总时间，单位为秒 • 写入次数/s：每秒写次数 • 写入大小/s：每秒写大小 • 写入时间 (s)：IO 写总时间，单位为秒 87 第 3 章 第二部分性能诊断 3.3.5.7.11 主机内存使用统计 该报表显示了主机内存在快照开始和结束时使用情况。 只有内存明显不够的情况下才需要关注，比如使用到了 Swap 交换空间。 字段说明： • 统计值：开始、结束快照 • 总内存：总共物理内存大小 • 已用内存：已经使用的物理内存大小 • 空闲内存：剩余可用的物理内存大小 • 总虚拟内存：Swap 空间总大小 • 已用虚拟内存：Swap 空间已经使用大小 • 空闲虚拟内存：Swap 空间剩余可用大小 • 共享内存：共享内存的大小 3.3.5.7.12 主机网络使用统计 该报告显示了主机上每个网络介质上网络 IO 的情况，如果网络负载高可以关注。 字段说明： • 网卡名：网络介质名称，每个网卡一行记录 • IP 地址：IP 地址 • 发送数据/s：每秒发送/接收的字节数 • 发送包/s：每秒发送/接收的网络包数 • 发送错误/s：每秒发送/接收数据时发送的错误数 • 发送丢失/s：每秒发送/接收数据时因为错误而丢弃的网络包数 • 接收数据/s：每秒发送/接收的字节数 • 接收包/s：每秒发送/接收的网络包数 88 第 3 章 第二部分性能诊断 • 接收错误/s：每秒发送/接收数据时发送的错误数 • 接收丢失/s：每秒发送/接收数据时因为错误而丢弃的网络包数 3.3.5.7.13 数据实例 IO profile 数据库实例 IO 反映了快照期间共享/本地/临时数据块和 WAL 日志读写情况。如果有 IO 性能问题，需要关注。 其中，WAL 日志只统计写的情况，不统计读。 字段说明： • 统计项：数据实例的 IO 统计项，主要报告共享、本地、临时块和 WAL • 每秒读写：每秒读写 IO 的值（次数或字节大小） • 每秒读：每秒读 IO 的值（次数或字节大小） • 每秒写：每秒写 IO 的值（次数或字节大小） 统计项说明： • Shared Blocks：共享块读写数 • Shared Blocks (MB)：共享块读写大小，单位为 MB • Local Blocks：本地块读写数 • Local Blocks (MB)：本地块读写大小，单位为 MB • Temp Blocks：临时块读写数 • Temp Blocks (MB)：临时块读写大小，单位为 MB • WAL Size (MB)：WAL 写日志大小，单位为 MB 重点关注每秒读写 IO 的大小，并和网络 IO 去比较，看看是不是 IO 遇到瓶颈了。另外，如果 IO 读写太高，需 要分析一下业务场景，或者调整一下数据库的配置参数。 3.3.5.7.14 内存统计 这部分主要是显示主机内存（Host）、数据库实例共享内存（SGA）及其占比： 89 第 3 章 第二部分性能诊断 字段： • 统计项：统计项 • 开始大小：快照开始时内存大小 • 结束大小：快照结束时内存大小 统计项： • Host Memory：主机内存 • SGA：共享内存 • % Host Mem used for SGA：共享内存占主机内存的百分比 说明：目前暂时未统计本地内存占比。 3.3.5.7.15 SQL 语句统计 该报告显示 Insert、Update、Delete、Select 等语句的执行次数和执行时间等信息，能看出业务类型是以查询为 主，还是以增删改为主： 字段： • 名称：SQL 语句类型名称，包括 Select、Insert、Delete、Update 和 Merge • 总次数：SQL 语句执行总次数 • 每秒次数：每秒执行的 SQL 语句数 • 每事物次数：每事物执行的 SQL 语句数 • % 占比：该类型的 SQL 执行次数占总次数的比例 3.3.5.7.16 主机 CPU 详细统计 该报表显示自动快照每分钟主机 CPU 统计结果，以及快照时刻的结果，根据这些统计值，能够粗略地看出主机 CPU 的变化情况： 90 第 3 章 第二部分性能诊断 字段说明： • 时间：CPU 统计采样时间 • % 用户：普通用户态 CPU 时间百分比 • % 优先：低优先用户态 CPU 时间百分比 • % 系统：系统态 CPU 时间百分比 • % 空闲：空闲 CPU 时间百分比 • 平均负载：最近 5 分钟 CPU 平均负载（Load Average） 3.3.5.7.17 时间模型统计 该报表将 DB Time 分解为 DB CPU 和 Wait Time（前台非空闲等待时间），并给出 SQL 解析、计划和执行时 间，以及 Idle Time（前台空闲等待时间）和后台等待时间。 字段说明： • 统计项：时间模型各统计项 • 时间 (s)：各统计项花费的总时间，单位为秒 91 第 3 章 第二部分性能诊断 • % 数据库时间：各统计项花费时间占 DB Time 的百分比 统计项说明： • DB CPU：Backend 进程花费在 CPU 上的时间 • Wait Time：Backend 进程非空闲等待时间 • Parse Time：Backend 进程花在语法解析上的时间 • Analyze Time：Backend 进程花在语义分析上的时间 • Plan Time：Backend 进程花在生成执行计划上的时间 • Execute Time：Backend 进程花在执行 SQL 语句上的时间 • PLSQL Compile Time：Backend 进程花在 PLSQL 函数的编译时间 • PLSQL Execute Time：Backend 进程花在 PLSQL 函数的执行时间 • PLPGSQL Compile Time：Backend 进程花在 PLPGSQL 函数的编译时间 • PLPGSQL Execute Time：Backend 进程花在 PLPGSQL 函数的执行时间 • Foreground Net Read Time：Backend 进程花在网络读上的时间 • Foreground Net Write Time：Backend 进程花在网络写上的时间 • Foreground DateFile IO Time：Backend 进程花在数据库对象（表、索引等）的文件 IO 时间 • Foreground Instance IO Time：Backend 进程花在数据库实例（对象 + 日志、统计等）的全部 IO 时间 • DB Time：Backend 进程执行 SQL 语句花费的总时间 • Idle Time：Backend 进程的空闲等待时间（不参与 DB Time 计算） • Background Wait Time：后台进程等待时间（不参与 DB Time 计算） • Background Net Read Time：后台进程花在网络读上的时间 • Background Net Write Time：后台进程花在网络写上的时间 • Background DateFile IO Time：后台进程花在数据库对象（表、索引等）的文件 IO 时间 • Background Instance IO Time：后台进程花在数据库实例（对象 + 日志、统计等）的全部 IO 时间 计算公式： • 数据库时间（DB Time）= 前台进程等待时间（FG Wait Time）+ 数据 CPU 时间（DB CPU） • 总等待时间（Wait Time）= 前台进程等待时间（FG Wait Time）+ 前台进程空闲等待时间（FG Idle Time） + 后台进程等待时间（BG Wait Time） 3.3.5.7.18 前后台等待事件统计 主要包括三个报告： • 前台等待事件分类 92 第 3 章 第二部分性能诊断 • 前台等待事件 • 后台等待事件 1、前台等待事件分类 该报告显示前台等待事件分类的统计结果： 字段说明： • 事件类型：等待事件所属分类 • 等待次数：等待事件发生次数 • 总时间 (s)：总等待事件，单位为秒 • 平均时间 (ms)：平均一次等待的时间，单位为毫秒 • 每事物等待数：平均每事物等待事件的次数 • % 数据库时间：等待事件占数据库时间的百分比 2、前台等待事件 该报告显示全部前台客户端会话进程等待事件的统计结果： 字段说明： 93 第 3 章 第二部分性能诊断 • 等待事件：等待事件名称 • 等待次数：等待事件发生次数 • 总时间 (s)：总等待事件，单位为秒 • 平均时间 (ms)：平均一次等待的时间，单位为毫秒 • 每事物等待数：平均每事物等待事件的次数 • % 数据库时间：等待事件占数据库时间的百分比 3、后台等待事件 该报告显示全部后台辅助进程等待事件的统计结果： 字段说明： • 等待事件：等待事件名称 • 等待次数：等待事件发生次数 • 总时间 (s)：总等待事件，单位为秒 • 平均时间 (ms)：平均一次等待的时间，单位为毫秒 • 每事物等待数：平均每事物等待事件的次数 3.3.5.7.19 数据库执行时间 数据库时间按照各库统计，显示在每个库上的 SQL 执行次数和时间。通过该报告可以看到哪个数据库上花费的 数据库时间最多： 字段说明： • 数据库：数据库名称 • 执行次数：当前数据库上的总执行次数 • 总时间 (s)：当前数据库上的总执行时间，单位为秒 • 平均时间 (ms)：平均执行时间（总时间除以执行次数） • % 数据库时间：当前数据库执行时间占总数据库时间的百分比 94 第 3 章 第二部分性能诊断 3.3.5.7.20 SQL 报文执行时间 数据库时间按照 SQL 报文的类型统计，显示在每种报文类型上的数据库时间。 一般说来说：如果 Simple Query 的报文占比高，说明语句没有使用 SQL 扩展协议。 字段说明： • 报文：SQL 语句执行时的报文 • 执行次数：报文执行次数 • 总时间 (s)：报文执行时间，单位为秒 • 平均时间 (ms)：平均执行时间（Time / Calls） • % 数据库时间：报文执行时间占总数据库时间的百分比 其中，SQL 报文主要有： • Simple Query：SQL 简单报文协议 • Parse：SQL 语法解析、语义分析报文 • Bind：SQL 绑定变量到解析的语句上报文 • Execute：SQL 语句执行报文 • Fastpath Function：客户端通过快速路径接口来向服务器发送简单的函数调用 • Close：客户端关闭语句（statement）或者入口（Protal） • Describe：客户端请求获得语句（statement）或者入口（Protal）的描述 • Flush：libpq 刷新网络通讯数据 • Sync：提交事务 • Wait Message：虚拟报文，是普通 SQL 报文执行时间之外的一段时间 3.3.5.7.21 内存统计 内存统计指共享内存： • Top 10 共享内存统计 1. Top 10 共享内存统计 95 第 3 章 第二部分性能诊断 通过该报告，能够知道数据库实例的共享内存主要由哪些部分组成，以及每部分的比例。本报告值展 示排名前 10 的共享内存项。 字段说明： • 共享内存：共享内存块的名称 • 开始大小：快照开始时刻共享内存块的大小 • 开始占比 (%)：快照结束时刻共享内存块占比 • 结束大小：快照开始时刻共享内存块的大小 • 结束占比 (%)：快照结束时刻共享内存块占比 3.3.5.7.22 实例 IO 统计 实例 IO 报告是对摘要部分 IO 统计的进一步分解，从多个维度展示数据库实例运行过程中的 IO 统计，主要包 括： • 按进程类型统计 • 按文件类型统计 • 按数据库名统计 • 按表空间统计 • 按数据库对象类型统计 • Top 10 读写的数据库对象统计 1. 按进程类型统计 以数据库实例进程的角度展示 IO 读写统计，这些进程包括后台辅助进程和客户端会话进程（Backend）。 96 第 3 章 第二部分性能诊断 字段说明： • 进程类型：产生 IO 的进程名称，其中 Client Backend 进程合并成一个显示 • 总次数：产生 IO 的次数 • 读次数/s：每秒产生读 IO 的次数 • 写次数/s：每秒产生写 IO 的次数 • 读取大小：读 IO 字节 • 读取大小/s：每秒读 IO 字节 • 写入大小：写 IO 字节 • 写入大小/s：每秒写 IO 字节 • 平均读写 (ms)：平均 IO 读写时间，单位：毫秒 进程名称包括： • archiver：后台归档进程 • bgwriter：后台写进程 • logger：后台 logger 进程 • wal writer：后台写 WAL 日志进程 • checkpointer：后台检查点进程 • autovacuum luncher：后台自动清理启动进程 • autovacuum worker：后台自动清理工作进程 • stats collector：后台统计进程 • logical reduplication：后台逻辑复制进程 • wal sender：WAL 发送者进程 • wal receiver：WAL 接收者进程 • backend：客户端会话进程 • background worker：其他后台 worker 进程 97 第 3 章 第二部分性能诊断 2. 按文件类型统计 以文件类型的角度统计 IO，可以看出哪些文件类型消耗的 IO 最多： 文件类型主要包括： • 审计文件（Audit File） • 临时缓冲区文件（Temp Buffer File） • 控制文件（Control File） • 数据文件（Data File） • 动态共享内存文件（DSM File） • 锁文件（Lock File） • 日志文件（Log File） • 逻辑复制文件（Logical Replication File） • 事务状态文件（Transaction File） • 统计文件（Stats File） • WAL 文件（WAL File） 3. 按库名统计 该报告以数据库维度来统计 IO，可以看到每个库的 IO 情况： 4. 按表空间统计 该报告以表空间的维度来统计 IO： 5. 按对象类型统计 该数据库对象类型的维度来统计 IO： 98 第 3 章 第二部分性能诊断 对象类型主要包括： • Relation：关系表 • Index：索引 • Sequence：序列 • View：视图 • Material View：物化视图 • Combine：组合 • TOAST：TOAST 表 • Foreign：外部表 6. Top 10 读写的数据库对象统计 该报告显示了 IO 读写总大小排名前 10 的数据文件对象（表、索引、序列、视图等）： 3.3.5.7.23 锁活动统计 这部分显示 LightLock 轻量级锁请求次数和等待情况，主要包括： • Top 10 锁请求次数的锁活动 • Top 10 等待时间的锁活动 1. Top 10 锁请求次数的锁活动 该报告显示锁请求次数最多的 10 个 LightLock 的统计。 99 第 3 章 第二部分性能诊断 字段说明： • 锁名称：LightLock 锁的名称 • 锁请求锁次数：LightLock 锁请求的次数 • % 非等待获得锁：LightLock 以不等待的方式获得锁的比例 • % 非等待未获得锁：LightLock 以不等待的方式未获得锁的比例 • % 等待获得锁：LightLock 以等待的方式获得锁的比例 • % 等待未获得锁：LightLock 以等待的方式未获得锁的比例 • 平均等待时间 (ms)：LightLock 平均等待时间，单位毫秒 • 总等待时间 (s)：LightLock 总等待时间，单位秒 2. Top 10 等待时间的锁活动 该报告显示锁等待时间最长的 10 个 LightLock，主要关注平均等待时间 (ms) 和总等待时间 (s) 两个字段。 3.3.5.7.24 关键实例活动统计 数据关键活动是数据库内部经常执行到的一些函数或者事件，其分类比较杂不足以单独形成一个报告，就汇总在 此报告里面。这些统计数据主要做一般参考。 100 第 3 章 第二部分性能诊断 三个报告包括： • 关键活动按类型统计 • 关键活动按执行次数统计 • 关键活动按执行时间统计 1、关键活动按类型统计 字段说明： • 类型：关键活动的分类 • 名称：关键活的名称 • 总次数：关键活动执行总次数 • 每秒次数：关键活动每秒执行次数 • 每事物次数：关键活动每事物执行次数 • 每秒执行 (ms)：快照期间内平均每秒执行关键活动时间，单位：毫秒 • 每事物执行 (ms)：快照期间内平均每事务执行关键活动时间，单位：毫秒 说明，关键实例活动的分类有： • Buffer: 内存缓冲区相关统计 • Executor: 执行器相关统计 • Lock: 对象锁相关统计 • Maintance: 维护操作（创建索引等）相关统计 • SMGR: 存储管理器相关统计 • Transaction：事务相关统计 • Wal: WAL 日志相关统计 • CheckPoint: 检查点相关统计 101 第 3 章 第二部分性能诊断 • Misc: 其他，比如登录、登出统计 2、关键活动按执行次数统计 报告结构跟按类型统计一致，按关键活动执行总次数排序。 3、关键活动按执行时间统计 报告结构跟按类型统计一致，按关键活动执行时间排序。 3.3.5.7.25 TOP SQL TOP SQL 报告记录了消耗资源最多的 SQL 语句，总共 15 张报表，从多个维度（DB Time，DB CPU，SQL 执 行次数，IO 时间，共享数据块等）显示 TOP SQL 的时间占比和资源消耗情况。 除了最后一个完整的 SQL 文本列表是显示 SQL 语句列表外，其他都是 TOP SQ 的各种排序和统计。包括： • 按数据库时间排序的 SQL 语句 • 按 CPU 时间排序的 SQL 语句 • 按解析时间排序的 SQL 语句 • 按计划时间排序的 SQL 语句 • 按执行时间排序的 SQL 语句 • 按执行次数排序的 SQL 语句 102 第 3 章 第二部分性能诊断 • 按返回元组数排序的 SQL 语句 • 按 I/O 时间排序的 SQL 语句 • 按逻辑读块数排序的 SQL 语句 • 按物理读块排序的 SQL 语句 • 按逻辑写块数排序的 SQL 语句 • 按物理写块数排序的 SQL 语句 • 按临时数据块使用排序的 SQL 语句 • 按本地数据块使用排序的 SQL 语句 • 完整 SQL 列表 下面以按数据库时间（DB Time）排序的 Top SQL 为例说明，该报表根据 SQL 语句的总体执行时间排序，显示 了花费 DB Time 最多的前 20 条 SQL 语句的统计情况。 字段说明： • QueryID：查询语句的唯一标识符，点击它可以跳转到完整 SQL 列表的对应的 SQL 语句上 103 第 3 章 第二部分性能诊断 • 数据库时间 (s)：SQL 语句总执行时间，单位为秒 • 执行次数：SQL 语句总执行次数 • 平均执行时间 (ms)：SQL 语句平均每次执行时间，单位为毫秒 • 数据库时间 (%)：该 SQL 语句执行时间占 DB Time 的百分比 • CPU 占比 (%)：该 SQL 语句的 CPU 时间占该 SQL 语句总执行时间的百分比 • 等待占比 (%)：该 SQL 语句的非空暇等待时间占该 SQL 语句总执行时间的百分比 • SQL 语句：该 SQL 语句前 40 个字符串，多于部分截断显示 具体调查某一条 SQL 语句的时候，还需要分析它涉及到的表和索引的统计信息，因为有时候性能问题往往是由 部分热表、热索引冲突导致的。 可以通过点击 QueryID 的链接跳转到完整 SQL 列表，查看具体的 SQL 字符串： 104 第 3 章 第二部分性能诊断 3.3.5.7.26 检查点和后台写统计 检查点和后台写统计主要是 Checkpointer 和 BgWriter 进程产生 IO 的统计： 105 第 3 章 第二部分性能诊断 该报告分两部分： 第一部分为 checkpoint 统计： • Scheduled checkpoints：已经被执行的计划中检查点的数量 • Requested checkpoints：已经被执行的请求检查点的数量 • Checkpoint write time(s)：在文件被写入磁盘的检查点处理部分花费的总时间，单位为秒 • Checkpoint sync time(s)：在文件被同步到磁盘中的检查点处理部分花费的总时间，单位为秒 • Checkpoints pages written：在检查点期间被写的缓冲区数目 第二部分为后台写统计： • Background pages written：被后台进程写的缓冲区数目 • Backend pages written：被后端进程直接写的缓冲区数量 • Backend fsync count：后端进程不得不直接执行 fsync 调用的次数 • Bgwriter interrupts (too many buffers)：后台写进程由于已经写了太多缓冲区而停止清洁扫描的次数 • Number of buffers allocated：被分配的缓冲区数量 这些值可以除以快照期间过去的总秒数，得到平均每秒上发生的 Checkpoint 和 Background Write 的值，更加具 有可比性。 3.3.5.7.27 数据库统计 该报表上能够看到每个 database 对象上事务、IO 读写、元组等统计结果。 106 第 3 章 第二部分性能诊断 字段说明： • 数据库 (database)：数据库名 • 提交数：提交的事务数 • 回滚数：回滚的事务数 • 死锁数：死锁次数 • 读取块数：物理读的 blocks 数 • 命中块数：缓存命中的 blocks 数 • 命中率%：缓存命中的 blocks 百分比 • 返回元组：查询返回的元组数 • 获取元组：查询取出的元组数 • 插入元组：查询插入的元组数 • 更新元组：查询更新的元组数 • 删除元组：查询删除的元组数 3.3.5.7.28 数据库对象统计 这部分主要是当前库的表、索引和用户自定义函数统计，包括以下 3 类报告： 1、关系表统计： • 按顺序扫描页数排序的关系表 • 按逻辑读页数排序的关系表 • 按物理读页数排序的关系表 • 按 DML 行数排序的关系表 • 按命中率排序的关系表 2、索引统计： • 按逻辑读页数排序的索引 • 按物理读页数排序的索引 • 按命中数排序的索引 • 存在未使用的索引 3、用户自定义函数统计： • 按执行时间排序的函数 • 按执行次数排序的函数 107 第 3 章 第二部分性能诊断 以顺序扫描页数排序的关系表报告为例，它描述了当前库下顺序扫描页最多的关系表统计结果： 字段说明： • 模式：关系表的 Schema • 表：关系表名称 • 顺序扫描：顺序扫描次数 • 扫描页数：顺序扫描页数 • 索引扫描：关系表上发起的索引扫描次数 • 索引获取：被索引扫描取得的活着的行的数量 3.3.5.7.29 快照期间配置参数 该报告记录了快照期间全部 GUC 参数，如果有参数发了变化，都会记录下来形成多个记录，并在说明字段里记 录变更的时间。 108 第 3 章 第二部分性能诊断 字段说明： • 配置参数：GUC 参数 • 值：GUC 参数的值 • 单位：GUC 参数值的单位 • 说明：def 表示用的默认值，如果一个值快照期间变更了，显示变更时间。 关于值和单位的计算，比如下图： • shared_buffers = 3932160 * 32kb = 120GB • wal_buffers = 16384 * 64kb = 1GB 关于 GUC 参数值变更情况的记录，如下，shared_buffers 和 wal_buffers 分别变更了一次，可以通过说明字段 里的时间去 perf.kwr_snapshots 查是哪个快照期间发生的修改： 通过这个报告，在不需要收集客户运行环境的 kingbase.conf 情况下，就能查询到快照期间的每一个 GUC 参数 109 第 3 章 第二部分性能诊断 的值及其更改情况。 3.3.5.7.30 KSH 报告主体 KSH 报告较多，在 KWR 报告中选取了两个具有代表性的报告展示，可以看到快照期间内哪个时间段数据库的 出现了性能问题。 两个报告包括： • Top 用户事件 • 分段时间等待事件统计 1、Top 用户事件 该报告上展示了排名靠前的客户端会话件等待事件，通过该报告可以知道数据库系统里哪些等待事件事件比较 多： 字段说明： • 事件类型：等待事件所属的分类，其中 OnCpu 是指非等待事件 • 事件名称：等待事件的名称 • 采样数：报告期间采样到的等待事件数量 • 事件占比：报告期间采样到的等待事件数量占全部等待事件的百分比 • 平均会话数：平均每秒等待事件出现的会话数量 2、分段时间等待事件统计 该报告展示按时段（比如 5 分钟）统计的等待事件： 字段说明： • 启动：统计时段开始时间 110 第 3 章 第二部分性能诊断 • 结束：统计时段结束时间 • 区间采样数：统计时段内等待事件采样数 • 事件类型：统计到的等待事件类型 • 事件次数：该等待事件类型下等待事件的数量 • 事件占比：该等待事件占全部等待事件的比例 3.3.6 活跃会话历史报告 SYS_KSH sys_stat_activity 里记录的等待事件是瞬时信息，没有对等待事件的时间进行累计，所以 KingbaseES 在 V9 中 引入了明细会话历史 (Kingbase Session History) 和相应的报告工具。用户可以使用该工具进行会话历史的分析，并 针对报告呈现的性能瓶颈进行优化。 sys_ksh 以每秒采样的方式进行会话和数据收集，并将采集数据放入内存的 Ringbuf 队列中，采集的数据主要包 括：会话、应用、等待事件、命令类型、QueryId 等。其主要使用场景是：当前或历史某个时点，发生了什么样的异 常，系统在执行/运行什么任务。 KSH 功能已经在 KWR 插件中实现，当 KWR 插件创建后就可以使用该功能。 3.3.6.1 SYS_KSH 的配置 sys_ksh 的参数在 kingbase.conf 中设置，典型用法可能是： ＃ kingbase.conf shared_preload_libraries = 'liboracle_parser, sys_stat_statements, sys_kwr' sys_stat_statements.track = 'top' # default = 'none' sys_kwr.collect_ksh = on # default = off KSH 相关参数： • track_activities：跟踪活动会话的等待事件、执行 SQL、状态等，默认：on • sys_stat_statements.max：设置 sys_stat_statement 跟踪的最大语句数，默认：5000 • sys_kwr.collect_ksh：启用或禁用 sys_ksh 数据收集，默认：关闭 • sys_kwr.ringbuf_size：设置 sys_ksh ringbuf 大小，在内存中可以存多少条数据，默认：100000 • sys_kwr.history_days：设置最大的 sys_ksh 历史数据存储，在磁盘上存储的时间，默认：8 天 • sys_kwr.language：KSH 报告展示中文/英语信息，默认：中文 注：采集报告需要在开启：track_activities（默认已开启），并设定 sys_kwr.collect_ksh = on 才可以查看报 告。开启该参数会有一定的性能损耗。 为了确保 KSH 能够采集到活动会话的信息，需要给数据库系统施加一些负载，比如用 TPCC、sysbench、loadrunner 等工具对其进行压测。 111 第 3 章 第二部分性能诊断 也可以通过以下语句给当前会话产生一个 TimeOut 等待事件，以便能够查看无负载情况下的 KSH 报告： SELECT sys_sleep(2); SELECT * FROM perf.ksh_report(); 当 KSH 没有采集到数据会提示： SELECT * FROM perf.ksh_report(); KSH 报告中的 queryid 依赖于 sys_stat_statements 表，当 sys_stat_statements 没有采集到数据会提示： SELECT * FROM perf.ksh_report(); 3.3.6.2 SYS_KSH 采集数据说明 KSH 采集的数据主要包括以下信息： 字段 类型 描述 ts ti mestamptz 采样时间 db_id oid 数据库 OID db_name va rchar(64) 数据库名称 pid int 进程 ID user_id oid 用户 OID user_name va rchar(64) 用户名 见续表 112 第 3 章 第二部分性能诊断 表 3.3.8 – 续表 字段 类型 描述 app_name va rchar(64) 应用名称 c lient_addr inet 客户端 IP 地址 wait_class text 等待事件类型 wait_event text 等待事件 query_id uint8 QueryId ba ckend_type text 后端类型，如：autovacuum, autovacuum worker 等 type text 语句类型，如：INSERT, SELECT, UPDATE, DELETE, UTILITY phase text 执行阶段，如：PARSE, PLAN, EXECUTION, UNKNOWN state text 状态，如：IDLE, IDLE IN TRANSACTION, RUNNING 等 3.3.6.3 使用 SYS_KSH 创建 KWR 插件： CREATE EXTENSION sys_kwr; 保存于内存 Ringbuf 的数据可以通过视图 perf.session_history 查看： SELECT * FROM perf.session_history; 其中保存于数据库的历史数据可以通过视图 perf.ksh_history 查看： SELECT * FROM perf.ksh_history; 可以通过 perf.ksh_report() 函数生成在线报告查看： SELECT * FROM perf.ksh_report({start_ts}, {duration}, {slot_width}, {format}); 参数说明： • start_ts: 报告开始时间，默认：当前时间-15 分钟 • duration: 报告时长，默认：15 分钟，最大不超过 60 • slot_width: 报告最小区间，输入 0 时系统自动计算合适的宽度，默认：0 • format: 报告生成格式，可选择’html’和’text’两种格式，默认 TEXT 格式 例：生成 TEXT 的 KSH 在线报告 113 第 3 章 第二部分性能诊断 select * from perf.ksh_report('2022-12-13 14:12:46',15,0,'text'); 可以通过 perf.ksh_report_to_file() 函数生成报告文件： SELECT * FROM perf.ksh_report_to_file({start_ts},{duration},{slot_width},{file_path},{format}); 参数说明： • start_ts: 报告开始时间，默认：当前时间-15 分钟 • duration: 报告时长，默认：15 分钟，最大不超过 60 • slot_width: 报告最小区间，输入 0 时系统自动计算合适的宽度，默认：0 • file_path: 报告生成地址，示例：’/home/username/ksh_report.html’ • format: 报告生成格式，可选择’html’和’text’两种格式，默认 TEXT 格式 例：生成 HTML 的 KSH 报告文件 select * from perf.ksh_report_to_file('2022-12-13 14:12:46',15,0,'/home/kingbase/ksh_report.html','html'); 若想查看两个快照期间的 KSH 报告，可以通过 perf.ksh_report_by_snapshots() 函数生成在线报告： SELECT * FROM perf.ksh_report_by_snapshots({start_snapid}, {end_snapid}, {slot_width}, {format}); 参数说明： • start_snapid: 起始快照号 • end_snapid: 结束快照号 • slot_width: 报告最小区间，输入 0 时系统自动计算合适的宽度，默认：0 • format: 报告生成格式，可选择’html’和’text’两种格式，默认 TEXT 格式 例：根据快照号生成 TEXT 的 KSH 在线报告 select * from perf.ksh_report_by_snapshots(1,2); 若想查看两个快照之间的 KSH 报告并保存到文件中，可以通过 perf.ksh_report_to_file_by_snapshots() 函数 生成报告文件。 SELECT * FROM perf.ksh_report_to_file_by_snapshots({start_snapid}, {end_snapid}, {file_path}, {format}, {slot_width}); 参数说明： • start_snapid: 起始快照号 • end_snapid: 结束快照号 • file_path: 报告生成地址，示例：’/home/username/ksh_report.html’ 114 第 3 章 第二部分性能诊断 • format: 报告生成格式，可选择’html’和’text’两种格式，默认 TEXT 格式 • slot_width: 报告最小区间，输入 0 时系统自动计算合适的宽度，默认：0 例：根据快照号生成 HTML 的 KSH 报告文件 select * from perf.ksh_report_to_file_by_snapshots(1,2,'/home/kingbase/ksh_report.html','html'); 3.3.6.4 查看 SYS_KSH 报告 KSH 输出报告的内容章节包括： • 报告和实例信息 • Top 用户事件 • Top 后台事件 • Top 数据库 • Top PL/SQL 过程 • Top 简单查询 • 等待事件高的 Top SQL • Top 会话 • Top 客户端 • Top 并行 SQL 等待事件 • Top 阻塞会话事件 • Top 重量级锁等待事件 • Top 轻量级锁等待事件 • Top SQL 命令类型 • Top 执行阶段 • 分段时间等待事件统计 • 完整 SQL 列表 KSH 网页版示例报告说明如下： 1. 报告和实例信息 该报告显示 KSH 环境和总体信息： 115 第 3 章 第二部分性能诊断 统计字段说明： • HostName：主机名 • Cpus：服务器 cpu 数 • SGA Size：共享内存大小 • Server Version：服务器版本 • Listen Address：数据库服务器的监听地址 • Report Begin Time：KSH 报告开始时间 • Report End Time：KSH 报告结束时间 • Sample Count：报告时间范围内采样次数 • Second Count：总共采样到的秒数（各客户端会话进程累加起来） • Avg Act Ses：平均每秒的活跃会话数 • Data Source：KSH 报告数据的来源（包括：内存、KSH 表、混合三种） 2. TOP 用户事件 该报告展示排名靠前的客户端会话等待事件，通过该报告可以知道数据库系统里哪些等待事件发生的比较多： 116 第 3 章 第二部分性能诊断 字段说明： • 事件类型：等待事件所属的分类，其中 OnCpu 是指非等待事件 • 事件名称：等待事件的名称 • 采样数：报告期间采样到的等待事件数量 • 事件占比：报告期间采样到的等待事件数量占全部等待事件的百分比 • 平均会话数：平均每秒等待事件出现的会话数量 3. TOP 后台事件 该报告展示排名靠前的后台进程的等待事件，通过该报告可以知道数据库系统里哪些等待事件发生的比较多： 字段说明： • 事件类型：等待事件所属的分类，其中 OnCpu 是指非等待事件 • 事件名称：等待事件的名称 • 采样数：报告期间采样到的等待事件数量 • 活动占比：报告期间采样到的等待事件数量占全部等待事件的百分比 • 平均会话数：平均每秒等待事件出现的会话数量 4. TOP 数据库 该报告展示访问次数排名靠前的数据库： 字段说明： • 数据库名：数据库名称 • 访问次数：报告期间访问数据库的次数 • 活动占比：报告期间采样到的访问次数占全部访问次数的百分比 • 平均会话数：平均每秒访问出现的会话数量 5. Top PL/SQL 过程 该报告展示采样到的 PL/SQL 函数执行情况： 117 第 3 章 第二部分性能诊断 字段说明： • 名称：PL/SQL 函数名称 • 数据库名：执行该 PL/SQL 函数的数据库名称 • 活动占比：该函 PL/SQL 数执行占全部 PL/SQL 函数执行的百分比 • 平均会话数：平均每秒 PL/SQL 出现的会话数量 6. Top 简单查询 该报告主要展示使用简单报文（Simple Query）的 SQL 语句，即没有使用绑定变量的 SQL 语句统计： 字段说明： • Query ID：执行简单报文的 SQL 语句的 ID，可以通过点击链接看详细 SQL 语句 • 活动占比：SQL 语句占简单报文 SQL 语句的百分比 • 平均会话数：该 SQL 语句每秒平均会话数 • 采样数：KSH 报告期间采样到的该 SQL 语句次数 • SQL 语句：SQL 语句字符串，超长则截断 7. 等待事件高的 TOP SQL 该报告统计等待时间最多的 SQL 语句： 字段说明： • Query ID：执行简单报文的 SQL 语句的 ID，可以通过点击链接看详细 SQL 语句 • 采样数：KSH 报告期间采样到的该 SQL 语句次数 • 活动占比：SQL 语句占简单报文 SQL 语句的百分比 • 事件名称：等待事件的名称 118 第 3 章 第二部分性能诊断 • 事件次数：等待时间发生的次数 • 事件占比：该 SQL 语句当前等待事件占其全部等待事件的比例 • SQL 语句：SQL 语句字符串，超长则截断 8. 完整的 SQL 文本列表 该报告展示 KSH 报告中出现的 Query ID 对应完整 SQL 语句： 字段说明： • Query Id：SQL 语句的 query • 采样数：该 SQL 语句的采样数 • SQL 语句：SQL 语句的完整字符串 9. TOP 会话 该报告展示 TOP 活跃会话的基本信息： 字段说明： • 会话 ID：活跃会话对应的进程 ID • 会话占比：该活跃会话出现的占比 • 事件名称：采样到的等待事件或者 CPU 的名称 119 第 3 章 第二部分性能诊断 • 事件占比：该事件占该会话全部事件的占比 • 客户程序：该会话的客户端名称 • 用户名：该会话的用户名 10. TOP 客户端 该报告按客户端连接来统计活跃会话情况： 字段说明： • 用户名：该客户端类型的用户名称 • 客户程序：该客户端应用名称 • 活动占比：统计到该客户端的活动占全部活动的比例 • 平均会话数：统计到该客户端的平均会话数 11. TOP 并行 SQL 等待事件 该报告主要展示客户端会话中的并行查询进程的 SQL 统计信息： 字段说明： • 会话 ID：统计到的并行进程的 PID • 活动占比：统计到该客户端的活动占全部活动的比例 • 事件名称：并行进程的等待事件或者 CPU 活动 • 事件占比：并行进程的等待事件或者 CPU 活动的占比 • 平均会话数：并行进程的平均活跃会话数 • 用户：并行进程的用户 • 客户程序：并行进程的客户端程序名称 • Query Id：并行进程执行的 SQL 语句 120 第 3 章 第二部分性能诊断 12. Top 阻塞会话事件 该报告按进程展示阻塞类的会话事件，即 Lock 类等待事件的统计： 字段说明： • 会话 ID：发生阻塞等待事件的进程的 PID • 活动占比：该会话阻塞事件占全部事件的占比 • 平均会话数：进程的平均活跃会话数 • 事件名称：并行进程的等待事件或者 CPU 活动 • 事件占比：并行进程的等待事件或者 CPU 活动的占比 • 用户：并行进程的用户 • 客户程序：并行进程的客户端程序名称 13. Top 重量级锁等待事件 该报告展示客户端会话进程的重量级锁等待事件统计： 字段说明： • 锁名称：统计的锁的名称 • 锁数量：统计的锁的数量 • 锁占比：统计的锁占全部锁的占比 • 活动占比：统计到该等待事件重量级锁的次数占全部活动的比例 • 平均会话数：该锁出现的平均会话数 14. Top 轻量级锁等待事件 121 第 3 章 第二部分性能诊断 该报告展示客户端会话进程的轻量级锁等待事件统计： 字段说明： • 锁名称：统计的锁的名称 • 锁数量：统计的锁的数量 • 锁占比：统计的锁占全部锁的占比 • 活动占比：统计到该等待事件轻量级锁的次数占全部活动的比例 • 平均会话数：该锁出现的平均会话数 15. Top SQL 命令类型 该报告展示客户端会话进程的 SQL 语句类型： 字段说明： • 命令类型：SQL 语句类型 • 差异 Query ID：该类型下 SQL 语句数量 • 命令占比：该 SQL 语句类型的占比 • 平均会话数：该 SQL 语句的平均会话数 16. Top 执行阶段 该报告展示客户端会话进程的 SQL 语句执行阶段： 字段说明： • 执行阶段：SQL 语句的执行阶段，主要包括解析（Parse）、计划（Plan）、绑定（Bind）、执行（Execute）、同步（Sync）等 122 第 3 章 第二部分性能诊断 • 阶段占比：该执行阶段占全部执行阶段采样数的比例 • 活动占比：该执行阶段采样数占全部采样数量的比例 • 平均会话数：该执行阶段的平均会话数 17. 分段时间等待事件统计 该报告展示按时段（比如 5 分钟）统计的等待事件： 字段说明： • 启动：统计时段开始时间 • 结束：统计时段结束时间 • 区间采样数：统计时段内等待事件采样数 • 事件类型：统计到的等待事件类型 • 事件次数：该等待事件类型下等待事件的数量 • 事件占比：该等待事件占全部等待事件的比例 通过该输出，可以发现报告区间发生的最多的等待事件、查询和客户端的信息，基于这些信息可以帮助使用者快 速定位当前系统的瓶颈点。 KSH 报告说明： • KSH 仅支持管理员在主服务器运行 (备机无法运行) • KSH 功能仅能在 kingbase 库上使用 • 历史数据的采样周期为 10 秒，实时数据的采样周期为 1 秒 • 报告的采集要求开启：track_activities 和 sys_stat_statements 并设置 sys_kwr.collect_ksh = on 3.3.6.5 KWR 和 KSH 比较 定位 sys_kwr sys_ksh 周期时间累积差异，查询特定时间段的累积变化 识别系统短暂 (transient) 变化，秒级时刻的实时 值 原理 以累积式计数反馈系统在一定周期内的整体负载 基于定时的采样方式进行会话和数据收集 和瓶颈 见续表 123 第 3 章 第二部分性能诊断 表 3.3.9 – 续表 场景 数据留存 3.3.7 sys_kwr sys_ksh 本次系统升级后有哪些整体改进，当前时段与基 当前或历史某个时点，发生了什么样的异常，系 线相比指标如何变化。 统在执行/运行什么任务 默认 1 小时采集，快照保留 8 天，超过 8 天后自 默认 1 秒，实时数据保留 1 小时，过后以 1/10 动删除 的比例存入历史数据 自动诊断和建议报告 SYS_KDDM KDDM 是 KingbaseES 性能自动诊断和建议的报告。它基于 KWR 快照采集的性能指标和数据库时间模型（DB Time），自动分析等待事件、IO、网络、内存和 SQL 执行时间等，给出一系列性能优化建议。通过 KDDM 报告， 能够实现数据库性能的快速调优。 KDDM 存在于 KWR 插件里，和 KWR 一起对外提供服务。 目前 KDDM 报告仅支持 TEXT 格式，不支持 HTML 格式输出。 3.3.7.1 快速生成 KDDM 报告 通过 KWR 自动快照或者手动快照创建一系列 KWR 快照，然后就可以通过 perf.kddm_report({snap_1}, {snap_2}) 来创建 KDDM 报告： CREATE EXTENSION sys_kwr; SELECT * FROM perf.create_snapshot(); INSERT INTO t1 SELECT generate_series(1, 1000000); SELECT * FROM perf.create_snapshot(); SELECT * FROM perf.kddm_report(1,2); 生成的报告如下： SELECT * FROM perf.kddm_report(1,2); 124 第 3 章 第二部分性能诊断 3.3.7.2 使用 KDDM 3.3.7.2.1 配置 GUC 参数 sys_kwr.language：指定 KDDM 报告输出的语言，TEXT 类型，有效的值：’english’/’eng’/’chinese’/’chn’，默 认’chinese’，即按中文显示报告。 说明：KDDM 报告依赖 KWR 快照，关于 KWR 快照部分 GUC 参数，请参考 KWR 相关章节。 3.3.7.2.2 生成 KDDM 报告 1. 生成 kddm 报告 SELECT * FROM perf.kddm_report(1, 2); 说明：KDDM 报告也会自动在数据库服务器 DATA 目录的 sys_log 下保存一份。 2. 生成 kddm 报告并保存到指定文件 SELECT * FROM perf.kddm_report_to_file(1, 2, '/home/kingbase/kddm_1_2.txt'); 3. 为指定 query id 的 SQL 语句生成 SQL 报告 SELECT * FROM perf.kddm_sql_report(1, 2, 5473583404117387630); 根据快照范围和具体的 SQL ID，给出 SQL 详细报告，主要用于分析耗时较多的 SQL 语句占用的 CPU，IO 资 源的使用情况。 125 第 3 章 第二部分性能诊断 3.3.7.3 KDDM 报告 kddm 报告根据 KWR 快照内容，给出以下的建议和报告： 1. 数据库时间分解报告 2. CPU 相关建议 • TOP SQL 建议 • 使用扩展 SQL 协议建议 • CPU 负载高建议 • 优化回滚事务建议 • 优化堆页面裁剪建议 • 使用索引建议 3. 等待事件相关建议 • TOP 等待事件建议 • LightLock 类等待事件建议 • IO 类等待事件建议 • Client 类等待建议 4. 完整 SQL 列表 每条具体建议包含三项内容： • 建议依据：描述了数据库当前存在的性能问题。 • 建议动作：按优先级给出性能优化的建议操作，通过指定的操作选项能够改善当前存在的性能问题。 • 参考信息：描述了当前性能问题的具体指标。 示例报告：TOP SQL 建议 该报告展示可以通过 TOP SQL 找到消耗资源最多的 SQL 语句，当设置成如下配置参数时，对外提供树状结构 显示 TOP SQL 的报告。 sys_stat_statements.track = 'all' 示例报告：使用索引建议 126 第 3 章 第二部分性能诊断 该报告展示可以通过使用索引来提高 SQL 性能的建议，不仅给出了具体的 SQL 语句，还会给出创建索引的 DDL 语句，以及预期收益率。 该报告也给出了存在但未被使用的索引，DBA 可以根据实际情况进一步操作。 合理的索引使用可以加速检索过程。 3.3.7.4 GUC 参数建议 KDDM 提供 GUC 参数建议功能，根据数据库服务器的硬件情况和用户指定的业务类型，显示建议结果： SELECT * FROM perf.kddm_guc_advisor(); --最大连接数：300，使用默认值 --CPU 核心数：96，自动获取 --总物理内存：256 GB，自动获取 --应用类型：OLTP，使用默认值 --建议参数列表： max_connections = 300 shared_buffers = 64GB effective_cache_size = 192GB maintenance_work_mem = 2GB work_mem = 55MB wal_buffers = 16MB min_wal_size = 1GB max_wal_size = 4GB 127 第 3 章 第二部分性能诊断 max_worker_processes = 24 max_parallel_workers_per_gather = 4 max_parallel_workers = 24 max_parallel_maintenance_workers = 4 可以通过制定最大连接数、CPU 核心数、内存大小、业务类型等参数来定制化 GUC 参数建议列表，函数的声 明如下： TEXT perf.kddm_guc_advisor( IN conn bigint = 0, IN service_type TEXT = 'oltp', IN cpu bigint = 0, IN memory bigint = 0); 参数说明： • conn：最大连接数，默认 300，范围：1-1000 • cpu：CPU 核心数，默认自动获取，范围：1-1000 • memory：内存大小，单位为 MB，默认自动获取，范围：128MB - 1T • service_type：业务类型，默认为’oltp’，可选范围： – oltp：事务型数据库服务器 – olap：分析性数据库服务器 – web：Web 服务器 – desktop：桌面应用 – mixed：混合应用 3.3.8 KWR 运行期对比报告 KWR DIFF KWR 报告是 Diff 报告的基础，在数据库运行过程中，通常在业务的高峰期和低谷期，或者在参数调整之后， 数据库的性能指标都会发生变化，那么分析这种变化的根因对于我们分析数据库性能，进行优化调整是非常必要的。 Diff 报告的作用就是分析两个 KWR 报告之间的差异，找出性能变化的原因或分析性能优化的效果。 Diff 报告仅支持 HTML 格式输出。 3.3.8.1 通过 快速生成 Diff 报告 KWR 自动快照或者手动快照创建一系列 KWR 快 照， 然 后 就 可 以 通 过 perf.kwr_diff_report_to_file({snap_1}, {snap_2}, {snap_3}, {snap_4}, {file_path}) 来创建 Diff 报告： 参数说明： 128 第 3 章 第二部分性能诊断 • snap_1: 生成第一份 KWR 报告的起始快照 ID • snap_2: 生成第一份 KWR 报告的结束快照 ID • snap_3: 生成第二份 KWR 报告的起始快照 ID • snap_4: 生成第二份 KWR 报告的结束快照 ID • file_path: 报告生成地址，示例：’/home/username/kwr_diff_report.html’ CREATE EXTENSION sys_kwr; SELECT * FROM perf.create_snapshot(); CREATE TABLE t1(a int); INSERT INTO t1 SELECT generate_series(1, 1000000); SELECT * FROM perf.create_snapshot(); INSERT INTO t1 SELECT generate_series(1, 1000000); SELECT * FROM perf.create_snapshot(); INSERT INTO t1 SELECT generate_series(1, 1000000); SELECT * FROM perf.create_snapshot(); SELECT * FROM perf.kwr_diff_report_to_file(1, 2, 3, 4, '/home/kingbase/kwr_diff_rpt_00.html'); 3.3.8.2 GUC 参数说明 KWR Diff 报告属于 KWR 的一个子模块，继承 KWR 的现有 GUC 参数。具体内容请参考 SYS_KWR 小节中 关于配置 GUC 参数的说明。 3.3.8.3 用户接口说明 生成 Diff 报告的接口有两个： perf.kwr_diff_report_to_file({snap_1}, {snap_2}, {snap_3}, {snap_4}, {file_path}) 该接口在用户指定的目 录下生成 Diff 报告。 perf.kwr_diff_report({snap_1}, {snap_2}, {snap_3}, {snap_4}) 该接口在默认的数据目录下的 sys_log 目录中 生成 Diff 报告，同时在 ksql 中生成在线报告。如果 sys_log 目录不存在，那么仅生成一份在线报告。默认报告的命 名格式为：kwr_diff_1_2_3_4_20220531_140633.html，即前缀 kwr_diff_4 个快照 ID_ 年月日 _ 时分秒.html。 3.3.8.4 查看 Diff 报告 3.3.8.4.1 报告结构 Diff 报告主要由三部分组成： 1、报告头： 这部分主要是列出数据库实例的版本、两份 KWR 报告的快照信息。 129 第 3 章 第二部分性能诊断 2、报告摘要： 这部分的主要报告是：主机配置、负载分析、实例效率百分比（目标 100%）、Top 10 前台等待事件、Top 10 前 台等待事件分类、主机 IO、IO 分析、内存统计以及 SQL 语句数统计。 3、报告主体： 报告主体提供了更加全面的性能指标对比信息，主要包括：时间模型和等待事件统计、内存统计、实例 IO 统 计、锁活动统计、关键活动统计、TOP SQL 统计、后台写统计、数据库统计、数据库对象统计和配置参数。 3.3.8.4.2 报告内容 Diff 报告内容在统计数据方面与原始 KWR 报告一致，同时提供了对比信息。下面举几个例子简要说明报告数据 的查看方法，其它报告相同。 在 Diff 报告中，1st 表示第一份 KWR 报告的统计数据，2nd 表示第二份 KWR 报告的统计数据，Diff 表示两份 报告统计量的差值，% Diff 表示第二份报告相比第一份报告统计量的变化百分比。 在报告头中，显示了数据库实例信息，启动时间，版本信息以及两份 KWR 报告的快照信息，并显示了快照时长 和数据库时间的%Diff。 在 Top 10 前台等待事件和 Top 10 前台等待事件分类这两个 diff 报告中，为了方便对比，查看两份 KWR 报告中 等待事件的变化情况，分成左右两部分进行了展示。如果某个事件仅在一份 KWR 报告中出现，那么在另一份 KWR 报告中用’-’ 表示。 在时间模型统计报告中，按“% 数据库时间”的 Diff 排序对两份 KWR 报告进行了对比。 130 第 3 章 第二部分性能诊断 在按数据库时间排序的 SQL 语句报告中，从平均时间 (ms)、总占比 (%)、CPU 占比 (%)、等待占比 (%) 这四 个方面对比了两份 KWR 报告中的 SQL 语句。点击 QueryID 列可以跳转到完整 SQL 列表的对应的 SQL 语句上。 131 第 4 章 第三部分性能优化 4 第 章 第三部分性能优化 简而言之，性能调优就是在不影响正确性的前提下，使得应用程序运行速度更快，完成特定功能所需的时间更 短。本部分将包含以下主题： • 性能优化方法 • CPU 资源优化 • IO 资源优化 • 网络资源优化 • 数据库等待资源、锁资源优化 4.1 性能优化方法 性能调优的实施应该优先考虑瓶颈点的优化，可以带来最大的性能收益。 性能调优主要从下面几个方面着手： 1. 数据库优化 数据库方面的优化主要包括 SQL 优化和数据库内核参数优化两部分。 • SQL 优化主要编写高性能查询 sql，以及对已有的查询语句进行修改，使得查询语句得出来的执行计划最优，同 时还需要调整有关的数据库参数来调整执行计划。 • 内核参数优化主要是根据不同应用调节不同内核参数进行优化，比如经常有大量数据频繁访问可以调大 shared_buffers，比如大量相同的（不包括常量）sql 语句执行而且 cpu 比较高，可以开启执行计划缓存。 2. 常用 OS 优化 主要是 cpu，内存，磁盘，网络的性能调优，比如磁盘的调度策略的优化，预读的优化，启动磁盘写缓存的策 略，directio，异步 IO 等优化。 3. 应用层优化 在上述措施无法奏效的情况下，考虑修改应用逻辑来进行优化，如更高效的 SQL 语句、减少某些应用逻辑、查 询分页等。 132 第 4 章 第三部分性能优化 4. 硬件优化 在上述措施无法奏效的情况下，考虑提升硬件规格：比如提升 cpu 频率、增加 CPU 核心数、比如提升磁盘能力 采用 ssd 盘阵、更换更高带宽的网络等。 5. 架构设计优化 在上述措施无法奏效的情况下：考虑架构层面的优化，如使用缓存数据库、读写分离、分库分表等措施。 下文中的优化手段将从性能优化点出发来做介绍，硬件和架构层面的优化不再展开。 4.2 CPU 资源优化 当数据库产生遇到 CPU 瓶颈时，通常我们可以考虑优化数据库中的慢语句。可以按照如下方式找出并进行调 整： 针对慢语句做执行计划分析，根据问题原因选用合理的手段。 本章节包含以下内容： • CPU 常用优化手段 • CPU 优化参数 4.2.1 CPU 常用优化手段 常用语句优化手段如下： • 收集统计信息 统计信息不准很有可能误导执行计划使用不合理的执行计划。 • 创建索引 高效的索引可以大幅度减少响应时间。 • 控制执行计划 对于复杂语句，由于优化器的局限性，可能会因为估算不准等原因导致生成执行计划不是最优，可以 考虑通过 HINT 或者修改参数的方式人为控制合理的执行计划。 • 使用并行 CPU 资源充裕的情况下，复杂的 SQL 语句可以使用并行来加快执行速度。 • 调整语句 使用更高效的方式来改写 SQL 语句，如 UNION 改成 UINION ALL，将条件尽量下推到基表等。 • 使用执行计划缓存 133 第 4 章 第三部分性能优化 使用 PBE 可以缓存执行计划，对于大量的短语句来说，执行之前的词法语法语义解析、生成执行计 划阶段在总时间的占比中会比较高，使用执行计划缓存可以有效降低数据库响应时间。 • 绑核 将服务进程 kingbase 绑定在固定的 CPU 核号上，增加 CPU 的 cache 命中率，以此提升性能指标。 具体见bindcpulist 。 有关 SQL 优化的详细内容，请参阅《KingbaseES 数据库 SQL 调优指南》。 4.2.2 CPU 优化参数 4.2.2.1 bindcpulist 功能介绍：启动 KingbaseES 服务进程时，根据此参数给出的 CPU 核号列表信息，利用操作系统的 CPU 亲和性 接口函数，将当前服务进程绑定在一个固定的 CPU 核号上，以此提高性能。绑核顺序是按参数列表顺序进行绑定。 参数级别：PGC_POSTMASTER。 语法规则：正确格式为 start_cpunum-end_cpunum | cpunum [, ...]。即：开始的 cpu 核号-结束的 cpu 核号， 或单独给出 CPU 核号。各项之间用逗号隔开。其他非数字，非’-’，非’,’ 的字符都视为非法字符。如 bindcpulist = ‘0-95’，表示将数据库进程绑定到 0 到 95 号 CPU 核心；bindcpulist = ‘0-3,10,20-30,50’，表示将数据库进程绑 定到 0 到 3 号核、10 号核、20 到 30 号核、50 号核新上。 此参数的使用受 License 文件中检查项：CPU 检查（cpuNum）限制。具体如下： 组 合 License 检查项：cpuNum GUC 配置项：bindcpulist 1 off 不可配置。由于 cpuNum 检查项无效，数据库启动失败 2 on，控制值为：0 可配置。配置参数严格检查，出现非法字符或出现超过当前服务器最大 序号 CPU 核号时，报错，数据库启动失败 3 on，控制值为：可用的最 可配置。配置参数中若出现大于可用的最大 CP 报错，数据库启动失败 大 CPU 核号 应用场景：CPU 利用率较高，且存在大量活跃链接的时候，通过绑核可以减少进程在不同核心之间切换带来的 上下文开销。 4.3 IO 资源优化 IO 资源的优化手段较多，一般包括： • 优化数据库内存参数 134 第 4 章 第三部分性能优化 • 优化数据库 I/O 相关参数 • 调整 IO 调度策略 • 多 IO 设备分担压力 • 优化文件系统挂载 • 配置预读和 IO 请求队列 4.3.1 优化数据库内存参数 KingbaseES 的内存主要包括共享内存和私有内存两大类，不合理的内存使用可能会带来 IO 问题：如 shared_buffers 过小导致数据换进换出，work_mem 过小导致排序使用临时文件。合理的使用内存参数可以较好的 发挥硬件的能力。 在做内存调整时也需要考虑 KingbaseES 使用的内存不要过大导致 swap。 KingbaseES 使用内存总大小为： shared_buffers(数据) + wal_buffers（日志）+ maintenance_work_mem（创建索引排序时使用）+ n*work_mem(n 为并发做排序的连接数) + 服务进程上下文使用的内存 (无法精确估计大小) + m*thread_stack_size (thread_stack_size 为进程栈的大小, KingbaseES 默认为 1MB; m 为当前连接数)； 在进行内存调整时首先要清楚是哪个部分的内存出现了问题，有针对性的进行参数调整。 4.3.1.1 共享内存参数 4.3.1.1.1 shared_buffers 原理：数据库服务器使用的共享内存缓冲区的数量，主要用于缓存数据，根据需求一般不能设置超过 80% 的内 存，但至少是 20%。 应用范围：数据库本身, 查询的数据量比较大，比较频繁使用到。 4.3.1.1.2 wal_buffers 原理：日志缓冲区大小, 共享内存里用于 WAL 数据（日志）的磁盘页面缓冲区的数目。 应用范围：如果单位时间事务的数据修改数据量较大，也就是事务的写比较多的情况，如果 IO 是瓶颈，可以调 整这个值到很大，有很多的缓存后，就不会频繁的写磁盘，降低 IO。缺省值为 8 ，8 个页面是 64k。当然也可以很 小，这个设置只需要大到能保存下一次事务生成的 WAL 数据即可, 因为这些数据在每次事务提交时都会写入磁盘。 4.3.1.2 私有内存区参数 4.3.1.2.1 work_mem 原理：内部排序和哈希操作可使用的工作内存大小。该内存是在开始使用临时磁盘文件之前使用的内存数目。 135 第 4 章 第三部分性能优化 应用范围：数据比较多大的情况，主要排序的数据有关系，排序数据越大，设置的就越大，比如 16g 内存，tpch 测试，单用户 10g 规模数据，设置 2g 的 work_mem。数值以 kB 为单位的, 缺省是 1024(1MB)，比如 tpcc 1000warehouse，并发 50 个，设置 20mb 即可。 注意: 对于复杂的查询, 可能会同时并发运行好几个排序或者哈希操作, 每个都会使用这个参数声明的这么多内存, 然后才会开始求助于临时文件。同样, 好几个正在运行的会话可能会同时进行排序操作。因此使用的总内存可能是 work_mem 的好几倍。ORDER BY, DISTINCT 和 mergejoin 都要用到排序操作, 而哈希操作在哈希连接、哈希聚 集和以哈希为基础的 IN 子查询处理中都会用到。 4.3.1.2.2 maintenance_work_mem 原理：在维护性操作 (比如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等) 中使用的 最大的内存数。 应用范围：在维护性操作 (比如 VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY 等）调整 大小，默认是 16MB，比如创建索引的索引数据很大，比如 10g，如果内存允许就可以调整这个参数，一般在需要创 建索引的时候调大，创建完之后再调小。 注意: 因为在一个数据库会话里, 任意时刻只有一个这样的操作可以执行, 并且一个数据库安装通常不会有太多这样 的工作并发执行, 把这个数值设置得比 work_mem 更大是安全的，更大的设置可以改进清理和恢复数据的速度，但是 要避免内存用尽的情况。 4.3.2 优化数据库 I/O 相关参数 4.3.2.1 fsync 原理：用来设置日志缓冲区刷盘时, 需要确认已经将其写入了磁盘。如果关闭该功能，那么由操作系统调度磁盘 写的操作, 能更好利用缓存机制, 提高 IO 性能。该性能的提高伴随了数据丢失的风险, 当操作系统或主机崩溃时, 不保 证刷出的日志是否真正写入了磁盘。 应用范围：应依据操作系统和主机的稳定性来配置，一般在追求极限性能是修改。 4.3.2.2 full_page_writes 原理：full_page_writes (boolean)，打开这个选项的时候, 数据库服务器在检查点 (checkpoint) 之后对页面的第 一次写入时将整个页面写到 WAL 里面。这么做是因为在操作系统崩溃过程中可能只有部分页面写入磁盘, 从而导致 在同一个页面中包含新旧数据的混合。在崩溃后的恢复期间, 由于在 WAL 里面存储的信息不够完整, 无法完全恢复该 页。把完整的页面影像保存下来就可以保证正确存储页面, 代价是增加了写入 WAL 的数据量。因为 WAL 重放总是 从一个检查点开始的, 所以在检查点后每个页面第一次改变的时候做 WAL 备份就足够了。因此, 一个减小全页面写开 136 第 4 章 第三部分性能优化 销的方法是增加检查点的间隔参数值。把这个选项关闭会加快正常操作的速度, 但是可能导致系统崩溃或者掉电之后 的数据库损坏, 它的危害类似关闭 fsync , 只是比较小而已。 应用范围：关闭可以改善性能，但是需要一定条件，如果有减小部分页面写入风险的硬件支持 (比如电池供电的 磁盘控制器) 或者文件系统支持 (比如 ReiserFS 4), 并且他们可以把风险降低到一个可以接受的低范畴, 那么可以关闭 这个选项。如果为了极限性能，并且是 IO 的问题，可以考虑使用。 4.3.2.3 commit_delay 原理：事务提交和日志刷盘的时间间隔, 应用范围：如果并发的非只读事务数目较多, 可以适当增加该值, 使日志缓冲区一次刷盘可以刷出较多的事务, 减 少 IO 次数, 提高性能。需要和 commit_sibling 配合使用。 4.3.2.4 commit_siblings 原理：触发 commit_delay 的并发事务数, 只有系统的并发活跃事务数达到了该值, 才会等待 commit_delay 的时 间将日志刷盘, 如果系统中并发活跃事务达不到该值,commit_delay 将不起作用, 防止在系统并发压力较小的情况下, 事务提交后空等其他事务造成时间的浪费。 应用范围：应根据系统写的负载配置，并发越大就配置越大，和 commit_delay 一起使用。 4.3.2.5 checkpoint_timeout 原理：两个相邻检查点之间的时间间隔，用于刷数据到磁盘。 应用范围：根据系统写的负载设置, 一般不要太频繁。可以和后台写线程配置相关参数配合使用，一般 pk 测试过 程中会设置的很长（如果内存够的话），可以不用频繁刷。 4.3.2.6 bgwriter_delay 原理：后台写线程的自动执行时间, 后台写线程的作用是将 shared_buffer 里的脏页面写回到磁盘, 减少 checkpoint 的压力。默认是 200ms。 应用范围：如果系统数据修改的压力一直很大, 建议将该时间间隔设置小一些, 以免积累的大量的脏页面到 checkpoint, 使 checkpoint 时间过长 (checkpoint 期间系统响应速度较慢)。tpcc 一般设置 10ms 可以减少压力； 4.3.2.7 bgwriter_lru_maxpages 原理：后台写线程一次写出的脏页面数，默认 100 个页面。 应用范围：依据系统写的负载修改，比如 tpcc，因为时间间隔短，一次写入的少页面少，比如设置 75。 137 第 4 章 第三部分性能优化 4.3.2.8 bgwriter_lru_multiplier 原理：后台写线程根据最近服务线程需要的 buffer 数量乘上这个比率估算出下次服务线程需要的 buffer 数量, 再 使用后台写进程写回脏页面, 使缓冲区能使用的干净页面达到这个估计值。默认是 2.0 应用范围：应根据系统写负载来配置。 4.3.3 调整 IO 调度策略 采用合适的磁盘调度算法，IO 调度策略一般包括：CFQ，Deadline，NOOP 和 Anticipatory 。 对于机械磁盘来说，deadline 是数据库的最佳选择，比如 tpcc 一般采用 deadline。固态硬盘一般可以不做调整。 1. CFQ 原理：公平算法原则，对于通用服务器来说通常是最好的选择。它试图均匀地分布对 I/O 带宽的访 问。在多媒体应用, 总能保证 audio、video 及时从磁盘读取数据。同时对于其他各类应用表现也很 好。每个进程一个 queue，每个 queue 按照上述规则进行 merge 和 sort。进程之间 round robin 调 度，每次执行一个进程的 4 个请求。 应用范围：默认算法，适用于大部份系统应用，适用于 io 大小非常均匀的场景 2. Deadline 原理：这个算法试图把每次请求的延迟降至最低。该算法重排了请求的顺序来提高性能。使用轮询的 调度器, 简洁小巧, 提供了最小的读取延迟和高的吞吐量。 应用范围：适合小文件读写，跳跃式读写，零散读写，特别适合于读取较多的环境，稍微复杂点的 OLTP 最好更换为 Deadline 3. NOOP 原理：这个算法实现了一个简单 FIFO 队列。他假定 I/O 请求由驱动程序或者设备做了优化或者重排 了顺序 (就像一个智能控制器完成的工作那样)。 应用范围：在有些 SAN 环境下，这个选择可能是最好选择。适用于随机存取设备, no seek cost，非 机械可随机寻址的磁盘。I/O 性能不是瓶颈的时候使用 NOOP，也适用于带有 TCQ 的磁盘。 4. Anticipatory 原理：这个算法推迟 I/O 请求，希望能对它们进行排序，获得最高的效率。同 deadline 不同之处在 于每次处理完读请求之后, 不是立即返回, 而是等待几个微秒，在这段时间内, 任何来自临近区域的请 求都被立即执行. 超时以后, 继续原来的处理. 基于下面的假设: 几个微妙内, 程序有很大机会提交另一 次请求. 调度器跟踪每个进程的 io 读写统计信息, 以获得最佳预期. 应用范围：适合大文件读写，整块式，重复读写 (web server)，适用于文件服务器 ftp/samba 等，不 适用数据库场景。 磁盘调度算法的设置： 138 第 4 章 第三部分性能优化 echo deadline >/sys/block/sda/queue/scheduler 将 sda 的调度策略设置为 deadline。 我们也可以直接在/etc/grub.conf 的 kernel 行最后添 elevator=deadline 来永久生效。 4.3.4 多 IO 设备分担压力 把数据、日志、索引放到不同的 I/O 设备上，或者使用 RAID 设备，以此来利用多个设备的 IO 能力来分担 IO 压力。 4.3.5 优化文件系统挂载 优化挂载文件系统的参数，推荐使用 xfs 和 ext4 文件系统。 挂载 XFS 参数： (rw, noatime,nodiratime,nobarrier) 挂载 ext4 参数： ext4 (rw,noatime,nodiratime,nobarrier,data=ordered) 比如：noatime 和 nodiratime ，去掉更新访问的时间。 nobarrier：现在的很多文件系统会在数据提交时强制底层设备刷新 cache，避免数据丢失，称之为 write barriers。但是，其实我们数据库服务器底层存储设备要么采用 RAID 卡，RAID 卡本身的电池可以掉电保护；要么采用 Flash 卡，它也有自我保护机制，保证数据不会丢失。所以我们可以安全的使用 nobarrier 挂载文件系统。 • data=ordered：(默认) 文件系统日志区仅存放元数据 • data=journal：把数据与元数据都先写入日志区（安全，慢） • data=writeback：不按日志区元数据顺序来写数据（不安全，快） 4.3.6 配置预读和 IO 请求队列 主要是指操作系统的预读和排队的大小的调整，主要包括： 1. readahead 预读扇区数调整，预读是提高磁盘性能的有效手段，目前对顺序读比较有效，主要利用数据的局部 性特点。比如在笔者的系统上，通过实验设置通读 256 块扇区性能较优。 调整命令： echo 256 /sys/block/sdb/queue/read_ahead_kb 139 第 4 章 第三部分性能优化 2. I/O 请求队列长度（调大能增加硬盘吞吐量，但要占用更多内存）： /sys/block/sdb/queue/nr_requests 调整情况： 比如：随机读取修改 减少预读：/sys/block/sdb/queue/read_ahead_kb，默认 128，调整为 16 增大队列：/sys/block/sdb/queue/nr_requests，默认 128，调整为 512 比如：顺序读取修改 增大预读：/sys/block/sdb/queue/read_ahead_kb，默认 128，调整为 256/512 减少队列：/sys/block/sdb/queue/nr_requests，默认 128，调整为 64/32 4.4 网络资源优化 本章节包含以下内容： • 网络常用优化手段 • 网络优化相关参数 4.4.1 网络常用优化手段 对于网络层性能问题，一般应该考虑减少网络的传输，如： • 应用层修改 SQL 语句或者逻辑，通过 LIMIT 等方式限制返回行数。（根据实际应用需求选择） • 在接口端配置 defaultRowFetchSize，使用批量返回模式。需要注意的是，对于应用确实需要使用较多返回数据 的情况，批量返回的模式反而会带来额外的开销。另外，如果需要在自动提交模式下使用批量返回模式，需要 在服务器端做额外配置，具体见网络优化相关参数 。 • 将占用网络资源较多的语句或者操作错峰执行 4.4.2 网络优化相关参数 4.4.2.1 enable_autocommit_fetch 原 理： 当 客 户 端 设 置 了 autocommit=true（自 动 提 交 模 式） 时， 对 于 查 询 请 求， 若 en- able_autocommit_fetch=off， 服 务 端 将 一 次 性 返 回 给 客 户 端 符 合 条 件 的 所 有 记 录。 对 于 结 果 集 的 数 据 量 比 较 大 的 情 况， 服 务 端 将 有 比 较 大 和 集 中 的 CPU 计 算 量 和 网 络 带 宽 使 用 量， 导 致 性 能 下 降。 若 en- able_autocommit_fetch=on，服务端将按照客户端指定 fetchsize 值（JDBC 客户端应用调用 setFetchSize 方法 140 第 4 章 第三部分性能优化 设置）的数据量按需执行查询，并按需获取数据集返回给客户端符合条件的记录。解决大数据集时服务端高的 cpu 计 算量和网络带宽使用量的问题。默认值是 off。GUC 参数级别为：PGC_BACKEND。 应用范围：当应用处理比较大的数据集且有性能上的需求时配置。 4.4.2.2 enable_autocommit_crossquery 原理：在 enable_autocommit_fetch=on 的情况下有效。当 enable_autocommit_crossquery =on 时，客 户端语句交叉执行，在不同语句执行后，前语句按照服务端调用 HoldablePortal 方式，可以继续执行。当 enable_autocommit_crossquery=off 时，客户端语句交叉执行，在不同语句执行后，前语句不能继续执行。默认值是 off。GUC 参数级别为：PGC_BACKEND。 由于服务端调用了 HoldablePortal 处理，可能出现 CPU 计算量比较大和使用临时文件的情况（临时文件的使用 与 work_mem 的配置有关，当执行查询获取的数据集记录大小值大于 work_mem 配置值的一半时，将使用临时文 件，磁盘的 IO 操作有影响性能的预期）。 4.5 数据库等待资源、锁资源优化 需具体分析等待资源。 • 修改数据库内核参数 • 修改应用逻辑 4.5.1 开启 XLogInsertLockFree 优化 当发现 wal_insert 等待事件占用时间占比较高时，可以开启 XLogInsert 无锁化优化。在开启 XLogInsert 无锁化优化的前提下，可以消除 wal_insert 等待事件，实现性能的提升。在 kingbase.conf 中将参数 enable_xlog_insert_lock_free 设置为 on，然后重启数据库，就会开启 XLogInsertLockFree 优化。 开启此参数后，将会有以下参数失效： commit_delay，事物提交和对应的 WAL 刷写到磁盘之间的时间间隔。 commit_siblings，在执行 commit_delay 前，最小的并发事物数。 141 版权声明 版权声明 北京人大金仓信息技术股份有限公司（简称：人大金仓）版权所有，并保留对本手册及本声明的一切权利。 未得到人大金仓的书面许可，任何人不得以任何方式或形式对本手册内的任何部分进行复制、摘录、备份、修 改、传播、翻译成其他语言、将其全部或部分用于商业用途。 免责声明 本手册内容依据现有信息制作，由于产品版本升级或其他原因，其内容有可能变更。人大金仓保留在没有任何通 知或者提示的情况下对手册内容进行修改的权利。 本手册仅作为使用指导，人大金仓在编写本手册时已尽力保证其内容准确可靠，但并不确保手册内容完全没有错 误或遗漏，本手册中的所有信息也不构成任何明示或暗示的担保。 技术支持 • 人大金仓官方网站：http://www.kingbase.com.cn/ • 人大金仓文档中心：http://help.kingbase.com.cn/ • 全国服务热线：400-601-1188 • 人大金仓技术支持与反馈信箱：support@kingbase.com.cn 142 服务周期承诺 服务周期承诺 由于市场需求在不断变化，技术创新和发展的进程不断加剧，产品的版本更迭不可避免。人大金仓对于产品版本 生命周期的有效管理，有助于您提前规划项目，更好地从产品服务终止上过渡。 表 1: KingbaseES 产品生命周期里程碑 关键里程碑点 定义 产品发布日期 产品正式发布版本，即 GA（general availability）版本的发布日期。 停止销售日期 正式停止销售的日期，版本停止接受订单日。该日之后，产品将不再销售。 停止功能升级日期 在该日期之后，不再提供新特性和新硬件支持。但依旧提供错误修复、安全修复、功 能维护等服务。 停止功能维护日期 在该日期之后，不再维护功能，修复问题。但依旧提供安全修复等服务 停止安全维护日期 在该日期之后，不再发布补丁版本修复中高风险漏洞，仅提供有限的支持。 产品服务终止日期 停止提供产品服务和支持的日期。包括软件维护版本，缺陷修复，以及针对该产品的 所有服务支持（包括服务热线和远程/现场支持）。 服务周期策略 金仓数据库管理系统 KingbaseES 产品确保以下的服务周期： 1）产品自发布之日起至产品停止功能升级（包含新特性、新硬件支持）之日不少于 5 年。 2）产品停止功能升级之日起至产品停止功能维护（主要包括问题修复）之日不少于 4 年。 3）产品功能维护停止之日起至产品停止安全维护（包括中高风险漏洞修复）之日不少于 2 年。 服务终止策略 金仓数据库管理系统 KingbaseES 产品确保在销售后，至少提供 6 年的服务支持。 注意: 人大金仓将会综合各方因素来确定产品服务终止日期。并将在实际产品服务终止日期之前至少 90 天，通过公 143 服务周期承诺 开方式宣布产品服务终止日期。 144