From: Jerry Sievers <gsievers19@comcast.net>
To: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Cc: jesper.pedersen@redhat.com,
  "pgsql-hackers\@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>,
  Bruce Momjian <bruce@momjian.us>
Subject: Re: pg_upgrade: Pass -j down to vacuumdb
References: <11b6b108-6ac0-79a6-785a-f13dfe60ab92@redhat.com>
	<ba90295e-9734-07bc-3453-bfa878948478@2ndquadrant.com>
	<2dc5eaeb-e60d-2a16-696e-7b4d3d3548cb@redhat.com>
	<59f9323a-fee8-88f6-16ab-2319149b00be@2ndquadrant.com>
Date: Thu, 03 Jan 2019 12:55:34 -0600
In-Reply-To: <59f9323a-fee8-88f6-16ab-2319149b00be@2ndquadrant.com> (Peter
	Eisentraut's message of "Thu, 3 Jan 2019 13:03:21 +0100")
Message-ID: <87ftu9r2op.fsf@jsievers.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/25.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:

> On 02/01/2019 20:47, Jesper Pedersen wrote:
>
>> Well, that really depends. The user passed -j to pg_upgrade in order for 
>> the upgrade to happen faster, so maybe they would expect, as I would, 
>> that the ANALYZE phase would happen in parallel too.
>
> pg_upgrade -j reduces the *downtime* caused by pg_upgrade during the
> upgrade process.  Also, during said downtime, nothing else is happening,
> so you can use all the resources of the machine.
>
> Once the system is back up, you don't necessarily want to use all the
> resources.  The analyze script is specifically written to run while
> production traffic is active.  If you just want to run the analyze as
> fast as possible, you can just run vacuumdb -j directly, without using
> the script.

Peter, I'm skeptical here.

I might permit a connection to a just pg_upgraded DB prior to any
analyze being known finished only for the most trivial case.  At my site
however, *trivial* systems are a small minority.

In fact, our automated upgrade workflow uses our home-built parallel
analyzer which predates vacuumdb -j.  Apps are not allowed into the DB
until a fast 1st pass has been done.

We run it in 2 phases...

$all preceeding upgrade steps w/system locked out
analyze-lite (reduced stats target)
open DB for application traffic
analyze-full

Of course we are increasing downtime by disallowing app traffic till
finish of analyze-lite however the assumption is that many queries would
be too slow to attempt without full analyzer coverage, albiet at a
reduced stats target.

IMO this is certainly a case of no 1-size-fits-all solution so perhaps a
--analyze-jobs option :-)

FWIW

Thanks

> Moreover, it's not clear that pg_upgrade and vacuumdb are bound the same
> way, so it's not a given that the same -j number should be used.
>
> Perhaps more documentation would be useful here.

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net