Message-ID: <5010FEF2.3050901@ringerc.id.au>
Date: Thu, 26 Jul 2012 16:25:22 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: Thomas Markus <t.markus@proventis.net>
CC: pgsql-admin@postgresql.org
Subject: Re: problems with access into system catalogs
References: <5010F555.9020209@proventis.net>
In-Reply-To: <5010F555.9020209@proventis.net>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

First, thank-you for an excellent complete question with versions, 
EXPLAIN ANALYZE, and exact messages.

My reply is interleaved below.

On 07/26/2012 03:44 PM, Thomas Markus wrote:
> I have 2 systems with CentOS 5.5 (2.6.18) x86_64, postgresql-9.0.6 64bit
>
> both systems contains the same content. But one system make troubles. 
> some system tables (eg pg_catalog.pg_class or pg_attribute) contain 
> much dead rows and all simple query take much time on one system. the 
> other one is fast.

[snip]

> other:
> INFO:  "pg_class": found 0 removable, 1547667 nonremovable row 
> versions in 31587 out of 31587 pages
> DETAIL:  1545530 dead row versions cannot be removed yet.
>
> a "select count(*) from pg_catalog.pg_class" returns only 2137
>

OK, so you have lots of bloat in the system catalogs. A few things to 
check for:

- Do you have any uncommitted two phase transactions?  Run:
   SELECT * from pg_prepared_xacts ;

- Do you have any long-lived 'IDLE IN TRANSACTION' connections ? Try:
   SELECT * FROM pg_stat_activity WHERE current_query = '<IDLE> in 
transaction' AND xact_start > current_timestamp - '1 minute'::interval;

Either of those can prevent vacuum from cleaning things up.

Do you very frequently create and drop tables, indexes, etc? Say, using 
a database unit testing framework?

--
Craig Ringer