作者
digoal
日期
2023-10-12
标签
PostgreSQL , PolarDB , 数据库 , 教学
背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
- 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
ARM机器使用以下docker image:
业务场景1 介绍: 学习成为数据库大师级别的优化技能
在上一个实验《沉浸式学习PostgreSQL|PolarDB 19: 体验最流行的开源企业ERP软件 odoo》 中, 学习了如何部署odoo和polardb|pg.
由于ODOO是非常复杂的ERP软件, 对于关系数据库的挑战也非常大, 所以通过odoo业务可以更快速提升同学的数据库优化能力, 发现业务对数据库的使用问题(如索引、事务对锁的运用逻辑问题), 数据库的代码缺陷, 参数或环境配置问题, 系统瓶颈等.
优化实验
odoo的压力测试脚本在如下目录中: PolarDB-PG-Benchmark/tests
1 优化TOP SQL性能
这个优化是比较通用的, 古话说得好, 擒贼先擒王, 打蛇打七寸. 找出TOP SQL, 对资源消耗的头部SQL进行优化, 效果立竿见影.
准备: 插件pg_stat_statements
实验步骤:
1、清理pg_stat_statements插件计数
2、使用固定的数据量、压测时长、并发、压测请求数 开启压测, 关闭压测
3、利用pg_stat_statements, 记录总资源消耗、被优化SQL的单次请求平均消耗. 用于评判优化效果.
4、利用pg_stat_statements找top sql并优化, 方法可参考:
- 《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级 - 数据库慢、卡死、连接爆增、慢查询多、OOM、crash、in recovery、崩溃等怎么办?怎么优化?怎么诊断?》
- 《PostgreSQL explain analyze 火山图火焰图 图形化性能分析软件 pg_flame》
- 《PostgreSQL 自动化性能诊断和优化产品 pganalyze》
- 《powa4 PostreSQL Workload Analyzer - PostgreSQL监控工具、带WEB展示 - 索引推荐,等待事件分析,命中率,配置变更跟踪等》
索引优化参考:
- 《PostgreSQL 虚拟索引 HypoPG 升级了 1.2.0发布》
- 《PostgreSQL 索引推荐 - HypoPG , pg_qualstats》
- 《PostgreSQL 虚拟|虚假 索引(hypothetical index) - HypoPG》
- 《PostgreSQL SQL自动优化案例 - 极简，自动推荐索引》
- 《自动选择正确索引访问接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
- 《PostgreSQL 优化器案例之 - order by limit 索引选择问题》
- 《PostgreSQL 数据库多列复合索引的字段顺序选择原理》
- 《PostgreSQL 多查询条件，多个索引的选择算法与问题诊断方法》
- 《跨云的K8S cloud native postgresql管理系统 谁在|会用? PG SaaS或工具或插件类产品 谁在|会用? (SQL规整、执行计划解读和优化建议、参数优化、AWR、索引推荐、错误日志解读和应对策略)》
5、执行完优化手段后, 再次重复1-3的操作.
对比优化效果:
- 总资源消耗
- 被优化SQL的单次请求平均消耗
- odoo压测结果变化
2 发现业务逻辑问题 - 锁冲突为例
这个实验主要用于发现业务层的数据库使用问题, 其中最突出的是锁等待的问题, 例如持锁时间过长, 持锁级别过高, 死锁等. 这些问题都会导致并发能力下降甚至雪崩.
准备: 开启log_lock_waits, 配置deadlock_timeout.
1、配置等待事件采集. pgpro-pwr, performance insight, pg_stat_monitor, pg_wait_sampling 都可以, 参考:
- 《PostgreSQL Oracle 兼容性之 - performance insight - AWS performance insight 理念与实现解读 - 珍藏级》
- 《PostgreSQL pg_stat_statements AWR 插件 pg_stat_monitor , 过去任何时间段性能分析 [推荐、收藏]》
- 《PostgreSQL 兼容Oracle插件 - pgpro-pwr AWR 插件》
- 《PostgreSQL 等待事件 及 等待采样统计 (pg_wait_sampling) 发布新版本 1.1.2》
- 《PostgreSQL 等待事件 及 等待采样统计(pg_wait_sampling)》
2、使用固定的数据量、压测时长、并发、压测请求数 开启压测, 关闭压测
3、观测数据库日志 $PGDATA/pg_log 或 $PGDATA/log 目录.
通过锁超时日志, 找到对应会话和SQL, 分析业务问题.
4、通过前面配置的 perf insight, pg_stat_monitor, pg_wait_sampling 找到TOP锁等待事件和对应的SQL.
5、优化手段举例:
秒杀场景可以用advisory lock来优化.
- 《沉浸式学习PostgreSQL|PolarDB 2: 电商高并发秒杀业务、跨境电商高并发队列消费业务》
- 《PostgreSQL 秒杀4种方法 - 增加 批量流式加减库存 方法》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 30 - (OLTP) 秒杀 - 高并发单点更新》
- 《聊一聊双十一背后的技术 - 不一样的秒杀技术, 裸秒》
- 《PostgreSQL 秒杀场景优化》
业务并行请求都锁冲突问题, 可以调整并行逻辑, 在业务层面进行线程-数据映射逻辑优化, 对锁进行隔离进行优化.
业务使用大锁, 且长时间持有锁带来的问题优化:
在事故现场如何发现锁冲突是什么业务导致的?
- 《PostgreSQL 锁等待排查实践 - 珍藏级 - process xxx1 acquired RowExclusiveLock on relation xxx2 of database xxx3 after xxx4 ms at xxx》
- 《PostgreSQL 锁等待监控 珍藏级SQL - 谁堵塞了谁》
雪崩的优化方法:
- 《重新发现PostgreSQL之美 - 40 雪崩, 压死骆驼的最后一根稻草》
- 《PostgreSQL AB表切换最佳实践 - 提高切换成功率，杜绝雪崩 - 珍藏级》
- 《PostgreSQL 设置单条SQL的执行超时 - 防雪崩》
- 《如何防止数据库雪崩(泛洪 flood)》
6、通过规范可以避免业务使用数据库不当导致的大部分问题
3 环境和参数性能优化
1、使用固定的数据量、压测时长、并发、压测请求数 开启压测
在压测过程收集数据, 观测问题
2、OS层观测工具参考
- iostat
- pmstat
- mpstat
- vmstat
- tcpdump
- top
- ps
- gdb
- pstack
指标参考:
- 《PostgreSQL 实时健康监控 大屏 - 低频指标 - 珍藏级》
- 《PostgreSQL 实时健康监控 大屏 - 高频指标(服务器) - 珍藏级》
- 《PostgreSQL 实时健康监控 大屏 - 高频指标 - 珍藏级》
3、发现问题
- 内存不足?
- 存储请求延迟、IOPS、带宽瓶颈?
- 文件系统锁等待瓶颈?
- 网络包转发、带宽?
- numa问题?
- 中断问题, cpu 绑核?
4、优化手段参考
- 《DBA不可不知的操作系统内核参数》
- 《PostgreSQL 操作系统监控插件 - system_stats - cpu,memory,network,filesystem,block dev等监控》
- 《PostgreSQL 11 postgresql.conf 参数模板 - 珍藏级》
- 《PostgreSQL 10 postgresql.conf 参数模板 - 珍藏级》
- 《DB吐槽大会,第48期 - PG 性能问题发现和分析能力较弱》
- 《DB吐槽大会,第54期 - PG 资源隔离、管理手段较少》
- 《转载 - Linux 多核下绑定硬件中断到不同 CPU（IRQ Affinity）》
4 整体变慢的性能优化通用方法
5 找出代码缺陷
最后这个实验, 是要找出数据库|OS内核的性能瓶颈.
1、使用固定的数据量、压测时长、并发、压测请求数 开启压测
在压测过程收集数据, 观测问题
2、观测工具参考
- perf
- dtrace
- systemtap
工具使用方法参考:
- 《PostgreSQL 源码性能诊断(perf profiling)指南(含火焰图生成分析FlameGraph) - 珍藏级》
- 《[转载] 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》
附赠一些常用的学习资料
- 《[未完待续] 数据库相关岗位面试准备建议 - 珍藏级》
- 《阿里巴巴 PostgreSQL、社区生态；PG开发者指南、原理、案例、管理优化实践《学习资料、视频》；《PG天天象上》沙龙纪录 - 珍藏级》
- 《PostgreSQL、Greenplum 《如来神掌》 - 目录 - 珍藏级》
- 《Oracle DBA 增值 PostgreSQL,Greenplum 学习计划 - 珍藏级》
知识点
要求的知识点比较综合, 需掌握os,存储,网络,数据库等原理和常用的工具, 方法论等.
思考
1 除了以上优化, 通常还需要结合数据库的原理进行优化, 例如
2 数据库中有大量的优化器因子配置项, 参数优化和硬件配置有什么关系?
- 《DB吐槽大会,第12期 - 没有自动成本校准器》
- 《优化器成本因子校对(disk,ssd,memory IO开销精算) - PostgreSQL real seq_page_cost & random_page_cost in disks,ssd,memory》
- 《优化器成本因子校对 - PostgreSQL explain cost constants alignment to timestamp》
3 数据库的cbo, geqo优化器优化规则说的是什么? 和统计信息如何配合? 如何选择JOIN方法JOIN顺序?
