沉浸式学习PostgreSQL|PolarDB 20: 学习成为数据库大师级别的优化技能

背景

欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.

• 系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.

本文的实验可以使用永久免费的阿里云云起实验室来完成.

如果你本地有docker环境也可以把镜像拉到本地来做实验:

x86_64机器使用以下docker image:

• 《amd64 image》

ARM机器使用以下docker image:

• 《arm64 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 秒杀场景优化》

业务并行请求都锁冲突问题, 可以调整并行逻辑, 在业务层面进行线程-数据映射逻辑优化, 对锁进行隔离进行优化.

• 《高并发队列处理业务的数据库性能优化 - IO扫描|CPU计算浪费 , 锁冲突 , 垃圾索引扫描浪费》

业务使用大锁, 且长时间持有锁带来的问题优化:

• 《DB吐槽大会,第84期 - PG逻辑备份释放对象元数据共享锁不及时》

在事故现场如何发现锁冲突是什么业务导致的?

• 《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、通过规范可以避免业务使用数据库不当导致的大部分问题

• 《PostgreSQL 数据库开发规范》

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 整体变慢的性能优化通用方法

• 《PostgreSQL (慢SQL|数据库整体变慢|性能抖动) 数据库性能分析与优化方法 - 珍藏级,建议收藏》
• 《2019-PostgreSQL 2天体系化培训 - 适合DBA》

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 除了以上优化, 通常还需要结合数据库的原理进行优化, 例如

• 《PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》

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顺序?

参考

• 《PostgreSQL (慢SQL|数据库整体变慢|性能抖动) 数据库性能分析与优化方法 - 珍藏级,建议收藏》
• 《2019-PostgreSQL 2天体系化培训 - 适合DBA》
• 《PostgreSQL性能优化综合案例讲解 - 2》
• 《PostgreSQL性能优化综合案例讲解 - 1》