X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4434A5E46FB for ; Fri, 27 Aug 2004 10:05:46 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 94996-06 for ; Fri, 27 Aug 2004 13:05:42 +0000 (GMT) Received: from web61307.mail.yahoo.com (web61307.mail.yahoo.com [216.155.196.150]) by svr1.postgresql.org (Postfix) with SMTP id DF6125E46F2 for ; Fri, 27 Aug 2004 10:05:36 -0300 (ADT) Message-ID: <20040827130541.14925.qmail@web61307.mail.yahoo.com> Received: from [68.227.223.161] by web61307.mail.yahoo.com via HTTP; Fri, 27 Aug 2004 06:05:41 PDT Date: Fri, 27 Aug 2004 06:05:41 -0700 (PDT) From: kris pal Subject: Re: vacuum pg_attribute causes high load To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <5707.1093577361@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1486297711-1093611941=:12274" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/277 X-Sequence-Number: 14672 --0-1486297711-1093611941=:12274 Content-Type: text/plain; charset=us-ascii 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 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 --0-1486297711-1093611941=:12274 Content-Type: text/html; charset=us-ascii
 
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 an d 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 --0-1486297711-1093611941=:12274--