Обсуждение: postmaster utilization
Hi,
We have scheduled postgres full backup on centos 7 machine. DB size is around 66 GB. We observed while backup is running, postmaster CPU % reaches to 90 - 100%,which results very strange behavior: that we are not able to perform any DDL and DML on to the database during the time.
Please suggest a way to limit it's CPU utilization or else let us know how we can access the database during the backup window. Please let us know on priority since this is our prod database.
Get back to us in case of any query/detail.
Regards,
Shiwangini
On 8/19/19 5:40 AM, Shiwangini Shishulkar wrote: > Hi, > > We have scheduled postgres full backup on centos 7 machine. DB size is > around 66 GB. We observed while backup is running, postmaster CPU % > reaches to 90 - 100%,which results very strange behavior: that we are not > able to perform any DDL and DML on to the database during the time. > Please suggest a way to limit it's CPU utilization or else let us know how > we can access the database during the backup window. Please let us know on > priority since this is our prod database. Are you compressing the backup? How many threads are you running? How many CPUs on your system? What version of Postgres? -- Angular momentum makes the world go 'round.
Greetings, * Ron (ronljohnsonjr@gmail.com) wrote: > On 8/19/19 5:40 AM, Shiwangini Shishulkar wrote: > >We have scheduled postgres full backup on centos 7 machine. DB size is > >around 66 GB. We observed while backup is running, postmaster CPU % > >reaches to 90 - 100%,which results very strange behavior: that we are not > >able to perform any DDL and DML on to the database during the time. > >Please suggest a way to limit it's CPU utilization or else let us know how > >we can access the database during the backup window. Please let us know on > >priority since this is our prod database. > > Are you compressing the backup? > How many threads are you running? > How many CPUs on your system? > What version of Postgres? ... What are you using to run the backup? Thanks, Stephen
Вложения
At Mon, 19 Aug 2019 10:07:30 -0400, Stephen Frost <sfrost@snowman.net> wrote in <20190819140730.GH16436@tamriel.snowman.net> > Greetings, > > * Ron (ronljohnsonjr@gmail.com) wrote: > > On 8/19/19 5:40 AM, Shiwangini Shishulkar wrote: > > >We have scheduled postgres full backup on centos 7 machine. DB size is > > >around 66 GB. We observed while backup is running, postmaster CPU % > > >reaches to 90 - 100%,which results very strange behavior: that we are not > > >able to perform any DDL and DML on to the database during the time. > > >Please suggest a way to limit it's CPU utilization or else let us know how > > >we can access the database during the backup window. Please let us know on > > >priority since this is our prod database. > > > > Are you compressing the backup? > > How many threads are you running? > > How many CPUs on your system? > > What version of Postgres? > > ... What are you using to run the backup? It seems to have been reported as BUG #15961.. https://www.postgresql.org/message-id/15965-413bf5d18aaeface@postgresql.org > PostgreSQL version: 11.4 > Operating system: CentOS Linux release 7.6.1810 (Core) > pg_dump -U postgres -d wg -f wg.sql regards. -- Kyotaro Horiguchi NTT Open Source Software Center
Greetings, * Kyotaro Horiguchi (horikyota.ntt@gmail.com) wrote: > At Mon, 19 Aug 2019 10:07:30 -0400, Stephen Frost <sfrost@snowman.net> wrote in <20190819140730.GH16436@tamriel.snowman.net> > > * Ron (ronljohnsonjr@gmail.com) wrote: > > > On 8/19/19 5:40 AM, Shiwangini Shishulkar wrote: > > > >We have scheduled postgres full backup on centos 7 machine. DB size is > > > >around 66 GB. We observed while backup is running, postmaster CPU % > > > >reaches to 90 - 100%,which results very strange behavior: that we are not > > > >able to perform any DDL and DML on to the database during the time. > > > >Please suggest a way to limit it's CPU utilization or else let us know how > > > >we can access the database during the backup window. Please let us know on > > > >priority since this is our prod database. > > > > > > Are you compressing the backup? > > > How many threads are you running? > > > How many CPUs on your system? > > > What version of Postgres? > > > > ... What are you using to run the backup? > > It seems to have been reported as BUG #15961.. > > https://www.postgresql.org/message-id/15965-413bf5d18aaeface@postgresql.org > > > PostgreSQL version: 11.4 > > Operating system: CentOS Linux release 7.6.1810 (Core) > > pg_dump -U postgres -d wg -f wg.sql Ah, then the pg_dump is clearly what's locking the tables against DDL. That wouldn't impact DML though (unless, of course, a DDL was issued and then blocked behind pg_dump, and then DML was attempted on the same table as that would then be blocked behind the DDL). There's not much help for that when you're taking logical backups, unfortunately. Of course, you could take physical backups instead. Thanks, Stephen