Thread: pg_upgrade performance with 150k tables

pg_upgrade performance with 150k tables

From
Bruce Momjian
Date:
I received a private email report yesterday from someone using
pg_upgrade with PG 9.0 who found it took five hours for pg_upgrade to
upgrade a database with 150k tables.  Yes, that is a lot of tables, but
pg_upgrade should be able to do better than that.

I have modified pg_upgrade in git master to cache scandir() and reduce
array lookups and the time is down to 38 minutes.  (He prototyped a hash
implementation that was 30 minutes but it was too much code for my
taste.)

I don't think this is reasonable to backpatch.   If anyone else sees
cases for pg_upgrade improvement, please let me know.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


pg_upgrade patch application process, and move to /bin?

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> I received a private email report yesterday from someone using
> pg_upgrade with PG 9.0 who found it took five hours for pg_upgrade to
> upgrade a database with 150k tables.  Yes, that is a lot of tables, but
> pg_upgrade should be able to do better than that.
> 
> I have modified pg_upgrade in git master to cache scandir() and reduce
> array lookups and the time is down to 38 minutes.  (He prototyped a hash
> implementation that was 30 minutes but it was too much code for my
> taste.)
> 
> I don't think this is reasonable to backpatch.   If anyone else sees
> cases for pg_upgrade improvement, please let me know.

One more question --- should I be sending pg_upgrade patches to the list
for approval?  The restructuring patch was large and didn't seem
necessary to post, and the speedups were tested by the bug reporter, so
I figured those were OK to apply.

Oh, and do we want to move pg_upgrade into /bin for 9.1?  There was
discussion about that six months ago.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +


Re: pg_upgrade patch application process, and move to /bin?

From
Robert Haas
Date:
On Wed, Oct 20, 2010 at 3:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> One more question --- should I be sending pg_upgrade patches to the list
> for approval?  The restructuring patch was large and didn't seem
> necessary to post, and the speedups were tested by the bug reporter, so
> I figured those were OK to apply.

I think it would be good to do that.  At least give people a chance to
comment, if they care.

> Oh, and do we want to move pg_upgrade into /bin for 9.1?  There was
> discussion about that six months ago.

I would be inclined to leave it in contrib for a few more releases.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


Re: pg_upgrade patch application process, and move to /bin?

From
Magnus Hagander
Date:
On Wed, Oct 20, 2010 at 21:28, Robert Haas <robertmhaas@gmail.com> wrote:
> On Wed, Oct 20, 2010 at 3:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
>> One more question --- should I be sending pg_upgrade patches to the list
>> for approval?  The restructuring patch was large and didn't seem
>> necessary to post, and the speedups were tested by the bug reporter, so
>> I figured those were OK to apply.
>
> I think it would be good to do that.  At least give people a chance to
> comment, if they care.

+1. It's also a good way for people to get a bit more involved in the code.



--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


Re: pg_upgrade patch application process, and move to /bin?

From
Bruce Momjian
Date:
Magnus Hagander wrote:
> On Wed, Oct 20, 2010 at 21:28, Robert Haas <robertmhaas@gmail.com> wrote:
> > On Wed, Oct 20, 2010 at 3:01 PM, Bruce Momjian <bruce@momjian.us> wrote:
> >> One more question --- should I be sending pg_upgrade patches to the list
> >> for approval? ?The restructuring patch was large and didn't seem
> >> necessary to post, and the speedups were tested by the bug reporter, so
> >> I figured those were OK to apply.
> >
> > I think it would be good to do that. ?At least give people a chance to
> > comment, if they care.
> 
> +1. It's also a good way for people to get a bit more involved in the code.

OK.

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + It's impossible for everything to be true. +