Re: pg_upgrade too slow on vacuum phase - Mailing list pgsql-general

On 5/12/20 5:27 AM, Kouber Saparev wrote:
> I am trying to upgrade PostgreSQL from 9.5 to 12 using pg_upgrade.
> 
> /usr/lib/postgresql/12/bin/pg_upgrade \
>    --old-datadir=/var/lib/postgresql/9.5/main \
>    --new-datadir=/var/lib/postgresql/12/main \
>    --old-bindir=/usr/lib/postgresql/9.5/bin \
>    --new-bindir=/usr/lib/postgresql/12/bin \
>    --old-options '-c config_file=/etc/postgresql/9.5/main/postgresql.conf' \
>    --new-options '-c config_file=/etc/postgresql/12/main/postgresql.conf' \
>    --link \
>    --jobs=16
> 
> It takes 47 minutes for the upgrade to finish (for a 28 GB database). It 
> hangs on two particular steps:
> 
> Analyzing all rows in the new cluster                       ok
> Freezing all rows in the new cluster                        ok

Are you sure?

 From here:

~/src/bin/pg_upgrade/pg_upgrade.c

prepare_new_cluster(void)
{
         /*
          * It would make more sense to freeze after loading the schema, 
but that
          * would cause us to lose the frozenids restored by the load. 
We use
          * --analyze so autovacuum doesn't update statistics later
          */
         prep_status("Analyzing all rows in the new cluster");
         exec_prog(UTILITY_LOG_FILE, NULL, true, true,
                           "\"%s/vacuumdb\" %s --all --analyze %s",
                           new_cluster.bindir, 
cluster_conn_opts(&new_cluster),
                           log_opts.verbose ? "--verbose" : "");
         check_ok();



/* -- NEW -- */
start_postmaster(&new_cluster, true);

check_new_cluster();
report_clusters_compatible();

pg_log(PG_REPORT,
             "\n"
             "Performing Upgrade\n"
             "------------------\n");

prepare_new_cluster();

stop_postmaster(false);

/*
     * Destructive Changes to New Cluster
     */

copy_xact_xlog_xid();

/* New now using xids of the old system */

/* -- NEW -- */
start_postmaster(&new_cluster, true);

prepare_new_globals();

create_new_objects();

stop_postmaster(false);


So the analyze(and freeze) are done before the new cluster are fully 
populated. Is the time being taken maybe for the loading schema/data 
portion?

> 
> Which basically runs:
> 
> vacuumdb --all --analyze
> vacuumdb --all --freeze
> 
> This is where all these 47 minutes are spent, yet I do not understand 
> neither why, nor how I can improve this part. Can I skip it somehow and 
> launch the vacuum manually afterwards?
> 
> Per postgresql.conf, I gave:
> 
> work_mem = 128MB
> maintenance_work_mem = 8GB
> max_parallel_maintenance_workers = 16
> max_parallel_workers = 16
> 
> The server has 44 GB available memory, and 24 cores.
> 
> Do you have any ideas how to speed-up the entire process?
> 
> --
> Kouber Saparev


-- 
Adrian Klaver
adrian.klaver@aklaver.com