From kristhegambler@yahoo.com Wed Aug 25 19:08:44 2004 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 891245E46C2 for ; Wed, 25 Aug 2004 16:08:44 -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 03191-10 for ; Wed, 25 Aug 2004 19:08:35 +0000 (GMT) Received: from web61310.mail.yahoo.com (web61310.mail.yahoo.com [216.155.196.153]) by svr1.postgresql.org (Postfix) with SMTP id 425E75E40AC for ; Wed, 25 Aug 2004 16:08:34 -0300 (ADT) Message-ID: <20040825185334.66446.qmail@web61310.mail.yahoo.com> Received: from [68.101.43.86] by web61310.mail.yahoo.com via HTTP; Wed, 25 Aug 2004 11:53:34 PDT Date: Wed, 25 Aug 2004 11:53:34 -0700 (PDT) From: kris pal Subject: regression database To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1924051113-1093460014=:61719" 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/252 X-Sequence-Number: 14647 --0-1924051113-1093460014=:61719 Content-Type: text/plain; charset=us-ascii Hello, I inherited a production Postgres database server which has a "Regression" database. The problem is that the size of the objects with in the Regression database are increasing so much that the database has grown into several GB. (Ex: pg_attribute and its index have grown into 2GB each and so on with few other objects as well). I am not sure what the implications of completely deleting the 'Regression' database (I don't need the regression testing). I found the documentation on how to do the regression tests but can you tell me if there is a documentation of how to take care of these space issues for "Regression" database? Or is there a way to reduce the space used by 'Regression' database? I would appreciate your help in resolving this issue. thanks, Kris. --------------------------------- Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. --0-1924051113-1093460014=:61719 Content-Type: text/html; charset=us-ascii
Hello,
 
I inherited a production Postgres database server which has a "Regression" database. The problem is that the size of the objects with in the Regression database are increasing so much that the database has grown into several GB.  (Ex: pg_attribute and its index have grown into 2GB each and so on with few other objects as well).
 
I am not sure what the implications of completely deleting the 'Regression' database (I don't need the regression testing). I found the documentation on how to do the regression tests but  can you tell me if there is a documentation of how to take  care of these space issues for "Regression" database?  Or is there a way to reduce the space used by 'Regression' database?
 
I would appreciate your help in resolving this issue.
 
thanks,
Kris.
 
 


Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses. --0-1924051113-1093460014=:61719-- From tgl@sss.pgh.pa.us Thu Aug 26 02:00:12 2004 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 BF4EF5E40BA for ; Wed, 25 Aug 2004 23:00:12 -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 35876-07 for ; Thu, 26 Aug 2004 02:00:05 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D2FDA5E3632 for ; Wed, 25 Aug 2004 23:00:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7Q209wm010973; Wed, 25 Aug 2004 22:00:09 -0400 (EDT) To: kris pal Cc: pgsql-admin@postgresql.org Subject: Re: regression database In-reply-to: <20040825185334.66446.qmail@web61310.mail.yahoo.com> References: <20040825185334.66446.qmail@web61310.mail.yahoo.com> Comments: In-reply-to kris pal message dated "Wed, 25 Aug 2004 11:53:34 -0700" Date: Wed, 25 Aug 2004 22:00:08 -0400 Message-ID: <10972.1093485608@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/255 X-Sequence-Number: 14650 kris pal writes: > I am not sure what the implications of completely deleting the > 'Regression' database (I don't need the regression testing). Is it named "Regression" with a capital R? If so, that's not the DB that is made by the standard regression tests --- that is called "regression". There is not any reason not to drop the standard regression database, if you're not using it. But there is no way that that DB would get that large unless something in your installation is using it. I'd suggest you find out what. regards, tom lane From kristhegambler@yahoo.com Thu Aug 26 18:12:51 2004 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 15FAB5E3632 for ; Thu, 26 Aug 2004 15:12:51 -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 57227-08 for ; Thu, 26 Aug 2004 18:12:47 +0000 (GMT) Received: from web61306.mail.yahoo.com (web61306.mail.yahoo.com [216.155.196.149]) by svr1.postgresql.org (Postfix) with SMTP id 8424D5E37D0 for ; Thu, 26 Aug 2004 15:12:45 -0300 (ADT) Message-ID: <20040826173209.18246.qmail@web61306.mail.yahoo.com> Received: from [68.101.43.86] by web61306.mail.yahoo.com via HTTP; Thu, 26 Aug 2004 10:32:09 PDT Date: Thu, 26 Aug 2004 10:32:09 -0700 (PDT) From: kris pal Subject: Re: regression database To: Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <10972.1093485608@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-65021217-1093541529=:4040" 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/267 X-Sequence-Number: 14662 --0-65021217-1093541529=:4040 Content-Type: text/plain; charset=us-ascii Hi tom, It is actually named 'regression'. I spoke with the person who build the server but apparently he is not well worsed in Postgres. He didn't explicitly create 'regression', I guess he selected some default installation options. I am not sure how to find out what is causing the 'regression' database to increase in size. Its size is not due to the tables with in the 'regression' but due to the system tables like "pg_attribute" with in 'regression' database. If you chould point to a resource which would help me understand how this 'regression' database is handled internally (what activity writes to this database) that would be great. Pls see below, the directory 2034862 corresponds to 'regression': ____________________________________________________ [postgres@dds base]$ du -sk /home/postgres/data/base/2034862 6236920 /home/postgres/data/base/2034862 [postgres@dds base]$ [postgres@dds base]$ cd /home/postgres/data/base/2034862 [postgres@dds 2034862]$ du -sk * | awk '{if ($1 > 512000) print $1" --> "$2}' 1049604 --> 1249 (over 1GB) 949484 --> 1249.1 (abt 1GB) 1049604 --> 16609 821104 --> 16609.1 The relname for the corresponding relfilenode: --------------+------------- pg_attribute | 1249 pg_attribute_relid_attnam_index | 16609 --------------+------------- ____________________________________________________ So am not sure why this thing is growing progressively. Any feedback/ help is greatly appreciated. thanks, Kris Pal. Tom Lane wrote:kris pal writes: > I am not sure what the implications of completely deleting the > 'Regression' database (I don't need the regression testing). Is it named "Regression" with a capital R? If so, that's not the DB that is made by the standard regression tests --- that is called "regression". There is not any reason not to drop the standard regression database, if you're not using it. But there is no way that that DB would get that large unless something in your installation is using it. I'd suggest you find out what. regards, tom lane --------------------------------- Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! --0-65021217-1093541529=:4040 Content-Type: text/html; charset=us-ascii
Hi tom,
 
It is actually named 'regression'. I spoke with the person who build the server but apparently he is not well worsed in Postgres. He didn't explicitly create 'regression', I guess he selected some default installation options.
 
I am not sure how to find out what is causing the 'regression' database to increase in size. Its size is not due to the tables with in the 'regression' but due to the system tables like "pg_attribute" with in 'regression' database. If you chould point to a resource which would help me understand how this 'regression' database is handled internally (what activity writes to this database) that would be great.
 
 
Pls see below, the directory 2034862 corresponds to 'regression':
 
____________________________________________________
 
[postgres@dds base]$ du -sk /home/postgres/data/base/2034862
6236920 /home/postgres/data/base/2034862
[postgres@dds base]$
[postgres@dds base]$ cd /home/postgres/data/base/2034862
[postgres@dds 2034862]$ du -sk * | awk '{if ($1 > 512000)  print $1" --> "$2}'
1049604 --> 1249    (over 1GB)
949484 --> 1249.1   (abt 1GB)
1049604 --> 16609
821104 --> 16609.1
The relname for the corresponding relfilenode:
--------------+-------------
 pg_attribute |        1249
 pg_attribute_relid_attnam_index |       16609
--------------+-------------
____________________________________________________
 
 
So am not sure why this thing is growing progressively.
Any feedback/ help is greatly appreciated.
 
thanks,
Kris Pal.


Tom Lane <tgl@sss.pgh.pa.us> wrote:
kris pal writes:
> I am not sure what the implications of completely deleting the
> 'Regression' database (I don't need the regression testing).

Is it named "Regression" with a capital R? If so, that's not the DB that
is made by the standard regression tests --- that is called "regression".

There is not any reason not to drop the standard regression database, if
you're not using it. But there is no way that that DB would get that
large unless something in your installation is using it. I'd suggest
you find out what.

regards, tom lane


Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages! --0-65021217-1093541529=:4040-- From kristhegambler@yahoo.com Thu Aug 26 22:17:12 2004 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 433065E46F4 for ; Thu, 26 Aug 2004 19:17:12 -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 40579-09 for ; Thu, 26 Aug 2004 22:17:06 +0000 (GMT) Received: from web61308.mail.yahoo.com (web61308.mail.yahoo.com [216.155.196.151]) by svr1.postgresql.org (Postfix) with SMTP id F2A605E46EC for ; Thu, 26 Aug 2004 19:17:04 -0300 (ADT) Message-ID: <20040826211532.30654.qmail@web61308.mail.yahoo.com> Received: from [68.101.43.86] by web61308.mail.yahoo.com via HTTP; Thu, 26 Aug 2004 14:15:32 PDT Date: Thu, 26 Aug 2004 14:15:32 -0700 (PDT) From: kris pal Subject: Re: regression database To: kris pal , Tom Lane Cc: pgsql-admin@postgresql.org In-Reply-To: <20040826173209.18246.qmail@web61306.mail.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1082699247-1093554932=:28430" 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/269 X-Sequence-Number: 14664 --0-1082699247-1093554932=:28430 Content-Type: text/plain; charset=us-ascii Actually I just noticed something. Previously for some reason, I thought the data directory corresponded to 'regression'. But I double checked it now with oid2name and it corresponds to our main database say "dds". But the issue still remains. In the sense that pg_attribute etc are still so huge and I have no idea why. This table is not the one we created in the schema. thanks, Kris Pal. kris pal wrote:Hi tom, It is actually named 'regression'. I spoke with the person who build the server but apparently he is not well worsed in Postgres. He didn't explicitly create 'regression', I guess he selected some default installation options. I am not sure how to find out what is causing the 'regression' database to increase in size. Its size is not due to the tables with in the 'regression' but due to the system tables like "pg_attribute" with in 'regression' database. If you chould point to a resource which would help me understand how this 'regression' database is handled internally (what activity writes to this database) that would be great. Pls see below, the directory 2034862 corresponds to 'regression': ____________________________________________________ [postgres@dds base]$ du -sk /home/postgres/data/base/2034862 6236920 /home/postgres/data/base/2034862 [postgres@dds base]$ [postgres@dds base]$ cd /home/postgres/data/base/2034862 [postgres@dds 2034862]$ du -sk * | awk '{if ($1 > 512000) print $1" --> "$2}' 1049604 --> 1249 (over 1GB) 949484 --> 1249.1 (abt 1GB) 1049604 --> 16609 821104 --> 16609.1 The relname for the corresponding relfilenode: --------------+------------- pg_attribute | 1249 pg_attribute_relid_attnam_index | 16609 --------------+------------- ____________________________________________________ So am not sure why this thing is growing progressively. Any feedback/ help is greatly appreciated. thanks, Kris Pal. Tom Lane wrote: kris pal writes: > I am not sure what the implications of completely deleting the > 'Regression' database (I don't need the regression testing). Is it named "Regression" with a capital R? If so, that's not the DB that is made by the standard regression tests --- that is called "regression". There is not any reason not to drop the standard regression database, if you're not using it. But there is no way that that DB would get that large unless something in your installation is using it. I'd suggest you find out what. regards, tom lane --------------------------------- Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! --------------------------------- Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. --0-1082699247-1093554932=:28430 Content-Type: text/html; charset=us-ascii
 
Actually I just noticed something. Previously for some reason, I thought the data directory corresponded to 'regression'. But I double checked it now with oid2name and it corresponds to our main database say "dds".
 
But the issue still remains. In the sense that pg_attribute etc are still so huge and I have no idea why. This table is not the one we created in the schema.
 
thanks,
Kris Pal.


kris pal <kristhegambler@yahoo.com> wrote:
Hi tom,
 
It is actually named 'regression'. I spoke with the person who build the server but apparently he is not well worsed in Postgres. He didn't explicitly create 'regression', I guess he selected some default installation options.
 
I am not sure how to find out what is causing the 'regression' database to increase in size. Its size is not due to the tables with in the 'regression' but due to the system tables like "pg_attribute" with in 'regression' database. If you chould point to a resource which would help me understand how this 'regression' database is handled internally (what activity writes to this database) that would be great.
 
 
Pls see below, the directory 2034862 corresponds to 'regression':
 
____________________________________________________
 
[postgres@dds base]$ du -sk /home/postgres/data/base/2034862
6236920 /home/postgres/data/base/2034862
[postgres@dds base]$
[postgres@dds base]$ cd /home/postgres/data/base/2034862
[postgres@dds 2034862]$ du -sk * | awk '{if ($1 > 512000)  print $1" --> "$2}'
1049604 --> 1249    (over 1GB)
949484 --> 1249.1   (abt 1GB)
1049604 --> 16609
821104 --> 16609.1
The relname for the corresponding relfilenode:
--------------+-------------
 pg_attribute |        1249
 pg_attribute_relid_attnam_index |       16609
--------------+-------------
____________________________________________________
 
 
So am not sure why this thing is growing progressively.
Any feedback/ help is greatly appreciated.
 
thanks,
Kris Pal.


Tom Lane <tgl@sss.pgh.pa.us> wrote:
kris pal writes:
> I am not sure what the implications of completely deleting the
> 'Regression' database (I don't need the regression testing).

Is it named "Regression" with a capital R? If so, that's not the DB that
is made by the standard regression tests --- that is called "regression".

There is not any reason not to drop the standard regression database, if
you're not using it. But there is no way that that DB would get that
large unless something in your installation is using it. I'd suggest
you find out what.

regards, tom lane


Do you Yahoo!?
New and Improved Yahoo! Mail - Send 10MB messages!


Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now. --0-1082699247-1093554932=:28430-- From tgl@sss.pgh.pa.us Fri Aug 27 03:29:19 2004 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 B466C5E46D2 for ; Fri, 27 Aug 2004 00:29:19 -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 23880-08 for ; Fri, 27 Aug 2004 03:29:23 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 752B35E37D0 for ; Fri, 27 Aug 2004 00:29:15 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i7R3TMLr005708; Thu, 26 Aug 2004 23:29:22 -0400 (EDT) To: kris pal Cc: pgsql-admin@postgresql.org Subject: Re: regression database In-reply-to: <20040826211532.30654.qmail@web61308.mail.yahoo.com> References: <20040826211532.30654.qmail@web61308.mail.yahoo.com> Comments: In-reply-to kris pal message dated "Thu, 26 Aug 2004 14:15:32 -0700" Date: Thu, 26 Aug 2004 23:29:21 -0400 Message-ID: <5707.1093577361@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/270 X-Sequence-Number: 14665 kris pal writes: > Actually I just noticed something. Previously for some reason, I > thought the data directory corresponded to 'regression'. But I double > checked it now with oid2name and it corresponds to our main database > say "dds". Ah, that makes more sense. > But the issue still remains. In the sense that pg_attribute etc are > still so huge and I have no idea why. Probably because you've created and deleted a lot of tables (does your app use temp tables a lot?). If you aren't good about vacuuming the system catalogs then you're going to get table bloat. If you're using something pre-7.4 then you may get index bloat even if you *are* vacuuming regularly :-(. My suggestions are to update to 7.4, if you're not there already (if you are, you may need to do VACUUM FULL and REINDEX to get rid of the bloat); then adopt a stricter regime of routine vacuuming. And check your FSM settings. regards, tom lane