Thread: Vaccum

Hi :

Want to get your opinion.

I am thinking to schedule to run Vaccum everynight.

1) As I understand, Vaccum is the command to delete old data, right?
1) Do I have to stop the postmaster before run the Vaccum command?
2) Should I run it everynight?  How often should I run it?

thanks
Fushan

Hello,

You can use vacuum at different levels.

As a command you can use vacuumdb database_name.

In psql, you can use
psql> vacuum table_name;

I don't think it is going to delete records. To delete records you need to
issue SQL command:
like: delete from table_name where condi.

It must delete the cells. when you delete the reocrds, the space does not
get compressed.

You can check the size changes of a table or database before and after you
issue "vacuum" command.

You don't need to shut down postmoaster.

It would be nice if you do such jobs at midnight, because such a job does
keep your CPU busy.

Bangh

"Leong, Fushan" wrote:

> Hi :
>
> Want to get your opinion.
>
> I am thinking to schedule to run Vaccum everynight.
>
> 1) As I understand, Vaccum is the command to delete old data, right?
> 1) Do I have to stop the postmaster before run the Vaccum command?
> 2) Should I run it everynight?  How often should I run it?
>
> thanks
> Fushan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

HI,

This is not necessary true, but you might feel understandable.

To delete records, just seems as the records are marked as voided. Space is not
collected. To run vaccum might do this kind space garbage collection.

To run vacummdb really save the space, but it works at only some extent. e.g.
index still goes quickly, it goes bigger and bigger, one day it eats all your
space you have, behaves as gets a virus. In this case, my solution is to use
pg_dump the original one, create new one and restory, this reaaly save your
space, but you cannot do this as frequently as you do "vacuum". It costs much
more CPU time to do it if your database is huge.

Bangh

"Leong, Fushan" wrote:

> You said "when you delete the records, the space does not get compressed".
> Can you explain
> more for me.  The reason I want to run vacuumdb is to free up some space so
> the database will
> not grow forever..........
>
> -----Original Message-----
> From: bangh [mailto:banghe@baileylink.net]
> Sent: Thursday, November 29, 2001 10:01 AM
> To: Leong, Fushan
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Vaccum
>
> Hello,
>
> You can use vacuum at different levels.
>
> As a command you can use vacuumdb database_name.
>
> In psql, you can use
> psql> vacuum table_name;
>
> I don't think it is going to delete records. To delete records you need to
> issue SQL command:
> like: delete from table_name where condi.
>
> It must delete the cells. when you delete the reocrds, the space does not
> get compressed.
>
> You can check the size changes of a table or database before and after you
> issue "vacuum" command.
>
> You don't need to shut down postmoaster.
>
> It would be nice if you do such jobs at midnight, because such a job does
> keep your CPU busy.
>
> Bangh
>
> "Leong, Fushan" wrote:
>
> > Hi :
> >
> > Want to get your opinion.
> >
> > I am thinking to schedule to run Vaccum everynight.
> >
> > 1) As I understand, Vaccum is the command to delete old data, right?
> > 1) Do I have to stop the postmaster before run the Vaccum command?
> > 2) Should I run it everynight?  How often should I run it?
> >
> > thanks
> > Fushan
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org

You said "when you delete the records, the space does not get compressed".
Can you explain
more for me.  The reason I want to run vacuumdb is to free up some space so
the database will
not grow forever..........

-----Original Message-----
From: bangh [mailto:banghe@baileylink.net]
Sent: Thursday, November 29, 2001 10:01 AM
To: Leong, Fushan
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Vaccum


Hello,

You can use vacuum at different levels.

As a command you can use vacuumdb database_name.

In psql, you can use
psql> vacuum table_name;

I don't think it is going to delete records. To delete records you need to
issue SQL command:
like: delete from table_name where condi.

It must delete the cells. when you delete the reocrds, the space does not
get compressed.

You can check the size changes of a table or database before and after you
issue "vacuum" command.

You don't need to shut down postmoaster.

It would be nice if you do such jobs at midnight, because such a job does
keep your CPU busy.

Bangh

"Leong, Fushan" wrote:

> Hi :
>
> Want to get your opinion.
>
> I am thinking to schedule to run Vaccum everynight.
>
> 1) As I understand, Vaccum is the command to delete old data, right?
> 1) Do I have to stop the postmaster before run the Vaccum command?
> 2) Should I run it everynight?  How often should I run it?
>
> thanks
> Fushan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

On Thu, 29 Nov 2001, bangh wrote:

> HI,
>
> This is not necessary true, but you might feel understandable.
>
> To delete records, just seems as the records are marked as voided. Space is not
> collected. To run vaccum might do this kind space garbage collection.
>
> To run vacummdb really save the space, but it works at only some extent. e.g.
> index still goes quickly, it goes bigger and bigger, one day it eats all your
> space you have, behaves as gets a virus. In this case, my solution is to use
> pg_dump the original one, create new one and restory, this reaaly save your
> space, but you cannot do this as frequently as you do "vacuum". It costs much
> more CPU time to do it if your database is huge.

I think REINDEX or just DROP INDEX/CREATE INDEX should do the same thing
without requiring the dump/restore.

Thanks for everyone input.  So let me reply in here to make sure I
understand it

1) If I run "vaccum all" or "vaccum tablename", it will just delete the
expired rows from the file but not free the physical disk space.  However,
it will not free the index
2) Vaccumdb will delete the expired rows fromt the file and free the
physical disk space.  However, it will not free the index
3) For index, you need to run reindex command to stop the grow


Other than table and index, any object that I should clean up once a while?

Fushan

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, November 29, 2001 10:40 AM
To: bangh
Cc: Leong, Fushan; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Vaccum


On Thu, 29 Nov 2001, bangh wrote:

> HI,
>
> This is not necessary true, but you might feel understandable.
>
> To delete records, just seems as the records are marked as voided. Space
is not
> collected. To run vaccum might do this kind space garbage collection.
>
> To run vacummdb really save the space, but it works at only some extent.
e.g.
> index still goes quickly, it goes bigger and bigger, one day it eats all
your
> space you have, behaves as gets a virus. In this case, my solution is to
use
> pg_dump the original one, create new one and restory, this reaaly save
your
> space, but you cannot do this as frequently as you do "vacuum". It costs
much
> more CPU time to do it if your database is huge.

I think REINDEX or just DROP INDEX/CREATE INDEX should do the same thing
without requiring the dump/restore.

"Leong, Fushan" wrote:

> Thanks for everyone input.  So let me reply in here to make sure I
> understand it
>
> 1) If I run "vaccum all" or "vaccum tablename", it will just delete the
> expired rows from the file but not free the physical disk space.

Why not? it is the same thing as vacuumdb, the difference is that the function
is called
as different name at different level (or location, in psql, it is vaccum, in
shell, it is vacuumdb.

To Vacuum is not to delete records. Say "free" or "space garbage collection" or
"clear up"
 may be better.

> However,
> it will not free the index
> 2) Vaccumdb will delete the expired rows fromt the file and free the
> physical disk space.  However, it will not free the index
> 3) For index, you need to run reindex command to stop the grow

Either index or table in a database will grow as long as you don't stop to use
that database. The measure we are talking is a way to avoid wasting of the
space.

Bangh

>
>
> Other than table and index, any object that I should clean up once a while?
>
> Fushan
>
> -----Original Message-----
> From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
> Sent: Thursday, November 29, 2001 10:40 AM
> To: bangh
> Cc: Leong, Fushan; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Vaccum
>
> On Thu, 29 Nov 2001, bangh wrote:
>
> > HI,
> >
> > This is not necessary true, but you might feel understandable.
> >
> > To delete records, just seems as the records are marked as voided. Space
> is not
> > collected. To run vaccum might do this kind space garbage collection.
> >
> > To run vacummdb really save the space, but it works at only some extent.
> e.g.
> > index still goes quickly, it goes bigger and bigger, one day it eats all
> your
> > space you have, behaves as gets a virus. In this case, my solution is to
> use
> > pg_dump the original one, create new one and restory, this reaaly save
> your
> > space, but you cannot do this as frequently as you do "vacuum". It costs
> much
> > more CPU time to do it if your database is huge.
>
> I think REINDEX or just DROP INDEX/CREATE INDEX should do the same thing
> without requiring the dump/restore.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

No problem, we are doing vaccume different tables at different times.

Because if you do vaccume it will decrease the performace of data base

you can write scripts like this.

-----cleanTable.sh--------------------

#!/sbin/sh
echo "############## START:## Clean Table  $1 ##"
/usr/local/pgsql/bin/psql -U xxxxx -d xxxxxxx << end
vacuum  verbose  analyze $1;
\q
end
echo "############## END:## Clean Table  $1 ##"

-----cleanDB.sh--------------------------

#!/sbin/sh

#the following entry should be made in crontab of postgresql account
#5      *       *       *       *       $HOME/cleanDB.sh

echo "############## START:## Clean DB ## "`date '+%d/%m/%y %H:%M:%S'`
HOR=`date '+ %H'`
if [ $HOR -eq 19 ]; then
        /sbin/sh $HOME/cleanTable.sh aaaaaaaaaaa
        /sbin/sh $HOME/cleanTable.sh bbbbbbbbbbbb

elif [ $HOR -eq 20 ]; then
        /sbin/sh $HOME/cleanTable.sh cccccccccccc

elif [ $HOR -eq 21 ]; then
        /sbin/sh $HOME/cleanTable.sh dddddd
        /sbin/sh $HOME/cleanTable.sh eeeeeee

elif [ $HOR -eq 22 ]; then
        /sbin/sh $HOME/cleanTable.sh ffffffff
        /sbin/sh $HOME/cleanTable.sh ggggggggggggg

elif [ $HOR -eq 03 ]; then
        /sbin/sh $HOME/cleanTable.sh hhhhhhhhh

else
        echo "-------Nothing to vacuum--------"
fi
echo "############## END  :## Clean DB ## "`date '+%d/%m/%y %H:%M:%S'`    
                                                          

__________________________________                    

  "Leong, Fushan" <fushan.leong@SonoSite.com> wrote:

Hi :

Want to get your opinion.

I am thinking to schedule to run Vaccum everynight.

1) As I understand, Vaccum is the command to delete old data, right?
1) Do I have to stop the postmaster before run the Vaccum command?
2) Should I run it everynight? How often should I run it?

thanks
Fushan

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org