DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=SgoqTzqlFiS27RGU8N/TWdxP3Yu6JrnZIEdXot6+VxsCfXeD3243ZnzGyGAJMdyOf7cXjNdANoae5yKfQfmQqVEOg++FiALkcluZMo7IgEr8ymCMTr+F6JFt8c8K1ws7i0zW5uNRh5tgijIcCSnVI3KkQZJVk5sJkJ4E1lEjS2I=
 ;
References: <874nc4yi2b.fsf@comcast.net>
Message-ID: <1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
Date: Wed, 10 Jul 2013 07:09:08 -0700 (PDT)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
To: Jerry Sievers <gsievers19@comcast.net>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
In-Reply-To: <874nc4yi2b.fsf@comcast.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

Jerry Sievers <gsievers19@comcast.net> wrote:

> Planning to pg_upgrade some large (3TB) clusters using hard link
> method.=A0 Run time for the upgrade itself takes around 5 minutes.
> Nice!!=A0 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.=A0 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?=A0 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.=A0 I forget the
particular value we used -- it may have been 3.=A0 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.=A0 The database ANALYZE with
the normal default target was done while normal production hit the
database, without too much of a performance hit.=A0 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


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