On 5/22/23 5:24 PM, Adrian Klaver wrote:
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
Yes, one big database with about 80 schemas and several other smaller
databases so -j should help, right?
Jeff
