其他
第48期吐槽:DBA都在寻找的监控诊断优化仙丹
文中参考文档可点击阅读原文打开, 推荐《最好的PostgreSQL学习镜像》。
第48期吐槽:PG 性能问题发现和分析能力较弱
1、产品的问题点
PG 性能问题发现和分析能力较弱 1 为什么需要可预期? 只有可预期的目标才不是盲盒, 才不会有惊讶, 才能用于决策到底要不要实施优化. 预期目标需要有数据支撑、逻辑支撑. 例如连接数激增, 是业务行为还是RT抖动、锁等待导致? SQL性能问题如何分析? 怎么优化? pg_stat_statements分析出来TOP SQL, 它合理不合理都需要深入分析. 例如总耗时最多的sql, 它是否已经优化到极限了? 其实不看一下执行计划也不好下判断. 是不是可以加一个维度, SQL还有多大的优化提升空间?缺乏 p99, p90这类指标为什么要定义基准? 有了基准才有度量标准, 不会出现为了优化而优化的情况, 走火入魔. 缺乏基准 宏观分析较弱 微观分析较弱 缺乏报告来指出每一条SQL应该如何优化. 缺乏可提升多少性能的预测(例如展示耗时、IO、CPU开销、cost等维度的提升预期) PG 性能问题发现和分析能力较弱 2 只有锁等待可能被记录下来, 执行计划不会被记录, 每个执行node花费的时间、扫描的blocks、返回的记录数, OP耗费的时间等都需要记录分析 : 执行计划的记录需要开启auto explain , 设置执行超时阈值, 并且需要等待问题再次发生, 而且不能针对单个sql的超时时间进行阈值设置, 只能设置全局阈值. 每条SQL的执行时长需求不一样, 单个阈值无法满足需求. (例如有些SQL就是分析型的. ) 对优化/诊断感兴趣可以阅读本文相关章节: 《PostgreSQL 2天体系化培训》 开启 log_lock_waits, 并且只 记录超过deadlock_timeout的SQL.很多诊断需要在编译时定义宏( pg_config_manual.h)TRACE_SORT、LOCK_DEBUG、BTREE_BUILD_STATS、WAL_DEBUGhttps://www.postgresql.org/docs/14/runtime-config-developer.html 没有内置的 AWR, 较难发现宏观问题 (例如等待事件) 没有内置的 性能洞察, 较难发现指定时间段的问题, 特别是业务曾经出现抖动的时间段. 缺少 trace功能, 类似Oracle (10046, 10053) , 较难诊断SQL问题. trace dev para, auto explain, rsq, lock, query, iotiming, ... 内置的probe使用门槛非常高, 需要开启debug 、需要使用dtrace或者stap(systemtap) 设置探针进行分析 只有重量锁等待日志打印, 缺少LW锁等待统计, 并且没有视图分析SQL级别的等待事件, 等待事件需要到log中查询, 对SQL的单点问题分析较弱, SQL在过去发生的问题很难分析. (是执行计划的问题、锁等待的问题、还是资源竞争的问题?)
2、问题点背后涉及的技术原理
1 基准是什么? 如何定义? QPS 业务相关, 请求量 RT 数据库相关, 响应速度 如何定义标准 1 业务角度: SQL ID, 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_trace10046 10053 方法 - SQL诊断跟踪》AWR插件 《PostgreSQL pg_stat_statementsAWR 插件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》 pg_profile 基准采集和对比插件, 但是还不够完善, 图表也不够美观(很难发现问题所在). https://github.com/zubkov-andrei/pg_profile 很多企业被逼自己开发PG的监控、诊断、优化、审计等工具, 例如海信聚好看的dbdoctor已商业化(也有个人免费版), pawsql, d-smart等. 感兴趣的朋友可以找找.
6、业务上避免这个坑牺牲了什么, 会引入什么新的问题
门槛非常高, 而且有些需求不能很好的解决: 基准 问题优化后的提升预测 auto_explain单一阈值问题等待事件无法统计 不支持自动化推荐解决方案 debug编译、宏、 auto_explain、iotiming都会引入开销
7、数据库未来产品迭代如何修复这个坑
希望全方位内置基准、性能洞察、分析、自动化推荐优化方法、优化效果预测等能力.
推荐试试DBdoctor，也许是救命良药:
systemtap参考:
《[转载] systemtap 跟踪分析 PostgreSQL》 《PostgreSQL 代码性能诊断之 - OProfile & Systemtap》 《stap trace blockdev's iops》 《USE blockdev --setra 0 and systemtap test real BLOCKDEV iops》 《设置进程亲和 - numactl 或 taskset - retrieve or set a process's CPU affinity (affect SYSTEMTAP TIME)》 《Systemtap examples, Identifying Contended User-Space Locks》 《Systemtap examples, Profiling - 6 Tracking System Call Volume Per Process》 《Systemtap examples, Profiling - 5 Tracking Most Frequently Used System Calls》 《Systemtap examples, Profiling - 4 Monitoring Polling Applications》 《Systemtap examples, Profiling - 3 Determining Time Spent in Kernel and User Space》 《Systemtap examples, Profiling - 2 Call Graph Tracing》 《Systemtap examples, Profiling - 1 Counting Function Calls Made》 《Systemtap examples, DISK IO - 7 Periodically Print I/O Block Time》 《Systemtap examples, DISK IO - 6 Monitoring Changes to File Attributes》 《Systemtap examples, DISK IO - 5 Monitoring Reads and Writes to a File》 《Systemtap examples, DISK IO - 4 I/O Monitoring (By Device)》 《Systemtap examples, DISK IO - 3 Track Cumulative IO》 《Systemtap examples, DISK IO - 2 Tracking I/O Time For Each File Read or Write》 《Systemtap examples, DISK IO - 1 Summarizing Disk Read/Write Traffic》 《Systemtap kernel.trace("*") events source code》 《Systemtap examples, Network - 5 Monitoring Network Packets Drops in Kernel》 《Systemtap examples, Network - 4 Monitoring TCP Packets》 《Systemtap examples, Network - 3 Monitoring Incoming TCP Connections》 《Systemtap examples, Network - 2 Tracing Functions Called in Network Socket Code》 《Systemtap examples, Network - 1 Network Profiling》 《SystemTap Errors Introduce》 《SystemTap User-Space Stack Backtraces for x86 processors arch only》 《Systemtap Function thread_indent:string(delta:long)》 《SystemTap Flight Recorder Mode》 《PostgreSQL Dynamic Tracing using systemtap env prepare》 《Systemtap: PostgreSQL probe, USE @var("varname") or $varname get all local and global variables》 《Systemtap EXP: fix process probe global variables output BUG?(PostgreSQL checkpoint__done)》 《SystemTap Tapset: common used functions - 2》 《SystemTap Tapset: common used functions - 1》 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 7 - others(statement,xlog,sort)》 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 6 - lock》 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 5 - read|write relation》 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 4 - buffer》 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 3 - checkpoint》 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 2 - query》 《Systemtap EXP: PostgreSQL IN-BUILD mark Class 1 - transaction》 《Systemtap EXP: trace PostgreSQL netflow per session or per sql》 《Systemtap EXP: trace PostgreSQL instruction or block of instructions per sql or per session》 《Systemtap EXP: Trace PostgreSQL iostat per SQL statement 2》 《Systemtap EXP: Trace PostgreSQL iostat per SQL statement 1》 《Systemtap: Generating Instrumentation module(.ko) for Other Computers》 《Systemtap : stap PROCESSING 5 steps introduce》 《Systemtap BUG? : stap "-R no effect"》 《Systemtap Example : OUTPUT hist_linear for processes io size and io time "use @entry"》 《Systemtap(2.4) Example : array aggregate elements sorted by statistic operator (EXP. output TOPn IO processes)》 《PostgreSQL Systemtap example : Customize probe "SEE salted md5 value transfered on network"》 《Systemtap(2.4) fixed BUG(1.8) : delete from statistics(aggregates) type stored in array elements》 《Systemtap(1.8) BUG? : delete from statistics(aggregates) type stored in array elements》 《PostgreSQL Systemtap example : connection|close and session duration static》 《PostgreSQL Systemtap example : Customize probe "connect and disconnect"》 《PostgreSQL Systemtap example : autovacuum_naptime & databases in cluster》 《Systemtap Formatted output》 《Systemtap Statistics (aggregates) Data Type》 《Systemtap Associative array Data Type》 《Systemtap Statement types》 《Systemtap Preprocessor macros》 《Systemtap parse preprocessing stage - Conditional compilation》 《Systemtap Language elements - 1》 《Systemtap Special probe points (begin, end, error, never)》 《Systemtap Timer probes》 《Systemtap Syscall probes》 《Systemtap kernel Trace probes》 《Systemtap kernel Marker probes》 《Systemtap PROCFS probes》 《Systemtap Userspace probing - 4》 《Systemtap Userspace probing - 3》 《Systemtap Userspace probing - 2》 《Systemtap Userspace probing - 1》 《Systemtap DWARF-less probing (kprobe)》 《systemtap Built-in probe point types (DWARF-based kernel or module probes)》 《systemtap Auxiliary functions and Embedded C》 《systemtap local & global variables》 《systemtap probe aliases (Prologue-style = & Epilogue-style +=) and suffixes》 《systemtap probe point's "context variables" or "target variables"》 《systemtap probe point followed by ! or ? or "if (expr)"》 《find systemtap pre-built probe points & probe points reference manual》 《systemtap SAFETY AND SECURITY》 《systemtap optimized for variables》 《systemtap receive strings from address》 《use systemtap statistics vs pgbench progress output》 《Systemtap statistics type example》 《Systemtap supported data type (long,string,array,statistic), note don't support numeric except long》 《Eclipse Systemtap IDE》 《PostgreSQL SystemTap on Linux - 1》
本期彩蛋 - 开源Clup: PostgreSQL&PolarDB高可用与日常管理软件
clup由《PostgreSQL从小工到专家》作者唐成乘数科技出品, 包含开源版本和企业版本, 是非常成熟的PostgreSQL&PolarDB集群管理软件.
官网: https://www.csudata.com/clup
开源项目地址: https://gitee.com/csudata
使用CLup可以轻松管理几十套至上百套PostgreSQL、PolarDB高可用的数据库集群，发生故障自动切换，不影响生产系统的运行。故障切换后有详细的故障日志，方便定位故障原因，还可以手工一键切换。CLup还提供了数据库的一些基本监控和TOP SQL的监控，CLup后续版本还会增加更多的功能。
管理基于PostgreSQL流复制的集群
管理基于共享存储的PolarDB集群
产品优势
最后推荐2本大佬的新书
文章中的参考文档请点击阅读原文获得.
欢迎关注我的github (https://github.com/digoal/blog) , 及视频号: