Re: Auto Vacuum of pg_catalog tables causes huge delay in opening new connections

Hi Tom,
On the test box this seems to have completely resolved our problem.

I'll be scheduling an upgrade on the production cluster to verify it.

Thanks

On Thu, Sep 13, 2012 at 11:08 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Mike Roest <mike(dot)roest(at)replicon(dot)com> writes:
> > We have a interesting thing happening on one of our DB's that when
> > autovacuum runs against the pg_catalog tables (or we run a manual vacuum)
> > we get a large delay in opening new connections.
>
> I think you're hitting the problem that was fixed here:
>
> Author: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Branch: master Release: REL9_2_BR [532fe28da] 2012-05-26 19:09:52 -0400
> Branch: REL9_1_STABLE Release: REL9_1_4 [6c1bf45ea] 2012-05-26 19:09:59
> -0400
> Branch: REL9_0_STABLE Release: REL9_0_8 [2ce097e6e] 2012-05-26 19:10:05
> -0400
> Branch: REL8_4_STABLE Release: REL8_4_12 [35cc2be6f] 2012-05-26 19:10:13
> -0400
> Branch: REL8_3_STABLE Release: REL8_3_19 [422022b12] 2012-05-26 19:10:19
> -0400
>
> Prevent synchronized scanning when systable_beginscan chooses a
> heapscan.
>
> The only interesting-for-performance case wherein we force heapscan
> here
> is when we're rebuilding the relcache init file, and the only such case
> that is likely to be examining a catalog big enough to be syncscanned
> is
> RelationBuildTupleDesc. But the early-exit optimization in that code
> gets
> broken if we start the scan at a random place within the catalog, so
> that
> allowing syncscan is actually a big deoptimization if pg_attribute is
> large
> (at least for the normal case where the rows for core system catalogs
> have
> never been changed since initdb). Hence, prevent syncscan here. Per
> my
> testing pursuant to complaints from Jeff Frost and Greg Sabino Mullane,
> though neither of them seem to have actually hit this specific problem.
>
> Back-patch to 8.3, where syncscan was introduced.
>
> > For our setup we're running postgres 9.1.1 compiled from source on Centos
> > 5.8 x64 (Dual Xeon x5650 with 32 gigs of ram)
>
> Try updating ...
>
> regards, tom lane
>