To: Craig James <craig_james@emolecules.com>
cc: pgsql-performance@postgresql.org
Subject: Re: pg_restore causes 100 
In-reply-to: <4695D995.9040207@emolecules.com> 
References: <46898AB3.7090409@emolecules.com>
	<87hcom5p41.fsf@oxford.xeocode.com>
	<469429FD.6080504@emolecules.com> <13642.1184117103@sss.pgh.pa.us>
	<4695D995.9040207@emolecules.com>
Comments: In-reply-to Craig James <craig_james@emolecules.com>
	message dated "Thu, 12 Jul 2007 00:34:45 -0700"
Date: Thu, 12 Jul 2007 10:27:03 -0400
Message-ID: <17711.1184250423@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Craig James <craig_james@emolecules.com> 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