Date: Tue, 23 Jul 2013 19:47:06 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Jerry Sievers <gsievers19@comcast.net>
Cc: Kevin Grittner <kgrittn@ymail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
Message-ID: <20130723234706.GA31462@momjian.us>
References: <874nc4yi2b.fsf@comcast.net>
 <1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
 <87r4f65sdm.fsf@comcast.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <87r4f65sdm.fsf@comcast.net>
User-Agent: Mutt/1.5.20 (2009-06-14)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

On Wed, Jul 10, 2013 at 10:47:33AM -0500, Jerry Sievers wrote:
> > 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.

9.2 already creates an incremental statistics script called
analyze_new_cluster.sh:

        Make pg_upgrade create a script to incrementally generate more
        accurate optimizer statistics (Bruce Momjian)

        This reduces the time needed to generate minimal cluster statistics
        after an upgrade.

It runs with a 1/10/default statistics target.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


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