Обсуждение: pg_attribute size

Поиск
Список
Период
Сортировка

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?

Re: pg_attribute size

От
Tom Lane
Дата:
Anj Adu <fotographs@gmail.com> 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

Re: pg_attribute size

От
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


Re: pg_attribute size

От
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@pinpointresearch.com> 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
>
>

Re: pg_attribute size

От
Tom Lane
Дата:
Anj Adu <fotographs@gmail.com> 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

Re: pg_attribute size

От
Anj Adu
Дата:
Agreed...


Thank you

On Mon, Oct 26, 2009 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Anj Adu <fotographs@gmail.com> 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
>

Re: pg_attribute size

От
Naomi Walker
Дата:
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@sss.pgh.pa.us> wrote:
>> Anj Adu <fotographs@gmail.com> 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@eldocomp.com
   ---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
theindividual or entity to which it is addressed, and may contain information that is privileged, confidential or
exemptfrom disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has
beenforwarded to you without proper authority, you are notified that any use or dissemination of this information in
anymanner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this
mailfrom your records. 

Re: pg_attribute size

От
Scott Marlowe
Дата:
On Mon, Oct 26, 2009 at 3:29 PM, Anj Adu <fotographs@gmail.com> 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?

Re: pg_attribute size

От
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@gmail.com> wrote:
> On Mon, Oct 26, 2009 at 3:29 PM, Anj Adu <fotographs@gmail.com> 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?
>