Skip site navigation (1) Skip section navigation (2)

Re: cleanup on pg_ system tables?

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: mark <dvlhntr(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: cleanup on pg_ system tables?
Date: 2010-09-20 19:24:22
Message-ID: AANLkTintp56aWA5D+ujxj0GRu5afvW_j=8f1LxTnNnnj@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Sep 20, 2010 at 1:25 PM, mark <dvlhntr(at)gmail(dot)com> wrote:
> Hi All,
>
> (pg 8.3.7 on RHEL  2.6.18-92.el5 )
>
> I ran the query below (copied from
> http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html ) on a
> production DB we have and I am looking at some pretty nasty looking
> numbers for tables in the pg_catalog schema. I have tried a reindex
> and vaccum but neither seem to be clearing these out, tried a cluster
> and it won't let me.
>
> I am viewing the problem wrong? is there anything I can do while the
> DB is online ? do I need to clean up other things first ?

You sure you tried VACUUM FULL, not just VACUUM? I've been in the same
boat on 8.3, actually, see:

  http://archives.postgresql.org/pgsql-performance/2010-04/msg00204.php

I migrated the server mentioned in that thread to 8.4, so at least I
don't have to deal with the max_fsm_pages problem; you might want to
double check your postmaster logfile to make sure you don't see a
bunch of warnings about max_fsm_pages.

I put in a twice-hourly cron job which runs a VACUUM ANALYZE on the
pg_catalog tables I'd been having trouble with, which has helped keep
pg_catalog bloat down. My pg_catalog tables are still somewhat bloated
(11 GB for pg_attribute), but at least they've been at a steady size
for the past few months without needing a VACUUM FULL.

Josh

In response to

pgsql-performance by date

Next:From: Joe MillerDate: 2010-09-20 20:38:37
Subject: Auto ANALYZE criteria
Previous:From: Josh BerkusDate: 2010-09-20 18:47:56
Subject: Need PostgreSQL data warehousing user, on the record

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group