Table space grow big - PostgreSQL

--_000_D78A8169F9436B4DB978300336168F3B3359C9CBB9SWBREXCH00u cs_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all


Please assist on this scenario, I am a junior DBA, perhaps the question I h=
ave is too simple please bear with me.


I have a server with one PostgreSQL database and the data reside in /usr/lo=
cal/pgsql/data/ running on Redhat 9 O/S.

File system looks as follows:

Filesystem Size Used Avail Use% Mounted on
/dev/hda3 73G 61G 8.7G 88% /
/dev/hda1 99M 63M 32M 67% /boot
none 441M 0 441M 0% /dev/shm


Disc space utilization looks as follows where the data/ directory seems to =
be taking a lot of space compared to any other directories:

From / directory on user root/:

Size directory
4.0K ./lost+found
58M ./boot
436K ./dev
900M ./proc
1.1G ./var
263M ./tmp
25M ./etc
26M ./root
57G ./usr
5.3M ./bin
2.0G ./home
4.0K ./initrd
68M ./lib
32K ./mnt
4.0K ./opt
15M ./sbin
4.0K ./misc
4.0K ./.automount
48K ./tftpboot
44K ./backup
211M ./u
8.0K ./backups
62G . - Size



/usr/local/pgsql/data/base directory shows the following where 95186722/ t=
akes a lot of space :

3.6M ./1
3.6M ./16975
51G ./95186722
4.8M ./4830693
51G .


Some files (which are called table space names, please correct me if I am w=
rong?) listed under 95186722/ directory shows different sizes where the big=
gest of all is 440M :

-rw------- 1 postgres postgres 16K May 5 12:50 219436402
-rw------- 1 postgres postgres 16K May 5 12:50 219436401
-rw------- 1 postgres postgres 16K May 5 12:50 219436400
-rw------- 1 postgres postgres 16K May 5 12:50 219436399
-rw------- 1 postgres postgres 34M May 5 12:50 219436274
-rw------- 1 postgres postgres 42M May 5 12:50 219436273
-rw------- 1 postgres postgres 43M May 5 12:50 219436272
-rw------- 1 postgres postgres 42M May 5 12:50 219436271
-rw------- 1 postgres postgres 34M May 5 12:50 219436270
-rw------- 1 postgres postgres 42M May 5 12:50 219436269
-rw------- 1 postgres postgres 42M May 5 12:50 219436268
-rw------- 1 postgres postgres 51M May 5 12:50 219436267
-rw------- 1 postgres postgres 34M May 5 12:50 219436266
-rw------- 1 postgres postgres 51M May 5 12:50 219436265
-rw------- 1 postgres postgres 15M May 5 12:50 218478745
-rw------- 1 postgres postgres 11M May 5 12:50 218478744
-rw------- 1 postgres postgres 10M May 5 12:50 218478743
-rw------- 1 postgres postgres 13M May 5 12:50 218478742
-rw------- 1 postgres postgres 440M May 5 12:50 216081969.1



Now the dump file of the very same database created by using command : pg_d=
ump -U user -O dbname > /tmp/filename is 2.8G big. :

-rw-r--r-- 1 root root 2.8G May 5 12:47 db.dump




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



Your response will be truly appreciated


Thanks
Khangelani Gama








Confidentiality Notice:http://ucs.co.za/conf.html



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

--_000_D78A8169F9436B4DB978300336168F3B3359C9CBB9SWBREXCH00u cs_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:x=3D"urn:schemas-microsoft-com:office:excel" xmlns:p=3D"urn:schemas-m=
icrosoft-com:office:powerpoint" xmlns:a=3D"urn:schemas-microsoft-com:office=
:access" xmlns:dt=3D"uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:s=3D"=
uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:rs=3D"urn:schemas-microsof=
t-com:rowset" xmlns:z=3D"#RowsetSchema" xmlns:b=3D"urn:schemas-microsoft-co=
m:office:publisher" xmlns:ss=3D"urn:schemas-microsoft-com:office:spreadshee=
t" xmlns:c=3D"urn:schemas-microsoft-com:office:component:spread sheet" xmlns=
:odc=3D"urn:schemas-microsoft-com:office:odc" xmlns:oa=3D"urn:schemas-micro=
soft-com:office:activation" xmlns:html=3D"http://www.w3.org/TR/REC-html40" =
xmlns:q=3D"http://schemas.xmlsoap.org/soap/envelope/" xmlns:rtc=3D"http://m=
icrosoft.com/officenet/conferencing" xmlns:D=3D"DAV:" xmlns:Repl=3D"http://=
schemas.microsoft.com/repl/" xmlns:mt=3D"http://schemas.microsoft.com/share=
point/soap/meetings/" xmlns:x2=3D"http://schemas.microsoft.com/office/excel=
/2003/xml" xmlns:ppda=3D"http://www.passport.com/NameSpace.xsd" xmlns:ois=
=3D"http://schemas.microsoft.com/sharepoint/soap/ois/" xmlns:dir=3D"http://=
schemas.microsoft.com/sharepoint/soap/directory/" xmlns:ds=3D"http://www.w3=
..org/2000/09/xmldsig#" xmlns:dsp=3D"http://schemas.microsoft.com/sharepoint=
/dsp" xmlns:udc=3D"http://schemas.microsoft.com/data/udc" xmlns:xsd=3D"http=
://www.w3.org/2001/XMLSchema" xmlns:sub=3D"http://schemas.microsoft.com/sha=
repoint/soap/2002/1/alerts/" xmlns:ec=3D"http://www.w3.org/2001/04/xmlenc#"=
xmlns:sp=3D"http://schemas.microsoft.com/sharepoint/" xmlns:sps=3D"http://=
schemas.microsoft.com/sharepoint/soap/" xmlns:xsi=3D"http://www.w3.org/2001=
/XMLSchema-instance" xmlns:udcs=3D"http://schemas.microsoft.com/data/udc/so=
ap" xmlns:udcxf=3D"http://schemas.microsoft.com/data/udc/xmlfile " xmlns:udc=
p2p=3D"http://schemas.microsoft.com/data/udc/parttopart" xmlns:wf=3D"http:/=
/schemas.microsoft.com/sharepoint/soap/workflow/" xmlns:dsss=3D"http://sche=
mas.microsoft.com/office/2006/digsig-setup" xmlns:dssi=3D"http://schemas.mi=
crosoft.com/office/2006/digsig" xmlns:mdssi=3D"http://schemas.openxmlformat=
s.org/package/2006/digital-signature" xmlns:mver=3D"http://schemas.openxmlf=
ormats.org/markup-compatibility/2006" xmlns:m=3D"http://schemas.microsoft.c=
om/office/2004/12/omml" xmlns:mrels=3D"http://schemas.openxmlformats.org/pa=
ckage/2006/relationships" xmlns:spwp=3D"http://microsoft.com/sharepoint/web=
partpages" xmlns:ex12t=3D"http://schemas.microsoft.com/exchange/service s/20=
06/types" xmlns:ex12m=3D"http://schemas.microsoft.com/exchange/service s/200=
6/messages" xmlns:pptsl=3D"http://schemas.microsoft.com/sharepoint/soap/ Sli=
deLibrary/" xmlns:spsl=3D"http://microsoft.com/webservices/SharePointPor tal=
Server/PublishedLinksService" xmlns:Z=3D"urn:schemas-microsoft-com:" xmlns:=
st=3D"
" xmlns=3D"http://www.w3.org/TR/REC-html40">

>

Re: 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

Re: Table space grow big - PostgreSQL

Khangelani Gama wrote:

> /usr/local/pgsql/data/base directory shows the following where
> 95186722/ takes a lot of space :

> 51G ./95186722

> Now the dump file of the very same database created by using
> command : pg_dump -U user -O dbname > /tmp/filename is 2.8G big.

> 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?

The size of a dump is often somewhat smaller than the raw data, but
this extreme difference suggests that your tables are bloated due to
inadequate routine maintenance. What version of PostgreSQL is this?
What is your vacuum policy? Please show the contents of your
postgresql.conf file with all comments stripped.

-Kevin


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

Re: Table space grow big - PostgreSQL

--=-/Vmx7xYSkG9YZ0RGA8fc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Wed, 2010-05-05 at 13:36 +0200, Khangelani Gama wrote:
> 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?

Which PostgreSQL version is this? I'm assuming it is an old one, since
you are running Red Hat 9.=20

Have you vacuumed it recently/regularly? If not, dead space (space that
is consumed by dead tuples) will consume lots of disk space, until they
are vacuumed. However, If you haven't vacuumed for a long time, it may
be the best to dump/restore the database, and then apply regular
maintenance:

http://www.postgresql.org/docs/current/static/maintenance.ht ml

Regards,=20
--=20
Devrim GÜNDÜZ
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
PostgreSQL RPM Repository: http://yum.pgrpms.org
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz

--=-/Vmx7xYSkG9YZ0RGA8fc
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)

iEYEABECAAYFAkvhWsEACgkQtl86P3SPfQ6i0ACdEz2E9vGSwmcZf88t3oLd 4Y15
lTEAoJVDcNeUhObzwR7rUNK4OFB2EMrE
=Vnxs
-----END PGP SIGNATURE-----

--=-/Vmx7xYSkG9YZ0RGA8fc--

Re: Table space grow big - PostgreSQL

Στις Wednesday 05 May 2010 14:45:26 ο/η Kevin=
Grittner έγραψε:
> Khangelani Gama wrote:
>
> > /usr/local/pgsql/data/base directory shows the following where
> > 95186722/ takes a lot of space :
>=20
> > 51G ./95186722
>=20
> > Now the dump file of the very same database created by using
> > command : pg_dump -U user -O dbname > /tmp/filename is 2.8G big.
>
> > 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?
>
> The size of a dump is often somewhat smaller than the raw data, but
> this extreme difference suggests that your tables are bloated due to
> inadequate routine maintenance. What version of PostgreSQL is this?=20
> What is your vacuum policy? Please show the contents of your
> postgresql.conf file with all comments stripped.
>

It depends, in the case of indexes, one SQL CREATE INDEX statement could re=
sult to many megs of memory on disk,
while, on the other hand, binary content (a bytea holding e.g. image bitmap=
s) will have bigger size in the dump rather than
on disk's raw data, since it is compressed there.
In our case the dump is twice the size of the db on disk, but generally i t=
hink there cant be no universal rule of what is
larger than what.

> -Kevin
>=20
>=20



--=20
Achilleas Mantzios

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

Re: Table space grow big - PostgreSQL

Many Thanks for all the replies.


The conversion project to version 8 is still in progress, hence we are stil=
l 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 o=
f 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 verbors=
e 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 "REINDE=
X 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/95=
186722 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 address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other =
than the intended addressee is prohibited. If you are not the intended add=
ressee please notify the writer immediately and destroy the e-mail. UCS Gr=
oup Limited and its subsidiaries distance themselves from and accept no lia=
bility for unauthorised use of their e-mail facilities or e-mails sent othe=
r than strictly for business purposes.

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

Re: Table space grow big - PostgreSQL

--=-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 <>

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: ] On Behalf Of Brett Parker

Sent: Wednesday, May 05, 2010 1:42 PM

To: 

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 ()

To make changes to your subscription:





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.

Re: Table space grow big - PostgreSQL

In your original email I think you showed a directory that held
something like 50Gb of files with a listing of that directory that
added up to something much smaller. If that is right - what else is
in that directory?

Does your monthly vacuum script work?


--
Ian.


On Wed, May 5, 2010 at 2:42 PM, Khangelani Gama
wrote:
> Many Thanks for all the replies.
>
>
> The conversion project to version 8 is still in progress, hence we are st=
ill experiencing problems on a version that's not supported. That's an hone=
st 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 verbo=
rse and vacuum analyze :
>
> Small picture inside the script:
>
> =A0 =A0 =A0 =A0echo "VACUUMING TABLE ${table} " >>$log 2>>$log
> =A0 =A0 =A0 =A0/usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -t=
c "REINDEX TABLE ${table}" >>$log 2>>$log
> =A0 =A0 =A0 =A0/usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -t=
c "VACUUM FULL VERBOSE ${table}" >>$log =A02>>$log
> =A0 =A0 =A0 =A0/usr/local/pgsql/bin/psql -U $dbuser -h localhost ${db} -t=
c "VACUUM ANALYZE ${table}" >>$log =A02>>$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 vacu=
um 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@postgres=
ql.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/ =A0grow 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 addre=
ssee only, and may contain the confidential information of UCS Group and/or=
its subsidiaries. =A0Any review, use or dissemination thereof by anyone ot=
her than the intended addressee is prohibited. =A0If you are not the intend=
ed addressee please notify the writer immediately and destroy the e-mail. =
=A0UCS Group Limited and its subsidiaries distance themselves from and acce=
pt no liability for unauthorised use of their e-mail facilities or e-mails =
sent other than strictly for business purposes.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

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

Re: Table space grow big - PostgreSQL

Khangelani Gama wrote:

> There is a script that runs once a month

Most likely that should be daily, or at least weekly.

> "REINDEX TABLE ${table}"
> "VACUUM FULL VERBOSE ${table}"
> "VACUUM ANALYZE ${table}"

That's the wrong order. Try:

"VACUUM FULL VERBOSE ANALYZE ${table}"
"REINDEX TABLE ${table}"

With your current order, the VACUUM FULL bloats the indexes you've
just rebuilt.

If you vacuum frequently enough, you should not need to use the FULL
option.

> Dumping and restoring the database doesn't decrease the space

Now, that's odd. You're not restoring back into the same database
without dropping it first (using the "clean" option), are you?
Perhaps you have some very wide indexes, or a very large number of
small tables?

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

Well, that wasn't the only thing I would look for; however, I'm not
sure how many of the things I usually check exist in 7.3 or work the
same way. :-(

-Kevin

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

Re: Table space grow big - PostgreSQL

Thanks again for the replies:


Responding to Kevin, Ian, and Inigo:



1. We'll try an change the order in the script
2. Daily vacuum analyze was disabled, which was running from a different sc=
ript, we'll try to put it back.
3. The monthly script does work.
4. In my original email about the directory that takes more space:

/usr/local/pgsql/data/base directory shows the following where 95186722/ ta=
kes a lot of space :

3.6M ./1
3.6M ./16975
51G ./95186722
4.8M ./4830693
51G .



There is nothing else in /usr/local/pgsql/data/base/95186722/ directory ex=
cept the table space names with different spaces for each one of them. And =
in other servers these table space sizes are now at 1.0G, for an example th=
ere is about 15 of them which makes about 15G.

-rw------- 1 postgres postgres 16K May 5 12:50 219436402
-rw------- 1 postgres postgres 16K May 5 12:50 219436401
-rw------- 1 postgres postgres 16K May 5 12:50 219436400
-rw------- 1 postgres postgres 16K May 5 12:50 219436399
-rw------- 1 postgres postgres 34M May 5 12:50 219436274
-rw------- 1 postgres postgres 42M May 5 12:50 219436273
-rw------- 1 postgres postgres 43M May 5 12:50 219436272
-rw------- 1 postgres postgres 42M May 5 12:50 219436271
-rw------- 1 postgres postgres 34M May 5 12:50 219436270
-rw------- 1 postgres postgres 42M May 5 12:50 219436269
-rw------- 1 postgres postgres 42M May 5 12:50 219436268
-rw------- 1 postgres postgres 51M May 5 12:50 219436267
-rw------- 1 postgres postgres 34M May 5 12:50 219436266
-rw------- 1 postgres postgres 51M May 5 12:50 219436265
-rw------- 1 postgres postgres 15M May 5 12:50 218478745
-rw------- 1 postgres postgres 11M May 5 12:50 218478744
-rw------- 1 postgres postgres 10M May 5 12:50 218478743
-rw------- 1 postgres postgres 13M May 5 12:50 218478742
-rw------- 1 postgres postgres 440M May 5 12:50 216081969.1












-----Original Message-----
From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
Sent: Wednesday, May 05, 2010 3:59 PM
To: pgsql-admin@postgresql.org; Khangelani Gama
Subject: Re: [ADMIN] Table space grow big - PostgreSQL

Khangelani Gama wrote:

> There is a script that runs once a month

Most likely that should be daily, or at least weekly.

> "REINDEX TABLE ${table}"
> "VACUUM FULL VERBOSE ${table}"
> "VACUUM ANALYZE ${table}"

That's the wrong order. Try:

"VACUUM FULL VERBOSE ANALYZE ${table}"
"REINDEX TABLE ${table}"

With your current order, the VACUUM FULL bloats the indexes you've
just rebuilt.

If you vacuum frequently enough, you should not need to use the FULL
option.

> Dumping and restoring the database doesn't decrease the space

Now, that's odd. You're not restoring back into the same database
without dropping it first (using the "clean" option), are you?
Perhaps you have some very wide indexes, or a very large number of
small tables?

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

Well, that wasn't the only thing I would look for; however, I'm not
sure how many of the things I usually check exist in 7.3 or work the
same way. :-(

-Kevin

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

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

Re: Table space grow big - PostgreSQL

I'm still confused about the disk space, even more so now that more
servers and figures of 1Gb and 15Gb have joined the party.

Presumably this:

> 3.6M ./1
> 3.6M ./16975
> 51G ./95186722
> 4.8M ./4830693
> 51G .

is the output from some $ du command. And is this

> -rw------- 1 postgres postgres 16K May 5 12:50 219436402
> -rw------- 1 postgres postgres 16K May 5 12:50 219436401
> -rw------- 1 postgres postgres 16K May 5 12:50 219436400
> -rw------- 1 postgres postgres 16K May 5 12:50 219436399
> -rw------- 1 postgres postgres 34M May 5 12:50 219436274
> -rw------- 1 postgres postgres 42M May 5 12:50 219436273
> -rw------- 1 postgres postgres 43M May 5 12:50 219436272
> -rw------- 1 postgres postgres 42M May 5 12:50 219436271
> -rw------- 1 postgres postgres 34M May 5 12:50 219436270
> -rw------- 1 postgres postgres 42M May 5 12:50 219436269
> -rw------- 1 postgres postgres 42M May 5 12:50 219436268
> -rw------- 1 postgres postgres 51M May 5 12:50 219436267
> -rw------- 1 postgres postgres 34M May 5 12:50 219436266
> -rw------- 1 postgres postgres 51M May 5 12:50 219436265
> -rw------- 1 postgres postgres 15M May 5 12:50 218478745
> -rw------- 1 postgres postgres 11M May 5 12:50 218478744
> -rw------- 1 postgres postgres 10M May 5 12:50 218478743
> -rw------- 1 postgres postgres 13M May 5 12:50 218478742
> -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1

the output from some $ ls command on
/usr/local/pgsql/data/base/95186722? You say there is nothing else in
that directory, but those sizes don't add up to 51Gb so something is
messed up somewhere.

Maybe this is nothing to do with postgres and a reboot and/or fsck of
the disk will fix everything.


--
Ian.


On Thu, May 6, 2010 at 7:33 AM, Khangelani Gama
wrote:
> Thanks again for the replies:
>
>
> Responding to Kevin, Ian, and Inigo:
>
>
>
> 1. We'll try an change the order in the script
> 2. Daily vacuum analyze was disabled, which was running from a different =
script, we'll try to put it back.
> 3. The monthly script does work.
> 4. In my original email about the directory that takes more space:
>
> /usr/local/pgsql/data/base directory shows the following where 95186722/ =
takes a lot of space =A0:
>
> 3.6M =A0 =A0./1
> 3.6M =A0 =A0./16975
> 51G =A0 =A0 ./95186722
> 4.8M =A0 =A0./4830693
> 51G =A0 =A0 .
>
>
>
> There is nothing else in /usr/local/pgsql/data/base/95186722/ =A0director=
y except the table space names with different spaces for each one of them. =
And in other servers these table space sizes are now at 1.0G, for an exampl=
e there is about 15 of them which makes about 15G.
>
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943=
6402
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943=
6401
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943=
6400
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A016K May =A05 12:50 21943=
6399
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A034M May =A05 12:50 21943=
6274
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943=
6273
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A043M May =A05 12:50 21943=
6272
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943=
6271
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A034M May =A05 12:50 21943=
6270
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943=
6269
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A042M May =A05 12:50 21943=
6268
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A051M May =A05 12:50 21943=
6267
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A034M May =A05 12:50 21943=
6266
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A051M May =A05 12:50 21943=
6265
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A015M May =A05 12:50 21847=
8745
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A011M May =A05 12:50 21847=
8744
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A010M May =A05 12:50 21847=
8743
> -rw------- =A0 =A01 postgres postgres =A0 =A0 =A013M May =A05 12:50 21847=
8742
> -rw------- =A0 =A01 postgres postgres =A0 =A0 440M May =A05 12:50 2160819=
69.1
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Wednesday, May 05, 2010 3:59 PM
> To: pgsql-admin@postgresql.org; Khangelani Gama
> Subject: Re: [ADMIN] Table space grow big - PostgreSQL
>
> Khangelani Gama wrote:
>
>> There is a script that runs once a month
>
> Most likely that should be daily, or at least weekly.
>
>> "REINDEX TABLE ${table}"
>> "VACUUM FULL VERBOSE ${table}"
>> "VACUUM ANALYZE ${table}"
>
> That's the wrong order. =A0Try:
>
> "VACUUM FULL VERBOSE ANALYZE ${table}"
> "REINDEX TABLE ${table}"
>
> With your current order, the VACUUM FULL bloats the indexes you've
> just rebuilt.
>
> If you vacuum frequently enough, you should not need to use the FULL
> option.
>
>> Dumping and restoring the database doesn't decrease the space
>
> Now, that's odd. =A0You're not restoring back into the same database
> without dropping it first (using the "clean" option), are you?
> Perhaps you have some very wide indexes, or a very large number of
> small tables?
>
>> There is nothing set in the postgresql.conf file that has to do
>> with vacuum analyze.
>
> Well, that wasn't the only thing I would look for; however, I'm not
> sure how many of the things I usually check exist in 7.3 or work the
> same way. =A0:-(
>
> -Kevin
>
> The contents of and attachments to this e-mail are intended for the addre=
ssee only, and may contain the confidential information of UCS Group and/or=
its subsidiaries. =A0Any review, use or dissemination thereof by anyone ot=
her than the intended addressee is prohibited. =A0If you are not the intend=
ed addressee please notify the writer immediately and destroy the e-mail. =
=A0UCS Group Limited and its subsidiaries distance themselves from and acce=
pt no liability for unauthorised use of their e-mail facilities or e-mails =
sent other than strictly for business purposes.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

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

Re: Table space grow big - PostgreSQL

An Example from another server where the directory grew so big:


/data/postgres7.3.4/data/base/31057006/ directory shows the following size:

4.0K ./pgsql_tmp
34G .


A 34GB, it's those tablespaces with different sizes for each in 31057006/ d=
irectory.



We dumped the database using pg_dump, the dump file was 9.8G of size. We in=
stalled a brand new disc and then created a new database name then imported=
the data from the dump file of 9.8GB size.

Then I checked the size of /data/postgres7.3.4/data/base/16976/ new directo=
ry from the new disc. The size looks as follows:

4.0K ./pgsql_tmp
6.0G .



6.0G is way smaller then the 34G which was in the old disc.


The reboot does work, I am not sure the fsck command could fix the problem.

Note: there is nothing else that is inside the directory that's showing thi=
s big sizes.


We need a root cause that grow the size so big in the directory I mentioned.














-----Original Message-----
From: Ian Lea [mailto:ian.lea@gmail.com]
Sent: Thursday, May 06, 2010 10:49 AM
To: Khangelani Gama
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Table space grow big - PostgreSQL

I'm still confused about the disk space, even more so now that more
servers and figures of 1Gb and 15Gb have joined the party.

Presumably this:

> 3.6M ./1
> 3.6M ./16975
> 51G ./95186722
> 4.8M ./4830693
> 51G .

is the output from some $ du command. And is this

> -rw------- 1 postgres postgres 16K May 5 12:50 219436402
> -rw------- 1 postgres postgres 16K May 5 12:50 219436401
> -rw------- 1 postgres postgres 16K May 5 12:50 219436400
> -rw------- 1 postgres postgres 16K May 5 12:50 219436399
> -rw------- 1 postgres postgres 34M May 5 12:50 219436274
> -rw------- 1 postgres postgres 42M May 5 12:50 219436273
> -rw------- 1 postgres postgres 43M May 5 12:50 219436272
> -rw------- 1 postgres postgres 42M May 5 12:50 219436271
> -rw------- 1 postgres postgres 34M May 5 12:50 219436270
> -rw------- 1 postgres postgres 42M May 5 12:50 219436269
> -rw------- 1 postgres postgres 42M May 5 12:50 219436268
> -rw------- 1 postgres postgres 51M May 5 12:50 219436267
> -rw------- 1 postgres postgres 34M May 5 12:50 219436266
> -rw------- 1 postgres postgres 51M May 5 12:50 219436265
> -rw------- 1 postgres postgres 15M May 5 12:50 218478745
> -rw------- 1 postgres postgres 11M May 5 12:50 218478744
> -rw------- 1 postgres postgres 10M May 5 12:50 218478743
> -rw------- 1 postgres postgres 13M May 5 12:50 218478742
> -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1

the output from some $ ls command on
/usr/local/pgsql/data/base/95186722? You say there is nothing else in
that directory, but those sizes don't add up to 51Gb so something is
messed up somewhere.

Maybe this is nothing to do with postgres and a reboot and/or fsck of
the disk will fix everything.


--
Ian.


On Thu, May 6, 2010 at 7:33 AM, Khangelani Gama
wrote:
> Thanks again for the replies:
>
>
> Responding to Kevin, Ian, and Inigo:
>
>
>
> 1. We'll try an change the order in the script
> 2. Daily vacuum analyze was disabled, which was running from a different =
script, we'll try to put it back.
> 3. The monthly script does work.
> 4. In my original email about the directory that takes more space:
>
> /usr/local/pgsql/data/base directory shows the following where 95186722/ =
takes a lot of space :
>
> 3.6M ./1
> 3.6M ./16975
> 51G ./95186722
> 4.8M ./4830693
> 51G .
>
>
>
> There is nothing else in /usr/local/pgsql/data/base/95186722/ directory =
except the table space names with different spaces for each one of them. An=
d in other servers these table space sizes are now at 1.0G, for an example =
there is about 15 of them which makes about 15G.
>
> -rw------- 1 postgres postgres 16K May 5 12:50 219436402
> -rw------- 1 postgres postgres 16K May 5 12:50 219436401
> -rw------- 1 postgres postgres 16K May 5 12:50 219436400
> -rw------- 1 postgres postgres 16K May 5 12:50 219436399
> -rw------- 1 postgres postgres 34M May 5 12:50 219436274
> -rw------- 1 postgres postgres 42M May 5 12:50 219436273
> -rw------- 1 postgres postgres 43M May 5 12:50 219436272
> -rw------- 1 postgres postgres 42M May 5 12:50 219436271
> -rw------- 1 postgres postgres 34M May 5 12:50 219436270
> -rw------- 1 postgres postgres 42M May 5 12:50 219436269
> -rw------- 1 postgres postgres 42M May 5 12:50 219436268
> -rw------- 1 postgres postgres 51M May 5 12:50 219436267
> -rw------- 1 postgres postgres 34M May 5 12:50 219436266
> -rw------- 1 postgres postgres 51M May 5 12:50 219436265
> -rw------- 1 postgres postgres 15M May 5 12:50 218478745
> -rw------- 1 postgres postgres 11M May 5 12:50 218478744
> -rw------- 1 postgres postgres 10M May 5 12:50 218478743
> -rw------- 1 postgres postgres 13M May 5 12:50 218478742
> -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Wednesday, May 05, 2010 3:59 PM
> To: pgsql-admin@postgresql.org; Khangelani Gama
> Subject: Re: [ADMIN] Table space grow big - PostgreSQL
>
> Khangelani Gama wrote:
>
>> There is a script that runs once a month
>
> Most likely that should be daily, or at least weekly.
>
>> "REINDEX TABLE ${table}"
>> "VACUUM FULL VERBOSE ${table}"
>> "VACUUM ANALYZE ${table}"
>
> That's the wrong order. Try:
>
> "VACUUM FULL VERBOSE ANALYZE ${table}"
> "REINDEX TABLE ${table}"
>
> With your current order, the VACUUM FULL bloats the indexes you've
> just rebuilt.
>
> If you vacuum frequently enough, you should not need to use the FULL
> option.
>
>> Dumping and restoring the database doesn't decrease the space
>
> Now, that's odd. You're not restoring back into the same database
> without dropping it first (using the "clean" option), are you?
> Perhaps you have some very wide indexes, or a very large number of
> small tables?
>
>> There is nothing set in the postgresql.conf file that has to do
>> with vacuum analyze.
>
> Well, that wasn't the only thing I would look for; however, I'm not
> sure how many of the things I usually check exist in 7.3 or work the
> same way. :-(
>
> -Kevin
>
> The contents of and attachments to this e-mail are intended for the addre=
ssee only, and may contain the confidential information of UCS Group and/or=
its subsidiaries. Any review, use or dissemination thereof by anyone othe=
r than the intended addressee is prohibited. If you are not the intended a=
ddressee please notify the writer immediately and destroy the e-mail. UCS =
Group Limited and its subsidiaries distance themselves from and accept no l=
iability for unauthorised use of their e-mail facilities or e-mails sent ot=
her than strictly for business purposes.
>
> --
> 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 address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other =
than the intended addressee is prohibited. If you are not the intended add=
ressee please notify the writer immediately and destroy the e-mail. UCS Gr=
oup Limited and its subsidiaries distance themselves from and accept no lia=
bility for unauthorised use of their e-mail facilities or e-mails sent othe=
r than strictly for business purposes.

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

Re: Table space grow big - PostgreSQL

Sorry in my previous comments I meant the "reboot does not work or does not=
make a difference"



-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql=
..org] On Behalf Of Khangelani Gama
Sent: Thursday, May 06, 2010 11:28 AM
To: pgsql-admin@postgresql.org
Cc: Ian Lea; Nico Botha
Subject: Re: [ADMIN] Table space grow big - PostgreSQL

An Example from another server where the directory grew so big:


/data/postgres7.3.4/data/base/31057006/ directory shows the following size:

4.0K ./pgsql_tmp
34G .


A 34GB, it's those tablespaces with different sizes for each in 31057006/ d=
irectory.



We dumped the database using pg_dump, the dump file was 9.8G of size. We in=
stalled a brand new disc and then created a new database name then imported=
the data from the dump file of 9.8GB size.

Then I checked the size of /data/postgres7.3.4/data/base/16976/ new directo=
ry from the new disc. The size looks as follows:

4.0K ./pgsql_tmp
6.0G .



6.0G is way smaller then the 34G which was in the old disc.


The reboot does work, I am not sure the fsck command could fix the problem.

Note: there is nothing else that is inside the directory that's showing thi=
s big sizes.


We need a root cause that grow the size so big in the directory I mentioned.














-----Original Message-----
From: Ian Lea [mailto:ian.lea@gmail.com]
Sent: Thursday, May 06, 2010 10:49 AM
To: Khangelani Gama
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Table space grow big - PostgreSQL

I'm still confused about the disk space, even more so now that more
servers and figures of 1Gb and 15Gb have joined the party.

Presumably this:

> 3.6M ./1
> 3.6M ./16975
> 51G ./95186722
> 4.8M ./4830693
> 51G .

is the output from some $ du command. And is this

> -rw------- 1 postgres postgres 16K May 5 12:50 219436402
> -rw------- 1 postgres postgres 16K May 5 12:50 219436401
> -rw------- 1 postgres postgres 16K May 5 12:50 219436400
> -rw------- 1 postgres postgres 16K May 5 12:50 219436399
> -rw------- 1 postgres postgres 34M May 5 12:50 219436274
> -rw------- 1 postgres postgres 42M May 5 12:50 219436273
> -rw------- 1 postgres postgres 43M May 5 12:50 219436272
> -rw------- 1 postgres postgres 42M May 5 12:50 219436271
> -rw------- 1 postgres postgres 34M May 5 12:50 219436270
> -rw------- 1 postgres postgres 42M May 5 12:50 219436269
> -rw------- 1 postgres postgres 42M May 5 12:50 219436268
> -rw------- 1 postgres postgres 51M May 5 12:50 219436267
> -rw------- 1 postgres postgres 34M May 5 12:50 219436266
> -rw------- 1 postgres postgres 51M May 5 12:50 219436265
> -rw------- 1 postgres postgres 15M May 5 12:50 218478745
> -rw------- 1 postgres postgres 11M May 5 12:50 218478744
> -rw------- 1 postgres postgres 10M May 5 12:50 218478743
> -rw------- 1 postgres postgres 13M May 5 12:50 218478742
> -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1

the output from some $ ls command on
/usr/local/pgsql/data/base/95186722? You say there is nothing else in
that directory, but those sizes don't add up to 51Gb so something is
messed up somewhere.

Maybe this is nothing to do with postgres and a reboot and/or fsck of
the disk will fix everything.


--
Ian.


On Thu, May 6, 2010 at 7:33 AM, Khangelani Gama
wrote:
> Thanks again for the replies:
>
>
> Responding to Kevin, Ian, and Inigo:
>
>
>
> 1. We'll try an change the order in the script
> 2. Daily vacuum analyze was disabled, which was running from a different =
script, we'll try to put it back.
> 3. The monthly script does work.
> 4. In my original email about the directory that takes more space:
>
> /usr/local/pgsql/data/base directory shows the following where 95186722/ =
takes a lot of space :
>
> 3.6M ./1
> 3.6M ./16975
> 51G ./95186722
> 4.8M ./4830693
> 51G .
>
>
>
> There is nothing else in /usr/local/pgsql/data/base/95186722/ directory =
except the table space names with different spaces for each one of them. An=
d in other servers these table space sizes are now at 1.0G, for an example =
there is about 15 of them which makes about 15G.
>
> -rw------- 1 postgres postgres 16K May 5 12:50 219436402
> -rw------- 1 postgres postgres 16K May 5 12:50 219436401
> -rw------- 1 postgres postgres 16K May 5 12:50 219436400
> -rw------- 1 postgres postgres 16K May 5 12:50 219436399
> -rw------- 1 postgres postgres 34M May 5 12:50 219436274
> -rw------- 1 postgres postgres 42M May 5 12:50 219436273
> -rw------- 1 postgres postgres 43M May 5 12:50 219436272
> -rw------- 1 postgres postgres 42M May 5 12:50 219436271
> -rw------- 1 postgres postgres 34M May 5 12:50 219436270
> -rw------- 1 postgres postgres 42M May 5 12:50 219436269
> -rw------- 1 postgres postgres 42M May 5 12:50 219436268
> -rw------- 1 postgres postgres 51M May 5 12:50 219436267
> -rw------- 1 postgres postgres 34M May 5 12:50 219436266
> -rw------- 1 postgres postgres 51M May 5 12:50 219436265
> -rw------- 1 postgres postgres 15M May 5 12:50 218478745
> -rw------- 1 postgres postgres 11M May 5 12:50 218478744
> -rw------- 1 postgres postgres 10M May 5 12:50 218478743
> -rw------- 1 postgres postgres 13M May 5 12:50 218478742
> -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Wednesday, May 05, 2010 3:59 PM
> To: pgsql-admin@postgresql.org; Khangelani Gama
> Subject: Re: [ADMIN] Table space grow big - PostgreSQL
>
> Khangelani Gama wrote:
>
>> There is a script that runs once a month
>
> Most likely that should be daily, or at least weekly.
>
>> "REINDEX TABLE ${table}"
>> "VACUUM FULL VERBOSE ${table}"
>> "VACUUM ANALYZE ${table}"
>
> That's the wrong order. Try:
>
> "VACUUM FULL VERBOSE ANALYZE ${table}"
> "REINDEX TABLE ${table}"
>
> With your current order, the VACUUM FULL bloats the indexes you've
> just rebuilt.
>
> If you vacuum frequently enough, you should not need to use the FULL
> option.
>
>> Dumping and restoring the database doesn't decrease the space
>
> Now, that's odd. You're not restoring back into the same database
> without dropping it first (using the "clean" option), are you?
> Perhaps you have some very wide indexes, or a very large number of
> small tables?
>
>> There is nothing set in the postgresql.conf file that has to do
>> with vacuum analyze.
>
> Well, that wasn't the only thing I would look for; however, I'm not
> sure how many of the things I usually check exist in 7.3 or work the
> same way. :-(
>
> -Kevin
>
> The contents of and attachments to this e-mail are intended for the addre=
ssee only, and may contain the confidential information of UCS Group and/or=
its subsidiaries. Any review, use or dissemination thereof by anyone othe=
r than the intended addressee is prohibited. If you are not the intended a=
ddressee please notify the writer immediately and destroy the e-mail. UCS =
Group Limited and its subsidiaries distance themselves from and accept no l=
iability for unauthorised use of their e-mail facilities or e-mails sent ot=
her than strictly for business purposes.
>
> --
> 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 address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other =
than the intended addressee is prohibited. If you are not the intended add=
ressee please notify the writer immediately and destroy the e-mail. UCS Gr=
oup Limited and its subsidiaries distance themselves from and accept no lia=
bility for unauthorised use of their e-mail facilities or e-mails sent othe=
r than strictly for business purposes.

--
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 address=
ee only, and may contain the confidential information of UCS Group and/or i=
ts subsidiaries. Any review, use or dissemination thereof by anyone other =
than the intended addressee is prohibited. If you are not the intended add=
ressee please notify the writer immediately and destroy the e-mail. UCS Gr=
oup Limited and its subsidiaries distance themselves from and accept no lia=
bility for unauthorised use of their e-mail facilities or e-mails sent othe=
r than strictly for business purposes.

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

Re: Table space grow big - PostgreSQL

--=-j55ABQF3uNh4XoEqfVgp
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit

I'm agree with ian.
There is something strange with your filesys...

Can you exec an 'ls -la' ? Perhaps somebody has move something else in
that folder with hidden name ( with a dot in folder name )
And, of course, a fsck should be advisable. :)

-----Original Message-----
From: Ian Lea
To: Khangelani Gama
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Table space grow big - PostgreSQL
Date: Thu, 6 May 2010 09:49:19 +0100


I'm still confused about the disk space, even more so now that more
servers and figures of 1Gb and 15Gb have joined the party.

Presumably this:

> 3.6M ./1
> 3.6M ./16975
> 51G ./95186722
> 4.8M ./4830693
> 51G .

is the output from some $ du command. And is this

> -rw------- 1 postgres postgres 16K May 5 12:50 219436402
> -rw------- 1 postgres postgres 16K May 5 12:50 219436401
> -rw------- 1 postgres postgres 16K May 5 12:50 219436400
> -rw------- 1 postgres postgres 16K May 5 12:50 219436399
> -rw------- 1 postgres postgres 34M May 5 12:50 219436274
> -rw------- 1 postgres postgres 42M May 5 12:50 219436273
> -rw------- 1 postgres postgres 43M May 5 12:50 219436272
> -rw------- 1 postgres postgres 42M May 5 12:50 219436271
> -rw------- 1 postgres postgres 34M May 5 12:50 219436270
> -rw------- 1 postgres postgres 42M May 5 12:50 219436269
> -rw------- 1 postgres postgres 42M May 5 12:50 219436268
> -rw------- 1 postgres postgres 51M May 5 12:50 219436267
> -rw------- 1 postgres postgres 34M May 5 12:50 219436266
> -rw------- 1 postgres postgres 51M May 5 12:50 219436265
> -rw------- 1 postgres postgres 15M May 5 12:50 218478745
> -rw------- 1 postgres postgres 11M May 5 12:50 218478744
> -rw------- 1 postgres postgres 10M May 5 12:50 218478743
> -rw------- 1 postgres postgres 13M May 5 12:50 218478742
> -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1

the output from some $ ls command on
/usr/local/pgsql/data/base/95186722? You say there is nothing else in
that directory, but those sizes don't add up to 51Gb so something is
messed up somewhere.

Maybe this is nothing to do with postgres and a reboot and/or fsck of
the disk will fix everything.


--
Ian.


On Thu, May 6, 2010 at 7:33 AM, Khangelani Gama
wrote:
> Thanks again for the replies:
>
>
> Responding to Kevin, Ian, and Inigo:
>
>
>
> 1. We'll try an change the order in the script
> 2. Daily vacuum analyze was disabled, which was running from a different script, we'll try to put it back.
> 3. The monthly script does work.
> 4. In my original email about the directory that takes more space:
>
> /usr/local/pgsql/data/base directory shows the following where 95186722/ takes a lot of space :
>
> 3.6M ./1
> 3.6M ./16975
> 51G ./95186722
> 4.8M ./4830693
> 51G .
>
>
>
> There is nothing else in /usr/local/pgsql/data/base/95186722/ directory except the table space names with different spaces for each one of them. And in other servers these table space sizes are now at 1.0G, for an example there is about 15 of them which makes about 15G.
>
> -rw------- 1 postgres postgres 16K May 5 12:50 219436402
> -rw------- 1 postgres postgres 16K May 5 12:50 219436401
> -rw------- 1 postgres postgres 16K May 5 12:50 219436400
> -rw------- 1 postgres postgres 16K May 5 12:50 219436399
> -rw------- 1 postgres postgres 34M May 5 12:50 219436274
> -rw------- 1 postgres postgres 42M May 5 12:50 219436273
> -rw------- 1 postgres postgres 43M May 5 12:50 219436272
> -rw------- 1 postgres postgres 42M May 5 12:50 219436271
> -rw------- 1 postgres postgres 34M May 5 12:50 219436270
> -rw------- 1 postgres postgres 42M May 5 12:50 219436269
> -rw------- 1 postgres postgres 42M May 5 12:50 219436268
> -rw------- 1 postgres postgres 51M May 5 12:50 219436267
> -rw------- 1 postgres postgres 34M May 5 12:50 219436266
> -rw------- 1 postgres postgres 51M May 5 12:50 219436265
> -rw------- 1 postgres postgres 15M May 5 12:50 218478745
> -rw------- 1 postgres postgres 11M May 5 12:50 218478744
> -rw------- 1 postgres postgres 10M May 5 12:50 218478743
> -rw------- 1 postgres postgres 13M May 5 12:50 218478742
> -rw------- 1 postgres postgres 440M May 5 12:50 216081969.1
>
>
>
>
>
>
>
>
>
>
>
>
> -----Original Message-----
> From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]
> Sent: Wednesday, May 05, 2010 3:59 PM
> To: pgsql-admin@postgresql.org; Khangelani Gama
> Subject: Re: [ADMIN] Table space grow big - PostgreSQL
>
> Khangelani Gama wrote:
>
>> There is a script that runs once a month
>
> Most likely that should be daily, or at least weekly.
>
>> "REINDEX TABLE ${table}"
>> "VACUUM FULL VERBOSE ${table}"
>> "VACUUM ANALYZE ${table}"
>
> That's the wrong order. Try:
>
> "VACUUM FULL VERBOSE ANALYZE ${table}"
> "REINDEX TABLE ${table}"
>
> With your current order, the VACUUM FULL bloats the indexes you've
> just rebuilt.
>
> If you vacuum frequently enough, you should not need to use the FULL
> option.
>
>> Dumping and restoring the database doesn't decrease the space
>
> Now, that's odd. You're not restoring back into the same database
> without dropping it first (using the "clean" option), are you?
> Perhaps you have some very wide indexes, or a very large number of
> small tables?
>
>> There is nothing set in the postgresql.conf file that has to do
>> with vacuum analyze.
>
> Well, that wasn't the only thing I would look for; however, I'm not
> sure how many of the things I usually check exist in 7.3 or work the
> same way. :-(
>
> -Kevin
>
> 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.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>




--=-j55ABQF3uNh4XoEqfVgp
Content-Type: text/html; charset="utf-8"
Content-Transfer-Encoding: 7bit








I'm agree with ian.

There is something strange with your filesys...



Can you exec an 'ls -la' ? Perhaps somebody has move something else in that folder with hidden name ( with a dot in folder name )

And, of course, a fsck should be advisable. :) 



-----Original Message-----

From: Ian Lea <>

To: Khangelani Gama <>

Cc: pgsql-admin@postgresql.org <>

Subject: Re: [ADMIN] Table space grow big - PostgreSQL

Date: Thu, 6 May 2010 09:49:19 +0100

I'm still confused about the disk space, even more so now that more

servers and figures of 1Gb and 15Gb have joined the party.



Presumably this:



> 3.6M    ./1

> 3.6M    ./16975

> 51G     ./95186722

> 4.8M    ./4830693

> 51G     .



is the output from some $ du command.  And is this



> -rw-------    1 postgres postgres      16K May  5 12:50 219436402

> -rw-------    1 postgres postgres      16K May  5 12:50 219436401

> -rw-------    1 postgres postgres      16K May  5 12:50 219436400

> -rw-------    1 postgres postgres      16K May  5 12:50 219436399

> -rw-------    1 postgres postgres      34M May  5 12:50 219436274

> -rw-------    1 postgres postgres      42M May  5 12:50 219436273

> -rw-------    1 postgres postgres      43M May  5 12:50 219436272

> -rw-------    1 postgres postgres      42M May  5 12:50 219436271

> -rw-------    1 postgres postgres      34M May  5 12:50 219436270

> -rw-------    1 postgres postgres      42M May  5 12:50 219436269

> -rw-------    1 postgres postgres      42M May  5 12:50 219436268

> -rw-------    1 postgres postgres      51M May  5 12:50 219436267

> -rw-------    1 postgres postgres      34M May  5 12:50 219436266

> -rw-------    1 postgres postgres      51M May  5 12:50 219436265

> -rw-------    1 postgres postgres      15M May  5 12:50 218478745

> -rw-------    1 postgres postgres      11M May  5 12:50 218478744

> -rw-------    1 postgres postgres      10M May  5 12:50 218478743

> -rw-------    1 postgres postgres      13M May  5 12:50 218478742

> -rw-------    1 postgres postgres     440M May  5 12:50 216081969.1



the output from some $ ls command on

/usr/local/pgsql/data/base/95186722?  You say there is nothing else in

that directory, but those sizes don't add up to 51Gb so something is

messed up somewhere.



Maybe this is nothing to do with postgres and a reboot and/or fsck of

the disk will fix everything.





--

Ian.





On Thu, May 6, 2010 at 7:33 AM, Khangelani Gama

<> wrote:

> Thanks again for the replies:

>

>

> Responding to Kevin, Ian, and Inigo:

>

>

>

> 1. We'll try an change the order in the script

> 2. Daily vacuum analyze was disabled, which was running from a different script, we'll try to put it back.

> 3. The monthly script does work.

> 4. In my original email about the directory that takes more space:

>

> /usr/local/pgsql/data/base directory shows the following where 95186722/ takes a lot of space  :

>

> 3.6M    ./1

> 3.6M    ./16975

> 51G     ./95186722

> 4.8M    ./4830693

> 51G     .

>

>

>

> There is nothing else in /usr/local/pgsql/data/base/95186722/  directory except the table space names with different spaces for each one of them. And in other servers these table space sizes are now at 1.0G, for an example there is about 15 of them which makes about 15G.

>

> -rw-------    1 postgres postgres      16K May  5 12:50 219436402

> -rw-------    1 postgres postgres      16K May  5 12:50 219436401

> -rw-------    1 postgres postgres      16K May  5 12:50 219436400

> -rw-------    1 postgres postgres      16K May  5 12:50 219436399

> -rw-------    1 postgres postgres      34M May  5 12:50 219436274

> -rw-------    1 postgres postgres      42M May  5 12:50 219436273

> -rw-------    1 postgres postgres      43M May  5 12:50 219436272

> -rw-------    1 postgres postgres      42M May  5 12:50 219436271

> -rw-------    1 postgres postgres      34M May  5 12:50 219436270

> -rw-------    1 postgres postgres      42M May  5 12:50 219436269

> -rw-------    1 postgres postgres      42M May  5 12:50 219436268

> -rw-------    1 postgres postgres      51M May  5 12:50 219436267

> -rw-------    1 postgres postgres      34M May  5 12:50 219436266

> -rw-------    1 postgres postgres      51M May  5 12:50 219436265

> -rw-------    1 postgres postgres      15M May  5 12:50 218478745

> -rw-------    1 postgres postgres      11M May  5 12:50 218478744

> -rw-------    1 postgres postgres      10M May  5 12:50 218478743

> -rw-------    1 postgres postgres      13M May  5 12:50 218478742

> -rw-------    1 postgres postgres     440M May  5 12:50 216081969.1

>

>

>

>

>

>

>

>

>

>

>

>

> -----Original Message-----

> From: Kevin Grittner []

> Sent: Wednesday, May 05, 2010 3:59 PM

> To: ; Khangelani Gama

> Subject: Re: [ADMIN] Table space grow big - PostgreSQL

>

> Khangelani Gama <> wrote:

>

>> There is a script that runs once a month

>

> Most likely that should be daily, or at least weekly.

>

>> "REINDEX TABLE ${table}"

>> "VACUUM FULL VERBOSE ${table}"

>> "VACUUM ANALYZE ${table}"

>

> That's the wrong order.  Try:

>

> "VACUUM FULL VERBOSE ANALYZE ${table}"

> "REINDEX TABLE ${table}"

>

> With your current order, the VACUUM FULL bloats the indexes you've

> just rebuilt.

>

> If you vacuum frequently enough, you should not need to use the FULL

> option.

>

>> Dumping and restoring the database doesn't decrease the space

>

> Now, that's odd.  You're not restoring back into the same database

> without dropping it first (using the "clean" option), are you?

> Perhaps you have some very wide indexes, or a very large number of

> small tables?

>

>> There is nothing set in the postgresql.conf file that has to do

>> with vacuum analyze.

>

> Well, that wasn't the only thing I would look for; however, I'm not

> sure how many of the things I usually check exist in 7.3 or work the

> same way.  :-(

>

> -Kevin

>

> 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.

>

> --

> Sent via pgsql-admin mailing list ()

> To make changes to your subscription:

> 

>