Re: Certain options in pg_upgrade don't seem to work.

On 8/14/24 03:41, DBA wrote:
> Hello, I'm inquiring because --j option don't seem to be working
> properly in pg_upgrade.
>
>
> *1. A description of what you are trying to achieve and what results you
> expect : *
>
>
> We took to much time to upgrade postgresql with pg_upgrade.
>
>
> So we have tested with --j option in pg_upgrade by below command 
>
>
> *time PGPASSWORD='1q2w3e4r' nohup /usr/lib/postgresql/16/bin/pg_upgrade
> -b /usr/lib/postgresql/15/bin/ -B /usr/lib/postgresql/16/bin/ -d
> /data/PG15 -D /data/PG16 -k -j 16 -U ecount_own > /data/tmp4/upgrade.log &*
>
>
> It took 520 minutes to upgrade and we used -j option to make pg_upgrade
> use multiple CPU processor but it didn't use them. (Only use single
> processor) 
>
> (It took the same amount of time as when tested without --j option.)
>
>
> Is it normal not to use multiple cpu cores even with the --j option?
>
>
> The docs says that you use multi-processors for restoring.
>
>
> *note.)* We have about 80 partition tables and each partition table has
> 9000 children tables.
>
>
>        I think it could be affected by the number of partition tables.
>

Very likely. The thing is, pg_upgrade does multiple tasks, and only some
of those are parallelized - the pg_upgrade [1] docs say:

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 it's only the "linking" of files that's done using multiple jobs:

...
Linking user relation files ok
...

But with 80 partitioned tables, and 9000 partitions for each, the most
expensive step is likely dump/restore of the schema. And those are not
parallelized - it always happens in a single process.

You can confirm this by watching the pg_upgrade output, to see which
steps take most of the time.

regards

--
Tomas Vondra