digoal
2020-10-26
PostgreSQL , PageIsVerified , 跳过shared buffer
查询数据时, 如果数据不在shared buffer里面, 会从数据文件中读数据, 然后加载到shared buffer中.
那么问题来了, 当数据库的shared buffer中都是热数据, 而用户发起了一个非常不常用的sql, 需要读取大量数据, 可能会导致挤出shared buffer中的热数据, 影响性能.
PG的做法是对于大小超过shared buffer 四分之一的表, (四分之一是硬编码), 并且采用了全表扫描时, 会打一个标记, 这个标记表示这些读进来的shared buffer page优先被挤出. 从而减少挤出其他热数据.
但是还有没有更可控的方法? 例如对于全表扫描直接跳过shared buffer?
PG 14扩展了一个接口, 也许未来会支持这个功能.
目前在pg_basebackup或者一些可以安全跳过shared buffer的地方会使用.
Extend PageIsVerified() to handle more custom options master github/master
author Michael Paquier <michael@paquier.xyz>
Mon, 26 Oct 2020 08:55:28 +0800 (09:55 +0900)
committer Michael Paquier <michael@paquier.xyz>
Mon, 26 Oct 2020 08:55:28 +0800 (09:55 +0900)
commit d401c5769ef6aeef0a28c147f3fb5afedcd59984
tree 3b97029410a4f0fbf26fde8e59e7429703355f13 tree | snapshot
parent ba9f18abd3650e385e9a35df7145a7c38af17e92 commit | diff
Extend PageIsVerified() to handle more custom options
This is useful for checks of relation pages without having to load the
pages into the shared buffers, and two cases can make use of that: page
verification in base backups and the online, lock-safe, flavor.
Compatibility is kept with past versions using a macro that calls the
new extended routine with the set of options compatible with the
original version.
Extracted from a larger patch by the same author.
Author: Anastasia Lubennikova
Reviewed-by: Michael Paquier, Julien Rouhaud
Discussion: https://postgr.es/m/608f3476-0598-2514-2c03-e05c7d2b0cbd@postgrespro.ru
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
