From: Jerry Sievers <gsievers19@comcast.net>
To: Kevin Grittner <kgrittn@ymail.com>
Cc: Jerry Sievers <gsievers19@comcast.net>
Cc: pgsql-admin\@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
Date: Wed, 10 Jul 2013 10:47:33 -0500
Message-ID: <87r4f65sdm.fsf@comcast.net> (raw)
In-Reply-To: <1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
References: <874nc4yi2b.fsf@comcast.net>
<1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gsievers19@comcast.net, kgrittn@ymail.com
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
In-Reply-To: <87r4f65sdm.fsf@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox