pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (8+ messages)  latest in thread

Message-ID: <60oekwpsbw.fsf@dev6.int.libertyrms.info>
Permalink:  ../60oekwpsbw.fsf@dev6.int.libertyrms.info/
Also on:    postgresql.org/message-id/60oekwpsbw.fsf@dev6.int.libertyrms.info

 · 

reply

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