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(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com