Обсуждение: pg_attribute size
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?
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
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
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 > >
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
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 >
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.
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?
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? >