Re: pg_dump dump catalog ACLs

On Wed, Apr 20, 2016 at 10:50:21PM -0400, Stephen Frost wrote:
> On Wednesday, April 20, 2016, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > On Wed, Apr 20, 2016 at 11:12:44AM -0400, Stephen Frost wrote:
> > > > On Sun, Apr 17, 2016 at 11:02:28PM -0400, Noah Misch wrote:
> > > > > (3) pg_dumpall became much slower around the time of these commits. On one
> > > > > machine (POWER7 3.55 GHz), a pg_dumpall just after initdb slowed from 0.25s at
> > > > > commit 6c268df^ to 4.0s at commit 7a54270. On a slower machine (Opteron
> > > > > 1210), pg_dumpall now takes 19s against such a fresh cluster.

> > > the additional time for
> > > pg_dump is due to the queries looking at the catalog objects and is
> > > therefore relatively fixed and is primairly only a large amount of the
> > > time when dumping databases which are mostly empty.
> >
> > Do you think it would be okay to release 9.6 with pg_dump still adding that
> > amount of time per database?
>
> For my 2c, the answer is "yes". I've actually looked at how this could be
> improved using a bit of caching in pg_dump for certain things, but I didn't
> think those would be appropriate to include in this patch and would be a
> general pg_dump performance improvement.
>
> I'm certainly open to improving these issues now if we agree that they
> should be fixed for 9.6. If we don't want to include such changes in 9.6
> then I will propose then for post-9.6.

Folks run clusters with ~1000 databases; we previously accepted at least one
complex performance improvement[1] based on that use case. On the faster of
the two machines I tested, the present thread's commits slowed "pg_dumpall
--schema-only --binary-upgrade" by 1-2s per database. That doubles pg_dump
runtime against the installcheck regression database. A run against a cluster
of one hundred empty databases slowed fifteen-fold, from 8.6s to 131s.
"pg_upgrade -j50" probably will keep things tolerable for the 1000-database
case, but the performance regression remains jarring. I think we should not
release 9.6 with pg_dump performance as it stands today.

[1] http://www.postgresql.org/message-id/flat/1718942738eb65c8407fcd864883f4c8(at)fuzzy(dot)cz