From: | Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> |
---|---|
To: | Thomas Markus <t(dot)markus(at)proventis(dot)net> |
Cc: | pgsql-admin(at)postgresql(dot)org |
Subject: | Re: problems with access into system catalogs |
Date: | 2012-07-26 08:25:22 |
Message-ID: | 5010FEF2.3050901@ringerc.id.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bèrto ëd Sèra | 2012-07-26 08:26:05 | Re: check_postgresql.pl for zabbix |
Previous Message | Rajat Sangal | 2012-07-26 07:46:42 | "Data import from Mysql to MS Sql Server" |