Thread

PG11 to PG14 Migration Slowness

Vigneshk Kvignesh <krrvignesh2@gmail.com> — 2023-01-12T09:15:41Z

Hi,

      I'm migrating  our existing PG instances from PG11.4  to PG14.3. I
have around 5 Million Tables in a single database. When migrating using
pg_upgrade, its taking 3 hours for the process to complete. I'm not sure if
its the intended behaviour or we're missing something here.
     Most of the tables (90%) in 5 Million are foreign tables. On analysis
found that most of the time is spent in pg_dump (~2.3 hours). In pg_dump
getTableAttrs(), dumpTable() functions take the most time, approx 1 hour
each since we're processing table by table. Also, there are no columns with
default values, which if present might take some time. We're using PG14's
pg_upgrade binary for the process.
    Since we have all these tables in one database, parallelism doesn't
have any effect here. Can we make binary upgrade for a single database run
in parallel ?
     Kindly advise us if we have missed anything here and possible
solutions for this problem.
So we're not sure on what we missed here.
Have added more info on the process below.

No. of Tables: 5 Million
Time Taken: 3 Hours
Command Used: $PG14_UPGRADE -Uroot -b $PG11_DIR/bin -B $PG14_DIR/bin -d
$PG11_DIR/data -D $PG14_DIR/data -k -r -j32
Version: PG11.4 to PG14.3
Environment: CentOS machine (32 cores(Intel), 128GB RAM)


Thanks and Regards,
Vignesh K.

Re: PG11 to PG14 Migration Slowness

Ilya Anfimov <ilan@tzirechnoy.com> — 2023-01-12T11:07:22Z

On Thu, Jan 12, 2023 at 02:45:41PM +0530, Vigneshk Kvignesh wrote:
>    Hi,
>     
>          I'm migrating  our existing PG instances from PG11.4  to PG14.3. I
>    have around 5 Million Tables in a single database. When migrating using
>    pg_upgrade, its taking 3 hours for the process to complete. I'm not sure
>    if its the intended behaviour or we're missing something here.

 Yes. In fact, you have a good hardware and I would expect longer time
on average.

>         Most of the tables (90%) in 5 Million are foreign tables. On analysis
>    found that most of the time is spent in pg_dump (~2.3 hours). In pg_dump
>    getTableAttrs(), dumpTable() functions take the most time, approx 1 hour
>    each since we're processing table by table. Also, there are no columns
>    with default values, which if present might take some time. We're using
>    PG14's pg_upgrade binary for the process.
>        Since we have all these tables in one database, parallelism doesn't
>    have any effect here. Can we make binary upgrade for a single database run
>    in parallel ?
>         Kindly advise us if we have missed anything here and possible
>    solutions for this problem.

 I  don't  see any problem. Three-hour downtime every three years
for such setup... You are lucky you have only that.

 But you could try some logical replication  to  the  new  server
version  for upgrading, if you really want to bother.  (Well, pg-
logical is my preferred on that scale, but the three general  op-
tions are: internal logical replication, pglogical, slony).



>    So we're not sure on what we missed here.
>    Have added more info on the process below.
>    No. of Tables: 5 Million
>    Time Taken: 3 Hours
>    Command Used: $PG14_UPGRADE -Uroot -b $PG11_DIR/bin -B $PG14_DIR/bin -d
>    $PG11_DIR/data -D $PG14_DIR/data -k -r -j32
>    Version: PG11.4 to PG14.3
>    Environment: CentOS machine (32 cores(Intel), 128GB RAM)
> 
>    Thanks and Regards,
>    Vignesh K.

Re: PG11 to PG14 Migration Slowness

Tom Lane <tgl@sss.pgh.pa.us> — 2023-01-12T15:48:46Z

Vigneshk Kvignesh <krrvignesh2@gmail.com> writes:
>       I'm migrating  our existing PG instances from PG11.4  to PG14.3. I
> have around 5 Million Tables in a single database. When migrating using
> pg_upgrade, its taking 3 hours for the process to complete. I'm not sure if
> its the intended behaviour or we're missing something here.

There was some work done in v15 to make pg_dump deal better with
zillions of tables.  Don't know if you can consider retargeting
to v15, or how much the speedups would help in your particular
situation.

Why are you using 14.3, when the current release is 14.6?

			regards, tom lane

Re: PG11 to PG14 Migration Slowness

Aleksander Alekseev <aleksander@timescale.com> — 2023-01-13T13:02:00Z

Hi Vigneshk,

>       I'm migrating  our existing PG instances from PG11.4  to PG14.3. I have around 5 Million Tables in a single database. When migrating using pg_upgrade, its taking 3 hours for the process to complete. I'm not sure if its the intended behaviour or we're missing something here.

Thanks for reporting this. I would say this is more or less an
expected behaviour. This being said I think we could do better than
that.

Could you identify the bottleneck or perhaps provide the minimal
automated steps (ideally, a script) to reproduce your issue in a clean
environment?

-- 
Best regards,
Aleksander Alekseev

Re: PG11 to PG14 Migration Slowness

Vigneshk Kvignesh <krrvignesh2@gmail.com> — 2023-01-27T11:57:40Z

Hi,

Sorry for the delayed response. We have an fdw extension, we started code
changes in the extension for PGv14 on 14.3, we just completed code changes,
testing and benchmarking. We'll retarget to 14.6
Also we'll take a look at the changes for pg_dump in v15 . Thanks for the
advice.

Thanks and Regards,
Vignesh K.

On Thu, 12 Jan 2023 at 21:18, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Vigneshk Kvignesh <krrvignesh2@gmail.com> writes:
> >       I'm migrating  our existing PG instances from PG11.4  to PG14.3. I
> > have around 5 Million Tables in a single database. When migrating using
> > pg_upgrade, its taking 3 hours for the process to complete. I'm not sure
> if
> > its the intended behaviour or we're missing something here.
>
> There was some work done in v15 to make pg_dump deal better with
> zillions of tables.  Don't know if you can consider retargeting
> to v15, or how much the speedups would help in your particular
> situation.
>
> Why are you using 14.3, when the current release is 14.6?
>
>                         regards, tom lane
>