Fujii Masao <masao.fu...@gmail.com> writes: > Hi, > > When multiple relations are deleted at the same transaction, > the files of those relations are deleted by one call to smgrdounlinkall(), > which leads to scan whole shared_buffers only one time. OTOH, > during recovery, smgrdounlink() (not smgrdounlinkall()) is called > for each file to delete, which leads to scan shared_buffers multiple times. > Obviously this can cause to increase the WAL replay time very much > especially when shared_buffers is huge.
Wonder if this is the case for streaming standbys replaying truncates also? Just couple days ago I was running a pg_upgrade test scenario but did not reach the point of upgrade yet. We made snapshots of our large reporting system putting them into a master and 1-slave streaming replication configuration. There are hundreds of unlogged tables that we wish to trunc before the upgrade to save time during the rsyncing of standbys, since a standard rsync replicates all data which is timeconsumeing and useless sending to the standbys. Indeed the tables are already trunc'd on the test master since it too was a recovered system upon initial start but the truncates took place anyway since it's part of our framework. It ran in just a few seconds on master. The standby however was $slow replaying these truncates which we noticed because the upgrade wil not proceed until master and 1 or more standbys are confirmed all at same checkpoint. I straced the standby's startup process to find it unlinking lots of tables, getting -1 on the unlink syscall since the non-init fork files were already missing. I can't describe just how slow if was but took minutes and the lines being output by strace were *not* blowing up my display as happens generally when any busy process is straced. And, the test systems were config'd with $large shared buffers of 64G. Please advise > > To alleviate this situation, I'd like to propose to change the recovery > so that it also calls smgrdounlinkall() only one time to delete multiple > relation files. Patch attached. Thought? > > Regards, -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 312.241.7800
