Thread: vacuuming and manu updates
Hi, I have a small table (100 rows) and I update its values every 8 seconds. This leads to poor performance until I vacuum. pg8.0 on w2000. Any suggestions on how often to vacuum or would I be better off doing delete and inserts for improved sustainable performance Thanks, Kim
On Wed, October 26, 2005 7:12 am, kadil said: > Hi, > > I have a small table (100 rows) and I update its values every 8 seconds. > This leads to poor performance until I vacuum. pg8.0 on w2000. Any > suggestions on how often to vacuum or would I be better off doing delete > and inserts for improved sustainable performance How long before the performance degradation in unacceptable? Vacuuming just that table shouldn't be much of a problem, so I'd say vacuum as often as necessary to keep performance within parameters. ;) Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
--- "Daniel T. Staal" <DStaal@usa.net> wrote: > On Wed, October 26, 2005 7:12 am, kadil said: > > Hi, > > > > I have a small table (100 rows) and I update its > values every 8 seconds. > > This leads to poor performance until I vacuum. > pg8.0 on w2000. Any > > suggestions on how often to vacuum or would I be > better off doing delete > > and inserts for improved sustainable performance > > How long before the performance degradation in > unacceptable? > > Vacuuming just that table shouldn't be much of a > problem, so I'd say > vacuum as often as necessary to keep performance > within parameters. ;) > > Daniel T. Staal is there a way to automate the vacuum process on windows (think cron on linux)? __________________________________ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com
On Wed, Oct 26, 2005 at 21:12:55 +1000, kadil <ksadil@bigpond.net.au> wrote: > Hi, > > I have a small table (100 rows) and I update its values every 8 seconds. This > leads to poor performance until I vacuum. pg8.0 on w2000. Any suggestions on > how often to vacuum or would I be better off doing delete and inserts for > improved sustainable performance How many rows do you update in this table every 8 seconds? You might do a vacuum once a minute. However, if you are only updating a couple of rows each pass, than every 5 minutes may be plenty. Note that an update is effectively an insert and delete, so changing updates to inserts and deletes isn't going to help performance.
--As of Wednesday, October 26, 2005 8:57 AM -0700, operationsengineer1@yahoo.com is alleged to have said: > is there a way to automate the vacuum process on > windows (think cron on linux)? --As for the rest, it is mine. I'm sure there is, but I haven't had to touch a Windows computer in a couple of years... Daniel T. Staal --------------------------------------------------------------- This email copyright the author. Unless otherwise noted, you are expressly allowed to retransmit, quote, or otherwise use the contents for non-commercial purposes. This copyright will expire 5 years after the author's death, or in 30 years, whichever is longer, unless such a period is in excess of local copyright law. ---------------------------------------------------------------
One option for automating your vacuum process 'a la cron' is to write a
batch file and then run it via the task scheduler at the desired interval.
You can find the task scheduler under: Start -> All Programs -> Accessories
-> System Tools -> Scheduled Tasks
HTH,
Kevin
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Daniel Staal
Sent: Wednesday, October 26, 2005 9:00 PM
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] vacuuming and manu updates
--As of Wednesday, October 26, 2005 8:57 AM -0700,
operationsengineer1@yahoo.com is alleged to have said:
> is there a way to automate the vacuum process on
> windows (think cron on linux)?
--As for the rest, it is mine.
I'm sure there is, but I haven't had to touch a Windows computer in a
couple of years...
Daniel T. Staal
---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
On Thu, 27 Oct 2005 10:59 am, Daniel Staal wrote: > --As of Wednesday, October 26, 2005 8:57 AM -0700, > > operationsengineer1@yahoo.com is alleged to have said: > > is there a way to automate the vacuum process on > > windows (think cron on linux)? > > --As for the rest, it is mine. > > I'm sure there is, but I haven't had to touch a Windows computer in a > couple of years... > > Daniel T. Staal yes, it is called a scheduled task. it works fine. I just changed my app to execute the vacuum via a sql transaction every hour and a half rather than as a scheduled task. Kim