performance of pg_upgrade "Copying user relation files"

Started by Glenn Pierceover 6 years ago5 messagesgeneral

glennpierce@gmail.com

over 6 years ago

I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade

Everything seems to start fine but when pg_upgrade gets to "Copying
user relation files"
It takes ages to complete copying.

ie
Each file like

/var/lib/pgsql/9.5/data/base/24602/25140
is 1G and taking ~5-10 minutes to copyand that directory is 223G of
about 990 files

After a couple of hours running pg_upgrade I had to give up.

looking a iotop I was seeing

99% IO load caused by [md0_raid1]

and only seeing pg_upgrade go to 99% every 3-4 seconds

I had no other processes using io or high cpu.

Has anyone got any pointers of what could be the issue ?

Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
directory is instantaneous

System is Centos 7

Thanks

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Glenn Pierce (#1)

Re: performance of pg_upgrade "Copying user relation files"

On 10/2/19 3:30 PM, Glenn Pierce wrote:

I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade

Everything seems to start fine but when pg_upgrade gets to "Copying
user relation files"
It takes ages to complete copying.

This is going to need more information:

1) What is the pg_upgrade command you are using?

2) Where are you upgrading from/to?

3) What OS, version?

ie
Each file like

/var/lib/pgsql/9.5/data/base/24602/25140
is 1G and taking ~5-10 minutes to copyand that directory is 223G of
about 990 files

After a couple of hours running pg_upgrade I had to give up.

looking a iotop I was seeing

99% IO load caused by [md0_raid1]

and only seeing pg_upgrade go to 99% every 3-4 seconds

I had no other processes using io or high cpu.

Has anyone got any pointers of what could be the issue ?

Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
directory is instantaneous

System is Centos 7

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Glenn Pierce (#1)

Re: performance of pg_upgrade "Copying user relation files"

On 10/2/19 3:30 PM, Glenn Pierce wrote:

I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade

Everything seems to start fine but when pg_upgrade gets to "Copying
user relation files"
It takes ages to complete copying.

Adding to previous list:

4) Which pg_upgrade version did you use, 9.5 or 9.6?

ie
Each file like

/var/lib/pgsql/9.5/data/base/24602/25140
is 1G and taking ~5-10 minutes to copyand that directory is 223G of
about 990 files

After a couple of hours running pg_upgrade I had to give up.

looking a iotop I was seeing

99% IO load caused by [md0_raid1]

and only seeing pg_upgrade go to 99% every 3-4 seconds

I had no other processes using io or high cpu.

Has anyone got any pointers of what could be the issue ?

Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
directory is instantaneous

System is Centos 7

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Glenn Pierce (#1)

Re: performance of pg_upgrade "Copying user relation files"

On 10/2/19 4:58 PM, Glenn Pierce wrote:
Please reply to list also.
Ccing list.

On Thu, 3 Oct 2019, 00:11 Adrian Klaver, <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 10/2/19 3:30 PM, Glenn Pierce wrote:

I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade

Everything seems to start fine but when pg_upgrade gets to "Copying
user relation files"
It takes ages to complete copying.

This is going to need more information:

1) What is the pg_upgrade command you are using?

I was using pg_upgrade from the installed postgres 9.6

/usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.5/bin/
--new-bindir=/usr/pgsql-9.6/bin/ --old-datadir=/var/lib/pgsql/9.5/data/
--new-datadir=/var/lib/pgsql/9.6/data/

I was following the article from

https://medium.com › postgresql-upg...
Web results
PostgreSQL upgrade on CentOS - Dzmitry Plashchynski - Medium

2) Where are you upgrading from/to?

Trying to upgrade from 9.5 to 9.6
Same machine with both versions installed.

3) What OS, version?

Centos 7.4

Thanks

ie
Each file like

/var/lib/pgsql/9.5/data/base/24602/25140
is 1G and taking ~5-10 minutes to copyand that directory is 223G of
about 990 files

After a couple of hours running pg_upgrade I had to give up.

looking a iotop I was seeing

99% IO load caused by [md0_raid1]

and only seeing pg_upgrade go to 99% every 3-4 seconds

I had no other processes using io or high cpu.

Has anyone got any pointers of what could be the issue ?

Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my home
directory is instantaneous

System is Centos 7

Thanks

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

Import Notes

Reply to msg id not found: CAM5ipV-3-y_f9-SLHnopJrsYuiwKP6dTFtzLtKT2wnJkPVdmXg@mail.gmail.com

Adrian Klaver

adrian.klaver@aklaver.com

over 6 years ago

In reply to: Adrian Klaver (#4)

Re: performance of pg_upgrade "Copying user relation files"

On 10/2/19 5:47 PM, Adrian Klaver wrote:

On 10/2/19 4:58 PM, Glenn Pierce wrote:
Please reply to list also.
Ccing list.

On Thu, 3 Oct 2019, 00:11 Adrian Klaver, <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

    On 10/2/19 3:30 PM, Glenn Pierce wrote:
     > I have been trying to upgrade postgres 9.5 to 9.6 with pg_upgrade
     >
     > Everything seems to start fine but when pg_upgrade gets to
"Copying
     > user relation files"
     > It takes ages to complete copying.

    This is going to need more information:

    1) What is the pg_upgrade command you are using?

I was using pg_upgrade from the installed postgres 9.6

/usr/pgsql-9.6/bin/pg_upgrade --old-bindir=/usr/pgsql-9.5/bin/
--new-bindir=/usr/pgsql-9.6/bin/
--old-datadir=/var/lib/pgsql/9.5/data/
--new-datadir=/var/lib/pgsql/9.6/data/

What happens if you use the -j option:

https://www.postgresql.org/docs/9.6/pgupgrade.html
-j
--jobs

number of simultaneous processes or threads to use

"The --jobs option allows multiple CPU cores to be used for
copying/linking of files and to dump and reload 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."

    I was following the article from

https://medium.com › postgresql-upg...
Web results
PostgreSQL upgrade on CentOS - Dzmitry Plashchynski - Medium

    2) Where are you upgrading from/to?

Trying to upgrade from 9.5 to 9.6
Same machine with both versions installed.

    3) What OS, version?

    Centos 7.4

Thanks

     >
     > ie
     > Each file like
     >
     > /var/lib/pgsql/9.5/data/base/24602/25140
     > is 1G and taking ~5-10 minutes to copyand that directory is
223G of
     > about 990 files
     >
     > After a couple of hours running pg_upgrade I had to give up.
     >
     > looking a iotop I was seeing
     >
     > 99% IO load caused by [md0_raid1]
     >
     > and only seeing pg_upgrade go to 99% every 3-4 seconds
     >
     > I had no other processes using io or high cpu.
     >
     > Has anyone got any pointers of what could be the issue ?
     >
     > Ps running cp on /var/lib/pgsql/9.5/data/base/24602/25140 to my
home
     > directory is instantaneous
     >
     > System is Centos 7
     >
     > Thanks
     >
     >

    --     Adrian Klaver
    adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com