To: Tatsuo Ishii <ishii@postgresql.org>
cc: pgsql-performance@postgresql.org
Subject: Re: pg_dump and thousands of schemas 
In-reply-to: <20120529.185149.1127748531209576379.t-ishii@sraoss.co.jp> 
References: <1337921645663-5709975.post@n5.nabble.com>
	<20887.1337956883@sss.pgh.pa.us>
	<20120526.101840.762942175522564494.t-ishii@sraoss.co.jp>
	<20120529.185149.1127748531209576379.t-ishii@sraoss.co.jp>
Comments: In-reply-to Tatsuo Ishii <ishii@postgresql.org>
	message dated "Tue, 29 May 2012 18:51:49 +0900"
Date: Tue, 29 May 2012 11:52:22 -0400
Message-ID: <12147.1338306742@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Tatsuo Ishii <ishii@postgresql.org> writes:
> So I did qucik test with old PostgreSQL 9.0.2 and current (as of
> commit 2755abf386e6572bad15cb6a032e504ad32308cc). In a fresh initdb-ed
> database I created 100,000 tables, and each has two integer
> attributes, one of them is a primary key. Creating tables were
> resonably fast as expected (18-20 minutes). This created a 1.4GB
> database cluster.

> pg_dump dbname >/dev/null took 188 minutes on 9.0.2, which was pretty
> long time as the customer complained. Now what was current?  Well it
> took 125 minutes. Ps showed that most of time was spent in backend.

Yeah, Jeff's experiments indicated that the remaining bottleneck is lock
management in the server.  What I fixed so far on the pg_dump side
should be enough to let partial dumps run at reasonable speed even if
the whole database contains many tables.  But if psql is taking
AccessShareLock on lots of tables, there's still a problem.

			regards, tom lane