From: Chris Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: vacuum pg_attribute causes high load
Date: Fri, 27 Aug 2004 10:29:39 -0400
Message-ID: <60oekwpsbw.fsf@dev6.int.libertyrms.info> (raw)
References: <5707.1093577361@sss.pgh.pa.us>
<20040827130541.14925.qmail@web61307.mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: cbbrowne@acm.org
Subject: Re: vacuum pg_attribute causes high load
In-Reply-To: <60oekwpsbw.fsf@dev6.int.libertyrms.info>
References: <5707.1093577361@sss.pgh.pa.us> <20040827130541.14925.qmail@web61307.mail.yahoo.com> <60oekwpsbw.fsf@dev6.int.libertyrms.info>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox