pg_attribute size

[Anj Adu]

I have a few databases where the size of pg_attribute > 6G..    This
keeps growing..

is there a recommended way to purge data from this table.. Could this
also be why tools like pgAdmin take forever to open the database
browser?

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Tom Lane-2]

Anj Adu <fotographs@...> writes:
> I have a few databases where the size of pg_attribute > 6G..    This
> keeps growing..

Have you got autovacuum disabled?  That should keep the bloat in check
if it's allowed to work normally.

                        regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Steve Crawford]

Anj Adu wrote:
> I have a few databases where the size of pg_attribute > 6G..    This
> keeps growing..
>
> is there a recommended way to purge data from this table.. Could this
> also be why tools like pgAdmin take forever to open the database
> browser?
>
>  
Do you have an extraordinary number of tables/columns?

Try (as the database administrator), "vacuum full pg_attribute" perhaps
followed by "reindex table pg_attribute". Be aware that this may pretty
much lock your database while it is running. See if things improve then
do as Tom says - make sure autovacuum is running.

Cheers,
Steve


--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Anj Adu]

We have several partitioned tables that get dropped every day ..We do
not do autovacuum as it is an IO hog (and most tables are dropped
anyways..and the large tables are never updated)..

I however did a plain vacuum analyze and that fixed the problem with
tools(e.g pgadmin) that accessed the data dictionary and were very
slow before the vacuum.

On Sun, Oct 25, 2009 at 9:41 PM, Steve Crawford
<scrawford@...> wrote:

> Anj Adu wrote:
>>
>> I have a few databases where the size of pg_attribute > 6G..    This
>> keeps growing..
>>
>> is there a recommended way to purge data from this table.. Could this
>> also be why tools like pgAdmin take forever to open the database
>> browser?
>>
>>
>
> Do you have an extraordinary number of tables/columns?
>
> Try (as the database administrator), "vacuum full pg_attribute" perhaps
> followed by "reindex table pg_attribute". Be aware that this may pretty much
> lock your database while it is running. See if things improve then do as Tom
> says - make sure autovacuum is running.
>
> Cheers,
> Steve
>
>

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Tom Lane-2]

Anj Adu <fotographs@...> writes:
> We have several partitioned tables that get dropped every day ..We do
> not do autovacuum as it is an IO hog (and most tables are dropped
> anyways..and the large tables are never updated)..

If you're not going to run autovacuum then that means *you* must take
responsibility for vacuuming everything on a reasonable schedule.
That includes the system catalogs.  I think you'd be better off taking
the effort to learn to tune autovacuum to fit your requirements.

                        regards, tom lane

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Anj Adu]

Agreed...


Thank you

On Mon, Oct 26, 2009 at 3:46 PM, Tom Lane <tgl@...> wrote:

> Anj Adu <fotographs@...> writes:
>> We have several partitioned tables that get dropped every day ..We do
>> not do autovacuum as it is an IO hog (and most tables are dropped
>> anyways..and the large tables are never updated)..
>
> If you're not going to run autovacuum then that means *you* must take
> responsibility for vacuuming everything on a reasonable schedule.
> That includes the system catalogs.  I think you'd be better off taking
> the effort to learn to tune autovacuum to fit your requirements.
>
>                        regards, tom lane
>

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Naomi Walker-2]

We dump and vacuum every database every evening, so we can control
when the vacuums occur.

How does one vacuum the system catalogs?  By each tablename pg_*?

Thanks,
Naomi

>
> On Mon, Oct 26, 2009 at 3:46 PM, Tom Lane <tgl@...> wrote:
>> Anj Adu <fotographs@...> writes:
>>> We have several partitioned tables that get dropped every day ..We do
>>> not do autovacuum as it is an IO hog (and most tables are dropped
>>> anyways..and the large tables are never updated)..
>> If you're not going to run autovacuum then that means *you* must take
>> responsibility for vacuuming everything on a reasonable schedule.
>> That includes the system catalogs.  I think you'd be better off taking
>> the effort to learn to tune autovacuum to fit your requirements.
>>
>>                        regards, tom lane
>>
>

--
------------------------------------------------------------------------
Naomi Walker                          Chief Information Officer
Eldorado Computing, Inc               nwalker@...
   ---An Mphasis Company               602-604-3100
------------------------------------------------------------------------
The grand essentials to happiness in this life are something to do,
something to love and something to hope for.
- Joseph Addison
------------------------------------------------------------------------

-- CONFIDENTIALITY NOTICE --

Information transmitted by this e-mail is proprietary to MphasiS and/or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at mailmaster@... and delete this mail from your records.

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Scott Marlowe-2]

On Mon, Oct 26, 2009 at 3:29 PM, Anj Adu <fotographs@...> wrote:
> We have several partitioned tables that get dropped every day ..We do
> not do autovacuum as it is an IO hog (and most tables are dropped
> anyways..and the large tables are never updated)..

1: autovac can be adjusted to use much less IO than regular vacuum.
2: You can tell it to ignore certain tables.

> I however did a plain vacuum analyze and that fixed the problem with
> tools(e.g pgadmin) that accessed the data dictionary and were very
> slow before the vacuum.

Huh.  Is the pg_attribute size much smaller?

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[Anj Adu]

I am guessing the analyze helped the pgadmin data-dictionary query
planning..The vacuum will help with space re-use. the size is not
smaller as I did not do a vacuum full.



On Mon, Oct 26, 2009 at 4:27 PM, Scott Marlowe <scott.marlowe@...> wrote:

> On Mon, Oct 26, 2009 at 3:29 PM, Anj Adu <fotographs@...> wrote:
>> We have several partitioned tables that get dropped every day ..We do
>> not do autovacuum as it is an IO hog (and most tables are dropped
>> anyways..and the large tables are never updated)..
>
> 1: autovac can be adjusted to use much less IO than regular vacuum.
> 2: You can tell it to ignore certain tables.
>
>> I however did a plain vacuum analyze and that fixed the problem with
>> tools(e.g pgadmin) that accessed the data dictionary and were very
>> slow before the vacuum.
>
> Huh.  Is the pg_attribute size much smaller?
>

--
Sent via pgsql-admin mailing list (pgsql-admin@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin