Thread: When do I Vacuum ?

When do I Vacuum ?

From
"Steve Brett"
Date:
hi,

I've got a DB driven web application which currently has approx 298 inserts
per day and 540 edits/deletes.

This figure will change daily and currently i have a cron job set up to
vacuum the database every hour during 'work time':

0 7-19/1,23 * * *       /usr/local/pgsql/bin/vacuumdb -z -d edb
30 7-19/4,23 * * *      /backup/dumpit

I've also started reading Bruce's book (and what a fine book it is ... :-)
and noticed that he suggests vacuuming once a day when the db is quiet.

Would this be sufficient ? The database drives a web based
calendar/appointment booking/reporting system i've written in php and is
used extensively by 3 departments and has approx 200 people log in each day.

Needless to say speed it paramount but I've got a sneaky feeling I might be
vacuuming too often ....

Many thanks for you help,

Steve



Re: When do I Vacuum ?

From
"Carsten Gerhardt"
Date:
Hi Steve,

"Steve Brett" <steve.brett@e-mis.com> wrote
> hi,
>
> I've got a DB driven web application which currently has approx 298 inserts
> per day and 540 edits/deletes.
>
> This figure will change daily and currently i have a cron job set up to
> vacuum the database every hour during 'work time':
>
> 0 7-19/1,23 * * *       /usr/local/pgsql/bin/vacuumdb -z -d edb
> 30 7-19/4,23 * * *      /backup/dumpit

I do the same on my DB.

> I've also started reading Bruce's book (and what a fine book it is ... :-)
> and noticed that he suggests vacuuming once a day when the db is quiet.
>
> Would this be sufficient ? The database drives a web based
> calendar/appointment booking/reporting system i've written in php and is
> used extensively by 3 departments and has approx 200 people log in each day.
>
> Needless to say speed it paramount but I've got a sneaky feeling I might be
> vacuuming too often ....

I didn't read the book, but I also read that one vacuum per day would by
enough. But my DB runs on a P100 with 64MB ram :-( after 20 to 30
inserts it seems to slow down, so I vacuum once an houre (at main working time)
and now I've less problems 8)

HTH
  Carsten


Re: When do I Vacuum ?

From
Jan Wieck
Date:
Carsten Gerhardt wrote:
> I didn't read the book, but I also read that one vacuum per day would by
> enough. But my DB runs on a P100 with 64MB ram :-( after 20 to 30
> inserts it seems to slow down, so I vacuum once an houre (at main working time)
> and now I've less problems 8)

    That   sounds  like  you're  doing  some  joins  of  non-  or
    insufficient indexed tables, do you?

    The question how often to vacuum depends on the database use.
    As  a  rule  of  thumb  I'd suggest start with vacuuming when
    approx. 10-30% of the data has been touched (UPDATED/DELETED)
    and  play  around  with it from there.  It all depends pretty
    much on the DB usage profile of your application.

    Note that the behaviour of  vacuum  will  change  with  v7.2,
    making more frequent vacuum runs lesser painful.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Re: When do I Vacuum ?

From
"Roderick A. Anderson"
Date:
On Fri, 7 Dec 2001, Jan Wieck wrote:

>     The question how often to vacuum depends on the database use.
>     As  a  rule  of  thumb  I'd suggest start with vacuuming when
>     approx. 10-30% of the data has been touched (UPDATED/DELETED)

Which brings up an interesting thought.  Is there any way to query the
databae and find out how much data has been 'CHANGED' since the last
vacuum?  I don't remember seeing any mention of this for a year or
longer in either GENERAL or HACKERS.
   If there is or could be, a low usage database could be probed and
then vacumm run if needed.  Not sure if the CPU cycles to run the cron
job and query are lower than the 'just do it' on a set schedule would
be.


Cheers,
Rod
--
                      Let Accuracy Triumph Over Victory

                                                       Zetetic Institute
                                                        "David's Sling"
                                                         Marc Stiegler


Re: When do I Vacuum ?

From
Andrew Gould
Date:
One way of tracking changes would be a last_change
date field which would be updated every time a row was
updated.  Of course, if the database is big enough
that your worried about resource utilization by
vacuum, then you might not like the additional space
consumption of a purely administrative field.

Best of luck,

Andrew Gould

--- "Roderick A. Anderson" <raanders@tincan.org>
wrote:
> On Fri, 7 Dec 2001, Jan Wieck wrote:
>
> >     The question how often to vacuum depends on
> the database use.
> >     As  a  rule  of  thumb  I'd suggest start with
> vacuuming when
> >     approx. 10-30% of the data has been touched
> (UPDATED/DELETED)
>
> Which brings up an interesting thought.  Is there
> any way to query the
> databae and find out how much data has been
> 'CHANGED' since the last
> vacuum?  I don't remember seeing any mention of this
> for a year or
> longer in either GENERAL or HACKERS.
>    If there is or could be, a low usage database
> could be probed and
> then vacumm run if needed.  Not sure if the CPU
> cycles to run the cron
> job and query are lower than the 'just do it' on a
> set schedule would
> be.
>
>
> Cheers,
> Rod
> --
>                       Let Accuracy Triumph Over
> Victory
>
>
>   Zetetic Institute
>
>    "David's Sling"
>
>     Marc Stiegler
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to majordomo@postgresql.org
> so that your
> message can get through to the mailing list cleanly


__________________________________________________
Do You Yahoo!?
Send your FREE holiday greetings online!
http://greetings.yahoo.com

Re: When do I Vacuum ?

From
Jan Wieck
Date:
Andrew Gould wrote:
> One way of tracking changes would be a last_change
> date field which would be updated every time a row was
> updated.  Of course, if the database is big enough
> that your worried about resource utilization by
> vacuum, then you might not like the additional space
> consumption of a purely administrative field.

    You  can  activate the per table statistics collection in the
    postgres  config  file.   You'll  then  find  the  number  of
    INSERT/UPDATE/DELETEed tuples as well as number of sequential
    and index scans done per table  (since  the  last  postmaster
    restart by default) in pg_stat_user_tables.

    Now  you  setup  a  table  for  remembering  a timestamp, the
    current row count and these  access  counters  per  table.  A
    little  script  run  by  cron periodically remembers all this
    info.

    This historical data will give  you  a  detailed,  per  table
    access   profile  over  time,  so  you  can  setup  different
    vacuuming schedules per table.  Vacuum huge tables  with  low
    update  rate less frequent than small tables with high update
    rate, do the vacuuming  when  these  tables  get  the  lowest
    access, and you'll get the most out of your server.

    I  wanted  that  statistics collector not just for the fun of
    doing it.  For Joe-User's little WebDB  all  this  is  surely
    overkill.  But  somebody  running  a  serious  server  with a
    complex schema and a couple hundred MB of data might consider
    it beeing worth done.

    One  of  these  days  I  will  think  about a standard set of
    analyzis tools we can add to contrib. Stay  tuned  and  happy
    vacuuming.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Re: When do I Vacuum ?

From
"Steve Brett"
Date:
thanks.

i'm gonna leave it at onece a day and see how it goes.

thanks for answering btw , i was beginning to think this was one of those
questions nobody answered ...

Steve
"Carsten Gerhardt" <carsten.gerhardt@ppi.de> wrote in message
news:3c0bb397$0$188$4d4ebb8e@read.news.de.uu.net...
> Hi Steve,
>
> "Steve Brett" <steve.brett@e-mis.com> wrote
> > hi,
> >
> > I've got a DB driven web application which currently has approx 298
inserts
> > per day and 540 edits/deletes.
> >
> > This figure will change daily and currently i have a cron job set up to
> > vacuum the database every hour during 'work time':
> >
> > 0 7-19/1,23 * * *       /usr/local/pgsql/bin/vacuumdb -z -d edb
> > 30 7-19/4,23 * * *      /backup/dumpit
>
> I do the same on my DB.
>
> > I've also started reading Bruce's book (and what a fine book it is ...
:-)
> > and noticed that he suggests vacuuming once a day when the db is quiet.
> >
> > Would this be sufficient ? The database drives a web based
> > calendar/appointment booking/reporting system i've written in php and is
> > used extensively by 3 departments and has approx 200 people log in each
day.
> >
> > Needless to say speed it paramount but I've got a sneaky feeling I might
be
> > vacuuming too often ....
>
> I didn't read the book, but I also read that one vacuum per day would by
> enough. But my DB runs on a P100 with 64MB ram :-( after 20 to 30
> inserts it seems to slow down, so I vacuum once an houre (at main working
time)
> and now I've less problems 8)
>
> HTH
>   Carsten
>



Re: When do I Vacuum ?

From
"Steve Brett"
Date:
many thanks,

it's been vacuumed daily for a week or so now and actually seems a bit more
responsive.

Steve
""Roderick A. Anderson"" <raanders@tincan.org> wrote in message
news:Pine.LNX.4.10.10112070948530.29916-100000@tincan.org...
> On Fri, 7 Dec 2001, Jan Wieck wrote:
>
> >     The question how often to vacuum depends on the database use.
> >     As  a  rule  of  thumb  I'd suggest start with vacuuming when
> >     approx. 10-30% of the data has been touched (UPDATED/DELETED)
>
> Which brings up an interesting thought.  Is there any way to query the
> databae and find out how much data has been 'CHANGED' since the last
> vacuum?  I don't remember seeing any mention of this for a year or
> longer in either GENERAL or HACKERS.
>    If there is or could be, a low usage database could be probed and
> then vacumm run if needed.  Not sure if the CPU cycles to run the cron
> job and query are lower than the 'just do it' on a set schedule would
> be.
>
>
> Cheers,
> Rod
> --
>                       Let Accuracy Triumph Over Victory
>
>                                                        Zetetic Institute
>                                                         "David's Sling"
>                                                          Marc Stiegler
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



Re: When do I Vacuum ?

From
"Joshua -Poet- Drake"
Date:
Hello,

I always Vaccuum once a day... period. It just makes management a whole lot
easier.

J


"Steve Brett" <stevebrett@ntlworld.com> wrote in message
news:9uh450$29gd$1@news.tht.net...
> thanks.
>
> i'm gonna leave it at onece a day and see how it goes.
>
> thanks for answering btw , i was beginning to think this was one of those
> questions nobody answered ...
>
> Steve
> "Carsten Gerhardt" <carsten.gerhardt@ppi.de> wrote in message
> news:3c0bb397$0$188$4d4ebb8e@read.news.de.uu.net...
> > Hi Steve,
> >
> > "Steve Brett" <steve.brett@e-mis.com> wrote
> > > hi,
> > >
> > > I've got a DB driven web application which currently has approx 298
> inserts
> > > per day and 540 edits/deletes.
> > >
> > > This figure will change daily and currently i have a cron job set up
to
> > > vacuum the database every hour during 'work time':
> > >
> > > 0 7-19/1,23 * * *       /usr/local/pgsql/bin/vacuumdb -z -d edb
> > > 30 7-19/4,23 * * *      /backup/dumpit
> >
> > I do the same on my DB.
> >
> > > I've also started reading Bruce's book (and what a fine book it is ...
> :-)
> > > and noticed that he suggests vacuuming once a day when the db is
quiet.
> > >
> > > Would this be sufficient ? The database drives a web based
> > > calendar/appointment booking/reporting system i've written in php and
is
> > > used extensively by 3 departments and has approx 200 people log in
each
> day.
> > >
> > > Needless to say speed it paramount but I've got a sneaky feeling I
might
> be
> > > vacuuming too often ....
> >
> > I didn't read the book, but I also read that one vacuum per day would by
> > enough. But my DB runs on a P100 with 64MB ram :-( after 20 to 30
> > inserts it seems to slow down, so I vacuum once an houre (at main
working
> time)
> > and now I've less problems 8)
> >
> > HTH
> >   Carsten
> >
>
>