Re: vacuum pg_attribute causes high load

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