Re: vacuum pg_attribute causes high load

kristhegambler@yahoo.com (kris pal) writes:
> 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.

No, you need to let the vacuum finish.

If you stop it early, it'll just increase system load and accomplish
_nothing_.

> 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 ?

In order to reindex system tables, you must shut the database down and
go in in single user mode.

<http://www.postgresql.org/docs/current/static/app-postgres.html>

That's a reasonable time to do a VACUUM FULL on pg_attribute; you can
be 100% certain that it won't interfere with anything else.

> 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?

At the end of a VACUUM on everything, FSM information is reported, thus:

INFO:  free space map: 605 relations, 2039 pages stored; 10896 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 30000 pages = 237 kB shared memory.
VACUUM

The "10896 total pages needed" indicates how much the vacuum needed;
my database evidently has things set reasonably appropriately.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://cbbrowne.com/info/oses.html
"I don't do drugs anymore 'cause I  find I get the same effect just by
standing up really fast." -- Jonathan Katz