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



We dumped the database using pg_dump, the dump file was 9.8G of size. We installed a brand new disc and then created a
newdatabase 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 directory 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 this 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
<Khangelani.Gama@ucs-software.co.za> 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
spacesfor each one of them. And in other servers these table space sizes are now at 1.0G, for an example there is about
15of 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 <Khangelani.Gama@ucs-software.co.za> 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
informationof UCS Group and/or its subsidiaries.  Any review, use or dissemination thereof by anyone other than the
intendedaddressee is prohibited.  If you are not the intended addressee please notify the writer immediately and
destroythe e-mail.  UCS Group Limited and its subsidiaries distance themselves from and accept no liability for
unauthoriseduse 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
>

The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
informationof UCS Group and/or its subsidiaries.  Any review, use or dissemination thereof by anyone other than the
intendedaddressee is prohibited.  If you are not the intended addressee please notify the writer immediately and
destroythe e-mail.  UCS Group Limited and its subsidiaries distance themselves from and accept no liability for
unauthoriseduse 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

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