【DB吐槽大会】第48期 - PG 性能问题发现和分析能力较弱

背景

1、产品的问题点

• PG 性能问题发现和分析能力较弱 1

• 缺乏基准

• 为什么要定义基准? 有了基准才有度量标准, 不会出现为了优化而优化的情况, 走火入魔.
• 《产品经理：依存度、规模化、可度量、周期性》

• 宏观分析较弱

• pg_stat_statements 分析出来TOP SQL, 它合理不合理都需要深入分析.
• 缺乏p99, p90这类指标

• 微观分析较弱

• 例如连接数激增, 是业务行为还是RT抖动、锁等待导致?
• SQL性能问题如何分析? 怎么优化?

• 缺乏如何优化的报告
• 缺乏可提升多少的预测

• 为什么需要可预期? 只有可预期的目标才不是盲盒, 才不会有惊讶, 才能用于决策到底要不要实施优化. 预期目标需要有数据支撑、逻辑支撑.

• PG 性能问题发现和分析能力较弱 2

• 没有内置的 AWR, 较难发现宏观问题 (等待事件)
• 没有内置的 性能洞察, 较难发现指定时间段的问题
• 缺少 trace功能, 类似Oracle (10046, 10053) , 较难诊断SQL问题. trace dev para, auto explain, rsq, lock, query, iotiming, ...

• 很多诊断需要编译时定义宏 TRACE_SORT、LOCK_DEBUG、BTREE_BUILD_STATS、WAL_DEBUG
• https://www.postgresql.org/docs/14/runtime-config-developer.html

• 内置的probe使用门槛非常高, 需要开启debug 、需要使用dtrace或者stap 设置探针进行分析
• 只有重量锁等待日志打印, 缺少LW锁等待统计, 并且没有视图分析SQL级别的等待事件, 等待事件需要到log中查询,

• 开启 log_lock_waits , 并且只 记录超过 deadlock_timeout 的SQL.

• 对SQL的单点问题分析较弱, SQL在过去发生的问题很难分析. (是执行计划的问题、锁等待的问题、还是资源竞争的问题?)

• 只有锁等待可能被记录下来, 执行计划不会被记录, 每个执行node花费的时间、扫描的blocks、返回的记录数, OP耗费的时间等都需要记录分析 :
• 执行计划的记录需要开启auto explain , 设置执行超时阈值, 并且需要等待问题再次发生, 而且不能针对单个sql的超时时间进行阈值设置, 只能设置全局阈值. 每条SQL的执行时长需求不一样, 单个阈值无法满足需求. (例如有些SQL就是分析型的, 本身就慢. )

2、问题点背后涉及的技术原理

• 1 基准是什么? 如何定义?

• 如何定义标准 1 业务角度: SQL ID, QPS, RT

• QPS 业务相关, 请求量
• RT 数据库相关, 响应速度

• 如何定义标准 2 DB角度: CPU使用率, IO使用率, 网络使用率, 空间使用率

• 2 资源水位如何?

• CPU, IO, 网络, 空间

• 3 数据库有性能问题吗?

• 宏观

• 4 什么问题?

• 微观

• 5 影响哪些业务(SQL ID维度)? 比正常情况(标准)差了多少?
• 6 什么时间发生的?
• 7 为什么会有这个问题?
• 8 如何解决?
• 9 预期优化后的SQL RT和QPS能提升多少? 能降低多少资源使用率(水位)?
• 10 如何规避同类问题?
• 11 如何提前发现问题?

3、这个问题将影响哪些行业以及业务场景

• 通用

4、会导致什么问题?

• PG提供的性能问题发现和分析手段有限, 发现问题的门槛较高, 需要专业DBA

5、业务上应该如何避免这个坑

• 宏观上监测资源有没有达到瓶颈: CPU使用率, IO使用率, 网络使用率, 空间使用率.

• 《Use PostgreSQL collect and analyze Operation System statistics》

• 根据业务给出的测试模型、测试数据、并发等数据, 压测数据库性能基准: sql id, qps, rt等指标

• pgbench + 《PostgreSQL 如何快速构建 海量 逼真 测试数据》

• SQL层面监测top SQL, 按TOP SQL逐条分析有没有优化空间.

• 《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级 - 数据库慢、卡死、连接爆增、慢查询多、OOM、crash、in recovery、崩溃等怎么办?怎么优化?怎么诊断?》

• 对于过去的问题, 开启io timing, auto_explain, log_lock_waits. 等待问题再次发生, 从日志中分析性能抖动的原因.

• 《PostgreSQL 函数调试、诊断、优化 & auto_explain & plprofiler》

• 现场分析SQL问题, 开启各个宏、开启debug、开启各个跟踪参数, 分析SQL问题所在.

• 《PostgreSQL 兼容Oracle sql_trace 10046 10053 方法 - SQL诊断跟踪》

• AWR插件

• 《PostgreSQL pg_stat_statements AWR 插件 pg_stat_monitor , 过去任何时间段性能分析 [推荐、收藏]》

• 性能洞察功能

• 《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》

• postgrespro的插件pgpro-pwr

• https://postgrespro.com/docs/enterprise/13/pgpro-pwr

• systemtap , dtrace

• 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 5 - read|write relation》

6、业务上避免这个坑牺牲了什么, 会引入什么新的问题

• 门槛非常高, 而且有些需求不能很好的解决:

• 基准
• 问题优化后的提升预测
• auto_explain 单一阈值问题
• 等待事件无法统计
• 不支持自动化推荐解决方案

• debug编译、宏、auto_explain、iotiming都会引入开销

7、数据库未来产品迭代如何修复这个坑

• 希望全方位内置基准、性能洞察、分析、自动化推荐优化方法、优化效果预测等能力.