Thread: When do I Vacuum ?
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
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
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
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 >
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
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 > > > >