Re: use CREATE DATABASE STRATEGY = FILE_COPY in pg_upgrade

On Wed, Jun 5, 2024 at 10:59 PM Matthias van de Meent
<boekewurm+postgres(at)gmail(dot)com> wrote:
>
> On Wed, 5 Jun 2024 at 18:47, Ranier Vilela <ranier(dot)vf(at)gmail(dot)com> wrote:
> >
> > Em ter., 4 de jun. de 2024 às 16:39, Nathan Bossart <nathandbossart(at)gmail(dot)com> escreveu:
> >>
> >> I noticed that the "Restoring database schemas in the new cluster" part of
> >> pg_upgrade can take a while if you have many databases, so I experimented
> >> with a couple different settings to see if there are any easy ways to speed
> >> it up. The FILE_COPY strategy for CREATE DATABASE helped quite
> >> significantly on my laptop. For ~3k empty databases, this step went from
> >> ~100 seconds to ~30 seconds with the attached patch. I see commit ad43a41
> >> made a similar change for initdb, so there might even be an argument for
> >> back-patching this to v15 (where STRATEGY was introduced). One thing I
> >> still need to verify is that this doesn't harm anything when there are lots
> >> of objects in the databases, i.e., more WAL generated during many
> >> concurrent CREATE-DATABASE-induced checkpoints.
> >>
> >> Thoughts?
> >
> > Why not use it too, if not binary_upgrade?
>
> Because in the normal case (not during binary_upgrade) you don't want
> to have to generate 2 checkpoints for every created database,
> especially not when your shared buffers are large. Checkpoints' costs
> scale approximately linearly with the size of shared buffers, so being
> able to skip those checkpoints (with strategy=WAL_LOG) will save a lot
> of performance in the systems where this performance impact matters
> most.

I agree with you that we introduced the WAL_LOG strategy to avoid
these force checkpoints. However, in binary upgrade cases where no
operations are happening in the system, the FILE_COPY strategy should
be faster.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com