Optimize DropRelFileNodeBuffers() for recovery

Enterprise / PostgreSQL - Amit Kapila [postgresql.org] - 12 January 2021 02:15 UTC

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

d6ad34f341 Optimize DropRelFileNodeBuffers() for recovery.
src/backend/storage/buffer/bufmgr.c | 136 +++++++++++++++++++++++++++++++++---
src/backend/storage/smgr/smgr.c | 30 ++++++--
src/include/storage/bufmgr.h | 2 +-
src/include/storage/smgr.h | 1 +
4 files changed, 154 insertions(+), 15 deletions(-)

Upstream: git.postgresql.org

  • Share