Thread: pg_upgrade on high number tables database issues

pg_upgrade on high number tables database issues

From
Pavel Stehule
Date:
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.

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

Regards

Pavel

Re: pg_upgrade on high number tables database issues

From
Jeff Janes
Date:
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

Re: pg_upgrade on high number tables database issues

From
Bruce Momjian
Date:
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. +



Re: pg_upgrade on high number tables database issues

From
Pavel Stehule
Date:

>
> 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

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

  + Everyone has their own god. +

Re: pg_upgrade on high number tables database issues

From
Bruce Momjian
Date:
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. +



Re: pg_upgrade on high number tables database issues

From
Pavel Stehule
Date:



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
 
--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +

Re: pg_upgrade on high number tables database issues

From
Bruce Momjian
Date:
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 inmlti-statement transactions that hold many locks; it
isparticularlyuseful for <application>pg_dump</>.
 

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + Everyone has their own god. +