Skip site navigation (1) Skip section navigation (2)

Re: pg_dump and thousands of schemas

From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: pgsql-performance(at)postgresql(dot)org, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pg_dump and thousands of schemas
Date: 2012-05-31 05:29:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackerspgsql-performance
> I'm not excited by this patch.  It dodges the O(N^2) lock behavior for
> the initial phase of acquiring the locks, but it does nothing for the
> lock-related slowdown occurring in all pg_dump's subsequent commands.
> I think we really need to get in the server-side fix that Jeff Janes is
> working on, and then re-measure to see if something like this is still
> worth the trouble.

Well, even with current backend, locking 100,000 tables has been done
in 3 seconds in my test. So even if Jeff Janes's fix is succeeded, I
guess it will just save 3 seconds in my case. and if number of tables
is smaller, the saving will smaller. This suggests that most of time
for processing LOCK has been spent in communication between pg_dump
and backend. Of course this is just my guess, though.

> I am also a tad concerned about whether we might not
> have problems with parsing memory usage, or some such, with thousands of
> tables being listed in a single command.

That's easy to fix. Just divide each LOCK statements into multiple
LOCK statements.

My big concern is, even if the locking part is fixed (either by Jeff
Jane's fix or by me) still much time in pg_dump is spent for SELECTs
against system catalogs. The fix will be turn many SELECTs into single
SELECT, probably using big IN clause for tables oids.
Tatsuo Ishii
SRA OSS, Inc. Japan

In response to

pgsql-performance by date

Next:From: Tatsuo IshiiDate: 2012-05-31 08:45:26
Subject: Re: pg_dump and thousands of schemas
Previous:From: Tom LaneDate: 2012-05-31 04:18:17
Subject: Re: pg_dump and thousands of schemas

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-05-31 05:40:50
Subject: Re: Bug in new buffering GiST build code
Previous:From: Erik RijkersDate: 2012-05-31 05:06:48
Subject: Re: FailedAssertion("!(PrivateRefCount[i] == 0)", File: "bufmgr.c", Line: 1741

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group