Re: postmaster utilization

Greetings,

* Kyotaro Horiguchi (horikyota(dot)ntt(at)gmail(dot)com) wrote:
> At Mon, 19 Aug 2019 10:07:30 -0400, Stephen Frost <sfrost(at)snowman(dot)net> wrote in <20190819140730(dot)GH16436(at)tamriel(dot)snowman(dot)net>
> > * Ron (ronljohnsonjr(at)gmail(dot)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