From: Jingtang Zhang <mrdrivingduck@gmail.com>
Content-Type: multipart/mixed;
	boundary="Apple-Mail=_F8F65EC8-7F86-4DF6-BF81-400504A4018C"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81.1.4\))
Subject: Re: Optimize CPU usage of dropping buffers during recovery
Date: Sun, 1 Feb 2026 16:20:00 +0800
References: <77956427-F0BC-4B2C-9114-81859CDA8E06@gmail.com>
To: pgsql-hackers@lists.postgresql.org,
 PostgreSQL-development <pgsql-hackers@postgresql.org>
In-Reply-To: <77956427-F0BC-4B2C-9114-81859CDA8E06@gmail.com>
Message-Id: <33A14CAC-0526-4547-AE84-E88EFDB0B8AF@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/33A14CAC-0526-4547-AE84-E88EFDB0B8AF%40gmail.com>
Precedence: bulk


--Apple-Mail=_F8F65EC8-7F86-4DF6-BF81-400504A4018C
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=us-ascii


A little bit modification for Perl test case and comments in v2 patch.

--
Regards, Jingtang


--Apple-Mail=_F8F65EC8-7F86-4DF6-BF81-400504A4018C
Content-Disposition: attachment;
	filename*0=v2-0001-Optimize-CPU-usage-of-dropping-buffers-during-recove.pat;
	filename*1=ch
Content-Type: application/octet-stream;
	x-unix-mode=0644;
	name="v2-0001-Optimize-CPU-usage-of-dropping-buffers-during-recove.patch"
Content-Transfer-Encoding: quoted-printable

=46rom=202d423d2b88e0f14a2fb51657ddfd93f7648598ca=20Mon=20Sep=2017=20=
00:00:00=202001=0AFrom:=20Jingtang=20Zhang=20<mrdrivingduck@gmail.com>=0A=
Date:=20Sun,=201=20Feb=202026=2016:12:48=20+0800=0ASubject:=20[PATCH]=20=
Optimize=20CPU=20usage=20of=20dropping=20buffers=20during=20recovery=0A=0A=
Initialize=20cached=20nblocks=20to=200=20when=20redo=20CREATE=20record.=0A=
---=0A=20src/backend/access/transam/xlogutils.c=20=20=20=20|=20=207=20=
+++=0A=20src/backend/catalog/storage.c=20=20=20=20=20=20=20=20=20=20=20=20=
=20|=20=207=20+++=0A=20src/test/recovery/t/060_truncate_empty.pl=20|=20=
69=20+++++++++++++++++++++++=0A=203=20files=20changed,=2083=20=
insertions(+)=0A=20create=20mode=20100644=20=
src/test/recovery/t/060_truncate_empty.pl=0A=0Adiff=20--git=20=
a/src/backend/access/transam/xlogutils.c=20=
b/src/backend/access/transam/xlogutils.c=0Aindex=20=
5fbe39133b8..387c7621218=20100644=0A---=20=
a/src/backend/access/transam/xlogutils.c=0A+++=20=
b/src/backend/access/transam/xlogutils.c=0A@@=20-489,6=20+489,13=20@@=20=
XLogReadBufferExtended(RelFileLocator=20rlocator,=20ForkNumber=20=
forknum,=0A=20=09=20*/=0A=20=09smgrcreate(smgr,=20forknum,=20true);=0A=20=
=0A+=09/*=0A+=09=20*=20Invalidate=20the=20cache=20if=20the=20cached=20=
value=20is=200,=20and=20let=20smgrnblocks=20ask=0A+=09=20*=20the=20=
kernel.=20The=20relation=20might=20be=20longer=20than=20the=20cached=20=
value=20due=20to=0A+=09=20*=20relation=20extension=20before=20crash.=0A+=09=
=20*/=0A+=09if=20(smgr->smgr_cached_nblocks[forknum]=20=3D=3D=200)=0A+=09=
=09smgr->smgr_cached_nblocks[forknum]=20=3D=20InvalidBlockNumber;=0A=20=09=
lastblock=20=3D=20smgrnblocks(smgr,=20forknum);=0A=20=0A=20=09if=20=
(blkno=20<=20lastblock)=0Adiff=20--git=20a/src/backend/catalog/storage.c=20=
b/src/backend/catalog/storage.c=0Aindex=20e443a4993c5..859f515411a=20=
100644=0A---=20a/src/backend/catalog/storage.c=0A+++=20=
b/src/backend/catalog/storage.c=0A@@=20-993,6=20+993,13=20@@=20=
smgr_redo(XLogReaderState=20*record)=0A=20=0A=20=09=09reln=20=3D=20=
smgropen(xlrec->rlocator,=20INVALID_PROC_NUMBER);=0A=20=09=09=
smgrcreate(reln,=20xlrec->forkNum,=20true);=0A+=0A+=09=09if=20=
(xlrec->forkNum=20=3D=3D=20MAIN_FORKNUM)=0A+=09=09{=0A+=09=09=09=
reln->smgr_cached_nblocks[MAIN_FORKNUM]=20=3D=200;=0A+=09=09=09=
reln->smgr_cached_nblocks[FSM_FORKNUM]=20=3D=200;=0A+=09=09=09=
reln->smgr_cached_nblocks[VISIBILITYMAP_FORKNUM]=20=3D=200;=0A+=09=09}=0A=
=20=09}=0A=20=09else=20if=20(info=20=3D=3D=20XLOG_SMGR_TRUNCATE)=0A=20=09=
{=0Adiff=20--git=20a/src/test/recovery/t/060_truncate_empty.pl=20=
b/src/test/recovery/t/060_truncate_empty.pl=0Anew=20file=20mode=20100644=0A=
index=2000000000000..1956733f05f=0A---=20/dev/null=0A+++=20=
b/src/test/recovery/t/060_truncate_empty.pl=0A@@=20-0,0=20+1,69=20@@=0A=
+use=20strict;=0A+use=20warnings=20FATAL=20=3D>=20'all';=0A+use=20=
PostgreSQL::Test::Cluster;=0A+use=20PostgreSQL::Test::Utils;=0A+use=20=
Test::More;=0A+use=20Time::HiRes=20qw(gettimeofday=20tv_interval);=0A+=0A=
+my=20$node=20=3D=20PostgreSQL::Test::Cluster->new('primary');=0A=
+$node->init();=0A+=0A+$node->append_conf('postgresql.conf',=20=
'shared_buffers=20=3D=204GB');=0A+$node->append_conf('postgresql.conf',=20=
'restart_after_crash=20=3D=20on');=0A+=0A+$node->start();=0A+=0A=
+$node->safe_psql('postgres',=0A+=20=20=20=20q[CREATE=20TABLE=20test=20=
(id=20int);]);=0A+=0A+#=20SIGSTOP=20checkpointer=20and=20run=20some=20=
transactions=0A+my=20$checkpointer_pid=20=3D=20=
$node->safe_psql('postgres',=0A+=20=20=20=20q[SELECT=20pid=20FROM=20=
pg_stat_activity=20WHERE=20backend_type=20=3D=20'checkpointer';]);=0A=
+chomp($checkpointer_pid);=0A+kill=20'STOP',=20$checkpointer_pid;=0A=
+note("Checkpointer=20stopped");=0A+=0A+$node->pgbench(=0A+=20=20=20=20=
'--no-vacuum=20--client=3D10=20--transactions=3D1000',=0A+=20=20=20=200,=0A=
+=20=20=20=20[qr{actually=20processed}],=0A+=20=20=20=20[qr{^$}],=0A+=20=20=
=20=20'concurrent=20CREATE=20and=20DROP=20TABLE=20transactions',=0A+=20=20=
=20=20{=0A+=20=20=20=20=20=20=20=20'truncate_empty_script'=20=3D>=20q(=0A=
+=20=20=20=20=20=20=20=20=20=20=20=20BEGIN;=0A+=20=20=20=20=20=20=20=20=20=
=20=20=20INSERT=20INTO=20test=20VALUES=20(:client_id);=0A+=20=20=20=20=20=
=20=20=20=20=20=20=20DELETE=20FROM=20test=20WHERE=20id=20=3D=20=
:client_id;=0A+=20=20=20=20=20=20=20=20=20=20=20=20CREATE=20TABLE=20=
test_empty_:client_id=20(id=20int);=0A+=20=20=20=20=20=20=20=20=20=20=20=20=
DROP=20TABLE=20test_empty_:client_id;=0A+=20=20=20=20=20=20=20=20=20=20=20=
=20COMMIT;=0A+=20=20=20=20=20=20=20=20)=0A+=20=20=20=20});=0A+=0A+#=20=
stop=20the=20node=20in=20immediate=20mode=20for=20crash=20recovery=0A=
+$node->stop('immediate');=0A+=0A+my=20$recovery_start=20=3D=20=
[gettimeofday];=0A+$node->start();=0A+my=20$recovery_end=20=3D=20=
[gettimeofday];=0A+my=20$recovery_time=20=3D=20=
tv_interval($recovery_start,=20$recovery_end);=0A+=0A+note("Crash=20=
recovery=20time:=20${recovery_time}=20seconds");=0A+=0A+my=20=
$log_content=20=3D=20$node->log_content();=0A+if=20($log_content=20=3D~=20=
/redo=20done=20at=20.+?=20system=20usage:=20CPU:=20user:=20([\d.]+)=20s,=20=
system:=20([\d.]+)=20s,=20elapsed:=20([\d.]+)=20s/m)=0A+{=0A+=20=20=20=20=
my=20$cpu_user=20=3D=20$1;=0A+=20=20=20=20my=20$cpu_system=20=3D=20$2;=0A=
+=20=20=20=20my=20$redo_elapsed=20=3D=20$3;=0A+=20=20=20=20=0A+=20=20=20=20=
note("Redo=20elapsed=20time:=20$redo_elapsed=20s");=0A+=20=20=20=20=
note("=20=20CPU=20user:=20$cpu_user=20s,=20system:=20$cpu_system=20s");=0A=
+}=0A+=0A+#=20consistency=20check=0A+my=20$result=20=3D=20=
$node->safe_psql('postgres',=20q[SELECT=20COUNT(*)=20FROM=20test;]);=0A=
+is($result,=20'0',=20'test=20table=20is=20empty=20after=20recovery');=0A=
+=0A+$node->stop();=0A+done_testing();=0A--=20=0A2.50.1=20(Apple=20=
Git-155)=0A=0A=

--Apple-Mail=_F8F65EC8-7F86-4DF6-BF81-400504A4018C--