Thread: postmaster utilization

postmaster utilization

From
Shiwangini Shishulkar
Date:
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

Re: postmaster utilization

From
Ron
Date:
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.



Re: postmaster utilization

From
Stephen Frost
Date:
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

Attachment

Re: postmaster utilization

From
Kyotaro Horiguchi
Date:
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



Re: postmaster utilization

From
Stephen Frost
Date:
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

Attachment