X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F30D65E46E1 for ; Sat, 28 Aug 2004 00:47:39 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 62365-03 for ; Sat, 28 Aug 2004 03:47:38 +0000 (GMT) Received: from web52802.mail.yahoo.com (web52802.mail.yahoo.com [206.190.39.166]) by svr1.postgresql.org (Postfix) with SMTP id 2AB345E46C2 for ; Sat, 28 Aug 2004 00:47:28 -0300 (ADT) Message-ID: <20040828034735.11121.qmail@web52802.mail.yahoo.com> Received: from [68.227.223.161] by web52802.mail.yahoo.com via HTTP; Fri, 27 Aug 2004 20:47:35 PDT Date: Fri, 27 Aug 2004 20:47:35 -0700 (PDT) From: kris pal Subject: Re: vacuum pg_attribute causes high load To: Christopher Browne , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1675168641-1093664855=:10478" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200408/293 X-Sequence-Number: 14688 --0-1675168641-1093664855=:10478 Content-Type: text/plain; charset=us-ascii Christopher, Thanks for your detailed response. Now I get the idea why vacuum is causing so much load. We should have vacuumed the database more often but didn't do it as we are more used to managing Oracle databases. This is the first Postgres instance that I am managing. The datafile for production database ("bbs") is abt 6GB, though the actual data is very small, abt 20MB. I used the dump (from pg_dump of "bbs") of the production database to import into a "test_database" - its datafile size was < 20MB. So instead of going through all these, am planning to have down time of 15 mins and do the following: 1)"pg_dump bmgs" 2)"drop the database bmgs" ** (this should essentially free up all the space occupied by pg_attribute and its indexes - right ?? ) 3)"recreate database bmgs" and 4)import the dump from step 1) into bmgs created in step 3) Do you think there will be issues with this approach? The system catalog will be built from scratch, and there won't be any data loss right. That way I can reclaim the space and then run Vacuum more often. thanks, Kris Pal. Christopher Browne wrote: 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 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" ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend --------------------------------- Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! --0-1675168641-1093664855=:10478 Content-Type: text/html; charset=us-ascii
 
Christopher,
 
Thanks for your detailed response. Now I get the idea why vacuum is causing so much load. We should have vacuumed the database more often but didn't do it as we are more used to managing Oracle databases. This is the first Postgres instance that I am managing.
 
The datafile for production database ("bbs") is abt 6GB, though the actual data is very small, abt 20MB. I used the dump (from pg_dump of "bbs") of the production database  to import into a "test_database" - its datafile size was < 20MB.
 
So instead of going through all these, am planning to have down time of 15 mins and do the following:
 
1)"pg_dump bmgs"
2)"drop the database  bmgs"  
** (this should essentially free up all the space occupied by pg_attribute and its indexes - right ?? )
3)"recreate database bmgs"
and
4)import the dump from step 1) into bmgs created in  step 3)
 
 
Do you think there will be issues with this approach? The system catalog will be built from scratch, and there won't be any data loss right. That way I can reclaim the space and then run Vacuum more often.
 
thanks,
Kris Pal.
 


Christopher Browne <cbbrowne@acm.org> wrote:
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 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 c onsuming 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_attribiut e" 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"

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage! --0-1675168641-1093664855=:10478--