From: Kevin Grittner <kgrittn@ymail.com>
To: Jerry Sievers <gsievers19@comcast.net>
To: 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 07:09:08 -0700 (PDT)
Message-ID: <1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com> (raw)
In-Reply-To: <874nc4yi2b.fsf@comcast.net>
References: <874nc4yi2b.fsf@comcast.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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.
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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: kgrittn@ymail.com, gsievers19@comcast.net
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
In-Reply-To: <1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
* 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