Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id A401E6334C0 for ; Wed, 5 May 2010 10:54:33 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 64361-01 for ; Wed, 5 May 2010 13:54:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from hermes.vectorsf.com (firewall.vectorsf.com [62.82.80.62]) by mail.postgresql.org (Postfix) with ESMTP id DF7F6632C94 for ; Wed, 5 May 2010 10:54:21 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by hermes.vectorsf.com (Postfix) with ESMTP id 47E1811C0250; Wed, 5 May 2010 15:54:16 +0200 (CEST) X-Virus-Scanned: amavisd-new at hermes.vectorsf.com Received: from hermes.vectorsf.com ([127.0.0.1]) by localhost (hermes.vectorsf.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id zYAGTbGMvMR0; Wed, 5 May 2010 15:54:11 +0200 (CEST) Received: from [10.70.4.200] (unknown [10.70.4.200]) (Authenticated sender: imartinez) by hermes.vectorsf.com (Postfix) with ESMTP id 8786911C008F; Wed, 5 May 2010 15:54:11 +0200 (CEST) Subject: Re: Table space grow big - PostgreSQL From: =?ISO-8859-1?Q?I=F1igo?= Martinez Lasala To: Khangelani Gama Cc: "pgsql-admin@postgresql.org" In-Reply-To: References: <20100505114210.GD16616@miranda> Content-Type: multipart/alternative; boundary="=-bgPkNKME2UdkFHQtVu6D" Date: Wed, 05 May 2010 15:54:13 +0200 Message-ID: <1273067653.1897.128.camel@deimos> Mime-Version: 1.0 X-Mailer: Evolution 2.28.3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/57 X-Sequence-Number: 34530 --=-bgPkNKME2UdkFHQtVu6D Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: 7bit What about "vacuumdb -azf -U postgres"? (or for each database, "VACUUM FULL") With that script you only vacuum specific tables, but not entire databases. vacuum analyze should also be launched more frequently. Perhaps once a day, if possible, or sooner. "vacuumdb -az -U postgres" (or for each database, "VACUUM ANALYZE" -----Original Message----- From: Khangelani Gama To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Table space grow big - PostgreSQL Date: Wed, 5 May 2010 15:42:00 +0200 Many Thanks for all the replies. The conversion project to version 8 is still in progress, hence we are still experiencing problems on a version that's not supported. That's an honest answer I can give. The thing is it's frustrating to not to have a source of support as we still have to give support on this version 7.3.4 There is a script that runs once a month which reindex, vacuum full verborse and vacuum analyze : Small picture inside the script: echo "VACUUMING TABLE ${table} " >>$log 2>>$log /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "REINDEX TABLE ${table}" >>$log 2>>$log /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "VACUUM FULL VERBOSE ${table}" >>$log 2>>$log /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "VACUUM ANALYZE ${table}" >>$log 2>>$log Dumping and restoring the database doesn't decrease the space There is nothing set in the postgresql.conf file that has to do with vacuum analyze. If we could have a way to reduce the space in /usr/local/pgsql/data/base/95186722 directory we would be sorted -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brett Parker Sent: Wednesday, May 05, 2010 1:42 PM To: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Table space grow big - PostgreSQL On 05 May 13:36, Khangelani Gama wrote: > Hi all > > > Please assist on this scenario, I am a junior DBA, perhaps the > question I have is too simple please bear with me. > > I have a server with one PostgreSQL database and the data reside in > /usr/local/pgsql/data/ running on Redhat 9 O/S. > The main question I have is: What makes the > /usr/local/pgsql/data/base/95186722/ grow so big while the actual > data with its schema is only 2.8G and that is there a way to reduce > the table space sizes or anything related to that? OK - what version of postgres would be a good starting question... But, at a guess, I'd say you have a fair amount of table churn, and that you're probably due running a VACUUM FULL over the database that is using the most space, it may fail, but if it does it should tell you what bit of config you'll need to change to make it work. The other possible usage of lots of space is indexes on the database tables, these are not included in the dump, and so the size of the dump doesn't accurately reflect the size of the on disc data. Hope that gives you somewhere to start from. Thanks, -- Brett Parker -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of UCS Group and/or its subsidiaries. Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited. If you are not the intended addressee please notify the writer immediately and destroy the e-mail. UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes. --=-bgPkNKME2UdkFHQtVu6D Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: 7bit What about "vacuumdb -azf -U postgres"? (or for each database, "VACUUM FULL")

With that script you only vacuum specific tables, but not entire databases.

vacuum analyze should also be launched more frequently. Perhaps once a day, if possible, or sooner.
"vacuumdb -az -U postgres" (or for each database, "VACUUM ANALYZE"

-----Original Message-----
From: Khangelani Gama <Khangelani.Gama@ucs-software.co.za>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: [ADMIN] Table space grow big - PostgreSQL
Date: Wed, 5 May 2010 15:42:00 +0200

Many Thanks for all the replies.


The conversion project to version 8 is still in progress, hence we are still experiencing problems on a version that's not supported. That's an honest answer I can give. The thing is it's frustrating to not to have a source of support as we still have to give support on this version 7.3.4


There is a script that runs once a month which reindex, vacuum full verborse and vacuum analyze :

Small picture inside the script:

        echo "VACUUMING TABLE ${table} " >>$log 2>>$log
        /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "REINDEX TABLE ${table}" >>$log 2>>$log
        /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "VACUUM FULL VERBOSE ${table}" >>$log  2>>$log
        /usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -tc "VACUUM ANALYZE ${table}" >>$log  2>>$log


Dumping and restoring the database doesn't decrease the space



There is nothing set in the postgresql.conf file that has to do with vacuum analyze.

If we could have a way to reduce the space in /usr/local/pgsql/data/base/95186722 directory we would be sorted








-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Brett Parker
Sent: Wednesday, May 05, 2010 1:42 PM
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Table space grow big - PostgreSQL

On 05 May 13:36, Khangelani Gama wrote:
> Hi all
>
>
> Please assist on this scenario, I am a junior DBA, perhaps the
> question I have is too simple please bear with me.
>
> I have a server with one PostgreSQL database and the data reside in
> /usr/local/pgsql/data/ running on Redhat 9 O/S.

<snippage class="lots" />

> The main question I have is: What makes the
> /usr/local/pgsql/data/base/95186722/  grow so big while the actual
> data with its schema is only 2.8G and that is there a way to reduce
> the table space sizes or anything related to that?

OK - what version of postgres would be a good starting question...

But, at a guess, I'd say you have a fair amount of table churn, and that
you're probably due running a VACUUM FULL over the database that is
using the most space, it may fail, but if it does it should tell you
what bit of config you'll need to change to make it work.

The other possible usage of lots of space is indexes on the database
tables, these are not included in the dump, and so the size of the dump
doesn't accurately reflect the size of the on disc data.

Hope that gives you somewhere to start from.

Thanks,
--
Brett Parker

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

The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential information of UCS Group and/or its subsidiaries.  Any review, use or dissemination thereof by anyone other than the intended addressee is prohibited.  If you are not the intended addressee please notify the writer immediately and destroy the e-mail.  UCS Group Limited and its subsidiaries distance themselves from and accept no liability for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

--=-bgPkNKME2UdkFHQtVu6D--