Обсуждение: performance of pg_upgrade "Copying user relation files"
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
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
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
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
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