Vacuum problem

From: S H <msq001(at)live(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Vacuum problem
Date: 2013-05-13 14:05:20
Message-ID: BAY155-W3134B1F6FA6993DDF1BC11FDA00@phx.gbl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,
I have my database in which i am executing vacuuming running manually in one hour.
In my production database sometime when vacuuming is running it is taking long time in opening connection.
My current version is version 8.1. Is there any known issue about open connection problem with vacuuming.
I found something but i am not sure if it is applicable to V8.1 too? Vacuum of pg_catalog tables causes huge delay in opening new connections.
http://www.postgresql.org/message-id/14249.1347556133@sss.pgh.pa.us
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.

Regards,SH

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2013-05-13 14:25:30 Re: Vacuum problem
Previous Message Bruce Momjian 2013-05-13 13:59:32 Re: Re: [GENERAL] pg_upgrade fails, "mismatch of relation OID" - 9.1.9 to 9.2.4