From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: pg_dump and thousands of schemas
Date: Tue, 29 May 2012 11:52:22 -0400
Message-ID: <12147.1338306742@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, ishii@postgresql.org
Subject: Re: pg_dump and thousands of schemas
In-Reply-To: <12147.1338306742@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox