To: Kevin Grittner <kgrittn@ymail.com>
Cc: Jerry Sievers <gsievers19@comcast.net>,
 "pgsql-admin\@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
References: <874nc4yi2b.fsf@comcast.net>
 <1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
From: Jerry Sievers <gsievers19@comcast.net>
Date: Wed, 10 Jul 2013 10:47:33 -0500
In-Reply-To: <1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
 (Kevin Grittner's message of "Wed\, 10 Jul 2013 07\:09\:08 -0700 \(PDT\)")
Message-ID: <87r4f65sdm.fsf@comcast.net>
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.2 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Kevin Grittner <kgrittn@ymail.com> writes:

> Jerry Sievers <gsievers19@comcast.net> wrote:
>
>> Planning to pg_upgrade some large (3TB) clusters using hard link
>> method.  Run time for the upgrade itself takes around 5 minutes.
>> Nice!!  Origin version 8.4 and destination version 9.1.
>>
>> Unfortunately the post-upgrade analyze of the entire cluster is going
>> to take a minimum of 1.5 hours running several threads to analyze all
>> tables.  This was measured in an R&D environment.
>>
>> Got to thinking, what if we make a SAN snapshot a few hours prior to
>> upgrade time, upgrade that... then analyze it and then dump the stats
>> table?
>
>> Has anyone else attempted anything similar?  Any feedback is
>> appreciated.
>
> I certainly understand the motivation, and it may be a good option
> if you test carefully beforehand.
>
> What I have done in a similar situation, to minimize down time, is
> to run a database ANALYZE with a very small target.  I forget the
> particular value we used -- it may have been 3.  Then ran an
> ANALYZE with the normal default target on a few key tables
> (identified in testing to be the ones eating most of the scan time
> with no statistics), and let users in.  The database ANALYZE with
> the normal default target was done while normal production hit the
> database, without too much of a performance hit.  With this
> technique we were able to let users in with near-normal performance
> with 10 or 15 minutes of down time rather than hours.

Thanks Kevin!  In fact, I've conceived of this solution route already
and may have to resort to it.  We do run with
default_statistics_target set fairly high at 1000 (legacy setting
here) without my knowing for certain that it's required across the
board (and most likely is not).

Curious though if it's known that the pg_statistic table can't be
reloded from it's own pg_dump due to that error that I highlighted in
the original post.

Agree that being able to manually load pg_statistic is of questionable
usefulness though perhaps viable under the right circumstances.

> --
> Kevin Grittner
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

-- 
Jerry Sievers
e: jerry.sievers@comcast.net
p: 312.241.7800


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