Re: performance of pg_upgrade "Copying user relation files" - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: performance of pg_upgrade "Copying user relation files" |
Date | |
Msg-id | 9196dbcf-0f3d-78e8-75a8-fb7300cfaa05@aklaver.com Whole thread Raw |
In response to | Re: performance of pg_upgrade "Copying user relation files" (Adrian Klaver <adrian.klaver@aklaver.com>) |
List | pgsql-general |
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
pgsql-general by date: