作者
digoal
日期
2021-04-15
标签
PostgreSQL , OOM , 内存
背景
PostgreSQL数据库主机系统触发OOM现象是数据库进程被KILL, 数据库进入crash restart(when restart_after_crash=on).
OOM的原因很多, 最常见的如下, 以及对应的规避方法.
1、分区表特别多, 需要注意分区的catalog cache占用大量内存(老版本可能会读入每个子分区的结构到catalog cache). 特别需要注意并不是explain里面已经裁减了就代表relcache也裁剪了.
- 避免方法: 使用pg_pathman分区插件或者PG 12以上的高版本PostgreSQL(高版本会解决分区裁剪问题).
PG 分区表的不断演进:
- 《PostgreSQL 14 preview - 分区表性能再次增强 - ExecInitModifyTable 分区裁剪精细化》
- 《PostgreSQL 14 preview - 优化器增强 Rework planning and execution of UPDATE and DELETE. (减少传导不必要的列value、避免为每个分区生成subplan)》
- 《PostgreSQL 14 preview - 分区表性能增强, Avoid creating duplicate cached plans for inherited FK constraints. 节约cache》
- 《PostgreSQL 12 preview - 分区表性能提升百倍》
- 《PostgreSQL 12 preview - partitions pruned at plan time. 原生分区表性能提升23.5倍，已与pg_pathman持平。》
- 《分区表锁粒度差异 - pg_pathman VS native partition table》
- 《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》
- 《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) - 分区表性能优化 (堪比pg_pathman)》
- 《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》
- 《HTAP数据库 PostgreSQL 场景与性能测试之 45 - (OLTP) 数据量与性能的线性关系(10亿+无衰减), 暨单表多大需要分区》
2、业务使用了大量长连接, 并且没有设置连接的生命周期, 或者生命周期很长. 连接时间越长, 访问的元数据积累越多, 导致每个会话的私有内存较大.
《PostgreSQL relcache在长连接应用中的内存霸占"坑"》
- 避免方法1: 降低应用到数据库的总连接数, 并且设置连接的生命周期(例如, 一个连接最多使用15分钟后自动释放).
- 避免方法2: 内核支持线程池
3、业务到数据库的总连接数过多, 因为每个连接都有私有内存, 连接越多, 消耗的内存越多, 最终可能导致OOM.
- 避免方法1: 降低应用到数据库的总连接数, 并且设置连接的生命周期(例如, 一个连接最多使用15分钟后自动释放).
- 避免方法2: PG 内核层支持线程池, 相当于进程数变少了
- 避免方法3: 使用pgbouncer这类连接池
4、实例未使用HUGEPAGE, 导致page table占用较大内存. 最终引起OOM.
- 避免方法: 开启HUGEPAGE
5、设置了较大的work_mem或hash_mem_multiplier, 并且有大量SQL使用了hash agg或hash join, 导致内存消耗过多.
- 避免方法: 调小work_mem或hash_mem_multiplier. 业务层减少此类请求的并发量. 此类SQL使用PG HINT把hash agg\hash join改成group agg或merge join等.
- 《PostgreSQL hint pg_hint_plan 的详细用法》
- 《PostgreSQL Oracle 兼容性之SQL OUTLINE插件 pg_hint_plan enable_hint_table》
- 注意,某些版本已经支持enable_hashagg_disk, 可以开启防止内存不足时无法使用hashagg.
6、数据库有性能问题, 高峰期引起了雪崩, 并且应用程序配置的连接池上限较大, 导致向数据库请求了大量连接, 最终耗费大量内存引起OOM.
- 避免方法1: 降低应用到数据库的总连接数, 并且设置连接的生命周期(例如, 一个连接最多使用15分钟后自动释放).
- 避免方法2: 设置数据库或USER的connection limit, 使用alter role或alter database都能设置. 使用这个方法是有损的, 但可以避免业务完全不可用.
- 避免方法3: 找到捣蛋SQL并优化.
- 《PostgreSQL 如何查找TOP SQL (例如IO消耗最高的SQL) (包含SQL优化内容) - 珍藏级 - 数据库慢、卡死、连接爆增、慢查询多、OOM、crash、in recovery、崩溃等怎么办?怎么优化?怎么诊断?》
每个进程的内存消耗可以在主机层面查询, 参考本文末尾. 云数据库服务通常也支持查询.
7、如果是分区数量导致的问题, 首先要分析是什么分区类型.
如果是枚举或hash分区, 通常一个长连接的每次SQL请求的数据定位到的分区都是不同的分区子表, 连接使用久了就会hold所有的分区子表元数据, 占用内存大.
如果是按时间的范围分区, 通常日志表场景使用较多, 每次SQL请求的基本上都是当前时期的分区子表, 所以长连接hold的分区数量不会太多, 不大会有内存消耗问题.
- 如果是hash或枚举分区产生的内存消耗问题怎么办? 除了前面提到的6点方法, 还有2种方法,
- 1、业务层改造减少分区数量也能减少内存消耗.
- 2、业务层集成pg的hash或枚举算法, 把和不同分区相关的数据操作分配到不同的线程, 不同的线程使用不同的数据库连接, 从而分配到不同的数据库连接, 这样每个连接只会touch到某些特定的分区表, 不会导致每个连接都hold 所有分区表.
参考
《PostgreSQL 14 preview - 打印其他会话的memory context, 诊断内存消耗问题 - pg_log_backend_memory_contexts(pid)》
《hash join, 结果集太大, 耗费大内存, 导致oom 怎么办?》
《PostgreSQL cheat functions - (内存上下文\planner内容\memory context等常用函数)》
《PostgreSQL Huge Page 使用建议 - 大内存主机、实例注意》
《Linux中进程内存RSS与cgroup内存的RSS统计 - 差异》
《PostgreSQL relcache在长连接应用中的内存霸占"坑"》
PG 13 支持 pg_shmem_allocations 可以查询PG系统使用的共享内存情况. Add system view pg_shmem_allocations to display shared memory usage (Andres Freund, Robert Haas)
postgres=# select * from pg_shmem_allocations;
name | off | size | allocated_size
-------------------------------------+-----------+-----------+----------------
Buffer IO Locks | 140660096 | 524288 | 524288
Buffer Descriptors | 5393792 | 1048576 | 1048576
Async Queue Control | 147076992 | 2492 | 2560
Wal Sender Ctl | 147070464 | 1280 | 1280
AutoVacuum Data | 147062016 | 5368 | 5376
PROCLOCK hash | 143136000 | 2904 | 2944
FinishedSerializableTransactions | 146097664 | 16 | 128
XLOG Ctl | 53504 | 4208272 | 4208384
Shared MultiXact State | 5392640 | 1028 | 1152
Proc Header | 146231552 | 104 | 128
Backend Client Host Name Buffer | 146444672 | 8256 | 8320
ReplicationSlot Ctl | 147067392 | 2400 | 2432
CommitTs | 4791424 | 133568 | 133632
KnownAssignedXids | 146341888 | 31720 | 31744
Prepared Transaction Table | 146585088 | 16 | 128
BTree Vacuum State | 147074688 | 1476 | 1536
Checkpoint BufferIds | 141184384 | 327680 | 327680
Wal Receiver Ctl | 147071744 | 2248 | 2304
PREDICATELOCKTARGET hash | 143835392 | 2904 | 2944
Backend Status Array | 146381568 | 54696 | 54784
...
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
