On Thu, 31 Aug 2000 ghaverla@freenet.edmonton.ab.ca wrote:
> On Thu, 31 Aug 2000, Sokel, John wrote:
>
> > It will typically make
> > 10000-20000 SQL updates/inserts per hour. This is fine at first, but as the
> > day progresses, the updates start falling behind, and the CPU usage by the
> > postmaster process starts heading toward 70-80%. When I do a vacuum, the
> > updates speed up dramatically, and the cpu usage goes back down to about
> > 10%.
> >
> > The questions are:
> > Is there a general rule on how often to run a Vacuum?
>
> Probably, but you should be able to calculate this. It takes
> X amount of time to do a vacuum (which is probably a function
> of how much work is needed by the vacuum), and you have the
> dbase response time as a function of how many inserts since
> the last vacuum. You do inserts until you (at least) gain
> enough time back by doing a vacuum.
>
> > Is there an Auto Vacuum setting to have the postmaster to this
> > automatically?
>
> Cron.
>
> > Is there a better approach to keeping this situation from happening (like
> > bundling many updates into a single transaction)?
>
> No idea.
Bundling many updates into a single transaction will help. I'm not an
expert but I guess that's because every update statement is a transaction
of its own, will all locking overhead. So you will notice a performance
improvement.
But you'll have to VACUUM you db on a regular base anyway. The vacuum
issue is unrelated to how many updates you pack into a transition,
i think...
>
> Matter Realisations http://www.materialisations.com/
> Gordon Haverland, B.Sc. M.Eng. President
> 101 9504 182 St. NW Edmonton, AB, CA T5T 3A7
> 780/481-8019 ghaverla @ freenet.edmonton.ab.ca
>
>
>
>
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it