Re: 15 pg_upgrade with -j

On 5/22/23 16:20, Jeff Ross wrote:
> Hello!
>
> We are moving from 10 to 15 and are in testing now.
>
> Our development database is about 1400G and takes 12 minutes to complete
> a pg_upgrade with the -k (hard-links) version.  This is on a CentOS 7
> server with 80 cores.
>
> Adding -j 40 to use half of those cores also finishes in 12 minutes and
> ps / top/ htop never show more than a single process at a time in use.
>
> Bumping that to -j 80 to use them all also finishes in 12 minutes and
> still only a single process.
>
> Running the suggested vacuum analyze after pg_upgrade completes takes
> about 19 minutes.  Adding -j 40 takes that time down to around 5
> minutes, jumps the server load up over 30 and htop shows 40 processes.
>
> If -j 40 helps there--why not with pg_upgrade?

From docs:

https://www.postgresql.org/docs/current/pgupgrade.html

The --jobs option allows multiple CPU cores to be used for
copying/linking of files and to dump and restore database schemas in
parallel; a good place to start is the maximum of the number of CPU
cores and tablespaces. This option can dramatically reduce the time to
upgrade a multi-database server running on a multiprocessor machine.

So is the 1400G mostly in one database in the cluster?

>
> The full commands we are using for pg_upgrade are pretty stock:
>
> time /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-10/bin/ -B
> /usr/pgsql-15/bin/ -d /var/lib/pgsql/10/data -D /var/lib/pgsql/15up -k
> time /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-10/bin/ -B
> /usr/pgsql-15/bin/ -d /var/lib/pgsql/10/data -D /var/lib/pgsql/15up -k -j 40
> time /usr/pgsql-15/bin/pg_upgrade -b /usr/pgsql-10/bin/ -B
> /usr/pgsql-15/bin/ -d /var/lib/pgsql/10/data -D /var/lib/pgsql/15up -k -j 80
>
> Our production database is closer to 1900G.  If we're looking at a 30
> minute pg_upgrade window we'll be okay but if there is anything we can
> do to knock that time down we will and any suggestions to do so would be
> greatly appreciated.
>
> Jeff Ross

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com