Re: Speeding up pg_upgrade

On Thu, Dec 7, 2017 at 10:37:30AM -0500, Stephen Frost wrote:
> Alexander,
>
> * Alexander Kukushkin (cyberdemn(at)gmail(dot)com) wrote:
> > Couple of months ago we at Zalando upgraded a few databases of different
> > sizes to 9.6.
>
> Thanks for sharing your experience!
>
> > During preparations to the I've found 2.5 pain-points:
> >
> > 1. We are using schema-based api deployment. Basically ~every week we
> > create a new schema in the database and hundreds of stored procedures in it.
> > Off course we remove old API schemas and trying not to keep more than
> > last 10. Before the upgrade we basically dropped all API schemas except the
> > one used in production.
> > And even in this case dump-restore phase was taking much more time than
> > relinking of datafiles.
> > Unfortunately I don't have any numbers right now, but usually run of
> > pg_upgrade was taking about 30-35 seconds, and about 2/3 of the time was
> > spend in dump-restore.
>
> Ok, so eliminating 2/3 of the time would mean bringing it down to more
> like 10 seconds. That certainly seems worthwhile to me. With the
> linking time being much less than the dump/restore, we could at least
> consider moving forward with Bruce's original idea where we do the
> dump/restore while the system is online but then the linking with it
> offline and get a serious performance boost out of it. That also avoids
> the issue with new files showing up while the system is running that I
> brought up when we were talking about having the linking done with the
> system online.
>
> > 2 ANALYZE phase is a pain. I think everybody agrees with it.
> >
> > 2.5 Usually ANALYZE stage 1 completes quite fast and performance becomes
> > reasonable, except one case: some of the columns might have non default
> > statistics target.
>
> Ok, if the stage-1 is very fast and performance is reasonable enough
> after that then perhaps it's not so bad to keep it as-is for now and
> focus on the dump/restore time. That said, we should certainly also
> work on improving this too.

I think the big problem with two-stage pg_upgrade is that the user steps
are more complex, so what percentage of users are going use the
two-stage method. The bad news is that only a small percentage of users
who will benefit from it will use it, and some who will not benefit it
will use it. Also, this is going to require significant server changes,
which have to be maintained.

I think we need some statistics on how many users are going to benefit
from this, and how are users suppose to figure out if they will benefit
from it?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +