MIME-Version: 1.0
References: <77956427-F0BC-4B2C-9114-81859CDA8E06@gmail.com>
 <33A14CAC-0526-4547-AE84-E88EFDB0B8AF@gmail.com>
In-Reply-To: <33A14CAC-0526-4547-AE84-E88EFDB0B8AF@gmail.com>
From: Jingtang Zhang <mrdrivingduck@gmail.com>
Date: Thu, 4 Jun 2026 15:19:24 +0800
Message-ID: 
 <CAPsk3_Aq4DzHZ_VeBy8kXag6DF-HF4F+vWAWa1L2EXkvo9FORQ@mail.gmail.com>
Subject: Re: Optimize CPU usage of dropping buffers during recovery
To: pgsql-hackers@lists.postgresql.org,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000e154cc0653685d30"
Archived-At: 
 <https://www.postgresql.org/message-id/CAPsk3_Aq4DzHZ_VeBy8kXag6DF-HF4F%2BvWAWa1L2EXkvo9FORQ%40mail.gmail.com>
Precedence: bulk

--000000000000e154cc0653685d30
Content-Type: text/plain; charset="UTF-8"

Hi hackers,

Reposting this patch to bump the thread -- the v2 I sent a
while ago didn't get any reviews.

To recap the problem: during crash recovery, when a transaction
involves creating and then dropping an empty relation (or a
relation that happens to be empty at the time of the crash),
DropRelationsAllBuffers() falls back to scanning the entire
buffer pool. This is because no WAL record ever references a
block of that relation, so smgr_cached_nblocks is never
initialized - it stays as InvalidBlockNumber, and the optimized
BufMapping lookup path introduced by commit d6ad34f3410 cannot
be used.

This can easily happen in practice. For example, refreshing a
materialized view whose query returns no rows, or truncating a
table and reloading it but the source query returns nothing.
With a large shared_buffers setting, the recovery time becomes
dominated by repeated full scans of the buffer pool.

The attached patch addresses this with a small change in
smgr_redo so that the optimized BufMapping path can be taken
in this scenario. The impact, measured on an earlier run with
16GB shared_buffers and 10 clients * 500 CREATE/DROP
transactions during crash recovery:

  w/o patch: CPU user: 77.58 s, system: 0.27 s
  patched:   CPU user: 0.14 s,  system: 0.09 s

In more detail, the fix has two parts that work together:

1. In smgr_redo(XLOG_SMGR_CREATE), set smgr_cached_nblocks to
   0 for the MAIN, FSM, and VM forks. A CREATE record means the
   relation was just created with zero blocks, so 0 is the
   correct cached value.

2. In XLogReadBufferExtended(), if the cached value is 0,
   invalidate it before calling smgrnblocks(), so that it does a
   fresh lseek to get the real file size. This handles the case
   where the relation was extended before the crash. The extra
   lseek is acceptable here because we are about to do I/O to
   read the block anyway.

For the common case where empty relations are created and
dropped without any data written, part (1) alone is sufficient:
the cached value stays 0, DropRelationsAllBuffers() sees 0
blocks to invalidate, takes the fast path, and returns
immediately. Part (2) is only needed as a safety net for the
less common case.

Regarding the INIT fork question I raised in v1: I now believe
we don't need to handle it. The CREATE record for an INIT fork
has forkNum == INIT_FORKNUM, not MAIN_FORKNUM, so the new
condition simply does not apply to it.

I've also verified that relfilenumber reuse within a single
recovery is not a concern. As documented in mdunlink(), the
first segment file is kept until the next checkpoint to prevent
relfilenumber reuse, and during redo there is no possibility of
allocating new relfilenumbers.

v3 attached, rebased on current master. Compared to v2, the
comments have been improved to better explain the relationship
between the two code changes and why the approach is safe.
Note that the Perl test case included in the patch is
only meant as a benchmark script for conveniently reproducing
the problem and measuring the improvement - it is not intended
as a regression test.

Any thoughts or reviews would be greatly appreciated.

--
Regards, Jingtang

--000000000000e154cc0653685d30
Content-Type: application/octet-stream;
	name="v3-0001-Optimize-CPU-usage-of-dropping-buffers-during-recove.patch"
Content-Disposition: attachment;
	filename="v3-0001-Optimize-CPU-usage-of-dropping-buffers-during-recove.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mpz5zhsg0>
X-Attachment-Id: f_mpz5zhsg0

RnJvbSBhOWYwNDM5MTQ3M2IzYTg4NDgyNDMzYjg4MTE2N2Q2NjIwMjdkOTY4IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBKaW5ndGFuZyBaaGFuZyA8bXJkcml2aW5nZHVja0BnbWFpbC5j
b20+CkRhdGU6IFNhdCwgMTggQXByIDIwMjYgMjE6MDc6MTMgKzA4MDAKU3ViamVjdDogW1BBVENI
IHY0XSBPcHRpbWl6ZSBDUFUgdXNhZ2Ugb2YgZHJvcHBpbmcgYnVmZmVycyBkdXJpbmcgcmVjb3Zl
cnkKCkluaXRpYWxpemUgY2FjaGVkIG5ibG9ja3MgdG8gMCB3aGVuIHJlZG8gQ1JFQVRFIHJlY29y
ZC4KLS0tCiBzcmMvYmFja2VuZC9hY2Nlc3MvdHJhbnNhbS94bG9ndXRpbHMuYyAgICB8IDEwICsr
KysKIHNyYy9iYWNrZW5kL2NhdGFsb2cvc3RvcmFnZS5jICAgICAgICAgICAgIHwgMjQgKysrKysr
KysKIHNyYy90ZXN0L3JlY292ZXJ5L3QvMDYwX3RydW5jYXRlX2VtcHR5LnBsIHwgNjkgKysrKysr
KysrKysrKysrKysrKysrKysKIDMgZmlsZXMgY2hhbmdlZCwgMTAzIGluc2VydGlvbnMoKykKIGNy
ZWF0ZSBtb2RlIDEwMDY0NCBzcmMvdGVzdC9yZWNvdmVyeS90LzA2MF90cnVuY2F0ZV9lbXB0eS5w
bAoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2FjY2Vzcy90cmFuc2FtL3hsb2d1dGlscy5jIGIv
c3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0veGxvZ3V0aWxzLmMKaW5kZXggNWZiZTM5MTMzYjgu
LjA5MDk0N2JhNWZlIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9hY2Nlc3MvdHJhbnNhbS94bG9n
dXRpbHMuYworKysgYi9zcmMvYmFja2VuZC9hY2Nlc3MvdHJhbnNhbS94bG9ndXRpbHMuYwpAQCAt
NDg5LDYgKzQ4OSwxNiBAQCBYTG9nUmVhZEJ1ZmZlckV4dGVuZGVkKFJlbEZpbGVMb2NhdG9yIHJs
b2NhdG9yLCBGb3JrTnVtYmVyIGZvcmtudW0sCiAJICovCiAJc21ncmNyZWF0ZShzbWdyLCBmb3Jr
bnVtLCB0cnVlKTsKIAorCS8qCisJICogSWYgdGhlIGNhY2hlZCBuYmxvY2tzIGlzIDAsIGl0IHdh
cyBzZXQgYnkgc21ncl9yZWRvKENSRUFURSkgdG8KKwkgKiBlbmFibGUgdGhlIG9wdGltaXplZCBk
cm9wLWJ1ZmZlciBwYXRoLiAgQnV0IHRoZSByZWxhdGlvbiBtYXkKKwkgKiBoYXZlIGJlZW4gZXh0
ZW5kZWQgYmVmb3JlIHRoZSBjcmFzaCwgc28gd2UgbXVzdCBpbnZhbGlkYXRlIHRoZQorCSAqIGNh
Y2hlIGFuZCBsZXQgc21ncm5ibG9ja3MoKSBkbyBhbiBsc2VlayB0byBnZXQgdGhlIHJlYWwgc2l6
ZS4KKwkgKiBUaGlzIGV4dHJhIGxzZWVrIGlzIGFjY2VwdGFibGUgaGVyZSBiZWNhdXNlIHdlJ3Jl
IGFib3V0IHRvIGRvCisJICogSS9PIHRvIHJlYWQgdGhlIGJsb2NrIGFueXdheS4KKwkgKi8KKwlp
ZiAoc21nci0+c21ncl9jYWNoZWRfbmJsb2Nrc1tmb3JrbnVtXSA9PSAwKQorCQlzbWdyLT5zbWdy
X2NhY2hlZF9uYmxvY2tzW2ZvcmtudW1dID0gSW52YWxpZEJsb2NrTnVtYmVyOwogCWxhc3RibG9j
ayA9IHNtZ3JuYmxvY2tzKHNtZ3IsIGZvcmtudW0pOwogCiAJaWYgKGJsa25vIDwgbGFzdGJsb2Nr
KQpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvY2F0YWxvZy9zdG9yYWdlLmMgYi9zcmMvYmFja2Vu
ZC9jYXRhbG9nL3N0b3JhZ2UuYwppbmRleCBlNDQzYTQ5OTNjNS4uYjkzYThiNTg3NTMgMTAwNjQ0
Ci0tLSBhL3NyYy9iYWNrZW5kL2NhdGFsb2cvc3RvcmFnZS5jCisrKyBiL3NyYy9iYWNrZW5kL2Nh
dGFsb2cvc3RvcmFnZS5jCkBAIC05OTMsNiArOTkzLDMwIEBAIHNtZ3JfcmVkbyhYTG9nUmVhZGVy
U3RhdGUgKnJlY29yZCkKIAogCQlyZWxuID0gc21ncm9wZW4oeGxyZWMtPnJsb2NhdG9yLCBJTlZB
TElEX1BST0NfTlVNQkVSKTsKIAkJc21ncmNyZWF0ZShyZWxuLCB4bHJlYy0+Zm9ya051bSwgdHJ1
ZSk7CisKKwkJLyoKKwkJICogSW5pdGlhbGl6ZSB0aGUgY2FjaGVkIG5ibG9ja3MgdG8gMCBmb3Ig
YSBuZXdseSBjcmVhdGVkCisJCSAqIHJlbGF0aW9uLCBzbyB0aGF0IERyb3BSZWxhdGlvbnNBbGxC
dWZmZXJzKCkgY2FuIHVzZSB0aGUKKwkJICogb3B0aW1pemVkIHBhdGggKEJ1Zk1hcHBpbmcgbG9v
a3VwKSBpbnN0ZWFkIG9mIHNjYW5uaW5nCisJCSAqIHRoZSBlbnRpcmUgYnVmZmVyIHBvb2wuCisJ
CSAqCisJCSAqIFRoaXMgaXMgc2FmZSBiZWNhdXNlIGEgQ1JFQVRFIHJlY29yZCBtZWFucyB0aGUg
cmVsYXRpb24KKwkJICogaGFzIGp1c3QgYmVlbiBjcmVhdGVkIHdpdGggemVybyBibG9ja3MuICBJ
ZiB0aGUgcmVsYXRpb24KKwkJICogd2FzIGV4dGVuZGVkIGJlZm9yZSB0aGUgY3Jhc2gsIFhMb2dS
ZWFkQnVmZmVyRXh0ZW5kZWQoKQorCQkgKiB3aWxsIGludmFsaWRhdGUgdGhpcyBjYWNoZWQgdmFs
dWUgYW5kIGxldCBzbWdybmJsb2NrcygpCisJCSAqIGRvIGEgZnJlc2ggbHNlZWsuCisJCSAqCisJ
CSAqIFdlIG9ubHkgZG8gdGhpcyBmb3IgTUFJTl9GT1JLTlVNIENSRUFURSByZWNvcmRzLCB3aGlj
aAorCQkgKiBjb3JyZXNwb25kIHRvIG5ldyByZWxhdGlvbiBjcmVhdGlvbi4gIEZTTSBhbmQgVk0g
Zm9ya3MKKwkJICogYXJlIGFsc28gc2V0IHRvIDAgYmVjYXVzZSB0aGV5IGNhbm5vdCBleGlzdCB5
ZXQgZm9yIGEKKwkJICogbmV3bHkgY3JlYXRlZCByZWxhdGlvbi4KKwkJICovCisJCWlmICh4bHJl
Yy0+Zm9ya051bSA9PSBNQUlOX0ZPUktOVU0pCisJCXsKKwkJCXJlbG4tPnNtZ3JfY2FjaGVkX25i
bG9ja3NbTUFJTl9GT1JLTlVNXSA9IDA7CisJCQlyZWxuLT5zbWdyX2NhY2hlZF9uYmxvY2tzW0ZT
TV9GT1JLTlVNXSA9IDA7CisJCQlyZWxuLT5zbWdyX2NhY2hlZF9uYmxvY2tzW1ZJU0lCSUxJVFlN
QVBfRk9SS05VTV0gPSAwOworCQl9CiAJfQogCWVsc2UgaWYgKGluZm8gPT0gWExPR19TTUdSX1RS
VU5DQVRFKQogCXsKZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlY292ZXJ5L3QvMDYwX3RydW5jYXRl
X2VtcHR5LnBsIGIvc3JjL3Rlc3QvcmVjb3ZlcnkvdC8wNjBfdHJ1bmNhdGVfZW1wdHkucGwKbmV3
IGZpbGUgbW9kZSAxMDA2NDQKaW5kZXggMDAwMDAwMDAwMDAuLjE5NTY3MzNmMDVmCi0tLSAvZGV2
L251bGwKKysrIGIvc3JjL3Rlc3QvcmVjb3ZlcnkvdC8wNjBfdHJ1bmNhdGVfZW1wdHkucGwKQEAg
LTAsMCArMSw2OSBAQAordXNlIHN0cmljdDsKK3VzZSB3YXJuaW5ncyBGQVRBTCA9PiAnYWxsJzsK
K3VzZSBQb3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyOwordXNlIFBvc3RncmVTUUw6OlRlc3Q6OlV0
aWxzOwordXNlIFRlc3Q6Ok1vcmU7Cit1c2UgVGltZTo6SGlSZXMgcXcoZ2V0dGltZW9mZGF5IHR2
X2ludGVydmFsKTsKKworbXkgJG5vZGUgPSBQb3N0Z3JlU1FMOjpUZXN0OjpDbHVzdGVyLT5uZXco
J3ByaW1hcnknKTsKKyRub2RlLT5pbml0KCk7CisKKyRub2RlLT5hcHBlbmRfY29uZigncG9zdGdy
ZXNxbC5jb25mJywgJ3NoYXJlZF9idWZmZXJzID0gNEdCJyk7Ciskbm9kZS0+YXBwZW5kX2NvbmYo
J3Bvc3RncmVzcWwuY29uZicsICdyZXN0YXJ0X2FmdGVyX2NyYXNoID0gb24nKTsKKworJG5vZGUt
PnN0YXJ0KCk7CisKKyRub2RlLT5zYWZlX3BzcWwoJ3Bvc3RncmVzJywKKyAgICBxW0NSRUFURSBU
QUJMRSB0ZXN0IChpZCBpbnQpO10pOworCisjIFNJR1NUT1AgY2hlY2twb2ludGVyIGFuZCBydW4g
c29tZSB0cmFuc2FjdGlvbnMKK215ICRjaGVja3BvaW50ZXJfcGlkID0gJG5vZGUtPnNhZmVfcHNx
bCgncG9zdGdyZXMnLAorICAgIHFbU0VMRUNUIHBpZCBGUk9NIHBnX3N0YXRfYWN0aXZpdHkgV0hF
UkUgYmFja2VuZF90eXBlID0gJ2NoZWNrcG9pbnRlcic7XSk7CitjaG9tcCgkY2hlY2twb2ludGVy
X3BpZCk7CitraWxsICdTVE9QJywgJGNoZWNrcG9pbnRlcl9waWQ7Citub3RlKCJDaGVja3BvaW50
ZXIgc3RvcHBlZCIpOworCiskbm9kZS0+cGdiZW5jaCgKKyAgICAnLS1uby12YWN1dW0gLS1jbGll
bnQ9MTAgLS10cmFuc2FjdGlvbnM9MTAwMCcsCisgICAgMCwKKyAgICBbcXJ7YWN0dWFsbHkgcHJv
Y2Vzc2VkfV0sCisgICAgW3Fye14kfV0sCisgICAgJ2NvbmN1cnJlbnQgQ1JFQVRFIGFuZCBEUk9Q
IFRBQkxFIHRyYW5zYWN0aW9ucycsCisgICAgeworICAgICAgICAndHJ1bmNhdGVfZW1wdHlfc2Ny
aXB0JyA9PiBxKAorICAgICAgICAgICAgQkVHSU47CisgICAgICAgICAgICBJTlNFUlQgSU5UTyB0
ZXN0IFZBTFVFUyAoOmNsaWVudF9pZCk7CisgICAgICAgICAgICBERUxFVEUgRlJPTSB0ZXN0IFdI
RVJFIGlkID0gOmNsaWVudF9pZDsKKyAgICAgICAgICAgIENSRUFURSBUQUJMRSB0ZXN0X2VtcHR5
XzpjbGllbnRfaWQgKGlkIGludCk7CisgICAgICAgICAgICBEUk9QIFRBQkxFIHRlc3RfZW1wdHlf
OmNsaWVudF9pZDsKKyAgICAgICAgICAgIENPTU1JVDsKKyAgICAgICAgKQorICAgIH0pOworCisj
IHN0b3AgdGhlIG5vZGUgaW4gaW1tZWRpYXRlIG1vZGUgZm9yIGNyYXNoIHJlY292ZXJ5Ciskbm9k
ZS0+c3RvcCgnaW1tZWRpYXRlJyk7CisKK215ICRyZWNvdmVyeV9zdGFydCA9IFtnZXR0aW1lb2Zk
YXldOworJG5vZGUtPnN0YXJ0KCk7CitteSAkcmVjb3ZlcnlfZW5kID0gW2dldHRpbWVvZmRheV07
CitteSAkcmVjb3ZlcnlfdGltZSA9IHR2X2ludGVydmFsKCRyZWNvdmVyeV9zdGFydCwgJHJlY292
ZXJ5X2VuZCk7CisKK25vdGUoIkNyYXNoIHJlY292ZXJ5IHRpbWU6ICR7cmVjb3ZlcnlfdGltZX0g
c2Vjb25kcyIpOworCitteSAkbG9nX2NvbnRlbnQgPSAkbm9kZS0+bG9nX2NvbnRlbnQoKTsKK2lm
ICgkbG9nX2NvbnRlbnQgPX4gL3JlZG8gZG9uZSBhdCAuKz8gc3lzdGVtIHVzYWdlOiBDUFU6IHVz
ZXI6IChbXGQuXSspIHMsIHN5c3RlbTogKFtcZC5dKykgcywgZWxhcHNlZDogKFtcZC5dKykgcy9t
KQoreworICAgIG15ICRjcHVfdXNlciA9ICQxOworICAgIG15ICRjcHVfc3lzdGVtID0gJDI7Cisg
ICAgbXkgJHJlZG9fZWxhcHNlZCA9ICQzOworICAgIAorICAgIG5vdGUoIlJlZG8gZWxhcHNlZCB0
aW1lOiAkcmVkb19lbGFwc2VkIHMiKTsKKyAgICBub3RlKCIgIENQVSB1c2VyOiAkY3B1X3VzZXIg
cywgc3lzdGVtOiAkY3B1X3N5c3RlbSBzIik7Cit9CisKKyMgY29uc2lzdGVuY3kgY2hlY2sKK215
ICRyZXN1bHQgPSAkbm9kZS0+c2FmZV9wc3FsKCdwb3N0Z3JlcycsIHFbU0VMRUNUIENPVU5UKCop
IEZST00gdGVzdDtdKTsKK2lzKCRyZXN1bHQsICcwJywgJ3Rlc3QgdGFibGUgaXMgZW1wdHkgYWZ0
ZXIgcmVjb3ZlcnknKTsKKworJG5vZGUtPnN0b3AoKTsKK2RvbmVfdGVzdGluZygpOwotLSAKMi4z
OS41IChBcHBsZSBHaXQtMTU0KQoK
--000000000000e154cc0653685d30--