Re: Speedup of relation deletes during recovery

Fujii Masao <masao(dot)fujii(at)gmail(dot)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(dot)consulting(at)comcast(dot)net
p: 312.241.7800