pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-admin@postgresql.org
Subject: Re: vacuum pg_attribute causes high load
Date: Fri, 27 Aug 2004 17:24:54 -0400
Message-ID: <m34qmoi89l.fsf@wolfe.cbbrowne.com> (raw)
References: <60oekwpsbw.fsf@dev6.int.libertyrms.info>
	<20040827182458.78631.qmail@web61304.mail.yahoo.com>

After a long battle with technology, kristhegambler@yahoo.com (kris pal), an earthling, wrote:
>  
>
>>>>>>>>>>>>>>>>>>>>>>>>>>
>>> 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.
>
> Chris Browne <cbbrowne@acm.org> wrote:
>>No, you need to let the vacuum finish.
>>If you stop it early, it'll just increase system load and accomplish _nothing_.
>
>>>>>>>>>>>>>>>>>>>>>>>>>>

> But while doing vacuum the load is getting so high that the system
> is almost freezing.

Right.  You said that the table was consuming 2GB of space.  It has to
read in that 2GB, and discard the bits that aren't necessary.

> Does the performance of 'Vacuum' command depend on other system/
> database parameters?  The 'vacuum pg_attribute" command never
> completes, its causing high load on the server. I had to wait for 15
> mins and kill it.  Because the system load was touching 10, slowing
> down the system therefore not letting anyone access the server.

What's happening, is, at root, that the vacuum process loads all the
data in the table (2GB, you indicated) into memory, looks at it, and
writes it back out.

That presumably leads to about 2GB of reads and 2GB of writes, which
takes a while.

If you want this to go as quickly as possible, shut the database down
and start the postmaster in single user mode so that there's no other
activity competing for the system's resources.

> Can we vacuum the "pg_attribiute" in parts? So that 'vacuum' will
> complete faster and there won't be load issues.  Thanks Tom Lane and
> Chris Browne for your help so far.

As I said, no, you need to let the vacuum finish.  It's one table, and
must be vacuumed in one piece.  If you stop it early, you're just
wasting your time.  You cannot do it in pieces, and if you did, that
wouldn't make it complete faster anyways.  That would make it take
MORE time.

Do the vacuum, and hold on until it is done.

Once that vacuum is done, later vacuums will take place MUCH quicker,
and in that you are clearly making enormous numbers of modifications
to the table, you need to vacuum the table regularly.
-- 
select 'cbbrowne' || '@' || 'ntlug.org';
http://cbbrowne.com/info/spiritual.html
Real Programmers use: "compress -d > a.out"



view thread (8+ messages)  latest in thread

Message-ID: <m34qmoi89l.fsf@wolfe.cbbrowne.com>
Permalink:  ../m34qmoi89l.fsf@wolfe.cbbrowne.com/
Also on:    postgresql.org/message-id/m34qmoi89l.fsf@wolfe.cbbrowne.com

 · 

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: <m34qmoi89l.fsf@wolfe.cbbrowne.com>
  References: <60oekwpsbw.fsf@dev6.int.libertyrms.info> <20040827182458.78631.qmail@web61304.mail.yahoo.com> <m34qmoi89l.fsf@wolfe.cbbrowne.com>

* 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