作者
digoal
日期
2021-01-13
标签
PostgreSQL , DropRelFileNodeBuffers , standby , drop 对象 , 遍历 shared buffer
背景
阿里云rds pg 9.4开始所有版本都已经支持了这个特性.
因为有一些企业SaaS行业用户, 在数据库中会创建大量的表、索引, 例如每个B端企业对应一套schema, 每个schema里面有上万的表和索引, 当企业账号注销后, 需要清理schema, 在主节点drop schema是很快的, 因为主节点释放被删除的对象的shared buffer采用了二分法查找, 而在从库没有使用二分法, 每一个对象删除都需要遍历整个shared buffer, 非常慢. 经常会出现从库延迟. 后来在recovery过程也加了二分法查找后就解决了.
社区重要在14的版本中引入这个功能了, 解决了SaaS类行业在drop 大量对象时可能导致的从库延迟问题. (特别是shared buffer非常大时, 慢的要命, 一万个对象清理可能要花费几个小时, 不延迟才怪.) 这个patch提交后, 不管有多少的对象需要drop , 主库删除结束后, 从库基本上不会有延迟.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=d6ad34f3410f101f9909c0918a49d6ce86fa216c
```
Optimize DropRelFileNodeBuffers() for recovery.
author Amit Kapila akapila@postgresql.org
Tue, 12 Jan 2021 02:15:40 +0000 (07:45 +0530)
committer Amit Kapila akapila@postgresql.org
Tue, 12 Jan 2021 02:15:40 +0000 (07:45 +0530)
commit d6ad34f3410f101f9909c0918a49d6ce86fa216c
tree 75a298544d588caa733ade247137b6681af4473a tree | snapshot
parent 9a4c0e36fbd671b5e7426a5a0670bdd7ba2714a0 commit | diff
Optimize DropRelFileNodeBuffers() for recovery.
The recovery path of DropRelFileNodeBuffers() is optimized so that
scanning of the whole buffer pool can be avoided when the number of
blocks to be truncated in a relation is below a certain threshold. For
such cases, we find the buffers by doing lookups in BufMapping table.
This improves the performance by more than 100 times in many cases
when several small tables (tested with 1000 relations) are truncated
and where the server is configured with a large value of shared
buffers (greater than equal to 100GB).
This optimization helps cases (a) when vacuum or autovacuum truncated off
any of the empty pages at the end of a relation, or (b) when the relation is
truncated in the same transaction in which it was created.
This commit introduces a new API smgrnblocks_cached which returns a cached
value for the number of blocks in a relation fork. This helps us to determine
the exact size of relation which is required to apply this optimization. The
exact size is required to ensure that we don't leave any buffer for the
relation being dropped as otherwise the background writer or checkpointer
can lead to a PANIC error while flushing buffers corresponding to files that
don't exist.
Author: Kirk Jamison based on ideas by Amit Kapila
Reviewed-by: Kyotaro Horiguchi, Takayuki Tsunakawa, and Amit Kapila
Tested-By: Haiying Tang
Discussion: https://postgr.es/m/OSBPR01MB3207DCA7EC725FDD661B3EDAEF660@OSBPR01MB3207.jpnprd01.prod.outlook.com
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
