From: Bruce Momjian <bruce@momjian.us>
To: Jerry Sievers <gsievers19@comcast.net>
Cc: Kevin Grittner <kgrittn@ymail.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
Date: Tue, 23 Jul 2013 19:47:06 -0400
Message-ID: <20130723234706.GA31462@momjian.us> (raw)
In-Reply-To: <87r4f65sdm.fsf@comcast.net>
References: <874nc4yi2b.fsf@comcast.net>
<1373465348.51692.YahooMailNeo@web162906.mail.bf1.yahoo.com>
<87r4f65sdm.fsf@comcast.net>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
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
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: bruce@momjian.us, gsievers19@comcast.net, kgrittn@ymail.com
Subject: Re: Dump/Reload pg_statistic to cut time from pg_upgrade?
In-Reply-To: <20130723234706.GA31462@momjian.us>
* 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