Re: pg_restore causes 100

From: Tom Lane
Subject: Re: pg_restore causes 100
Date: ,
Msg-id: 17711.1184250423@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: pg_restore causes 100  (Craig James)
List: pgsql-performance

Tree view

Join with lower/upper limits doesn't scale well  (Craig James, )
 Re: Join with lower/upper limits doesn't scale well  (Gregory Stark, )
  Equivalent queries produce different plans  (Craig James, )
   Re: Equivalent queries produce different plans  (Craig James, )
   Re: Equivalent queries produce different plans  (Tom Lane, )
    pg_restore causes 100  (Craig James, )
     Re: pg_restore causes 100  (Tom Lane, )

Craig James <> writes:
> Now here's the weird thing.  I'm running a pg_restore of a database
> (on the order of 4GB compressed, maybe 34M rows of ordinary data, and
> 15M rows in one BLOB table that's typically 2K per blob).  When I do
> this, ALL of the postgress backends start working at about 1% CPU
> apiece.

It's not surprising that they'd all start eating some CPU, if that's a
schema restore and not just bulk data loading.  Any catalog change is
going to broadcast "shared cache inval" messages that all the backends
have to process to make sure they get rid of any now-obsolete cached
catalog information.

> This means that the 120 "idle" postgres backends are together
> using almost 100% of one CPU on top of the 100% CPU being used by
> pg_restore.  See the output of top(1) below.

Perhaps you need to try to cut down the number of idle processes ...

I don't think anyone's ever spent any time trying to micro-optimize
the shared cache inval code paths.  It could be we could cut your
1% figure some, if we were willing to put effort into that.  But it's
not going to go to zero.

            regards, tom lane


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: TRUNCATE TABLE
From: Adriaan van Os
Date:
Subject: Re: TRUNCATE TABLE