15 pg_upgrade with -j

From: Jeff Ross <jross(at)openvistas(dot)net>
To: PostgreSQL <pgsql-general(at)postgresql(dot)org>
Subject: 15 pg_upgrade with -j
Date: 2023-05-22 23:20:35
Message-ID: 85a18793-627a-2a8b-1296-c965505694e6@openvistas.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2023-05-22 23:24:36 Re: 15 pg_upgrade with -j
Previous Message Merlin Moncure 2023-05-22 22:56:58 Re: CTE, lateral or jsonb_object_agg ?