When do I Vacuum ?

could someone please post an answer, even if it's just a pointer to
somewhere else ... please ....
:-)

Steve

"Steve Brett" <steve.brett@e-mis.com> wrote in message
news:9ufpug$217v$1@news.tht.net...

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

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

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)

Steve,
Just to have an answer out there (being no expert); it seems to me that you
have very little traffic on your database, and vacuuming once a day is
probably plenty.
My database has several thousand inserts/edits/deletes per day and I've
found that I don't have significant performance changes if I forget to
vacuum every day.

Thanks,
Peter Darley

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Steve Brett
Sent: Monday, December 03, 2001 7:37 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] When do I Vacuum ?

could someone please post an answer, even if it's just a pointer to
somewhere else ... please ....
:-)

Steve

"Steve Brett" <steve.brett@e-mis.com> wrote in message
news:9ufpug$217v$1@news.tht.net...

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

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

http://archives.postgresql.org

"Steve Brett" <steve.brett@e-mis.com> writes:

could someone please post an answer, even if it's just a pointer to
somewhere else ... please ....

Sorry, I just got your message--looks like the list got hung up for a
little while.

The nice thing about VACUUM is that if you're running it "too often"
it won't have very much work to do so it'll complete very quickly. The
only thing to watch out for is lock contention between VACUUM and
"real work" (which is why Bruce suggests running it at quiet times).

So once an hour isn't going to hurt anything most likely, though it is
probably overkill for your current situation.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

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

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

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

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

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

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