pg_upgrade on high number tables database issues

On Mon, Mar 10, 2014 at 6:58 AM, Pavel Stehule <pavel.stehule@gmail.com>wrote:

Hello

I had to migrate our databases from 9.1 to 9.2. We have high number of
databases per cluster (more than 1000) and high number of tables (indexes)
per database (sometimes more than 10K, exceptionally more than 100K).

I seen two problems:

a) too long files pg_upgrade_dump_db.sql, pg_upgrade_dump_all.sql in
postgres HOME directory. Is not possible to change a directory for these
files.

Those files should go into whatever your current directory is when you
execute pg_upgrade. Why not just cd into whatever directory you want them
to be in?

b) very slow first stage of upgrade - schema export is very slow without
high IO or CPU utilization.

Just the pg_upgrade executable has low IO and CPU utilization, or the
entire server does?

There were several bottlenecks in this area removed in 9.2 and 9.3.
Unfortunately the worst of those bottlenecks were in the server, so they
depend on what database you are upgrading from, and so won't help you much
upgrading from 9.1.

Cheers,

Jeff

On Mon, Mar 10, 2014 at 09:54:36AM -0700, Jeff Janes wrote:

On Mon, Mar 10, 2014 at 6:58 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hello

I had to migrate our databases from 9.1 to 9.2. We have high number of
databases per cluster (more than 1000) and high number of tables (indexes)
per database (sometimes more than 10K, exceptionally more than 100K).

I seen two problems:

a) too long files pg_upgrade_dump_db.sql, pg_upgrade_dump_all.sql in
postgres HOME directory. Is not possible to change a directory for these
files.

Those files should go into whatever your current directory is when you execute
pg_upgrade. Why not just cd into whatever directory you want them to be in?

b) very slow first stage of upgrade - schema export is very slow without
high IO or CPU utilization.

Just the pg_upgrade executable has low IO and CPU utilization, or the entire
server does?

There were several bottlenecks in this area removed in 9.2 and 9.3.
Unfortunately the worst of those bottlenecks were in the server, so they depend
on what database you are upgrading from, and so won't help you much upgrading
from 9.1.

Yes, I assume 9.3 will be much better, though Jeff is right that if it
is pg_dump locking that is hurting you, you might not see a win even in
9.3.

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

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

There were several bottlenecks in this area removed in 9.2 and 9.3.
Unfortunately the worst of those bottlenecks were in the server, so they

depend

on what database you are upgrading from, and so won't help you much

upgrading

from 9.1.

Yes, I assume 9.3 will be much better, though Jeff is right that if it
is pg_dump locking that is hurting you, you might not see a win even in
9.3.

I'll see it next year when we plan to migrate to 9.4

I though so some form of "superlock" can be interesting, because nobody can
work with database when it is upgraded.

Regards

Pavel

On Mon, Mar 10, 2014 at 07:40:42PM +0100, Pavel Stehule wrote:

There were several bottlenecks in this area removed in 9.2 and 9.3.
Unfortunately the worst of those bottlenecks were in the server, so they

depend

on what database you are upgrading from, and so won't help you much

upgrading

from 9.1.

Yes, I assume 9.3 will be much better, though Jeff is right that if it
is pg_dump locking that is hurting you, you �might not see a win even in
9.3.

I'll see it next year when we plan to migrate to 9.4

I though so some form of "superlock" can be interesting, because nobody can
work with database when it is upgraded.

Remember pg_upgrade is using pg_dump, which then connecting to a
backend, so passing that super-lock mode there is not ideal. The fixes
in 9.3 improve locking in all user cases, not just upgrades.

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

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2014-03-10 20:11 GMT+01:00 Bruce Momjian <bruce@momjian.us>:

On Mon, Mar 10, 2014 at 07:40:42PM +0100, Pavel Stehule wrote:

There were several bottlenecks in this area removed in 9.2 and 9.3.
Unfortunately the worst of those bottlenecks were in the server,

so they

depend

on what database you are upgrading from, and so won't help you much

upgrading

from 9.1.

Yes, I assume 9.3 will be much better, though Jeff is right that if

it

is pg_dump locking that is hurting you, you might not see a win

even in

9.3.

I'll see it next year when we plan to migrate to 9.4

I though so some form of "superlock" can be interesting, because nobody

can

work with database when it is upgraded.

Remember pg_upgrade is using pg_dump, which then connecting to a
backend, so passing that super-lock mode there is not ideal. The fixes
in 9.3 improve locking in all user cases, not just upgrades.

nice

Thank you

Pavel

On Mon, Mar 10, 2014 at 08:12:20PM +0100, Pavel Stehule wrote:

Remember pg_upgrade is using pg_dump, which then connecting to a
backend, so passing that super-lock mode there is not ideal. �The fixes
in 9.3 improve locking in all user cases, not just upgrades.

nice

FYI, the 9.3.0 release notes have all the details on pg_upgrade
improvements. This is the pg_dump fix:

Add per-resource-owner lock caches (Jeff Janes)

This speeds up lock bookkeeping at statement completion in
mlti-statement transactions that hold many locks; it is
particularly useful for <application>pg_dump</>.

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

+ Everyone has their own god. +

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers