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

Re: vacuum pg_attribute causes high load

From: kris pal <kristhegambler(at)yahoo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: vacuum pg_attribute causes high load
Date: 2004-08-27 13:05:41
Message-ID: 20040827130541.14925.qmail@web61307.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-admin
 
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(at)sss(dot)pgh(dot)pa(dot)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 

In response to

Responses

pgsql-admin by date

Next:From: G u i d o B a r o s i oDate: 2004-08-27 13:13:43
Subject: REINDEX process
Previous:From: Christian HacheDate: 2004-08-27 12:05:19
Subject: Unir diferentes BD en Postgres

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