Further pg_upgrade analysis for many tables

On Wed, Nov 7, 2012 at 09:17:29PM -0500, Bruce Momjian wrote:

Things look fine through 2k, but at 4k the duration of pg_dump, restore,
and pg_upgrade (which is mostly a combination of these two) is 4x,
rather than the 2x as predicted by the growth in the number of tables.
To see how bad it is, 16k tables is 1.3 hours, and 32k tables would be
5.6 hours by my estimates.

You can see the majority of pg_upgrade duration is made up of the
pg_dump and the schema restore, so I can't really speed up pg_upgrade
without speeding those up, and the 4x increase is in _both_ of those
operations, not just one.

Also, for 16k, I had to increase max_locks_per_transaction or the dump
would fail, which kind of surprised me.

I tested 9.2 and git head, but they produced identical numbers. I did
use synchronous_commit=off.

Any ideas? I am attaching my test script.

Thinking this might be related to some server setting, I increased
shared buffers, work_mem, and maintenance_work_mem, but this produced
almost no improvement:

tables pg_dump restore pg_upgrade
1 0.30 0.24 11.73(-)
1000 6.46 6.55 28.79(2.45)
2000 29.82 20.96 69.75(2.42)
4000 95.70 115.88 289.82(4.16)
8000 405.38 505.93 1168.60(4.03)

shared_buffers=1GB
tables pg_dump restore pg_upgrade
1 0.26 0.23
1000 6.22 7.00
2000 23.92 22.51
4000 88.44 111.99
8000 376.20 531.07

shared_buffers=1GB
work_mem/maintenance_work_mem = 500MB
1 0.27 0.23
1000 6.39 8.27
2000 26.34 20.53
4000 89.47 104.59
8000 397.13 486.99

Any ideas what else I should test? It this O(2n) or O(n^2) behavior?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 11/7/12 9:17 PM, Bruce Momjian wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

CREATE TABLE test991 (x SERIAL);

I ran it for 0, 1k, 2k, ... 16k tables, and got these results:

tables pg_dump restore pg_upgrade(increase)
0 0.30 0.24 11.73(-)
1000 6.46 6.55 28.79(2.45x)
2000 29.82 20.96 69.75(2.42x)
4000 95.70 115.88 289.82(4.16x)
8000 405.38 505.93 1168.60(4.03x)
16000 1702.23 2197.56 5022.82(4.30x)

I can reproduce these numbers, more or less. (Additionally, it ran out
of shared memory with the default setting when dumping the 8000 tables.)

But this issue seems to be entirely the fault of sequences being
present. When I replace the serial column with an int, everything
finishes within seconds and scales seemingly linearly.

On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian <bruce@momjian.us> wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

...

Any ideas? I am attaching my test script.

Have you reviewed the thread at:
http://archives.postgresql.org/pgsql-performance/2012-09/msg00003.php
?

There is a known N^2 behavior when using pg_dump against pre-9.3 servers.

There was a proposed patch to pg_dump to work around the problem when
it is used against older servers, but it is was not accepted and not
entered into a commitfest. For one thing because it there was doubts
about how stable it would be at very large scale and it wasn't tested
all that thoroughly, and for another, it would be a temporary
improvement as once the server itself is upgraded to 9.3, the kludge
in pg_dump would no longer be an improvement.

The most recent version (that I can find) of that work-around patch is at:

http://archives.postgresql.org/pgsql-performance/2012-06/msg00071.php

I don't know if that will solve your particular case, but it is
probably worth a try.

Cheers,

Jeff

On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:

On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian <bruce@momjian.us> wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

...

Any ideas? I am attaching my test script.

Have you reviewed the thread at:
http://archives.postgresql.org/pgsql-performance/2012-09/msg00003.php
?

There is a known N^2 behavior when using pg_dump against pre-9.3 servers.

I am actually now dumping git head/9.3, so I assume all the problems we
know about should be fixed.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Thu, Nov 8, 2012 at 12:30:11PM -0500, Peter Eisentraut wrote:

On 11/7/12 9:17 PM, Bruce Momjian wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

CREATE TABLE test991 (x SERIAL);

I ran it for 0, 1k, 2k, ... 16k tables, and got these results:

tables pg_dump restore pg_upgrade(increase)
0 0.30 0.24 11.73(-)
1000 6.46 6.55 28.79(2.45x)
2000 29.82 20.96 69.75(2.42x)
4000 95.70 115.88 289.82(4.16x)
8000 405.38 505.93 1168.60(4.03x)
16000 1702.23 2197.56 5022.82(4.30x)

I can reproduce these numbers, more or less. (Additionally, it ran out
of shared memory with the default setting when dumping the 8000 tables.)

But this issue seems to be entirely the fault of sequences being
present. When I replace the serial column with an int, everything
finishes within seconds and scales seemingly linearly.

I did some more research and realized that I was not using --schema-only
like pg_upgrade uses. With that setting, things look like this:

--schema-only
tables pg_dump restore pg_upgrade
1 0.27 0.23 11.73(-)
1000 3.64 5.18 28.79(2.45)
2000 13.07 14.63 69.75(2.42)
4000 43.93 66.87 289.82(4.16)
8000 190.63 326.67 1168.60(4.03)
16000 757.80 1402.82 5022.82(4.30)

You can still see the 4x increase, but it now for all tests ---
basically, every time the number of tables doubles, the time to dump or
restore a _single_ table doubles, e.g. for 1k tables, a single table
takes 0.00364 to dump, for 16k tables, a single table takes 0.04736 to
dump, a 13x slowdown.

Second, with --schema-only, you can see the dump/restore is only 50% of
the duration of pg_upgrade, and you can also see that pg_upgrade itself
is slowing down as the number of tables increases, even ignoring the
dump/reload time.

This is all bad news. :-( I will keep digging.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:

On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian <bruce@momjian.us> wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

...

Any ideas? I am attaching my test script.

Have you reviewed the thread at:
http://archives.postgresql.org/pgsql-performance/2012-09/msg00003.php
?

There is a known N^2 behavior when using pg_dump against pre-9.3 servers.

I am actually now dumping git head/9.3, so I assume all the problems we
know about should be fixed.

Are sure the server you are dumping out of is head?

Using head's pg_dump, but 9.2.1 server, it takes me 179.11 seconds to
dump 16,000 tables (schema only) like your example, and it is
definitely quadratic.

But using head's pg_dump do dump tables out of head's server, it only
took 24.95 seconds, and the quadratic term is not yet important,
things still look linear.

But even the 179.11 seconds is several times faster than your report
of 757.8, so I'm not sure what is going on there. I don't think my
laptop is particularly fast:

Intel(R) Pentium(R) CPU B960 @ 2.20GHz

Is the next value, increment, etc. for a sequence stored in a catalog,
or are they stored in the 8kb file associated with each sequence? If
they are stored in the file, than it is shame that pg_dump goes to the
effort of extracting that info if pg_upgrade is just going to
overwrite it anyway.

Cheers,

Jeff

Jeff Janes <jeff.janes@gmail.com> writes:

Are sure the server you are dumping out of is head?

I experimented a bit with dumping/restoring 16000 tables matching
Bruce's test case (ie, one serial column apiece). The pg_dump profile
seems fairly flat, without any easy optimization targets. But
restoring the dump script shows a rather interesting backend profile:

samples % image name symbol name
30861 39.6289 postgres AtEOXact_RelationCache
9911 12.7268 postgres hash_seq_search
2682 3.4440 postgres init_sequence
2218 2.8482 postgres _bt_compare
2120 2.7223 postgres hash_search_with_hash_value
1976 2.5374 postgres XLogInsert
1429 1.8350 postgres CatalogCacheIdInvalidate
1282 1.6462 postgres LWLockAcquire
973 1.2494 postgres LWLockRelease
702 0.9014 postgres hash_any

The hash_seq_search time is probably mostly associated with
AtEOXact_RelationCache, which is run during transaction commit and scans
the relcache hashtable looking for tables created in the current
transaction. So that's about 50% of the runtime going into that one
activity.

There are at least three ways we could whack that mole:

* Run the psql script in --single-transaction mode, as I was mumbling
about the other day. If we were doing AtEOXact_RelationCache only once,
rather than once per CREATE TABLE statement, it wouldn't be a problem.
Easy but has only a narrow scope of applicability.

* Keep a separate list (or data structure of your choice) so that
relcache entries created in the current xact could be found directly
rather than having to scan the whole relcache. That'd add complexity
though, and could perhaps be a net loss for cases where the relcache
isn't so bloated.

* Limit the size of the relcache (eg by aging out
not-recently-referenced entries) so that we aren't incurring O(N^2)
costs for scripts touching N tables. Again, this adds complexity and
could be counterproductive in some scenarios.

regards, tom lane

On Fri, Nov 9, 2012 at 6:59 AM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:

On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian <bruce@momjian.us> wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

...

Any ideas? I am attaching my test script.

Have you reviewed the thread at:
http://archives.postgresql.org/pgsql-performance/2012-09/msg00003.php
?

There is a known N^2 behavior when using pg_dump against pre-9.3 servers.

I am actually now dumping git head/9.3, so I assume all the problems we
know about should be fixed.

Are sure the server you are dumping out of is head?

Using head's pg_dump, but 9.2.1 server, it takes me 179.11 seconds to
dump 16,000 tables (schema only) like your example, and it is
definitely quadratic.

But using head's pg_dump do dump tables out of head's server, it only
took 24.95 seconds, and the quadratic term is not yet important,
things still look linear.

I also ran a couple of experiments with git head. From 8k to 16k I'm
seeing slightly super-linear scaling (2.25x), from 32k to 64k a
quadratic term has taken over (3.74x).

I ran the experiments on a slightly beefier machine (Intel i5 @ 4GHz,
Intel SSD 320, Linux 3.2, ext4). For 16k, pg_dump took 29s, pg_upgrade
111s. At 64k the times were 150s/1237s. I didn't measure it, but
occasional peek at top suggested that most of the time was spent doing
server side processing of restore.

I also took two profiles (attached). AtEOXact_RelationCache seems to
be the culprit for the quadratic growth.

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

On Fri, Nov 9, 2012 at 7:53 AM, Ants Aasma <ants@cybertec.at> wrote:

I also took two profiles (attached). AtEOXact_RelationCache seems to
be the culprit for the quadratic growth.

One more thing that jumps out as quadratic from the profiles is
transfer_all_new_dbs from pg_upgrade (20% of total CPU time at 64k).
Searching for non-primary files loops over the whole file list for
each relation. This would be a lot faster if we would sort the file
list first and use binary search to find the related files.

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

On Thu, Nov 8, 2012 at 9:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Jeff Janes <jeff.janes@gmail.com> writes:

Are sure the server you are dumping out of is head?

I experimented a bit with dumping/restoring 16000 tables matching
Bruce's test case (ie, one serial column apiece). The pg_dump profile
seems fairly flat, without any easy optimization targets. But
restoring the dump script shows a rather interesting backend profile:

samples % image name symbol name
30861 39.6289 postgres AtEOXact_RelationCache
9911 12.7268 postgres hash_seq_search

...

There are at least three ways we could whack that mole:

* Run the psql script in --single-transaction mode, as I was mumbling
about the other day. If we were doing AtEOXact_RelationCache only once,
rather than once per CREATE TABLE statement, it wouldn't be a problem.
Easy but has only a narrow scope of applicability.

That is effective when loading into 9.3 (assuming you make
max_locks_per_transaction large enough). But when loading into <9.3,
using --single-transaction will evoke the quadratic behavior in the
resource owner/lock table and make things worse rather than better.

But there is still the question of how people can start using 9.3 if
they can't use pg_upgrade, or use the pg_dump half of the dump/restore
in, order to get there.

It seems to me that pg_upgrade takes some pains to ensure that no one
else attaches to the database during its operation. In that case, is
it necessary to run the entire dump in a single transaction in order
to get a consistent picture? The attached crude patch allows pg_dump
to not use a single transaction (and thus not accumulate a huge number
of locks) by using the --pg_upgrade flag.

This seems to remove the quadratic behavior of running pg_dump against
pre-9.3 servers. It is linear up to 30,000 tables with a single
serial column, at about 1.5 msec per table.

I have no evidence other than a gut feeling that this is a safe thing to do.

I've also tested Tatsuo-san's group-"LOCK TABLE" patch against this
case, and it is minimal help. The problem is that there is no syntax
for locking sequences, so they cannot be explicitly locked as a group
but rather are implicitly locked one by one and so still suffer from
the quadratic behavior.

Cheers,

Jeff

On Thu, Nov 8, 2012 at 08:59:21PM -0800, Jeff Janes wrote:

On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Nov 8, 2012 at 03:46:09PM -0800, Jeff Janes wrote:

On Wed, Nov 7, 2012 at 6:17 PM, Bruce Momjian <bruce@momjian.us> wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

...

Any ideas? I am attaching my test script.

Have you reviewed the thread at:
http://archives.postgresql.org/pgsql-performance/2012-09/msg00003.php
?

There is a known N^2 behavior when using pg_dump against pre-9.3 servers.

I am actually now dumping git head/9.3, so I assume all the problems we
know about should be fixed.

Are sure the server you are dumping out of is head?

Well, I tested again with 9.2 dumping/loading 9.2 and the same for git
head, and got these results:

pg_dump restore
9.2 git 9.2 git

1 0.13 0.11 0.07 0.07
1000 4.37 3.98 4.32 5.28
2000 12.98 12.19 13.64 14.25
4000 47.85 50.14 61.31 70.97
8000 210.39 183.00 302.67 294.20
16000 901.53 769.83 1399.25 1359.09

As you can see, there is very little difference between 9.2 and git
head, except maybe at the 16k level for pg_dump.

Is there some slowdown with a mismatched version dump/reload? I am
attaching my test script.

Using head's pg_dump, but 9.2.1 server, it takes me 179.11 seconds to
dump 16,000 tables (schema only) like your example, and it is
definitely quadratic.

Are you using a SERIAL column for the tables. I am, and Peter
Eisentraut reported that was a big slowdown.

But using head's pg_dump do dump tables out of head's server, it only
took 24.95 seconds, and the quadratic term is not yet important,
things still look linear.

Again, using SERIAL?

But even the 179.11 seconds is several times faster than your report
of 757.8, so I'm not sure what is going on there. I don't think my
laptop is particularly fast:

Intel(R) Pentium(R) CPU B960 @ 2.20GHz

I am using server-grade hardware, Xeon E5620 2.4GHz:

http://momjian.us/main/blogs/pgblog/2012.html#January_20_2012

Is the next value, increment, etc. for a sequence stored in a catalog,
or are they stored in the 8kb file associated with each sequence? If

Each sequence is stored in its own 1-row 8k table:

test=> CREATE SEQUENCE seq;
CREATE SEQUENCE

test=> SELECT * FROM seq;
-[ RECORD 1 ]-+--------------------
sequence_name | seq
last_value | 1
start_value | 1
increment_by | 1
max_value | 9223372036854775807
min_value | 1
cache_value | 1
log_cnt | 0
is_cycled | f
is_called | f

they are stored in the file, than it is shame that pg_dump goes to the
effort of extracting that info if pg_upgrade is just going to
overwrite it anyway.

Actually, pg_upgrade needs pg_dump to restore all those sequence values.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 2012-11-08 12:30:11 -0500, Peter Eisentraut wrote:

On 11/7/12 9:17 PM, Bruce Momjian wrote:

As a followup to Magnus's report that pg_upgrade was slow for many
tables, I did some more testing with many tables, e.g.:

CREATE TABLE test991 (x SERIAL);

I ran it for 0, 1k, 2k, ... 16k tables, and got these results:

tables pg_dump restore pg_upgrade(increase)
0 0.30 0.24 11.73(-)
1000 6.46 6.55 28.79(2.45x)
2000 29.82 20.96 69.75(2.42x)
4000 95.70 115.88 289.82(4.16x)
8000 405.38 505.93 1168.60(4.03x)
16000 1702.23 2197.56 5022.82(4.30x)

I can reproduce these numbers, more or less. (Additionally, it ran out
of shared memory with the default setting when dumping the 8000 tables.)

But this issue seems to be entirely the fault of sequences being
present. When I replace the serial column with an int, everything
finishes within seconds and scales seemingly linearly.

I don't know the pg_dump code at all but I would guess that without the
serial there are no dependencies, so the whole dependency sorting
business doesn't need to do very much...

Greetings,

Andres Freund

On Thu, Nov 8, 2012 at 7:25 PM, Bruce Momjian <bruce@momjian.us> wrote:

I did some more research and realized that I was not using --schema-only
like pg_upgrade uses. With that setting, things look like this:

...

For profiling pg_dump in isolation, you should also specify
--binary-upgrade. I was surprised that it makes a big difference,
slowing it down by about 2 fold.

Cheers,

Jeff

On Fri, Nov 9, 2012 at 3:06 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Nov 8, 2012 at 08:59:21PM -0800, Jeff Janes wrote:

On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian <bruce@momjian.us> wrote:

I am actually now dumping git head/9.3, so I assume all the problems we
know about should be fixed.

Are sure the server you are dumping out of is head?

Well, I tested again with 9.2 dumping/loading 9.2 and the same for git
head, and got these results:

pg_dump restore
9.2 git 9.2 git

1 0.13 0.11 0.07 0.07
1000 4.37 3.98 4.32 5.28
2000 12.98 12.19 13.64 14.25
4000 47.85 50.14 61.31 70.97
8000 210.39 183.00 302.67 294.20
16000 901.53 769.83 1399.25 1359.09

For pg_dump, there are 4 possible combinations, not just two. you can
use 9.2's pg_dump to dump from a 9.2 server, use git's pg_dump to dump
from a 9.2 server, use git's pg_dump to dump from a git server, or use
9.2's pg_dump to dump from a git server (although that last one isn't
very relevant)

As you can see, there is very little difference between 9.2 and git
head, except maybe at the 16k level for pg_dump.

Is there some slowdown with a mismatched version dump/reload? I am
attaching my test script.

Sorry, from the script I can't really tell what versions are being
used for what.

Using head's pg_dump, but 9.2.1 server, it takes me 179.11 seconds to
dump 16,000 tables (schema only) like your example, and it is
definitely quadratic.

Are you using a SERIAL column for the tables. I am, and Peter
Eisentraut reported that was a big slowdown.

Yes, I'm using the same table definition as your example.

But using head's pg_dump do dump tables out of head's server, it only
took 24.95 seconds, and the quadratic term is not yet important,
things still look linear.

Again, using SERIAL?

Yep.

Is the next value, increment, etc. for a sequence stored in a catalog,
or are they stored in the 8kb file associated with each sequence? If

Each sequence is stored in its own 1-row 8k table:

test=> CREATE SEQUENCE seq;
CREATE SEQUENCE

test=> SELECT * FROM seq;
-[ RECORD 1 ]-+--------------------
sequence_name | seq
last_value | 1
start_value | 1
increment_by | 1
max_value | 9223372036854775807
min_value | 1
cache_value | 1
log_cnt | 0
is_cycled | f
is_called | f

they are stored in the file, than it is shame that pg_dump goes to the
effort of extracting that info if pg_upgrade is just going to
overwrite it anyway.

Actually, pg_upgrade needs pg_dump to restore all those sequence values.

I did an experiment where I had pg_dump just output dummy values
rather than hitting the database. Once pg_upgrade moves the relation
files over, the dummy values disappear and are set back to their
originals. So I think that pg_upgrade depends on pg_dump only in a
trivial way--they need to be there, but it doesn't matter what they
are.

Cheers,

Jeff

On Fri, Nov 9, 2012 at 08:20:59AM +0200, Ants Aasma wrote:

On Fri, Nov 9, 2012 at 7:53 AM, Ants Aasma <ants@cybertec.at> wrote:

I also took two profiles (attached). AtEOXact_RelationCache seems to
be the culprit for the quadratic growth.

One more thing that jumps out as quadratic from the profiles is
transfer_all_new_dbs from pg_upgrade (20% of total CPU time at 64k).
Searching for non-primary files loops over the whole file list for
each relation. This would be a lot faster if we would sort the file
list first and use binary search to find the related files.

I am confused why you see a loop. transfer_all_new_dbs() does a
merge-join of old/new database names, then calls gen_db_file_maps(),
which loops over the relations and calls create_rel_filename_map(),
which adds to the map via array indexing. I don't see any file loops
in there --- can you be more specific?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Fri, Nov 9, 2012 at 04:23:40PM -0800, Jeff Janes wrote:

On Fri, Nov 9, 2012 at 3:06 PM, Bruce Momjian <bruce@momjian.us> wrote:

On Thu, Nov 8, 2012 at 08:59:21PM -0800, Jeff Janes wrote:

On Thu, Nov 8, 2012 at 4:33 PM, Bruce Momjian <bruce@momjian.us> wrote:

I am actually now dumping git head/9.3, so I assume all the problems we
know about should be fixed.

Are sure the server you are dumping out of is head?

Well, I tested again with 9.2 dumping/loading 9.2 and the same for git
head, and got these results:

pg_dump restore
9.2 git 9.2 git

1 0.13 0.11 0.07 0.07
1000 4.37 3.98 4.32 5.28
2000 12.98 12.19 13.64 14.25
4000 47.85 50.14 61.31 70.97
8000 210.39 183.00 302.67 294.20
16000 901.53 769.83 1399.25 1359.09

For pg_dump, there are 4 possible combinations, not just two. you can
use 9.2's pg_dump to dump from a 9.2 server, use git's pg_dump to dump
from a 9.2 server, use git's pg_dump to dump from a git server, or use
9.2's pg_dump to dump from a git server (although that last one isn't
very relevant)

True, but I thought doing matching versions was a sufficient test.

Using head's pg_dump, but 9.2.1 server, it takes me 179.11 seconds to
dump 16,000 tables (schema only) like your example, and it is
definitely quadratic.

Are you using a SERIAL column for the tables. I am, and Peter
Eisentraut reported that was a big slowdown.

Yes, I'm using the same table definition as your example.

OK.

But using head's pg_dump do dump tables out of head's server, it only
took 24.95 seconds, and the quadratic term is not yet important,
things still look linear.

Again, using SERIAL?

Yep.

Odd why yours is so much after.

they are stored in the file, than it is shame that pg_dump goes to the
effort of extracting that info if pg_upgrade is just going to
overwrite it anyway.

Actually, pg_upgrade needs pg_dump to restore all those sequence values.

I did an experiment where I had pg_dump just output dummy values
rather than hitting the database. Once pg_upgrade moves the relation
files over, the dummy values disappear and are set back to their
originals. So I think that pg_upgrade depends on pg_dump only in a
trivial way--they need to be there, but it doesn't matter what they
are.

Oh, wow, I had not thought of that. Once we move the sequence files
into place from the old cluster, whatever was assigned to the sequence
counter by pg_dump restored is thrown away. Good point.

I am hesistant to add an optimization to pg_dump to fix this unless we
decide that pg_dump uses sequences in some non-optimal way that would
not warrant us improving general sequence creation performance.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Sat, Nov 10, 2012 at 7:10 PM, Bruce Momjian <bruce@momjian.us> wrote:

I am confused why you see a loop. transfer_all_new_dbs() does a
merge-join of old/new database names, then calls gen_db_file_maps(),
which loops over the relations and calls create_rel_filename_map(),
which adds to the map via array indexing. I don't see any file loops
in there --- can you be more specific?

Sorry, I was too tired when posting that. I actually meant
transfer_single_new_db(). More specifically the profile clearly showed
that most of the time was spent in the two loops starting on lines 193
and 228.

Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de

On Fri, Nov 9, 2012 at 04:23:40PM -0800, Jeff Janes wrote:

Actually, pg_upgrade needs pg_dump to restore all those sequence values.

I did an experiment where I had pg_dump just output dummy values
rather than hitting the database. Once pg_upgrade moves the relation
files over, the dummy values disappear and are set back to their
originals. So I think that pg_upgrade depends on pg_dump only in a
trivial way--they need to be there, but it doesn't matter what they
are.

FYI, thanks everyone for testing this.  I will keep going on my tests
--- seems I have even more things to try in my benchmarks.  I will
publish my results soon.

In general, I think we are getting some complaints about dump/restore
performance with a large number of tables, irregardless of pg_upgrade,
so it seems worthwhile to try to find the cause.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Sat, Nov 10, 2012 at 07:17:34PM +0200, Ants Aasma wrote:

On Sat, Nov 10, 2012 at 7:10 PM, Bruce Momjian <bruce@momjian.us> wrote:

I am confused why you see a loop. transfer_all_new_dbs() does a
merge-join of old/new database names, then calls gen_db_file_maps(),
which loops over the relations and calls create_rel_filename_map(),
which adds to the map via array indexing. I don't see any file loops
in there --- can you be more specific?

Sorry, I was too tired when posting that. I actually meant
transfer_single_new_db(). More specifically the profile clearly showed
that most of the time was spent in the two loops starting on lines 193
and 228.

Wow, you are right on target. I was so focused on making logical
lookups linear that I did not consider file system vm/fsm and file
extension lookups. Let me think a little and I will report back.
Thanks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +