pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: kris pal <kristhegambler@yahoo.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: vacuum pg_attribute causes high load
Date: Fri, 27 Aug 2004 06:05:41 -0700 (PDT)
Message-ID: <20040827130541.14925.qmail@web61307.mail.yahoo.com> (raw)
In-Reply-To: <5707.1093577361@sss.pgh.pa.us>

 
The server is running Postgres 7.4.1 on Linux.
I tried to do a "vacuum pg_attribute" but its taking a while (I guess because of the size- abt 2GB) and the load on the server is going very high. Because of this the database server is not responding. So I killed the psql session. 
 
1) Can you tell me if there is any other work around to vacuum the pg_attribute system table ? Like doing it in parts so that it won't cause a high load. 
 
2)
 
Also I want to REINDEX by doing:
----------------------------------------------------------------------------
drop index pg_attribute_relid_attnam_index;
vacuum pg_attribute;
create the index again;
vacuum analyze pg_attribute;
----------------------------------------------------------------------------
 
But I get the : "ERROR:  permission denied: "pg_attribute_relid_attnam_index" is a system catalog"
 
* So how can I REINDEX this one ?
 

3) The config file has: 

#max_fsm_pages = 20000          # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000       # min 100, ~50 bytes each
 
So I guess Postgres is using the defaults. Where can I find the current values or default values. Do you know if I can find some reading material abt FSM settings and default values?
 
thanks,
Kris Pal.
 


Tom Lane <tgl@sss.pgh.pa.us> wrote:
kris pal writes:
> Actually I just noticed something. Previously for some reason, I
> thought the data directory corresponded to 'regression'. But I double
> checked it now with oid2name and it corresponds to our main database
> say "dds".

Ah, that makes more sense.

> But the issue still remains. In the sense that pg_attribute etc are
> still so huge and I have no idea why.

Probably because you've created and deleted a lot of tables (does your
app use temp tables a lot?). If you aren't good about vacuuming the
system catalogs then you're going to get table bloat. If you're using
something pre-7.4 then you may get index bloat even if you *are*
vacuuming regularly :-(.

My suggestions are to update to 7.4, if you're not there already
(if you are, you may need to do VACUUM FULL and REINDEX to get rid
of the bloat); then adopt a stricter regime of routine vacuuming.
And check your FSM settings.

regards, tom lane

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

view thread (8+ messages)  latest in thread

Message-ID: <20040827130541.14925.qmail@web61307.mail.yahoo.com>
Permalink:  ../20040827130541.14925.qmail@web61307.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040827130541.14925.qmail@web61307.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: kristhegambler@yahoo.com, tgl@sss.pgh.pa.us
  Subject: Re: vacuum pg_attribute causes high load
  In-Reply-To: <20040827130541.14925.qmail@web61307.mail.yahoo.com>
  References: <5707.1093577361@sss.pgh.pa.us> <20040827130541.14925.qmail@web61307.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox