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: (view raw or whole 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. 
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?
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 

In response to


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-2015 The PostgreSQL Global Development Group